MySQL my.cnf performance tuning recommendations
I am kind of hoping that someone might be able to offer some assistance with optimizing a my.cnf file for an extremely high volume mysql database server.
Our architecture is as follows:
Memory : 96GB
CPUs : 12
OS & Mysql : 64-bit
Disk Space : 1.2 TB
DB Engine : MyISAM
Our web application is used by roughly 300 client simultaneously. We need our my.cnf tuned to give the best possible performance for this infrastructure.
I am fully aware that indexes and optimized queries are a major factor in this, but we would like to start with a system that is configured properly and then follow that up with systematically re-engineering our queries accordingly.
Here is our current my.cnf file content:
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
log-bin=mysql-bin
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1
log-slow-queries = /var/log/mysqld_slow_queries.log
long_query_time = 10
max_connections = 500
key_buffer_size = 32768M
#max_allowed_packet = 2M
#table_open_cache = 128
#sort_buffer_size = 1024K
#net_buffer_length = 64K
#read_buffer_size = 1024K
#read_rnd_buffer_size = 1024K
#myisam_sort_buffer_size = 8M
query_cache_size = 128M
query_cache_limit = 128M
interactive_timeout = 300
wait_timeout = 300
# Added values after load testing
thread_cache_size = 8
#tmp_table_size = 256M
#max_heap_table_size = 256M
#table_cache = 512
#join_buffer_size = 512
log-error=/var/log/mysqld.log
innodb_buffer_pool_size=128M
#innodb_file_per_table
#innodb_log_file_size=250M
##innodb_buffer_pool_size=64M
#innodb_buffer_pool_size=1024M
#innodb_log_buffer_size=4M
##log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[myisamchk]
#key_buffer = 64M
#sort_buffer = 64M
#read_buffer = 16M
#write_buffer = 16M
Any suggestions? Thanks folks.
Edit by RolandoMySQLDBA
Since all you data is MyISAM, please run this query and show the output
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 3 PowerOf1024) B;
@ Rolando - Thanks...the results of that query was 4G.
Answer
Try starting with the Percona wizard and comparing their recommendations against your current settings one by one. Don't worry there aren't as many applicable settings as you might think.
https://tools.percona.com/wizard
Update circa 2020: Sorry, this tool reached it's end of life: https://www.percona.com/blog/2019/04/22/end-of-life-query-analyzer-and-mysql-configuration-generator/
Everyone points to key_buffer_size first which you have addressed. With 96GB memory I'd be wary of any tiny default value (likely to be only 96M!).