I'm upgrading to a faster server with whm/cpanel and the mysql databases will be on a SSD. I've read a bit about not wanting any query cache and stuff like that, but as a novice, I've learned that tinkering with my.cnf can be disastrous.
Here's the current my.cnf for my "hard drive" based webserver.
[mysqld] query_cache_limit=32M query_cache_size=128M query_cache_type=1 query_cache_min_res_unit=4096 open_files_limit=16000 table_open_cache=25000 table_definition_cache=2000 key_buffer_size=128M innodb_buffer_pool_size=2000M innodb_buffer_pool_instances=2 join_buffer_size=4M sort_buffer_size=4M read_rnd_buffer_size=4M tmp_table_size=3048M max_heap_table_size=3048M thread_cache_size=256 max_connections=250 table_cache=60000
What would you change in it on if mysql is on a SSD?
Here's a bit of info from tuner.sh all:
Avg. qps = 447 WORKER THREADS Current thread_cache_size = 256 Current threads_cached = 96 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 250 Current threads_connected = 20 Historic max_used_connections = 116 The number of used connections is 46% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 794 M Current InnoDB data space = 1.10 G Current InnoDB buffer pool free = 14 % Current innodb_buffer_pool_size = 1.95 G Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 3.62 G Configured Max Per-thread Buffers : 3.02 G Configured Max Global Buffers : 2.21 G Configured Max Memory Limit : 5.23 G Physical Memory : 15.56 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 222 M Current key_buffer_size = 128 M Key cache miss rate is 1 : 1115 Key buffer free ratio = 72 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 128 M Current query_cache_used = 28 M Current query_cache_limit = 32 M Current Query cache Memory fill ratio = 22.42 % Current query_cache_min_res_unit = 4 K Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 4 M Current read_rnd_buffer_size = 4 M Sort buffer seems to be fine JOINS Current join_buffer_size = 4.00 M You have had 166 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 120260 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 60000 tables Current table_definition_cache = 2000 tables You have a total of 733 tables You have 851 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 2.97 G Current tmp_table_size = 2.97 G Of 262494 temp tables, 33% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 1005 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 101097 Your table locking seems to be fine
The OLD web server is an 8 core single Xeon @ 16GB ram. The NEW web server is a 24 core dual Xeon @ 64GB ram. The SSD on the NEW server will contain the OS and the Database only, and all the sites are WordPress based resulting in about 250,000 pageviews total a day.
Thanks so much for any help or suggestions!