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
alaric AT caerllewys DOT net alaric AT metrocast DOT net phil AT
co.ordinate DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
