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.bolleng...@baculasystems.com >> <mailto:eric.bolleng...@baculasystems.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 >> <step...@seismo.berkeley.edu >> <mailto:step...@seismo.berkeley.edu> >> <mailto:step...@seismo.berkeley.edu >> <mailto:step...@seismo.berkeley.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 >> step...@seismo.berkeley.edu <mailto:step...@seismo.berkeley.edu> >> <mailto:step...@seismo.berkeley.edu >> <mailto:step...@seismo.berkeley.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@lists.sourceforge.net >> <mailto:Bacula-users@lists.sourceforge.net> >> <mailto:Bacula-users@lists.sourceforge.net >> <mailto:Bacula-users@lists.sourceforge.net>> >> https://lists.sourceforge.net/lists/listinfo/bacula-users >> >> >> -- Stephen Thompson Berkeley Seismological Laboratory step...@seismo.berkeley.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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
