On 01/23/2012 10:28 AM, Uwe Schuerkamp wrote:
> 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.
If max_heap_table_size is 16M, then in-memory temporary tables are
limited to 16M too. Maximum in-memory temporary table size is the
smaller of tmp_table-size and max_heap_table_size. You only ever have a
single DB connection; why are you allowing 151 connections?
Cut max_connections to 10, increase tmp_table_size and
max_heap_table_size to 64M or even 128M, increase table_cache to 64,
disable the query cache because you're going to have few if any
frequently-repeated queries, update to MySQL 5.5, and seriously,
seriously consider converting to InnoDB. It is a MUCH higher
performance storage engine than MyISAM. Remember that MyISAM was
designed to yield *acceptable* performance in shared installations on
machines with less than 32MB of RAM.
--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.org
Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
It's not the years, it's the mileage.
------------------------------------------------------------------------------
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
