MariaDB is the single most common bottleneck we see when doing performance tuning on customers’ Zimbra servers. This Zimbra-specific MariaDB performance tuning guide will help your mailbox servers run faster and web client users to see noticeable speed improvements in the user interface’s responsiveness.
Zimbra MariaDB Level Set
Zimbra uses MariaDB to store all of the metadata associated with an account’s mailbox objects. The email folder tree, whether an email is read or unread, tags etc. are all stored in MariaDB. When you look at the list of emails in the web client and see a snippet from the first line of the body of every email following the email’s Subject, that snippet too is stored in MariaDB. It’s only when a user clicks on a specific email in the email list that Zimbra will read the mail blob from disk and load the mail blob’s contents into the reading pane. Consequently, the more users, email blobs, email folders etc. you have, the bigger your MariaDB databases will become.
MariaDB can use several different storage engines; Zimbra uses the InnoDB storage engine for its data-safe characteristics and performance.
MariaDB Performance Tuning Background
MariaDB is often a bottleneck because: (a) the /opt/zimbra/conf/my.cnf file is never adjusted by Zimbra after the initial installation of Zimbra (even if you add more RAM to the server); (b) some of the defaults in /opt/zimbra/conf/my.cnf are suboptimal, and; (c) the default values for some settings that should be in /opt/zimbra/conf/my.cnf (but are not put there by the Zimbra installer) are also suboptimal.
Let’s address each of those facets in turn…
But before we get started, please take a backup of your existing my.cnf file! As the Zimbra user, run:
cp -axv /opt/zimbra/conf/my.cnf /opt/zimbra/conf/my.cnf-backup
Zimbra MariaDB Performance Tuning Bottleneck One: RAM Utilization
In MariaDB, the InnoDB storage engine is restricted in the amount of RAM it can use by a parameter in my.cnf. That parameter’s initial value is set by the Zimbra installer based on how much RAM is configured in the system at the time the installer is run. That initial value is then never updated by Zimbra; not by patches, in-place upgrades, never. Again, if you added RAM to your mailbox servers after installing Zimbra, MariaDB will never be able to use it unless you edit my.cnf manually.
MariaDB performance tuning wikis generally say that this parameter (innodb_buffer_pool_size) should be set to between 110% and 125% of the total size of all of your InnoDB databases. In our experience, with the tuning adjustments below, we find we can set innodb_buffer_pool_size to as little as 50% of the total size of all of your InnoDB databases before users begin to notice slowness in the web client UI.
So our first task is to find out the total size of all of Zimbra’s InnoDB databases. To do that, I prefer to run a Perl script called “mysqltuner.pl”. Do not implement any tuning suggestions proffered by the tool, but the tool will give us the accurate total size of the InnoDB databases, and it will also parse the MariaDB error log and let us know if it finds any errors or warnings in that log (hopefully none, but important to know).
If you do have issues in the error log, stop and open a Support Case with Zimbra to address those errors before moving forward.
To get the tool, as root run:
wget mysqltuner.pl && mv index.html /opt/zimbra/mysqltuner.pl && chmod +x /opt/zimbra/mysqltuner.pl && chown zimbra.zimbra /opt/zimbra/mysqltuner.pl && su - zimbra
and then run “./mysqltuner.pl”. Note the following line in mysqltuner’s output, like:
[!!] InnoDB buffer pool / data size: 17.0G / 23.3G
The above line tells us that the total size of all of our InnoDB databases is 23.3GB, but that we are only giving MariaDB 17GB of RAM in which to fit all of the database data. Since that’s not of course physically possible, that means that MariaDB will be doing a lot of RAM and disk thrashing to move in to the buffer pool data users are requesting that is presently on disk and not in the buffer pool. To make room in the buffer pool, MariaDB needs to write some of the data already in the buffer pool back out to disk. And then if the data it just wrote out is again needed, well… you get the picture that this could cause a lot of RAM and disk thrashing — and slowness.
To fix this, just comment out the existing line, and then add a new line in ~/conf/my.cnf like so, with the value set (ideally) to ~110% to ~125% of the “data size:” reported by mysqltuner.pl above:
# innodb_buffer_pool_size = 9954162278 innodb_buffer_pool_size = 25600M
Keep in mind however that Zimbra recommends the total of memory allocated to both the Java heap and MariaDB not exceed 80% of the RAM in your server. If the server is a single Zimbra server, that’s a good limit. If the server is only a mailbox server, you can go a little higher. Essentially, it is much better to under-provision the InnoDB buffer pool size and not let the Zimbra server use swap than it is to create a large InnoDB buffer pool, only to have the operating system swap out some of that InnoDB data to disk. Put differently, MaraiDB is much more efficient at swapping data back and forth between disk and an udersized InnoDB buffer pool than the operating system is when swapping out some of that buffer pool to the swap file.
So before we restart Zimbra after making the above change, let’s see if we need to add more RAM to the server. First, to get the memory allocated to Java:
zimbra@mail:~$ zmlocalconfig mailboxd_java_heap_size mailboxd_java_heap_size = 6144 zimbra@mail:~$
The above means that 6 GB is presently allocated to the Java heap. If we are going to give MariaDB 25GB of memory, that’s 31GB of memory for Java and MariaDB combined. The server should then have a minimum of 36GB or RAM for maximum performance if you are following Zimbra’s own 80% rule; if you have less you’ll need to add more RAM. Some of the changes we make below will also increase RAM consumption, so at the end of the exercise, you’ll need to add up all of the incremenatl RAM increases to calculate how much RAM you’ll need to add to the server before restarting it.
If it’s not practical or cost-efffective to add more RAM to the server, consider instead moving mailboxes to a different mailbox server. As a general rule, when the InnoDB databases start getting close to 25GB in size, our best practice is it’s time to deploy an additional mailbox server. Arbitrary? It might seem so, but shortly before he passed away, Zimbra Ultimate Guru John Holder convinced me that having a larger number of smaller mailbox servers is much better than having a smaller number of larger mailbox servers. Consequently, it’s rare these days that I build mailbox servers with more than 32GB of RAM.
As an aside, if a mailbox server hosts only Network Edition Archive mailboxes, you can (and should) ignore that 25GB recommended limit; performance on a mailbox server hosting only Archive mailboxes is typically not an issue–Cross-Mailbox searches will just take a little longer to complete.
Zimbra MariaDB Performance Tuning Bottleneck Two: my.cnf Parameters Set Suboptimally
Here we focus on only two parameters: innodb_open_files and table_open_cache. Zimbra’s Performance Tuning Guidelines for Large Deployments wiki recommend values for these two attributes that I’ve never been able to square.
If you look at this MariaDB tuning guide, you will see that innodb_open_files is the maximum number of .ibd files MariaDB can have open at any one time. How many should that be?
Well, on our mailbox servers we find we have 100 mboxgroup sudbdirectories, each with 16 *.ibd files, so that would mean innodb_open_files need not be set to higher than 1600. Zimbra however sets innodb_open_files to 2710 by default. Perhaps in earlier versions of Zimbra there were more *.ibd files in each mboxgroup subdirectory? Or more mboxgroup subdirectories? Dunno. You can keep the default setting, reduce it to 1600, or set it somewhere in between (we set it to 1800). It may not matter provided however it is at least equal to the total number of ibd files you have — which you should confirm on your system.
As re the table_open_cache attribute, this is less straightforward, but fortunately MariaDB has an excellent tuning article we can leverage.
Essentially, the article says that we should increase the value for table_open_cache until we note over time that the total number of tables ever opened increases at a very slow rate, if at all.
Fortunately in Zimbra’s case, mysqltuner.pl reports that we have 1,792 total tables, so we have something of a baseline. Recall that MariaDB creates a fair number of temporary tables in RAM and on disk, so in our customer hosting environment on Amazon Web Services we see on our tuned mailbox servers that have been running for a month or longer uninterrupted:
zimbra@mail6:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 189444 Server version: 10.1.25-MariaDB Zimbra MariaDB binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global status like 'opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 2422 | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> quit Bye zimbra@mail6:~$
Zimbra’s default value for this attribute is 1200, but we typically set this to 3000 for our own environment and for most customers’ on-premises Zimbra systems. We do monitor the the “opened_tables” value above periodically (the longer mailboxd has been running, the better) and adjust table_open_cache as needed to have “opened_tables” stay at a near static value over several days of normal Zimbra workloads, as recommended by MariaDB.
Here’s what the relevant portions of my.cnf look like after making the above adjustments to these two parameters whose values have been set suboptimally:
zimbra@mail6:~$ grep "innodb_open_files\|table_open_cache" ~/conf/my.cnf | grep -v "max_connections" # table_open_cache = 1200 table_open_cache = 3000 innodb_open_files = 1800 # innodb_open_files = 2710 zimbra@mail6:~$
Zimbra MariaDB Performance Tuning Bottleneck Three: my.cnf Missing Parameters
There are default values MariaDB will use if a parameter is not set explicitly in ~/conf/my.cnf. Zimbra’s Performance Tuning Guide for Large Deployments wiki mentions a few of these, which the Zimbra installer now includes. We will be adding four “missing” parameters to ~/conf/my.cnf: tmp_table_size, max_heap_table_size, table_definition_cache and innodb_io_capacity.
MariaDB creates temporary tables for storing query results sets. Queries whose results set exceed the value of tmp_table_size get stored on disk. The default value for this parameter is 16MB. To avoid disk writes and reads and improve performance, we’d like to be able to store the results of a greater number of queries in RAM.
Both tmp_table_size and max_heap_table_size best practices (generally) dictate should be set identically, so we add two new lines to the bottom of the [mysqld] section of the ~/conf/my.cnf file to increase the maximum size of temporary tables that can be created in RAM from 16MB to 64MB:
tmp_table_size = 64M max_heap_table_size = 64M
Going from 16MB to 64MB could use a lot more RAM (as much as 5GB more), so you may want to start by increasing the above two parameters’ value to 32M to start. You can then monitor the following two report lines from mysqltuner.pl to check for improvements:
[OK] Highest usage of available connections: 48% (53/110) <...> [OK] Temporary tables created on disk: 11% (11K on disk / 101K total)
If we go straight to 64MB from the default 16MB, that’s 48MB more per connection. Since the maximum number of connections seen is 53, that means the above change will cause MariaDB to consume ~2.5GB more RAM. This amount of newly-needed RAM should be counted when considering how much RAM to add to the server. There is of course a tradeoff between RAM consumption and performance related to these two parameters, but in our experience when the percentage of temporary tables created on disk is under like 20%, there’s no real need to increase tmp_table_size and max_heap_table_size further. Just remember that they should be set to the same identical value. You can read more about these two attributes here and here.
The next attribute, table_definition_cache does what it is named. This is related to our other attribute table_open_cache above. Essentially, Each time MariaDB goes to load a table into RAM, it also needs to know the “definition” of the table (kind of like metadata of the table). If the definition isn’t in RAM already, MariaDB needs to get the definition from disk before it can use the table. The default value for table_definition_cache is 400, but recall we have 1,792 tables, so we need to explicitly set this excluded parameter and set it to a number slightly greater than the number of tables we have, like so:
table_definition_cache = 1800
Our last missing parameter in ~/conf/my.cnf is innodb_io_capacity, which sets the maximum number of IOPS InnoDB will use when accessing disks to perform background tasks. When using like 10K SATA drive spinning disks in a RAID6 array, the default value of 200 IOPS was sensible, to prevent InnoDB from monopolizing storage throughput. There is also a “bursting” capability whose default is set to 2,000 IOPS, but it’s not clear when that bursting is actually allowed. Further, these days, when even base level gp3 SSD disks on Amazon Web Services have a minimum of 3,000 IOPS, such a small IOPS limitation creates a needless bottleneck.
Consequently, we need to explicitly set this excluded parameter and set it to a number approximately 2/3rds of the IOPS capacity of the disks where /opt/zimbra/db/data lives, by adding the following line just underneath our new table_definition_cache line above. For an AWS gp3 disk with 3,000 IOPS, we would set:
innodb_io_capacity = 2000
Once you have made all of these changes, be sure to recalculate the increases in RAM consumption which will be generated by these changes, and add an appropriate amount of RAM to the system as needed to minimize swapping.
Zimbra MariaDB Performance Tuning Executive Summary and Recap
The process we followed above essentially comprises:
- Make a backup of /opt/zimbra/conf/my.cnf.
- Obtain and run mysqltuner.pl to get the total size of all of our InnoDB databases.
- Note the amount of RAM already in the server and the amount of RAM consumed by the Java heap.
- Adjust the size of the InnoDB buffer pool as needed, but don’t increase it so much that the server starts to use the operating system swap file heavily.
- Adjust the innodb_open_files and table_open_cache parameters as appropriate.
- Add the four new parameters to /opt/zimbra/conf/my.cnf with their values set as appropriate for your environment.
- Recalculate the amount of additional RAM needed due to the changes you are making and document the new amount of RAM to be provisioned to the server appropriately, to minimize swapping.
- Shut down the server, add the needed RAM and then boot up the server and enjoy your new, high-performance Zimbra mailbox server!
- Reminder: consider adding another mailbox server and moving mailboxes when the total size of your InnoDB databases reaches ~25GB.
If you’d like help with this, please start the conversation by filling out the form below!
Hope that helps,
L. Mark Stone
Mission Critical Email LLC
7 May 2023
The information provided in this blog is intended for informational and educational purposes only. The views expressed herein are those of Mr. Stone personally. The contents of this site are not intended as advice for any purpose and are subject to change without notice. Mission Critical Email makes no warranties of any kind regarding the accuracy or completeness of any information on this site, and we make no representations regarding whether such information is up-to-date or applicable to any particular situation. All copyrights are reserved by Mr. Stone. Any portion of the material on this site may be used for personal or educational purposes provided appropriate attribution is given to Mr. Stone and this blog.
