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.

Improve this question
5
  • are you experiencing specific slowness based on your current settings?
    – Randy
    Commented Jun 5, 2012 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
    Commented Jun 5, 2012 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
    Commented Jun 5, 2012 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
    Commented Jun 5, 2012 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
    Commented Jun 5, 2012 at 21:56

2 Answers 2

Reset to default
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!).

Improve this answer
7
  • Thanks KCD! I did go there and will try out their recommended settings. That was very appreciated!
    – Skittles
    Commented Jun 5, 2012 at 22:13
  • No problem. Any of those results vary much from what you had?
    – KCD
    Commented Jun 5, 2012 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
    Commented Jun 5, 2012 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
    Commented Dec 11, 2013 at 12:18
  • 1
    That link now points to Percona Toolkit, it seems they discontinued the Wizard: percona.com/blog/2019/04/22/…
    – Lucas Bustamante
    Commented Apr 1, 2020 at 23:52
7

I tried this tool and it gave me good results.

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.