MySQL seems to be running too slow; LONG
I got this output from mysqltuner:
Any advice is appreciated: The main issues is that this db server
handles a Mediawiki database and when I upload files it takes way to
long to do its job likewise when serving up the data to produce the
webpages.
Output:
> -------- General Statistics --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.61-0+squeeze1
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics -------------------------------------------
> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in MyISAM tables: 40M (Tables: 100)
> [--] Data in InnoDB tables: 764M (Tables: 71)
> [--] Data in MEMORY tables: 0B (Tables: 1)
> [!!] Total fragmented tables: 73
>
> -------- Performance Metrics -------------------------------------------------
> [--] Up for: 5h 55m 13s (211K q [9.909 qps], 1K conn, TX: 3B, RX: 43M)
> [--] Reads / Writes: 96% / 4%
> [--] Total buffers: 343.0M global + 2.7M per thread (200 max threads)
> [OK] Maximum possible memory usage: 880.5M (22% of installed RAM)
> [OK] Slow queries: 0% (8/211K)
> [OK] Highest usage of available connections: 2% (4/200)
> [OK] Key buffer size / total MyISAM indexes: 20.0M/16.2M
> [OK] Key buffer hit rate: 100.0% (7M cached / 1K reads)
> [OK] Query cache efficiency: 50.1% (92K cached / 185K selects)
> [!!] Query cache prunes per day: 36659
> [!!] Sorts requiring temporary tables: 11% (11 temp sorts / 98 sorts)
> [!!] Temporary tables created on disk: 38% (2K on disk / 6K total)
> [OK] Thread cache hit rate: 99% (4 created / 1K connections)
> [!!] Table cache hit rate: 3% (128 open / 3K opened)
> [OK] Open file limit used: 1% (16/1K)
> [OK] Table locks acquired immediately: 99% (120K immediate / 120K locks)
> [!!] InnoDB data size / buffer pool: 764.0M/285.0M
>
> -------- Recommendations -----------------------------------------------------
> General recommendations:
> Run OPTIMIZE TABLE to defragment tables for better performance
> MySQL started within last 24 hours - recommendations may be inaccurate
> Enable the slow query log to troubleshoot bad queries
> When making adjustments, make tmp_table_size/max_heap_table_size equal
> Reduce your SELECT DISTINCT queries without LIMIT clauses
> Increase table_cache gradually to avoid file descriptor limits
> Variables to adjust:
> query_cache_size (> 16M)
> sort_buffer_size (> 1M)
> read_rnd_buffer_size (> 256K)
> tmp_table_size (> 20M)
> max_heap_table_size (> 20M)
> table_cache (> 128)
> innodb_buffer_pool_size (>= 764M)
One curious thing I have noted is that the actual database has 3 kinds
of table structures: innodb, myisam, & memory;
also collation is mostly binary, but 3 tabls are latin1_swedish.
hardware is:
> Operating system
> Debian Linux 6.0
> Webmin version
> 1.580
> Time on system
> Sat May 5 13:18:01 2012
> Kernel and CPU
> Linux 2.6.32-5-amd64 on x86_64
> Processor information
> AMD Processor model unknown, 6
> cores
> System uptime
> 6 hours, 02 minutes
> Running processes
> 265
> CPU load averages
> 0.53 (1 min) 0.60 (5 mins) 0.52 (15
> mins)
> CPU usage
> 26% user, 2% kernel, 0% IO, 73%
> idle
> Real memory
> 3.87 GB total, 2.13 GB used
>
>
> Virtual memory
> 7.57 GB total, 16.61 MB used
>
>
> Local disk space
> 909.45 GB total, 92.27 GB used
>
>
> Package updates
> All installed packages are up to
> date
>
I know I need to add more memory & will do so:
any other tips are appreciated.
Thanks frosty
Reply to: