How to Optimize the MySQL Performance in Zarafa?

Zarafa is Groupware application that provides email storage and collaboration on server side. Zarafa has its own mail clients WebAccess and Webapp. The Zarafa server communicates with the client side by using SOAP technology. With the help of client side plug-in, it can be integrated with Outlook email client too which makes it a popular alternative for Exchange. The connection between Outlook and Zarafa server are established in a secured way by using TLS/SSL or an HTTPS proxy.

Zarafa application stores all its data in a MySQL database which is the most widely used relational database management system. Ibdata files are MySQL database that stores the metadata of InnoDB tables, change buffer, doublewrite buffer and undo logs.  The .frm files (it’s not a part of MyISAM engine, it’s a part of server itself) stores the definitions of the database tables and related information. Once the .From file data gets corrupt then the Zarafa server data turns inaccessible.

Tuning Database Performance in Zarafa:

While you are setting up the Zarafa server, you must set the cache settings for better performance. If a request has been made for an item that is available in the cache, then simply the request can be fulfilled easily instead of making calls to other processes. If the item isn’t cached, then each time when a request is made the disk resources are consumed and calls to other processes have been made which in turn degrades the Zarafa server configuration. If you are managing the RAM efficiently for caching purpose, then, you can tune your database performance and the overall server system performance.

Zarafa cache configuration(.cfg) file:

For the Zarafa server, you must allocate around 80% of the RAM to be used for cache.  For handling more server loads the cache sizes must be set as high as possible and affordable. Each time you restart the Zarafa server the caches are purged. The entire related configuration files data is cleared and the cache will take some time to build up the data and grow again. Meanwhile the server performance will be lowered. If you reload the server instead of restarting, then the configuration files are re-read and the performance will be higher.

You can set and modify the cache settings in /etc/zarafa/server.cfg file. The values for cell cache (cache_cell_size), Object cache (cache_object_size), and indexed object cache (cache_indexedobject_size) should be set accordingly so that the swapping on server can be reduced and memory can be efficiently utilized for caching.

Cell cache: More the value of your cell cache better is the server performance. All the table view data is cached in the cell cache. If maximum of the table view cells will be cached, lesser will be the need to traverse through the folders and querying databases. You can set up to 50% of your RAM for Cell cache.
 

Object cache: Object cache stores the database objects and their respective folders in the database hierarchy. The default value for cache_object_size is 5 MB.
 

You can use the below equation for calculating Object cache:
cache_object_size = <Concurrent users> * <max items in a folder> * 24

To get the complete cache statistics and other server statistics you can use the following USR1 signal: zarafa-stats --system

Indexed object cache: Unique id's of the individual database objects is stored within the Indexed object cache
You must set the values as given below so that the RAM can be efficiently used. For an efficient caching in Zarafa, 25 % percent of the RAM should be reserved for cell cache. The Object cache requires around 16-64MB and indexed object caches requires 16-128 MB of RAM.

How to Optimize the MySQL configuration? 

You can tune your MySQL database configuration for a new installation of Zarafa server or for existing one.
Optimize the MySQL configuration before installation:

You must optimize the MySQL configuration before installation. You can set the related values in the MySQL configuration file. The file is stored with a .cnf extension at the location: /etc/my.cnf or /etc/mysql/my.cnf

innodb_buffer_pool stores the cache data and database table indexes. innodb_log_file stores the transaction log data. innodb_log_buffer is required to write to the log files on disk and carry on the transactions. innodb_file_per_table lets you reallocate and reclaim the disk space when you are using one database file(ibdata1 file). It will create two files per table (frm and ibd).

You can accordingly configure the values of the innodb_buffer_pool,  innodb_log_file_size, innodb_log_buffer_size, innodb_file_per_table keeping in mind that the system doesn’t run out of memory, and efficient caching and disk swapping must be balanced.

Following are the approximate values for the above fields:

• innodb_buffer_pool size: Around 50% of the machine physical memory size.
• innodb_log_file_size:  25% of the innodb_buffer_pool_size
• innodb_log_buffer_size: 32Mb
• innodb_file_per_table: This value should be set accordingly so that the files per table (frm and ibd) can be handled and the disk space can be pre allocated if required.

Optimize the MySQL configuration for an existing Zarafa environment:

To do so, follow the given steps: 

• Firstly, you must take a full backup of your Zarafa database
• Exit  all the  ongoing  Zarafa server processes
• Ensure that all the MySQL database  transaction logs have been cleared 
• Stop the MySQL server and shut it down. After that check that the MySQL database logs ensure a clean server shutdown. 
• Go to the configuration file located at /etc/my.cnf  and setup the innodb settings 
• Change the location of the old ib_logfiles by moving them from the mysql datadir to /tmp 
• Start MySQL and activate the new database settings.
• You can change the innodb_log_file_size by customizing the values in configuration files. After making the modifications ensure  that the changes have been implemented or not
• MySQL logs stores and lists all the errors along with details. See the logs for finding and resolving the errors that might have occurred during the process.
• Now start the Zarafa server and other processes. 

For performance tuning of Zarafa database, you need to configure the cache data sizes values to achieve maximum performance. To achieve maximum performance in spite of setting the cache parameters, you need to take care of other Hardware considerations as well.

First factor is the RAM - higher is the RAM,  higher will be the speed, hence better is the performance. 

If the server load is high, then using a multi server setup is the best idea as the load is split over different servers that causes increased performance. 

If you are using a multi-processor system, then it will increase the CPU performance, but the performance of the Zarafa server will be slightly decreased due to context switching and hyper threading. So, turning off hyper threading is a good option to reduce the Input Output usage and thus improving server performance in turn. You can use a hardware RAID controller for improving the server performance.