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)
signature.asc
Description: PGP signature
_______________________________________________ infrastructure mailing list -- infrastructure@lists.fedoraproject.org To unsubscribe send an email to infrastructure-le...@lists.fedoraproject.org
