Menu
▾
▴
Re: [Bacula-devel] Unneeded database index
|
From: Jason A. K. <ja...@ka...> - 2013-08-16 19:00:16
|
I love Bacula and we run it using mysql. We have noticed some real jumps in speed moving to a more current release of mysql. (You may have some dependency issues as bacula-enterprise doesn't like the mysql with the caps in the RPM name....) but it's worth it in terms of the speed. It looks like it took 65 minutes to write the spooled attributes to the DB last night. rpm -q -a | grep -i mysql MySQL-client-5.6.10-1.el6.x86_64 MySQL-server-5.6.10-1.el6.x86_64 MySQL-devel-5.6.10-1.el6.x86_64 perl-DBD-MySQL-4.013-3.el6.x86_64 MySQL-shared-5.6.10-1.el6.x86_64 MySQL-shared-compat-5.6.10-1.el6.x86_64 bacula-enterprise-mysql-6.4.2-1.el6.x86_64 Our largest backup has 14.5Million files | 263,550 | xxxxx.xxxxxxx.xxx | 2013-08-12 20:31:35 | B | F | 14,508,799 | 452,155,407,322 | T | | 263,736 | xxxxx.xxxxxxx.xxx | 2013-08-13 20:31:45 | B | F | 14,512,018 | 452,474,497,322 | T | | 263,939 | xxxxx.xxxxxxx.xxx | 2013-08-14 20:48:17 | B | F | 14,514,009 | 452,553,211,379 | T | | 264,129 | xxxxx.xxxxxxx.xxx | 2013-08-15 20:48:34 | B | F | 14,521,656 | 452,670,800,484 | T | We spool it to disk then write it to tape. That way the tape drive doesn't end up waiting for the tinny little files. We also Spool Attributes. mysql> show table status; +-----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+ | BaseFiles | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2013-08-15 11:04:08 | NULL | NULL | latin1_swedish_ci | NULL | | | | CDImages | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2013-08-15 11:04:08 | NULL | NULL | latin1_swedish_ci | NULL | | | | Client | InnoDB | 10 | Compact | 233 | 281 | 65536 | 0 | 16384 | 0 | 691 | 2013-08-15 11:04:08 | NULL | NULL | latin1_swedish_ci | NULL | | | | Counters | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2013-08-15 11:04:08 | NULL | NULL | latin1_swedish_ci | NULL | | | | Device | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2013-08-15 11:04:08 | NULL | NULL | latin1_swedish_ci | NULL | | | | File | InnoDB | 10 | Compact | 1090267976 | 141 | 154803372032 | 0 | 54603546624 | 290455552 | 30355832161 | 2013-08-01 11:31:25 | NULL | NULL | latin1_swedish_ci | NULL | max_rows=2115098112 avg_row_length=111 | | | FileSet | InnoDB | 10 | Compact | 24 | 682 | 16384 | 0 | 0 | 0 | 139 | 2013-08-15 13:52:27 | NULL | NULL | latin1_swedish_ci | NULL | | | | Filename | InnoDB | 10 | Compact | 31013378 | 64 | 1984954368 | 0 | 2410676224 | 7340032 | 104180734 | 2013-08-15 13:58:45 | NULL | NULL | latin1_swedish_ci | NULL | | | | Job | InnoDB | 10 | Compact | 18243 | 259 | 4734976 | 0 | 1589248 | 4194304 | 264318 | 2013-08-15 13:58:46 | NULL | NULL | latin1_swedish_ci | NULL | | | | JobHisto | InnoDB | 10 | Compact | 9843 | 267 | 2637824 | 0 | 589824 | 4194304 | NULL | 2013-08-15 13:58:46 | NULL | NULL | latin1_swedish_ci | NULL | | | | JobMedia | InnoDB | 10 | Compact | 752616 | 64 | 48840704 | 0 | 26804224 | 7340032 | 67092902 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | Location | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | LocationLog | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | Log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | Media | InnoDB | 10 | Compact | 289 | 396 | 114688 | 0 | 32768 | 0 | 468 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | MediaType | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 3 | 2013-08-15 13:58:54 | NULL | NULL | latin1_swedish_ci | NULL | | | | Path | InnoDB | 10 | Compact | 6748404 | 113 | 765460480 | 0 | 1242562560 | 5242880 | 21277898 | 2013-08-15 14:00:28 | NULL | NULL | latin1_swedish_ci | NULL | | | | PathHierarchy | InnoDB | 10 | Compact | 19347 | 82 | 1589248 | 0 | 376832 | 4194304 | NULL | 2013-03-21 09:51:47 | NULL | NULL | latin1_swedish_ci | NULL | | | | PathVisibility | InnoDB | 10 | Compact | 34944 | 45 | 1589248 | 0 | 507904 | 4194304 | NULL | 2013-03-21 09:51:47 | NULL | NULL | latin1_swedish_ci | NULL | | | | Pool | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 16384 | 0 | 6 | 2013-08-15 14:00:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | RestoreObject | InnoDB | 10 | Compact | 3591 | 1181 | 4243456 | 0 | 49152 | 4194304 | 6516 | 2013-03-21 09:51:47 | NULL | NULL | latin1_swedish_ci | NULL | | | | Status | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2013-08-15 14:00:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | Storage | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 0 | 14 | 2013-08-15 14:00:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | UnsavedFiles | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2013-08-15 14:00:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | Version | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2013-08-15 14:00:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | b29990010040029218091025083 | InnoDB | 10 | Compact | 116070 | 49 | 5783552 | 0 | 2637824 | 1048576 | NULL | 2013-08-15 12:35:58 | NULL | NULL | latin1_swedish_ci | NULL | | | | b29990010040029218091025718 | InnoDB | 10 | Compact | 116070 | 49 | 5783552 | 0 | 2637824 | 1048576 | NULL | 2013-08-15 08:57:10 | NULL | NULL | latin1_swedish_ci | NULL | | | +-----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+ On Fri, 2013-08-16 at 20:12 +0200, Kern Sibbald wrote: > My own database is 2G. But in testing Bacula I tested > backups of 10 Million files, which is a relatively large number > for a single backup, but really big backups range from > 20 Million to 40 Million files. My personal database was > MySQL for something like 12 years, but for the last > couple I have been using Postgres, and I do most of > my testing on Postgres DBs. > > Making Postgres perform really well is a science and an art as > for best performance it needs really fast disks, but I leave those > discussions for the experts. I just program Bacula -- I am not > even very good at even answering support questions since I > do not work daily on support. > > The Enterprise version has a lot of improvements and tuning for large > databases that we see our customers use, and these will over time > filter back to the community version. > > Regards, > Kern > > On 08/16/2013 07:28 PM, stefano scotti wrote: > > > That is a really large database :) > > > > > > What modification did you do at your my.conf ? > > The performance issues are only during inserting or even during > > selecting? > > Have you got concurrent jobs? > > What's your hardware configuration? I'm particularly interested in > > hard drives bus, rpm and raid settings. > > > > > > > > > > > > > > > > 2013/8/16 Kern Sibbald <ke...@si...> > > 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 > > >>> Bac...@li... > > >>> > > 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 > > > Bac...@li... > > > 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 > > Bac...@li... > > 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 > > Bac...@li... > > 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 Bac...@li... https://lists.sourceforge.net/lists/listinfo/bacula-devel -- ---------------------------------------------------------------------------- Jason A. Kates (ja...@ka...) Fax: 208-975-1514 Phone: 660-960-0070 ============================================================================ |