MySQL performance tips for Zabbix

Posted by Ricardo Santos in database, mysql, performance, zabbix on Oct 24th, 2011 | 7 comments

Most of these tips is useful for many application, but I’ll keep focus on Zabbix.

1. Use a Dedicated Server 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

2. Create one file per table
   

   1	innodb_file_per_table=1

   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:
   http://dom.as/2009/05/21/innodb-tablespace/
   http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table

   Personally I lost all my data because the ibdata file crashed (all data inside one file).

   Ref.: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

3. Percona vs Community Edition Percona Server is a modified version from MySQL Community Edition.Some benchmarks show us about Percona performance advantages:
   http://www.percona.com/software/percona-server/benchmarks/
4. Use partitioning tables and disable the Housekeeper 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
5. Use tmpfs filesystem for Temporary Files Using memory instead of local disks will allow a much faster creation of temporary tables on MySQL.First, create the mountpoint:
   

   1	mkdir /tmp/mysqltmp

   Add this line in your /etc/fstab:

   1	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:

   1	tmpdir = /tmp/mysqltmp

6. Set your Buffer Pool properly 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:
   

   1	innodb_buffer_pool_size=14G

   Ref.: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

It’s the /etc/my.cnf sample for a server with 24GB RAM:

If you need, there are some references about MySQL parameters:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html
http://www.mysqlperformanceblog.com/