I had some time with an actual MySQL consultant on Friday and we went over MySQL performance tuning for my heaviest zabbix server (299 hosts, 18905 items, 7210 triggers, 5415 services). The server that I'm using is a 2x dual core with 6 15k SAS drives and a RAID array with 512MB of cache. Some of the recommendations:
1. use a tmpfs tmpdir
In /etc/my.cnf configure:
tmpdir=/mytmp
I'm using RHEL5 and in /etc/fstab i put:
tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=27,gid=27 0 0
You'll have to mkdir /mytmp and the numeric uid and gid values for your mysql user+group need to go on that line. Then you should be able to mount /mytmp and use tmpfs for mysql's temp directory. I don't know about the size and nr_inodes options there, I just saw those in linux tmpfs docs on the web and they seemed reasonable to me.
2. Buffer cache/pool settings.
In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. I had a long-running system and i sized it so that on a 16GB box that i should only have about 1GB of buffer cache available. If you use /usr/bin/free on a redhat system value in the "+/- buffer cache" row under the "free" column shows you how much buffer cache you have. We also setup innodb to use O_DIRECT so that the data cached in the innodb buffer pool would not be duplicated in the filesystem buffer cache. So, in /etc/my.cnf:
innodb_buffer_pool_size=12000M
innodb_flush_method=O_DIRECT
3. Size the log files.
I haven't applied this one yet because I changed this configuration, restarted mysql and mysql started complaining like I had corrupted the whole database and freaked me out. I got errors like:
"Incorrect information in file: './zabbix/users.frm'"
The correct way to resize this is documented here:
http://dev.mysql.com/doc/refman/5.0/...-removing.html
In /etc/my.cnf the value I'm going to try is:
innodb_log_file_size=64M
The value that I had was the default (8MB?) and that means that MySQL is constantly flushing from the logfiles to the table spaces. It is better to increase this size on write-mostly databases to keep zabbix streaming to the logfiles and not flushing into the tablespaces constantly. However, the penalty is slower shutdown and startup times. I have another zabbix server where this parameter was set to 512M and bouncing the mysql server leads to lots of downtown on the server.
4. other parameters
innodb_file_per_table
Use file_per_table to keep tablespaces more compact and use "optimize table" periodically. And when you set this value in my.cnf you don't get an actual file_per_table until you run an optimize on all the tables. This'll take a long time on the large zabbix history* and trends* tables.
log_slow_queries=/var/log/mysql.slow.log
Turn on slow query logging.
thread_cache_size=4
I forgot to ask the consultant about this setting but it seemed to have a large effect when I set it initially (EDIT: reading up online it seems to affect the hit rate of Threads_created per Connection in 'show global status' -- with it set to 4 i had 3228483 Connections and 5840 Threads_created, which is a 99.2% hit rate -- higher number of Threads_created is worse).
query_cache_limit=1M
Consultant advised keeping this to 1MB or less. I had tuned it higher.
query_cache_size=128M
Consultant advised that this was a good value and not raising this any higher
tmp_table_size=256M
max_heap_table_size=256M
I tuned these based on some other tuning docs, and the consultant concurred, but they shouldn't go any higher, and they should be set to the same value.
table_cache=256
This was also based on other tuning docs, but based on the number of tables in a zabbix database the consultant agreed, with 73 tables in zabbix plus temp tables and mysql tables and stuff that this was appropriate.
max_connections = 400
I jacked this up because i had a measured max conns of 70 and wanted headroom and configuring connections is reasonably cheap in mysql
innodb_flush_log_at_trx_commit=2
I had tried this before and not seen a huge benefit, but the consultant advised that since i didn't care about strict ACID that this would help a lot for high volume writes.
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
The mysql consultant recommended keeping these values no higher than this, because they are per-thread buffers, and that this was based on performance analysis done by engineers at mysql. I had run into conflicting recommendations on the net to increase the size of the join_buffer_size in particular.
Results: its only been about 40 mins so far, but the server has settled down with a load average bouncing between about 5.0 to 8.0 so far when before it was bouncing between 5.0 and 15.0. Idle CPU has jumped from only 20-40% to 40-50% (CPU was actually my limiting factor). I'll try to remember to post some zabbix graphs later after it settles down and fills up the innodb buffer pool and i get some idea of stable-state performance....
1. use a tmpfs tmpdir
In /etc/my.cnf configure:
tmpdir=/mytmp
I'm using RHEL5 and in /etc/fstab i put:
tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=27,gid=27 0 0
You'll have to mkdir /mytmp and the numeric uid and gid values for your mysql user+group need to go on that line. Then you should be able to mount /mytmp and use tmpfs for mysql's temp directory. I don't know about the size and nr_inodes options there, I just saw those in linux tmpfs docs on the web and they seemed reasonable to me.
2. Buffer cache/pool settings.
In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. I had a long-running system and i sized it so that on a 16GB box that i should only have about 1GB of buffer cache available. If you use /usr/bin/free on a redhat system value in the "+/- buffer cache" row under the "free" column shows you how much buffer cache you have. We also setup innodb to use O_DIRECT so that the data cached in the innodb buffer pool would not be duplicated in the filesystem buffer cache. So, in /etc/my.cnf:
innodb_buffer_pool_size=12000M
innodb_flush_method=O_DIRECT
3. Size the log files.
I haven't applied this one yet because I changed this configuration, restarted mysql and mysql started complaining like I had corrupted the whole database and freaked me out. I got errors like:
"Incorrect information in file: './zabbix/users.frm'"
The correct way to resize this is documented here:
http://dev.mysql.com/doc/refman/5.0/...-removing.html
In /etc/my.cnf the value I'm going to try is:
innodb_log_file_size=64M
The value that I had was the default (8MB?) and that means that MySQL is constantly flushing from the logfiles to the table spaces. It is better to increase this size on write-mostly databases to keep zabbix streaming to the logfiles and not flushing into the tablespaces constantly. However, the penalty is slower shutdown and startup times. I have another zabbix server where this parameter was set to 512M and bouncing the mysql server leads to lots of downtown on the server.
4. other parameters
innodb_file_per_table
Use file_per_table to keep tablespaces more compact and use "optimize table" periodically. And when you set this value in my.cnf you don't get an actual file_per_table until you run an optimize on all the tables. This'll take a long time on the large zabbix history* and trends* tables.
log_slow_queries=/var/log/mysql.slow.log
Turn on slow query logging.
thread_cache_size=4
I forgot to ask the consultant about this setting but it seemed to have a large effect when I set it initially (EDIT: reading up online it seems to affect the hit rate of Threads_created per Connection in 'show global status' -- with it set to 4 i had 3228483 Connections and 5840 Threads_created, which is a 99.2% hit rate -- higher number of Threads_created is worse).
query_cache_limit=1M
Consultant advised keeping this to 1MB or less. I had tuned it higher.
query_cache_size=128M
Consultant advised that this was a good value and not raising this any higher
tmp_table_size=256M
max_heap_table_size=256M
I tuned these based on some other tuning docs, and the consultant concurred, but they shouldn't go any higher, and they should be set to the same value.
table_cache=256
This was also based on other tuning docs, but based on the number of tables in a zabbix database the consultant agreed, with 73 tables in zabbix plus temp tables and mysql tables and stuff that this was appropriate.
max_connections = 400
I jacked this up because i had a measured max conns of 70 and wanted headroom and configuring connections is reasonably cheap in mysql
innodb_flush_log_at_trx_commit=2
I had tried this before and not seen a huge benefit, but the consultant advised that since i didn't care about strict ACID that this would help a lot for high volume writes.
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
The mysql consultant recommended keeping these values no higher than this, because they are per-thread buffers, and that this was based on performance analysis done by engineers at mysql. I had run into conflicting recommendations on the net to increase the size of the join_buffer_size in particular.
Results: its only been about 40 mins so far, but the server has settled down with a load average bouncing between about 5.0 to 8.0 so far when before it was bouncing between 5.0 and 15.0. Idle CPU has jumped from only 20-40% to 40-50% (CPU was actually my limiting factor). I'll try to remember to post some zabbix graphs later after it settles down and fills up the innodb buffer pool and i get some idea of stable-state performance....
Comment