Hi,
2.#this has to be set to 50-80% of your physical memory (depends what else you run in this machine), but not higher than 2G for 32bit platform.
innodb_buffer_pool_size=2G
3.# this has to be set to about 25% of innodb_buffer_pool_size variable
4.# 0 here disables flush on every commit, instead of that mysql flushes logs every second. So if your box goes down you might loose 1 second of data which is not important for zabbix but might be important for other application. Consider this before disabling flush on commit. This improves write performance alot.
innodb_flush_log_at_trx_commit=0
5.# Following variables might improve or might not improve your performance. If your box has several CPUs and fast disk system set this to the number of CPUs you have. If you however have slow disk this might create thread tharshing and work towards performance degradation. If not sure set small value like 2 to have 2 threads running instead of 1.
innodb_thread_concurrency=2
thread_concurrency = 2
6.# this should be more than number of zabbix_server daemons, if you have queue backup and your box is not very busy most likely you need to increase number of zabbix_server daemons, dont forget to increase this number as well as because zabbix server completely crashes if any daemon is not able to connect to mysql.
max_connections = 200
7.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
8.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.
9.thread_cache_size=4
10.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.
11.query_cache_size=128M
12.Consultant advised that this was a good value and not raising this any higher
tmp_table_size=256M
max_heap_table_size=256M
13.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
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
