Hello Eric, the second patch changes nothing in the behaviour:
The mysql query lasts exaclty as long as before, and there is no error. regards, Philipp Am Montag 22 Februar 2010 10:26:48 schrieb Eric Bollengier: > Hi Philipp, > > Le Lundi 22 Février 2010 09:34:02, Philipp Storz a écrit : > > Hello Eric, > > > > how can we go on to solve the problem with mysql and base jobs? > > Good question, as it's quite hard to know where is the performance > problem (i'm not able to read and understand the explain output), it could > take some time if we add indexes everywhere without info... > > > Like it behaves now, it is unusable. Unfortunately, I do not have much > > skills in database tuning, but there has to be a big problem for mysql > > with the mentinoned INSERT: > > Did you start by using the mysql-bigmem configuration file ? You can also > check the mysql manual on the memory section, and depending on your > configuration (innodb/myisam), you can set variables to handle your > situation (for example, if you have 1 million files, you need few > megabytes to do join and sort operations on memory rather than on disk > with only 8kb of ram) > > > INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) SELECT > > B.JobId AS BaseJobId, 2 AS JobId, B.FileId, B.FileIndex FROM basefile2 AS > > A, new_basefile2 AS B WHERE A.Path = B.Path AND A.Name = B.Name ORDER BY > > B.FileId > > Maybe MySQL can't handle very well this join if the new_basefile or > basefile2 tables don't have proper indexes. > > Can you do some tests with this two patches? (don't apply them at the same > time to start, only one should be required) > > Thanks for your help! > > > the problem can be easily reproduced. > > > > regards, > > > > Philipp > > > > Am Sonntag 21 Februar 2010 22:41:30 schrieb Eric Bollengier: > > > Hi, > > > > > > This index was giving this result on postgres and sqlite > > > > > > http://sourceforge.net/apps/wordpress/bacula/2009/09/28/performance-iss > > >ue -w ith-a-useless-index-on-postgresql/ > > > > > > It should do the same with MySQL... > > > > > > Bye > > > > > > Le Dimanche 21 Février 2010 10:00:35, Philipp Storz a écrit : > > > > Update: > > > > > > > > I tried the suggestion posted by Dan Langille > > > > > > > > (CREATE INDEX FilenameId_2 ON File (FilenameId, PathId);) > > > > ,but unfortunately this did not help in my case. > > > > > > > > regards, > > > > Philipp > > > > > > ----------------------------------------------------------------------- > > >-- -- --- Download Intel® Parallel Studio Eval > > > Try the new software tools for yourself. Speed compiling, find bugs > > > proactively, and fine-tune applications for parallel performance. > > > See why Intel Parallel Studio got high marks during beta. > > > http://p.sf.net/sfu/intel-sw-dev > > > _______________________________________________ > > > Bacula-devel mailing list > > > Bacula-devel@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/bacula-devel > -- Philipp Storz philipp.st...@dass-it.de dass IT GmbH Phone: +49.221.3565666-92 http://www.dass-IT.de/ Fax : +49.221.3565666-10 Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRB52500 GF: Stephan Dühr, Maik Außendorf, Jörg Steffens, Philipp Storz ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
