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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
