mysql> SELECT ClientId, SUM(JobFiles) AS NB FROM Job WHERE PurgedFiles=0
GROUP BY ClientId ORDER BY NB DESC;
+----------+------------+
| ClientId | NB |
+----------+------------+
| 10 | 2239841562 |
| 68 | 636728984 |
| 157 | 291057196 |
| 198 | 138969710 |
| 226 | 64823112 |
| 175 | 32000653 |
| 193 | 31050266 |
| 82 | 30262301 |
| 128 | 29301627 |
| 151 | 22475008 |
| 19 | 21240274 |
| 29 | 20033877 |
| 83 | 19478910 |
| 11 | 19457193 |
| 71 | 19082044 |
| 182 | 17809346 |
| 2 | 16574343 |
| 160 | 16026000 |
| 59 | 15619494 |
| 208 | 15302525 |
| 137 | 14738802 |
| 211 | 14222314 |
| 87 | 13960994 |
| 52 | 13780046 |
| 161 | 13779654 |
| 185 | 13302479 |
| 37 | 13225958 |
| 67 | 13109596 |
| 20 | 12671550 |
| 51 | 12466348 |
| 181 | 12251756 |
| 202 | 12042804 |
| 171 | 11780484 |
| 1 | 11757542 |
| 32 | 11638635 |
| 110 | 11638577 |
| 149 | 11204765 |
| 212 | 10868461 |
| 130 | 10852372 |
| 97 | 10595084 |
| 86 | 10499474 |
| 190 | 10494196 |
| 150 | 10333134 |
| 78 | 10217282 |
| 199 | 10180544 |
| 91 | 10130751 |
| 79 | 9970247 |
| 58 | 9810940 |
| 147 | 9674228 |
| 104 | 9596818 |
| 116 | 9452029 |
| 89 | 9332180 |
| 165 | 9212989 |
| 188 | 9188834 |
| 105 | 8983345 |
| 77 | 8949849 |
| 99 | 8831861 |
| 203 | 8754005 |
| 153 | 8529691 |
| 142 | 8289018 |
| 9 | 8094114 |
| 23 | 7835491 |
| 74 | 7752206 |
| 117 | 7734697 |
| 169 | 7713724 |
| 98 | 7676700 |
| 114 | 7596313 |
| 103 | 7594553 |
| 3 | 7569220 |
| 22 | 7534628 |
| 163 | 7394539 |
| 80 | 7327367 |
| 191 | 7290254 |
| 133 | 7278652 |
| 187 | 7238320 |
| 73 | 7236988 |
| 24 | 7135758 |
| 13 | 7128109 |
| 55 | 6963791 |
| 81 | 6911802 |
| 173 | 6876827 |
| 109 | 6755680 |
| 92 | 6665835 |
| 177 | 6600357 |
| 136 | 6533864 |
| 210 | 6520067 |
| 101 | 6512996 |
| 108 | 6451729 |
| 18 | 6318452 |
| 152 | 6279522 |
| 8 | 6222276 |
| 107 | 6211843 |
| 178 | 6173749 |
| 144 | 6145270 |
| 93 | 6142275 |
| 139 | 6111513 |
| 15 | 6060498 |
| 197 | 6051544 |
| 158 | 6025219 |
| 179 | 5961047 |
| 170 | 5936383 |
| 167 | 5927953 |
| 66 | 5840134 |
| 47 | 5747998 |
| 33 | 5723391 |
| 64 | 5639616 |
| 168 | 5566232 |
| 7 | 5564096 |
| 102 | 5557595 |
| 126 | 5529101 |
| 155 | 5518324 |
| 84 | 5473028 |
| 70 | 5406265 |
| 16 | 5327062 |
| 75 | 5320136 |
| 196 | 5315381 |
| 176 | 5230530 |
| 27 | 5197330 |
| 54 | 5179433 |
| 180 | 5161361 |
| 12 | 5112984 |
| 76 | 5084587 |
| 17 | 5044930 |
| 4 | 5037036 |
| 207 | 4940761 |
| 45 | 4811348 |
| 132 | 4737109 |
| 156 | 4595621 |
| 28 | 4532633 |
| 218 | 4480522 |
| 40 | 4413881 |
| 140 | 4237828 |
| 217 | 4090761 |
| 209 | 3837518 |
| 127 | 3519580 |
| 205 | 3334936 |
| 122 | 2912512 |
| 219 | 2852438 |
| 118 | 2697189 |
| 223 | 2458155 |
| 120 | 2374121 |
| 85 | 2131980 |
| 184 | 1997627 |
| 39 | 1921967 |
| 215 | 1434650 |
| 94 | 1346155 |
| 119 | 1344102 |
| 6 | 1339676 |
| 216 | 1314965 |
| 131 | 1284235 |
| 69 | 1045568 |
| 143 | 1041213 |
| 129 | 1000119 |
| 220 | 899042 |
| 65 | 825059 |
| 21 | 784129 |
| 72 | 780538 |
| 90 | 780387 |
| 192 | 735048 |
| 221 | 700934 |
| 222 | 700639 |
| 106 | 673953 |
| 121 | 666393 |
| 225 | 609158 |
| 30 | 584147 |
| 224 | 549788 |
| 95 | 492221 |
| 138 | 485395 |
| 206 | 462543 |
| 115 | 447957 |
| 96 | 437252 |
| 213 | 395689 |
| 26 | 394206 |
| 166 | 366626 |
| 154 | 339426 |
| 164 | 325200 |
| 36 | 313950 |
| 88 | 292422 |
| 100 | 292099 |
| 214 | 277613 |
| 159 | 274184 |
| 162 | 273890 |
| 189 | 252673 |
| 123 | 234201 |
| 35 | 219973 |
| 41 | 219826 |
| 53 | 219767 |
| 63 | 219749 |
| 135 | 219746 |
| 141 | 219344 |
| 124 | 219157 |
| 57 | 219070 |
| 134 | 215349 |
| 227 | 154642 |
| 112 | 134792 |
| 125 | 114623 |
| 31 | 99493 |
| 49 | 98341 |
| 34 | 92193 |
| 50 | 90190 |
| 46 | 88746 |
| 111 | 87960 |
| 148 | 70591 |
| 62 | 68151 |
| 145 | 65377 |
| 42 | 65290 |
| 25 | 63220 |
| 60 | 62653 |
| 38 | 62183 |
| 43 | 46063 |
| 228 | 45989 |
| 44 | 45433 |
| 113 | 44317 |
| 186 | 1 |
| 5 | 0 |
| 56 | 0 |
| 172 | 0 |
| 195 | 0 |
| 174 | 0 |
| 48 | 0 |
| 61 | 0 |
+----------+------------+
221 rows in set (0.21 sec)
On 10/09/2015 10:01 AM, Eric Bollengier wrote:
> Very good point Ana,
>
> So, you might want to add to the query "AND PurgedFiles = 0"
>
> Thanks,
>
> Eric
>
> Le 09. 10. 15 14:24, Ana Emília M. Arruda a écrit :
>> Hello Eric!
>>
>> Thank you. I thought that you were looking for the number of filename
>> per Client that had not been pruned yet :).
>>
>> Best regards,
>> Ana
>>
>> On Fri, Oct 9, 2015 at 3:17 AM, Eric Bollengier
>> <eric.bollengier AT baculasystems DOT com
>> <mailto:eric.bollengier AT baculasystems DOT com>> wrote:
>>
>> Thanks Ana!
>>
>> Something such as
>>
>> SELECT ClientId, SUM(JobFiles) AS NB FROM Job GROUP BY ClientId
>> ORDER BY NB DESC;
>>
>> should also do the trick a bit more faster ;-)
>>
>> Best Regards,
>> Eric
>>
>> Le 07. 10. 15 15:23, Ana Emília M. Arruda a écrit :
>>
>> Hello Stephen,
>>
>> On Mon, Oct 5, 2015 at 2:17 PM, Stephen Thompson
>> <stephen AT seismo.berkeley DOT edu
>> <mailto:stephen AT seismo.berkeley DOT edu>
>> <mailto:stephen AT seismo.berkeley DOT edu
>> <mailto:stephen AT seismo.berkeley DOT edu>>> wrote:
>>
>>
>> Regarding:
>> > Would be nice also if you can give the number of
>> Filename per Client
>> (from the job table).
>>
>> Do you have a sample SQL to retrieve this stat?
>>
>>
>> select Client.Name, count(distinct Filename.FilenameId) from
>> Client,
>> Filename, File, Job where Filename.FilenameId=File.FilenameId and
>> File.JobId=Job.JobId and Job.ClientId=Client.ClientId group by
>> Client.ClientId;
>>
>> The above query should work.
>>
>> Best regards,
>> Ana
>>
>>
>>
>> thanks,
>> Stephen
>>
>>
>>
>>
>>
>>
>>
>> On 10/03/2015 12:02 AM, Eric Bollengier wrote:
>> > Hello Stephen,
>> >
>> > On 10/03/2015 12:00 AM, Stephen Thompson wrote:
>> >>
>> >>
>> >> All,
>> >>
>> >> I believe I'm having mysql database issues since
>> upgrading to
>> 7.2 (from
>> >> 7.0.2). I run mysql innodb with 900Gb database that's
>> largely
>> the File
>> >> table.
>> >
>> > For large catalog, we usually advise to use PostgreSQL
>> where we have
>> > multi-terabytes databases in production.
>> >
>> >> Since upgrading, I lose a few jobs a night due to
>> database locking
>> >> timeouts, which I have set to 3600. I also log slow
>> queries.
>> >
>> > Can you get some information about these locks? On which
>> table?
>> Can you
>> > give some statistics on your catalog like the size and
>> the number of
>> > records of the File, Filename and Path table? Would be
>> nice also
>> if you
>> > can give the number of Filename per Client (from the job
>> table).
>> >
>> > You might have many orphan Filenames, and MySQL is not
>> always
>> very good
>> > to join large tables (it uses nested loops, and cannot
>> use the
>> index on
>> > the Text column in all queries).
>> >
>> >> It appears that typically during a months I have about
>> 90-100
>> queries
>> >> that take longer than 15 minutes to run. Already this
>> month
>> (upgraded
>> >> earlier this week), I have 32 queries that take longer
>> than 15
>> minutes.
>> >> At this rate (after 2 days) that will up my regular
>> average
>> of 90-100
>> >> to 480!
>> >>
>> >> Something is wrong and the coincidence is pretty strong
>> that it's
>> >> related to the upgrade.
>> >
>> > Maybe, but I'm not sure, we did not change a lot of
>> thing in this
>> area,
>> > we did mostly refactoring.
>> >
>> > Best Regards,
>> > Eric
>> >
>>
>> --
>> Stephen Thompson Berkeley Seismological
>> Laboratory
>> stephen AT seismo.berkeley DOT edu <mailto:stephen AT
>> seismo.berkeley DOT edu>
>> <mailto:stephen AT seismo.berkeley DOT edu
>> <mailto:stephen AT seismo.berkeley DOT edu>>
>> 215 McCone Hall # 4760
>> Office: 510.664.9177 <tel:510.664.9177> <tel:510.664.9177
>> <tel:510.664.9177>> University of
>> California, Berkeley
>> Remote: 510.214.6506 <tel:510.214.6506> <tel:510.214.6506
>> <tel:510.214.6506>> (Tue,Wed) Berkeley, CA
>> 94720-4760
>>
>>
>>
>> ------------------------------------------------------------------------------
>>
>> _______________________________________________
>> Bacula-users mailing list
>> Bacula-users AT lists.sourceforge DOT net
>> <mailto:Bacula-users AT lists.sourceforge DOT net>
>> <mailto:Bacula-users AT lists.sourceforge DOT net
>> <mailto:Bacula-users AT lists.sourceforge DOT net>>
>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>
>>
>>
--
Stephen Thompson Berkeley Seismological Laboratory
stephen AT seismo.berkeley DOT edu 215 McCone Hall # 4760
Office: 510.664.9177 University of California, Berkeley
Remote: 510.214.6506 (Tue,Wed) Berkeley, CA 94720-4760
------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
