Before installing Kopano Core 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 = 32M max_connections = kopano (server.cfg) threads(default 8) + thread_limit(default 40) + 9 #innodb_file_per_table = OFF #innodb_data_file_path = ibdata1:100G;ibdata2:100G:autoextend #innodb_autoextent_increment = 1000 #query_cache_size = 32MB #innodb_flush_method = O_DSYNC or 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 Kopano Core 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, but three is recommended. The preferred size for the log_file_size is 25% of the innodb_buffer_pool_size, with 100MB minimum and 1GB maximum
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 Kopano Core environment, follow these steps:
- Make a full backup of your database
- Stop the Kopano Core-server process
- Wait a few minutes, too make sure the MySQL transaction logs are flushed
- Stop the MySQL server
- Make sure MySQL made a clean shutdown, check the mysql logs
- Change the innodb settings in the /etc/my.cnf
- Move the old ib_logfiles from the mysql datadir to /tmp
- Start mysql to activate the new settings
- If you changed the innodb_log_file_size, check the new size on the filesystem
- Check the mysql logs for errors
- Start the Kopano Core-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.