On 08/16/2013 08:40 PM, Jason A. Kates wrote: > 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.
That is nice to hear. I guess that Oracle may be pushing a little bit of their high end technology back to MySQL, which would be a really good thing. Spooling attributes is critical for large databases ... Thanks for using Bacula :-) Kern > (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 <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 >> ------------------------------------------------------------------------------ >> 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
