Most of these tips is useful for many application, but I’ll keep focus on Zabbix.
Database is the main bottleneck from Zabbix. Try to use a Dedicated Server for MySQL and make sure that server has great resources (CPU, memory and fast disks).
This is the specs for an environment with 3000 values per second:
Dell PowerEdge R610
CPU: Intel Xeon L5520 2.27GHz (16 cores)
Memory: 24GB RAM
Disks: 6x SAS 10k with RAID10 by hardware
By default, InnoDB creates all tables inside an unique datafile. With this option the new tables will have your own datafile. So after the change, You’ll need recreate the tables.
It opens some possibilities like put your tables in different filesystems and makes backup with more consistency.
Peter Zaitsev – http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim.
Tristan – cPanel Staff – http://forums.cpanel.net/f43/innodb_file_per_table-converting-per-table-data-innodb-167942.html
Issue with shared InnoDB /var/lib/mysql/ibdata1 storage
InnoDB tables currently store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.
Some discussions about this:
Personally I lost all my data because the ibdata file crashed (all data inside one file).
Some benchmarks show us about Percona performance advantages:
Housekeeper reduces the MySQL performance (see History Tables – Housekeeper). So a simple alternative is use the Partitioning native resource from MySQL.
In this blog there is another article about it: Partitioning Tables
Using memory instead of local disks will allow a much faster creation of temporary tables on MySQL.
First, create the mountpoint:
Add this line in your /etc/fstab:
tmpfs /tmp/mysqltmp tmpfs rw,uid=mysql,gid=mysql,size=1G,nr_inodes=10k,mode=0700 0 0
Make sure to adjust the size parameter. For reference, use 08~10% from physical memory.
Finally, you need to define this path in /etc/my.cnf and restart MySQL:
tmpdir = /tmp/mysqltmp
This is one of most important parameters in /etc/my.cnf. It defines how much memory InnoDB can use.
I recommend something like 70~80% from physical memory:
It’s the /etc/my.cnf sample for a server with 24GB RAM:
datadir = /var/lib/mysql/data
tmpdir = /tmp/mysqltmp
connect_timeout = 60
wait_timeout = 28800
max_connections = 2048
max_allowed_packet = 64M
max_connect_errors = 1000
tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 512
log_error = /var/log/mysql/mysql-error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 20
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:128M;ibdata2:128M:autoextend:max:4096M
innodb_file_per_table = 1
innodb_status_file = 1
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 14G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_flush_log_at_trx_commit = 2
innodb_support_xa = 0
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_stats_update_need_lock = 0
# other stuff
event_scheduler = 1
query_cache_type = 0
If you need, there are some references about MySQL parameters: