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 > > > ------------------------------------------------------------------------------ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users