Hi, i'm not using unmodified MySQL configuration. My Bacula database has about 21 GB and i'm mainly having problems with inserting into File table after the virtual full backup of our e-mail server. Job has more then 7 000 000 of files and insert takes about 5 hours. Just to explain why i'm searching how to speed up things without HW upgrades.
azur ______________________________________________________________ >Hello, > >Thanks for your question. I have asked my database expert who >says the same as I do but more in detail. I will include his response >below. > >Bottom line: we have spent a long time determining the best indexes >for Bacula, which are the ones we release in the code, so we do not >recommend making any changes. That said, we have not tested removing >the "extra" index for several years and it may be that database engines >have evolved. The problem with changing the Bacula default index setup >is that it may perform well in the beginning, but seemingly simple >changes can have big surprises in little used queries that are >seldom used, but by removing or changing >an index you can make a difference of a factor of 1000, which means that >an unusual query that runs rather quickly can become a real bottleneck. > >So change indexes at your own risk. > >Probably you are using an untunned MySQL with the default conf (my.cnf) >file rather than one of the examples for larger databases. >By proper tunning you can significantly improve the performance. >If you have a big database say bigger >than 1 or 2 GB, our experience is that you will get *much* better >performance >with PostgreSQL, providing you tune the conf parameters correctly (out >of the >box, the Postgres conf is a real bummer). > >Best regards, >Kern > >=== email from someone who knows SQL better than I do ==== > >The single index on JobId is compact, the other is very large, I guess the >scanning time is not exactly the same (i.e. the single index on JobId can >be much faster) > >Yes, Postgresql can use composed index and may not require it, but for >MySQL, I have some doubt if it can be used all the time (maybe recent >versions). It is hard to read their execution plan to be sure how >they use it. > >This user could do some tests, it's rather simple to drop >the index and run a large restore on a big database (probably not >interesting if the File table contains less than 300,000,000 records). > >If we have a bit of time one day, it might be good to test this >modification on large and "recent" databases. > >Anyway, if this user is using MySQL AND has performance problems >during insert, he probably didn't modify the default my.cnf. > >===================== end included email ============================== > > > >On 08/16/2013 01:30 PM, azurIt wrote: >> Hi, >> >> i'm having some MySQL performance difficulties so i started to search what >> can i do better. My table 'File' had these indexes created: >> CREATE INDEX file_jobid_idx on File (JobId); >> CREATE INDEX file_jpf_idx on File (JobId, PathId, FilenameId); >> >> Which looks correct according to documentation: >> http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html >> >> BUT! The first index apperas to be unneeded as it's part of the second >> index. According to MySQL documentation, 'any leftmost prefix of the index >> can be used by the optimizer to find row': >> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html >> >> I suggest to remove it. >> >> The same applies also for PostgreSQL: >> http://www.postgresql.org/docs/9.2/interactive/indexes-multicolumn.html >> >> azur >> >> ------------------------------------------------------------------------------ >> Get 100% visibility into Java/.NET code with AppDynamics Lite! >> It's a free troubleshooting tool designed for production. >> Get down to code-level detail for bottlenecks, with <2% overhead. >> Download for free and get started troubleshooting in minutes. >> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk >> _______________________________________________ >> Bacula-devel mailing list >> Bacula-devel@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/bacula-devel >> > > ------------------------------------------------------------------------------ Get 100% visibility into Java/.NET code with AppDynamics Lite! It's a free troubleshooting tool designed for production. Get down to code-level detail for bottlenecks, with <2% overhead. Download for free and get started troubleshooting in minutes. http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
