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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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!).
Method 2
I tried this tool and it gave me good results.
https://github.com/major/MySQLTuner-perl
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0