1: Make sure you have enough ram in your mysql box (ie, several 10s of Gb)
2: Make sure you tune mysql properly. Most of the supplied config examples are for sub-1Gb memory configuration. 3: Make sure you have the _correct_ indexes built. this is in the bacula knowledgebase. 4: For systems with 10s of millions of files - Seriously consider moving to postgres. MySQL is a memory hog. On 23/01/12 15:28, Uwe Schuerkamp wrote: > Hi folks, > > we're running four bacula installations, most of them on version 5.0.x > compiled from source on CentOS 5.x / 6.x 64bit servers. We're mostly > happy with the setup, backups are fast, reliable and generally do not > cause us a lot of headaches. > > Today, a colleague asked me to restore some data from the last backup > of a client on our largest bacula install, namely (according to bweb) > > DB Size: > Total clients: 107 Total bytes stored: 34.41 TB > Total files: 47495362 Database size: 31.64 GB > > MySQL isn't exactly huge, and restoring the data didn't look like too > much of a big deal at first: > > +-------+-------+----------+----------------+---------------------+--------------+ > | 9,582 | F | 527,265 | 55,999,595,573 | 2012-01-20 21:05:03 | > OFFLINE14_02 | > | 9,652 | I | 1,150 | 1,534,499,185 | 2012-01-21 18:34:56 | > OFFLINE15_01 | > +-------+-------+----------+----------------+---------------------+--------------+ > > So we're talking a mere 500,000 files (he only needed a single dir out > of the bunch). > > 4,5 hours later, Bacula is still sitting at the "Building Directory > Tree" message, without so much as a single "." or "+" hopefully > showing up in the terminal, indicating some kind of progress. > > I've run mysqltuner on the db a couple of times as this isn't the > first time we've had problems during a restore, and it looks ok (to my > untrained, non-dba eyes anyway): > > ###################################################################### > -------- General Statistics > -------------------------------------------------- > [--] Skipped version check for MySQLTuner script > [OK] Currently running supported MySQL version 5.1.52-log > [OK] Operating on 64-bit architecture > > -------- Storage Engine Statistics > ------------------------------------------- > [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster > [--] Data in MyISAM tables: 31G (Tables: 33) > [!!] Total fragmented tables: 2 > > -------- Performance Metrics > ------------------------------------------------- > [--] Up for: 35s (57 q [1.629 qps], 12 conn, TX: 44K, RX: 3K) > [--] Reads / Writes: 100% / 0% > [--] Total buffers: 12.0G global + 83.2M per thread (151 max threads) > [!!] Maximum possible memory usage: 24.3G (137% of installed RAM) > [OK] Slow queries: 0% (0/57) > [OK] Highest usage of available connections: 0% (1/151) > [OK] Key buffer size / total MyISAM indexes: 11.9G/15.3G > [OK] Key buffer hit rate: 100.0% (6K cached / 2 reads) > [!!] Query cache efficiency: 0.0% (0 cached / 23 selects) > [OK] Query cache prunes per day: 0 > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts) > [!!] Temporary tables created on disk: 34% (8 on disk / 23 total) > [OK] Thread cache hit rate: 91% (1 created / 12 connections) > [OK] Table cache hit rate: 85% (41 open / 48 opened) > [OK] Open file limit used: 1% (83/4K) > [OK] Table locks acquired immediately: 100% (38 immediate / 38 locks) > [!!] Connections aborted: 8% > > -------- Recommendations > ----------------------------------------------------- > General recommendations: > Run OPTIMIZE TABLE to defragment tables for better performance > MySQL started within last 24 hours - recommendations may be > inaccurate > Reduce your overall MySQL memory footprint for system stability > When making adjustments, make tmp_table_size/max_heap_table_size > equal > Reduce your SELECT DISTINCT queries without LIMIT clauses > Your applications are not closing MySQL connections properly > Variables to adjust: > *** MySQL's maximum memory usage is dangerously high *** > *** Add RAM before increasing MySQL buffer variables *** > query_cache_limit (> 16M, or use smaller result sets) > tmp_table_size (> 61M) > max_heap_table_size (> 16M) > > ###################################################################### > > For the restore run mentioned above, I'm seeing a 40MB mysql tmp table > in /tmp updated every once in a while, and there's lots of write > activity to the partition that holds /tmp. > > I'm now running a "repair table File" after cancelling the restore > job, but I guess there's something seriously wrong with the above > setup. The other bacula servers generally run on smaller machines, but > come up with a dir tree after five to ten minutes for a comparable job > which is acceptable, but 5 hours seems way off the mark. > > the bacula db was created using bacula's own mysql init script, so I > assume all the indices where created (and more importantly, no extra > ones that might slow bacula down) correctly. Insert performance it > great during backups, we usually achieve around 30-50MB/sec sustained > for 3 to 4 jobs running in parallel. > > Mem usage (as opposed to mysql tuner's warning) is ok during the > restore run, no swapping, 5GB of 18G total still used for buffer > cache. > > > Thanks in advance for any help / hints or thoughts, > > Uwe > > PS: Please let me know if I should provide more info on the setup what > would help in analyzing this problem. > ------------------------------------------------------------------------------ Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
