Oh, I must have missed the part about 7 million files. In that case, you will need a well tuned DB, and personally, I would use Postgres. 5 hours seems to me much too long for the insert. I am sure that DB tuning will make a big difference -- maybe as much as a factor of 10.
Kern On 08/16/2013 03:01 PM, azurIt wrote: > 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 > ------------------------------------------------------------------------------ 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
