MySQL my cnf performance tuning recommendations

Question

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  amp  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 lt 0 0 IF PowerOf1024 gt 3 0 PowerOf1024    0 4999   SUBSTR   KMG  IF PowerOf1024 lt 0 0  IF PowerOf1024 gt 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

User · Accepted 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!).

User · Answer

I tried this tool and it gave me good results   https   github com major MySQLTuner-perl

[mysql] MySQL my.cnf performance tuning recommendations

Examples related to mysql

Examples related to optimization

Examples related to performance

Examples related to my.cnf