24

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.

Skittles
  • 2,866
  • 9
  • 31
  • 37
  • are you experiencing specific slowness based on your current settings? – Randy Jun 05 '12 at 21:29
  • 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. – JMC Jun 05 '12 at 21:43
  • 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. – JMC Jun 05 '12 at 21:47
  • 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' ;-) – JMC Jun 05 '12 at 21:50
  • 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. – Skittles Jun 05 '12 at 21:56

2 Answers2

25

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

KCD
  • 9,873
  • 5
  • 66
  • 75
  • Thanks KCD! I did go there and will try out their recommended settings. That was very appreciated! – Skittles Jun 05 '12 at 22:13
  • No problem. Any of those results vary much from what you had? – KCD Jun 05 '12 at 22:19
  • 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. – Skittles Jun 05 '12 at 22:23
  • Would be interested to know how this went. I tried tuning our server with the Percona recommended settings, and saw no measurable improvement. – qris Dec 11 '13 at 12:18
  • 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 – KCD Dec 11 '13 at 22:01
  • 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. – TurmDrummer Feb 17 '14 at 13:24
  • 1
    That link now points to Percona Toolkit, it seems they discontinued the Wizard: https://www.percona.com/blog/2019/04/22/end-of-life-query-analyzer-and-mysql-configuration-generator/ – Lucas Bustamante Apr 01 '20 at 23:52
7

I tried this tool and it gave me good results.

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

Sooraj
  • 412
  • 4
  • 11