Wiki
Clone wikipnfs_space_usage_by_creation_time / Home
[root@t3dcachedb pnfs_space_usage_by_creation_time]# psql -U chimera -d chimera -f ./pnfs_icrtime_igid.sql
psql:./pnfs_icrtime_igid.sql:38: LOG: duration: 15396.805 ms statement: with phedex as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as phedex_GB from t_inodes where iuid=2980 group by to_char(icrtime, 'yyyy-mm') ), ethz_bphys as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as ethz_bphys_GB from t_inodes where igid=530 group by to_char(icrtime, 'yyyy-mm') ), ethz_ecal as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as ethz_ecal_GB from t_inodes where igid=529 group by to_char(icrtime, 'yyyy-mm') ), ethz_ewk as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as ethz_ewk_GB from t_inodes where igid=531 group by to_char(icrtime, 'yyyy-mm') ), ethz_higgs as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as ethz_higgs_GB from t_inodes where igid=532 group by to_char(icrtime, 'yyyy-mm') ), ethz_susy as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as ethz_susy_GB from t_inodes where igid=533 group by to_char(icrtime, 'yyyy-mm') ), psi_bphys as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as psi_bphys_GB from t_inodes where igid=534 group by to_char(icrtime, 'yyyy-mm') ), psi_pixel as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as psi_pixel_GB from t_inodes where igid=535 group by to_char(icrtime, 'yyyy-mm') ), uniz_bphys as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as uniz_bphys_GB from t_inodes where igid=536 group by to_char(icrtime, 'yyyy-mm') ), uniz_higgs as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as uniz_higgs_GB from t_inodes where igid=537 group by to_char(icrtime, 'yyyy-mm') ), uniz_pixel as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as uniz_pixel_GB from t_inodes where igid=538 group by to_char(icrtime, 'yyyy-mm') ), withoutgroup as ( select to_char(icrtime, 'yyyy-mm') as period, trunc(sum(isize)/1000/1000/1000) as withoutgroup_GB from t_inodes where iuid<>2980 and igid=500 group by to_char(icrtime, 'yyyy-mm') ) select * from phedex natural full outer join ethz_bphys natural full outer join ethz_ecal natural full outer join ethz_ewk natural full outer join ethz_higgs natural full outer join ethz_susy natural full outer join psi_bphys natural full outer join psi_pixel natural full outer join uniz_bphys natural full outer join uniz_higgs natural full outer join uniz_pixel natural full outer join withoutgroup order by period asc ;
period | phedex_gb | ethz_bphys_gb | ethz_ecal_gb | ethz_ewk_gb | ethz_higgs_gb | ethz_susy_gb | psi_bphys_gb | psi_pixel_gb | uniz_bphys_gb | uniz_higgs_gb | uniz_pixel_gb | withoutgroup_gb ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 1st row is a bug addressed to the dCache team 1970-01 | 0 | | 0 | | 968 | 2 | 12226 | | | 806 | | ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2008-09 | | | | | | | | | | | | | 2008-10 | | | | | | | | | | | | 19| 2008-11 | | | | | 4 | | | | | | | 3| 2008-12 | 360 | | | | | | | | | | | 1| ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2009-01 | | | | | | | | | | | | 242| 2009-02 | | | | | | | | | | | | | 2009-03 | | | | | | | | | | | | | 2009-04 | | | | | 1232 | | | | | | | | 2009-05 | | | | | | | | | | | | | 2009-06 | | | | | | | | | | | | | 2009-07 | | | | | | | 10 | | | | | | 2009-08 | | | | | | | 3 | | | | | | 2009-09 | | 2 | | | | | | | | | | | 2009-10 | | 947 | | | | | | | | | | | 2009-11 | | 338 | | | | | | | | | | | 2009-12 | | 1254 | | | | | | | | | | 32| ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2010-01 | | 264 | | | | | | | | | | | 2010-02 | | | | | | | | | | | | | 2010-03 | | 2 | | | | 1 | | | | | | | 2010-04 | 1 | | | | | 6 | | | | | | | 2010-05 | 101 | | | | | 44 | 19 | | | | | | 2010-06 | | | | | | | 59 | | | | | | 2010-07 | | | | | | 204 | 3 | | | | | 13| 2010-08 | | 240 | | | | | 29 | | 57 | | | 956| 2010-09 | | | | | | | 5 | | | | | | 2010-10 | | 11 | | | | 44 | 67 | | 4136 | | | 21| 2010-11 | | | | | | | 38 | | 6293 | | | 527| 2010-12 | | | | | | | 17 | | | | | 204| ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2011-01 | | | | | | | 11 | | 327 | | | 92| 2011-02 | 27 | | | | | 576 | 239 | | 233 | | | 377| 2011-03 | | | | | | 109 | 233 | | 347 | | | 362| 2011-04 | | | | | | 1 | 155 | | 194 | | | 131| 2011-05 | | | | | 70 | 62 | | | 133 | | | 81| 2011-06 | 62 | 78 | | | | 14 | 16 | | 204 | | | 73| 2011-07 | | 319 | | | 186 | 7 | 103 | | 1345 | | | | 2011-08 | | | | | | | | | 874 | | | | 2011-09 | 843 | | | | 7 | 6 | 302 | | 614 | 52 | | | 2011-10 | | | | | 19 | 31 | 2757 | | 414 | 73 | | | 2011-11 | 169 | | | | | 58 | 1843 | | 1104 | 36 | | 209| 2011-12 | | | | | | 81 | 168 | | 709 | 603 | | 1190| ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2012-01 | | | | | | 773 | 45 | | 5 | 39 | | 634| 2012-02 | | 79 | | | | 303 | 316 | | 9 | | | | 2012-03 | | | | | | 149 | 3541 | | 44 | | | 211| 2012-04 | | | | | | 63 | 3476 | | 68 | | | | 2012-05 | | | | | | 439 | 1957 | | 575 | | | | 2012-06 | 1448 | | 123 | | 28 | 1467 | 2025 | | 276 | | | 2815| 2012-07 | 3611 | | 339 | | | 108 | 1442 | | | | | 118| 2012-08 | 7108 | | 293 | | | 735 | 8 | | 243 | | | 21| 2012-09 | 26182 | | 13 | 2 | | 1036 | 5122 | | | | | 36| 2012-10 | 7763 | | 63 | 250 | 47 | 2643 | 460 | | | | | 461| 2012-11 | 24034 | | 379 | 163 | 101 | 256 | 5 | | | | | 191| 2012-12 | 18132 | | 68 | | 1 | 1532 | 704 | | | | | 214| ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2013-01 | 571 | | 6595 | 5 | 26 | 1874 | | | 67 | | | 181| 2013-02 | 12523 | | 104 | | 2758 | 28480 | 385 | | | | | 14072| 2013-03 | 2133 | | 4302 | | 734 | 21008 | | | 351 | | | 1872| 2013-04 | 1639 | | | | 35 | 13129 | 43 | | | | | 598| 2013-05 | 7229 | | | 139 | 47 | 9429 | 6 | | | | 1110 | 8168| 2013-06 | 2690 | | 15 | 1021 | | 9687 | | | | 3 | 3150 | 1268| 2013-07 | 370 | | 644 | 537 | 1 | 4756 | 2 | | | | | 341| 2013-08 | | | 963 | 98 | 18 | 1795 | 258 | | | | 6051 | | 2013-09 | 1864 | | 1117 | 2020 | 6 | 551 | | 32 | | | 4958 | 90| 2013-10 | 329 | | 407 | 110 | | 3076 | | | | 2 | 498 | 71| 2013-11 | 7991 | | 3514 | 97 | 201 | 14376 | | 3 | | | 2461 | | 2013-12 | 650 | | 3316 | 2 | 2 | 1256 | | | | | 2511 | | ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2014-01 | | | 102 | | 30 | 2240 | | | | 19840 | | | 2014-02 | 3645 | | 593 | 62 | 15 | 203 | | | | 247 | 1325 | | 2014-03 | | | 71 | 13 | 897 | 272 | | | | 9427 | 2328 | | 2014-04 | | | 22 | | 330 | 750 | | 1 | | 28535 | 2323 | | 2014-05 | 19380 | | 202 | | 24 | 8685 | 1726 | 2 | | 24055 | 3398 | | 2014-06 | | | 190 | 62 | 62 | 510 | | 1 | | 6665 | 2368 | | 2014-07 | 231 | | 4454 | | 325 | 122 | | | | 36475 | 50 | | 2014-08 | 5778 | | 15 | | 193 | 178 | | | | 5860 | 146 | | 2014-09 | 4 | | 2 | 3 | 74 | 253 | | | | 284 | | | 2014-10 | | | 3353 | | 52 | 672 | 3920 | | | 1766 | | | 2014-11 | | | 1158 | 4 | 27 | 1306 | 565 | | | 643 | | | 2014-12 | 18062 | | 365 | 293 | 107 | 1226 | 338 | | | 54 | | | ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------| 2015-01 | | | 1920 | | 137 | 602 | | 4 | | 783 | 1752 | 118| 2015-02 | 2831 | | 1486 | | 1 | 1334 | | 4 | | 4737 | 7 | | 2015-03 | 43 | | 1535 | | 85 | 2675 | 26869 | | | 12382 | | | 2015-04 | 812 | | | | 1 | 1687 | 15314 | | | 6448 | | | 2015-05 | | | | | | 3 | | | | 781 | | | ---------+-----------+---------------+--------------+-------------+---------------+--------------+--------------+--------------+---------------+---------------+---------------+----------------+
Updated