Hello,
I have 2 virtual servers (VMware ESXi) and need help optimizing MySQL.
I quote below the results of mysqltuner.pl:
Server 1:
Server 2:
Both Servers run cPanel RELEASE with CloudLinux.
I have 2 virtual servers (VMware ESXi) and need help optimizing MySQL.
I quote below the results of mysqltuner.pl:
Server 1:
Code:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.70-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 32860)
[--] Data in InnoDB tables: 127M (Tables: 1378)
[--] Data in MEMORY tables: 0B (Tables: 41)
[!!] Total fragmented tables: 175
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 10h 52m 7s (18M q [89.440 qps], 569K conn, TX: 116B, RX: 3B)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 2.3G global + 4.5M per thread (2000 max threads)
[OK] Maximum possible memory usage: 11.0G (81% of installed RAM)
[OK] Slow queries: 2% (449K/18M)
[OK] Highest usage of available connections: 3% (70/2000)
[OK] Key buffer size / total MyISAM indexes: 1.0G/909.8M
[OK] Key buffer hit rate: 99.9% (7B cached / 5M reads)
[OK] Query cache efficiency: 76.1% (9M cached / 12M selects)
[!!] Query cache prunes per day: 243852
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 742K sorts)
[!!] Joins performed without indexes: 88072
[!!] Temporary tables created on disk: 34% (474K on disk / 1M total)
[OK] Thread cache hit rate: 99% (70 created / 569K connections)
[!!] Table cache hit rate: 0% (4K open / 45M opened)
[OK] Open file limit used: 18% (9K/50K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
[OK] InnoDB data size / buffer pool: 127.2M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 4000)Server 2:
Code:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.70-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 50700)
[--] Data in InnoDB tables: 2G (Tables: 21022)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 21023
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 43m 30s (10M q [44.017 qps], 338K conn, TX: 29B, RX: 1B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (10% of installed RAM)
[OK] Slow queries: 0% (882/10M)
[OK] Highest usage of available connections: 7% (35/500)
[!!] Key buffer size / total MyISAM indexes: 8.0M/1.1G
[!!] Key buffer hit rate: 91.6% (36M cached / 3M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 353K sorts)
[!!] Joins performed without indexes: 4105
[!!] Temporary tables created on disk: 35% (585K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 121M opened)
[OK] Open file limit used: 0% (91/50K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[!!] InnoDB data size / buffer pool: 2.6G/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
key_buffer_size (> 1.1G)
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 2G)Both Servers run cPanel RELEASE with CloudLinux.
Last edited by a moderator:
