>What modification did you do at your my.conf ?
key_buffer_size = 16M max_allowed_packet = 1M thread_stack = 256K thread_cache_size = 8 innodb_buffer_pool_size = 512M table_open_cache = 10000 open-files-limit = 50000 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 >The performance issues are only during inserting or even during selecting? Yes but not so huge, selecting takes about 10-20 minutes. >Have you got concurrent jobs? Yes, 3 concurrent jobs but only one is inserting at a time (because of DB locking). >What's your hardware configuration? I'm particularly interested in hard >drives bus, rpm and raid settings. - SATA controller: Intel Corporation 82801JI (ICH10 Family) SATA AHCI Controller - 2x WD VelociRaptor 150 GB, 10 000 rpm - linux SW RAID 1 - 8 GB RAM > > > > >2013/8/16 Kern Sibbald <k...@sibbald.com> > >> 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 >> > > > >-- > > Please consider the environment before printing this email > ------------------------------------------------------------------------------ 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
