Way back in May some of us were in Raleigh for a Hackathon: https://fedoraproject.org/wiki/CI_and_Infrastructure_Hackathon_2017 There, we talked about the MBS and wondered what kind of new storage requirements it would impose on koji. We all worried about it, but we didn't have numbers to figure out how worried we should be. --- Here, below, find a query that Mike McLean shared for the koji database. It very, very roughly shows rpm storage usage per-quarter in koji, with a couple caveats. - It only counts rpm sizes. It doesn't count logs, or signed copied, or isos, or anything like that. - It is also biased towards newer quarters. More recent quarters haven't had time yet for garbage collection to kick in, so while they are larger in magnitude, it needs to be taken in context, with salt, etc. Here's a run:: koji=# select date_trunc('quarter', completion_time) as bin, round(sum(size1)/1073741824, 3) as rpm_gb, round(sum(size2)/1073741824,3) as archive_gb, round(sum(size1+size2)/1073741824, 3) as total_gb from (select build.id, build.completion_time, coalesce((select sum(size) from rpminfo where build_id=build.id),0) as size1, coalesce((select sum(size) from archiveinfo where build_id=build.id),0) as size2 from build where build.state=1 and build.volume_id=0) as data group by date_trunc('quarter', completion_time) order by bin; bin | rpm_gb | archive_gb | total_gb --------------------+----------------------------- 2007-04-01 00:00:00 | 254.416 | 0.000 | 254.416 2007-07-01 00:00:00 | 314.764 | 0.000 | 314.764 2007-10-01 00:00:00 | 317.851 | 0.000 | 317.851 2008-01-01 00:00:00 | 427.718 | 0.000 | 427.718 2008-04-01 00:00:00 | 393.187 | 0.000 | 393.187 2008-07-01 00:00:00 | 404.171 | 0.000 | 404.171 2008-10-01 00:00:00 | 392.340 | 0.000 | 392.340 2009-01-01 00:00:00 | 565.137 | 0.000 | 565.137 2009-04-01 00:00:00 | 349.749 | 0.000 | 349.749 2009-07-01 00:00:00 | 464.736 | 0.000 | 464.736 2009-10-01 00:00:00 | 324.401 | 0.000 | 324.401 2010-01-01 00:00:00 | 331.923 | 0.000 | 331.923 2010-04-01 00:00:00 | 304.823 | 0.000 | 304.823 2010-07-01 00:00:00 | 293.123 | 0.000 | 293.123 2010-10-01 00:00:00 | 262.374 | 0.000 | 262.374 2011-01-01 00:00:00 | 307.806 | 0.000 | 307.806 2011-04-01 00:00:00 | 243.162 | 0.000 | 243.162 2011-07-01 00:00:00 | 239.072 | 0.000 | 239.072 2011-10-01 00:00:00 | 287.522 | 0.000 | 287.522 2012-01-01 00:00:00 | 388.169 | 0.000 | 388.169 2012-04-01 00:00:00 | 347.497 | 0.000 | 347.497 2012-07-01 00:00:00 | 384.210 | 0.000 | 384.210 2012-10-01 00:00:00 | 369.828 | 0.000 | 369.828 2013-01-01 00:00:00 | 522.266 | 0.000 | 522.266 2013-04-01 00:00:00 | 451.709 | 0.000 | 451.709 2013-07-01 00:00:00 | 733.928 | 0.000 | 733.928 2013-10-01 00:00:00 | 551.316 | 0.000 | 551.316 2014-01-01 00:00:00 | 530.781 | 0.000 | 530.781 2014-04-01 00:00:00 | 747.820 | 0.000 | 747.820 2014-07-01 00:00:00 | 1008.080 | 0.000 | 1008.080 2014-10-01 00:00:00 | 665.198 | 0.000 | 665.198 2015-01-01 00:00:00 | 839.060 | 0.000 | 839.060 2015-04-01 00:00:00 | 931.210 | 0.000 | 931.210 2015-07-01 00:00:00 | 887.543 | 0.000 | 887.543 2015-10-01 00:00:00 | 700.465 | 0.000 | 700.465 2016-01-01 00:00:00 | 1189.887 | 152.489 | 1342.376 2016-04-01 00:00:00 | 983.871 | 43.799 | 1027.670 2016-07-01 00:00:00 | 983.195 | 99.737 | 1082.932 2016-10-01 00:00:00 | 1165.745 | 27.005 | 1192.749 2017-01-01 00:00:00 | 1681.824 | 149.185 | 1831.009 2017-04-01 00:00:00 | 1861.706 | 8.289 | 1869.995 2017-07-01 00:00:00 | 2610.738 | 132.464 | 2743.203 2017-10-01 00:00:00 | 2236.426 | 4195.877 | 6432.303 (43 rows) Interesting! Now, let's see the same query, but with the MBS builds *excluded* (the MBS user is userid 3819):: koji=# select date_trunc('quarter', completion_time) as bin, round(sum(size1)/1073741824, 3) as non_mbs_rpm_gb, round(sum(size2)/1073741824,3) as non_mbs_archive_gb, round(sum(size1+size2)/1073741824, 3) as non_mbs_total_gb from (select build.id, build.completion_time, coalesce((select sum(size) from rpminfo where build_id=build.id),0) as size1, coalesce((select sum(size) from archiveinfo where build_id=build.id),0) as size2 from build where build.state=1 and build.volume_id=0 and build.owner!=3819) as data group by date_trunc('quarter', completion_time) order by bin; bin | non_mbs_rpm_gb | non_mbs_archive_gb | non_mbs_total_gb ----------------------------------------------------+----------------- 2007-04-01 00:00:00 | 254.416 | 0.000 | 254.416 2007-07-01 00:00:00 | 314.764 | 0.000 | 314.764 2007-10-01 00:00:00 | 317.851 | 0.000 | 317.851 2008-01-01 00:00:00 | 427.718 | 0.000 | 427.718 2008-04-01 00:00:00 | 393.187 | 0.000 | 393.187 2008-07-01 00:00:00 | 404.171 | 0.000 | 404.171 2008-10-01 00:00:00 | 392.340 | 0.000 | 392.340 2009-01-01 00:00:00 | 565.137 | 0.000 | 565.137 2009-04-01 00:00:00 | 349.749 | 0.000 | 349.749 2009-07-01 00:00:00 | 464.736 | 0.000 | 464.736 2009-10-01 00:00:00 | 324.401 | 0.000 | 324.401 2010-01-01 00:00:00 | 331.923 | 0.000 | 331.923 2010-04-01 00:00:00 | 304.823 | 0.000 | 304.823 2010-07-01 00:00:00 | 293.123 | 0.000 | 293.123 2010-10-01 00:00:00 | 262.374 | 0.000 | 262.374 2011-01-01 00:00:00 | 307.806 | 0.000 | 307.806 2011-04-01 00:00:00 | 243.162 | 0.000 | 243.162 2011-07-01 00:00:00 | 239.072 | 0.000 | 239.072 2011-10-01 00:00:00 | 287.522 | 0.000 | 287.522 2012-01-01 00:00:00 | 388.169 | 0.000 | 388.169 2012-04-01 00:00:00 | 347.497 | 0.000 | 347.497 2012-07-01 00:00:00 | 384.210 | 0.000 | 384.210 2012-10-01 00:00:00 | 369.828 | 0.000 | 369.828 2013-01-01 00:00:00 | 522.266 | 0.000 | 522.266 2013-04-01 00:00:00 | 451.709 | 0.000 | 451.709 2013-07-01 00:00:00 | 733.928 | 0.000 | 733.928 2013-10-01 00:00:00 | 551.316 | 0.000 | 551.316 2014-01-01 00:00:00 | 530.781 | 0.000 | 530.781 2014-04-01 00:00:00 | 747.820 | 0.000 | 747.820 2014-07-01 00:00:00 | 1008.080 | 0.000 | 1008.080 2014-10-01 00:00:00 | 665.198 | 0.000 | 665.198 2015-01-01 00:00:00 | 839.060 | 0.000 | 839.060 2015-04-01 00:00:00 | 931.210 | 0.000 | 931.210 2015-07-01 00:00:00 | 887.543 | 0.000 | 887.543 2015-10-01 00:00:00 | 700.465 | 0.000 | 700.465 2016-01-01 00:00:00 | 1189.887 | 152.489 | 1342.376 2016-04-01 00:00:00 | 983.871 | 43.799 | 1027.670 2016-07-01 00:00:00 | 983.195 | 99.737 | 1082.932 2016-10-01 00:00:00 | 1165.745 | 27.005 | 1192.749 2017-01-01 00:00:00 | 1657.196 | 149.185 | 1806.381 2017-04-01 00:00:00 | 1565.237 | 8.289 | 1573.526 2017-07-01 00:00:00 | 2417.401 | 132.464 | 2549.865 2017-10-01 00:00:00 | 1932.821 | 4195.869 | 6128.691 (43 rows) Finally, let's see the storage usage of only MBS-managed builds:: koji=# select date_trunc('quarter', completion_time) as bin, round(sum(size1)/1073741824, 3) as mbs_rpm_gb, round(sum(size2)/1073741824,3) as mbs_archive_gb, round(sum(size1+size2)/1073741824, 3) as mbs_total_gb from (select build.id, build.completion_time, coalesce((select sum(size) from rpminfo where build_id=build.id),0) as size1, coalesce((select sum(size) from archiveinfo where build_id=build.id),0) as size2 from build where build.state=1 and build.volume_id=0 and build.owner=3819) as data group by date_trunc('quarter', completion_time) order by bin; bin | mbs_rpm_gb | mbs_archive_gb | mbs_total_gb --------------------------------------------+------------- 2017-01-01 00:00:00 | 24.627 | 0.000 | 24.627 2017-04-01 00:00:00 | 296.469 | 0.000 | 296.469 2017-07-01 00:00:00 | 193.337 | 0.000 | 193.337 2017-10-01 00:00:00 | 303.605 | 0.008 | 303.612 (4 rows)
Attachment:
signature.asc
Description: PGP signature
_______________________________________________ infrastructure mailing list -- infrastructure@xxxxxxxxxxxxxxxxxxxxxxx To unsubscribe send an email to infrastructure-leave@xxxxxxxxxxxxxxxxxxxxxxx
