MySQL tuning

From Zarafa wiki

(Difference between revisions)
Jump to: navigation, search
m (fixed step number for /tmp)
Line 37: Line 37:
#Start the zarafa-server process
#Start the zarafa-server process
For step 3, you could also login to mysql as a user with super privilege, and run the following command:
mysql> set global innodb_fast_shutdown=0;
Then the server will flush the transaction logs when it is shut down
Then the server will flush the transaction logs when it is shut down

Revision as of 12:55, 20 February 2012

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 = 500

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.

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