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
