MySQL my.cnf performance tuning recommendations
Solution 1
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!).
Solution 2
I tried this tool and it gave me good results.
https://github.com/major/MySQLTuner-perl
Comments
-
Skittles almost 2 years
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.