MySQL tuning

From Zarafa wiki

(Difference between revisions)
Jump to: navigation, search
Line 8: Line 8:
  max_allowed_packet = 16M
  max_allowed_packet = 16M
  max_connections = [threads parameter in server.cfg] * 2 + 1
  max_connections = [threads parameter in server.cfg] * 2 + 1
-
  innodb_file_per_table
+
  #innodb_file_per_table
 +
#innodb_data_file_path = ibdata1:100G;ibdata2:100G:autoextend
 +
#innodb_autoextent_increment = 1000
 +
#query_cache_size = 32MB
 +
#innodb_flush_method = O_DIRECT
 +
 
The ''innodb_buffer_pool'' is the memory buffer that InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated Zarafa server, you may set this to up to 50% of the machine physical memory size. However, do not set it too large because competition for the physical memory might cause paging in the operating system, or even ''out of memory'' problems.
The ''innodb_buffer_pool'' is the memory buffer that InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated Zarafa server, you may set this to up to 50% of the machine physical memory size. However, do not set it too large because competition for the physical memory might cause paging in the operating system, or even ''out of memory'' problems.
Line 17: Line 22:
The size of the ''innodb_log_buffer_size'' that InnoDB uses to write to the log files on disk. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. If you have big transactions, making the log buffer larger will save disk I/O. This value should be 32Mb.
The size of the ''innodb_log_buffer_size'' that InnoDB uses to write to the log files on disk. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. If you have big transactions, making the log buffer larger will save disk I/O. This value should be 32Mb.
-
The ''innodb_file_per_table'' will create two files per table (frm and ibd): If you don't add this option, all your database will be in a big ibdata1. Worst, you cannot reclaim space when using one file for your database.
+
The ''innodb_file_per_table'' will create two files per table (frm and ibd): If you don't add this option, all your database will be in a big ibdata1. Worst, you cannot reclaim space when using one file for your database. On the other hand having this option set will increase the amount of needed file handles also mechanisms to preallocate disk space like innodb_data_file_path and innodb_autoextent_increment will not work this way.
Attention: These values should not be set too high, as the system could run out of memory. The amount of RAM necessary for the connections has to be considered in the calculations. See the [http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html#id3524079 MySQL manual] for detailed explanation.
Attention: These values should not be set too high, as the system could run out of memory. The amount of RAM necessary for the connections has to be considered in the calculations. See the [http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html#id3524079 MySQL manual] for detailed explanation.

Revision as of 08:41, 18 November 2013

Before installing Zarafa on a server, it's really advisable to optimize the MySQL configuration.

To tune the MySQL please change/add the following setting to the MySQL configuration file, usually /etc/my.cnf or /etc/mysql/my.cnf, below the [mysqld] section:

innodb_buffer_pool_size = 
innodb_log_file_size = 
innodb_log_buffer_size = 32M
max_allowed_packet = 16M
max_connections = [threads parameter in server.cfg] * 2 + 1
#innodb_file_per_table
#innodb_data_file_path = ibdata1:100G;ibdata2:100G:autoextend
#innodb_autoextent_increment = 1000
#query_cache_size = 32MB
#innodb_flush_method = O_DIRECT


The innodb_buffer_pool is the memory buffer that InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated Zarafa server, you may set this to up to 50% of the machine physical memory size. However, do not set it too large because competition for the physical memory might cause paging in the operating system, or even out of memory problems.

The innodb_log_file_size is the size of the transaction log. By default there are two logfiles. The preferred value size for the log_file_size is 25% of the innodb_buffer_pool_size.

The size of the innodb_log_buffer_size that InnoDB uses to write to the log files on disk. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. If you have big transactions, making the log buffer larger will save disk I/O. This value should be 32Mb.

The innodb_file_per_table will create two files per table (frm and ibd): If you don't add this option, all your database will be in a big ibdata1. Worst, you cannot reclaim space when using one file for your database. On the other hand having this option set will increase the amount of needed file handles also mechanisms to preallocate disk space like innodb_data_file_path and innodb_autoextent_increment will not work this way.

Attention: These values should not be set too high, as the system could run out of memory. The amount of RAM necessary for the connections has to be considered in the calculations. See the MySQL manual for detailed explanation.

Tuning existing systems

To tune an existing Zarafa environment, follow these steps:

  1. Make a full backup of your database
  2. Stop the zarafa-server process
  3. Wait a few minutes, too make sure the MySQL transaction logs are flushed
  4. Stop the MySQL server
  5. Make sure MySQL made a clean shutdown, check the mysql logs
  6. Change the innodb settings in the /etc/my.cnf
  7. Move the old ib_logfiles from the mysql datadir to /tmp
  8. Start mysql to activate the new settings
  9. If you changed the innodb_log_file_size, check the new size on the filesystem
  10. Check the mysql logs for errors
  11. Start the zarafa-server process


Then the server will flush the transaction logs when it is shut down and the ib_log* files can be safely moved, without risk of losing data.

Note on step 7: /tmp is most likely cleaned on reboot. Make sure MySQL is correctly running with the new ib_logfiles before you restart your server.

Personal tools