MySQL optimization for a 16GB ram server.

[johnburk]

Server is a 8-core Xeon with 16GB of ram running multiple wordpress blogs.

Highest usage of available connections based on the last 6 weeks has been 382.

Any suggestions on how I can improve and optimize my.cnf?


Mysqltuner output
Fragmentation of 60 to 100 happens within 2 to 4 hours of optimizing the database.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 2018)
[--] Data in InnoDB tables: 262M (Tables: 720)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 34)
[!!] Total fragmented tables: 91

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 17h 26m 49s (35M q [109.964 qps], 474K conn, TX: 889B, RX: 8B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 2.6G global + 14.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 8.1G (51% of installed RAM)
[OK] Slow queries: 0% (2K/35M)
[OK] Highest usage of available connections: 10% (43/400)
[OK] Key buffer size / total MyISAM indexes: 1.2G/1.2G
[OK] Key buffer hit rate: 99.9% (540M cached / 344K reads)
[OK] Query cache efficiency: 60.5% (17M cached / 29M selects)
[!!] Query cache prunes per day: 240109
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 2285
[OK] Temporary tables created on disk: 23% (369K on disk / 1M total)
[OK] Thread cache hit rate: 99% (43 created / 474K connections)
[!!] Table cache hit rate: 0% (5K open / 3M opened)
[OK] Open file limit used: 4% (6K/128K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
[OK] InnoDB data size / buffer pool: 262.7M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_cache (> 64000)

My.cnf

[mysqld]
connect_timeout=360
default-storage-engine=MyISAM
innodb_additional_mem_pool_size=40M
innodb_buffer_pool_size=512M
innodb_commit_concurrency=16
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=12M
innodb_max_dirty_pages_pct=90
innodb_thread_concurrency=16
interactive_timeout=2400
join_buffer_size=2M
key_buffer_size=1280M
local-infile=0
log-slow-queries
long_query_time=1
max_allowed_packet=32M
max_connections=400
max_heap_table_size=256M
open_files_limit=100000
query_cache_limit=32M
query_cache_min_res_unit=512
query_cache_size=512M
read_buffer_size=4M
read_rnd_buffer_size=2M
sort_buffer_size=6M
table_cache=64K
table_definition_cache=8K
table_open_cache=64000
thread_cache_size=4M
tmp_table_size=256M
wait_timeout=2400

 

[cPanelMichael]

Hello

I just wanted to note that the mysqlmymonlite script might be better suited as a tuner for MySQL version 5.5:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[thinkbot]

lower table_cache to something like 6000
since it scales bad on high numbers

table_definition_cache to 2000

Many of the values you put there are too big, it seems like it was copied from somewhere without thought

The best thing would be to add caching to wordpress

 

[johnburk]

Hello

I just wanted to note that the mysqlmymonlite script might be better suited as a tuner for MySQL version 5.5:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

I will give it a try, but I am not comfortable with adding my mysql root password in a plain text environment.

- - - Updated - - -

lower table_cache to something like 6000
since it scales bad on high numbers

table_definition_cache to 2000

Many of the values you put there are too big, it seems like it was copied from somewhere without thought

The best thing would be to add caching to wordpress

All sites already work with caching.

The values are based on advice of tools such as mysqltuner.pl and tuning-primer.sh which both seem to love giving the advice to increase values.

 

[thinkbot]

Exactly, tools always suggest to increase and increase

put there this, it's cleaned up version of your my.cnf
with enabled slow query log, after it gatherers some slow queries for few days, you can run pt-query-digest and review them

Also install Munin module from WHM, you will have there server resource monitoring nicely displayed on graphs

[mysqld]
local-infile=0

connect_timeout=360
wait_timeout=2400
interactive_timeout=2400
default-storage-engine=MyISAM

max_connections = 400
max_user_connections = 100

key_buffer_size=1400M
join_buffer_size=2M
sort_buffer_size=256K

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1		

query_cache_type = 1
query_cache_size = 150M
query_cache_limit = 1M

max_allowed_packet=32M
tmp_table_size=256M
max_heap_table_size=256M
open_files_limit=100000

table_definition_cache=2000
table_open_cache=6000

thread_cache_size=64

innodb_buffer_pool_size=512M
innodb_file_per_table=1

 

[johnburk]

Exactly, tools always suggest to increase and increase

put there this, it's cleaned up version of your my.cnf
with enabled slow query log, after it gatherers some slow queries for few days, you can run pt-query-digest and review them

Also install Munin module from WHM, you will have there server resource monitoring nicely displayed on graphs

[mysqld]
local-infile=0

connect_timeout=360
wait_timeout=2400
interactive_timeout=2400
default-storage-engine=MyISAM

max_connections = 400
max_user_connections = 100

key_buffer_size=1400M
join_buffer_size=2M
sort_buffer_size=256K

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1		

query_cache_type = 1
query_cache_size = 150M
query_cache_limit = 1M

max_allowed_packet=32M
tmp_table_size=256M
max_heap_table_size=256M
open_files_limit=100000

table_definition_cache=2000
table_open_cache=6000

thread_cache_size=64

innodb_buffer_pool_size=512M
innodb_file_per_table=1

Thank you.

Unfortunately cPanel has not updated Munin for a few years. They still use 1.4.7 which has some bugs in term of memory usage.