MySQL my.cnf performance tuning recommendations [closed]

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 & 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.

Eric

are you experiencing specific slowness based on your current settings? – Parasynapsis

That's a pretty good machine for only 300 web users, even more so if this is just a DB server.. Is this Windows or Linux? Either way, I generally recommend having separate Disks/LUNs for the OS/Swap and the Data volume (where datadir resides). Stay away from software raid, etc. There is a tool called 'mk-query-advisor' that might help you tune this setup. Unless your 'front-end' application is doing the 'proper thing' (caching static data, only querying when needed, etc.), tuning might not buy you much. – Severe

You can also add a maintenance script (after a backup of course) that calls 'OPTIMIZE TABLE tbl_name' each week to help keep things optimized. Also looking into partitioning large tables, etc. – Severe

I'd check the Query Logs too. If you have 300 simultaneous web users that cause numerous types of 'SELECT [some generic data] from [large lookup table]'; that's an optimization (data caching) for the front-end web app to do. Just sayin' ;-) – Severe

System is a linux VM. The slowness we seem to be experiencing is, in part, due to some really poorly designed queries authored by a third party. Secondly, the web server (also linux) ties to this server through php connections. We have max'd out our connections on a couple of instances today and when performing queries with joins, it tends to inhibit the clients from being able to perform normal web-based operations. We were running this mysql server on the web server, which is 32 bit. – Eric

Answer 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!).

Phonon

Thanks KCD! I did go there and will try out their recommended settings. That was very appreciated! – Eric

No problem. Any of those results vary much from what you had? – Phonon

Yes...quite a bit of changes. Just restarted the server using them. We'll have to wait to see what a full production day tomorrow yields. Full with crons and customer service reps using he system. – Eric

Would be interested to know how this went. I tried tuning our server with the Percona recommended settings, and saw no measurable improvement. – Chairmanship

TBH I found innodb tuning is more about defining limits that reflect your system rather than speed under normal load. I.e. pushing out the maximum connections/memory you can handle – Phonon

KCD Thank you very much. We allways had problems finding a fast and secure configuration for our developer machines. Percona provided us with that. Their knowledge about safety options and performance really helped us. – Potentiate

That link now points to Percona Toolkit, it seems they discontinued the Wizard: percona.com/blog/2019/04/22/… – Pfister

Answer 2

I tried this tool and it gave me good results.

https://github.com/major/MySQLTuner-perl

Anthropolatry