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.
--
NIONEX --- Ein Unternehmen der Bertelsmann AG
------------------------------------------------------------------------------
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
