Zimbra contains a number of databases to speed up things like attachment indexing, but MySQL is one of the most important. In Zimbra, MySQL is a separate instance on every mailbox server used to hold mailbox meta-data. “Meta-data” means things like: what emails are in which mailbox folders. Zimbra’s MySQL is configured to use the transaction-safe InnoDB database engine, which gets a specific amount of memory dedicated to the InnoDB Buffer Pool via a setting in /opt/zimbra/conf/my.cnf.
In most larger Zimbra installations we have historically seen a lack of RAM for Java as the primary bottleneck, but lately we are seeing sub-optimal MySQL settings more frequently. In our view, the root cause of this is virtualization; let me first explain why this is important, how this comes about and then what you can do about it.
What’s The Problem?
Pretty much every MySQL tuning blog/whitepaper/textbook we know says that MySQL’s InnoDB buffer pool should be set to be at least 10% -20% larger than the size of the InnoDB database(s). The InnoDB buffer pool size is the amount of RAM MySQL allocates for InnoDB database uses. InnoDB creates a lot of temporary objects for manipulating data, which are typically 10% or so of the size of the entire InnoDB databases. Having some room for growth is also good. If the InnoDB buffer pool is smaller than the InnoDB databases, then disk swapping happens because MySQL will try to load the entire InnoDB database, plus temporary objects, into RAM.
The InnoDB buffer pool size is configurable via a single parameter within /opt/zimbra/conf/my.cnf. The Zimbra installer creates my.cnf the first time Zimbra is installed, and sets the InnoDB buffer pool size based on the amount of RAM present at the time Zimbra is first installed.
Most templated virtual machine environments (we use XenServer for our Zimbra Hosting farm) launch newly created virtual machines with a small amount of RAM. If the system admin neglects to increase the amount of RAM before installing Zimbra, the InnoDB buffer pool size will be set way too small. And here’s the catch: subsequent runnings of the Zimbra installer – even during Zimbra upgrades – make no changes whatsoever to /opt/zimbra/conf/my.cnf.
So the first thing to do is to give your newly created Zimbra server the amount of RAM it should have before you install Zimbra.
Large Deployment Specifics
Even when you allocate sufficient RAM to a Zimbra server before you install Zimbra, we are seeing that dedicated mailbox servers with adequate provisioned RAM but more than a few thousand mailboxes quickly “outgrow” Zimbra’s default InnoDB buffer pool settings. The Zimbra installer’s InnoDB buffer pool tuning algorithm has no clue whether the server is a single multi-function Zimbra server (where there will be a lot of Zimbra daemons clamoring for RAM) or a dedicated mailbox server where more RAM may safely be allocated to MySQL. You get what you get…
How Do I Determine If I Have A Problem With My Zimbra Server?
Easy-Peasy! The mysqltuner.pl script from Major Hayden will do the job for you. Here’s how to download, configure and run it:
- Become root and download the mysqltuner.pl script to /opt/zimbra.
- Change the ownership and permissions so the zimbra user can run the script.
- Run the script and check the results.
Below is from a dedicated mailbox server with ~6,000 mailboxes. As you can see, the Zimbra installer has allocated 3.5GB to the InnoDB buffer pool, but the InnoDB database is already larger at 4GB. We need to fix this!
root@mailboxsrvr5:/zcs/8.0.4-Patch_1# cd /opt/zimbra
root@mailboxsrvr5:/opt/zimbra# wget mysqltuner.pl/mysqltuner.pl
--2013-08-29 09:58:33-- http://mysqltuner.pl/mysqltuner.pl
Resolving mysqltuner.pl... 220.127.116.11, 2607:f298:5:104b::417:6481
Connecting to mysqltuner.pl|18.104.22.168|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl [following]
--2013-08-29 09:58:33-- https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Resolving raw.github.com... 22.214.171.124
Connecting to raw.github.com|126.96.36.199|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 41847 (41K) [text/plain]
Saving to: `mysqltuner.pl'
100%[=====================================================>] 41,847 --.-K/s in 0.02s
2013-08-29 09:58:34 (1.81 MB/s) - `mysqltuner.pl' saved [41847/41847]
root@mailboxsrvr5:/opt/zimbra# chown zimbra.zimbra mysqltuner.pl
root@mailboxsrvr5:/opt/zimbra# chmod 700 mysqltuner.pl
root@mailboxsrvr5:/opt/zimbra# su - zimbra
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 0B (Tables: 1)
[--] Data in InnoDB tables: 3G (Tables: 1412)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 302
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 22d 20h 0m 51s (47M q [24.152 qps], 113K conn, TX: 33B, RX: 5B)
[--] Reads / Writes: 43% / 57%
[--] Total buffers: 3.6G global + 2.6M per thread (110 max threads)
[OK] Maximum possible memory usage: 3.8G (32% of installed RAM)
[OK] Slow queries: 0% (12K/47M)
[OK] Highest usage of available connections: 25% (28/110)
[OK] Key buffer size / total MyISAM indexes: 8.0M/101.0K
[OK] Key buffer hit rate: 100.0% (4K cached / 0 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (948 temp sorts / 780K sorts)
[OK] Temporary tables created on disk: 6% (5K on disk / 87K total)
[OK] Thread cache hit rate: 99% (28 created / 113K connections)
[OK] Table cache hit rate: 27% (1K open / 4K opened)
[OK] Open file limit used: 0% (51/524K)
[OK] Table locks acquired immediately: 100% (18M immediate / 18M locks)
[!!] InnoDB data size / buffer pool: 4.0G/3.5G
-------- Recommendations -----------------------------------------------------
Run OPTIMIZE TABLE to defragment tables for better performance
Variables to adjust:
query_cache_size (>= 8M)
innodb_buffer_pool_size (>= 3G)
The Fix Methodology
The fix is simple: make a backup copy of /opt/zimbra/conf/my.cnf, increase the InnoDB buffer pool parameter in /opt/zimbra/conf/my.cnf and restart Zimbra. Here’s the before/after line in my.cnf:
Before, as set by the Zimbra installer:
innodb_buffer_pool_size = 3773829120
After editing with joe, nano, vi or similar editor to the actual database size of 4GB plus 20% = ~4.8GB so to provide some room for growth we’ll set it at 5GB:
# Increased buffer pool size per results from mysqltuner.pl run on 28 August 2013.
# See Change ticket C00058295.
# innodb_buffer_pool_size = 3773829120
innodb_buffer_pool_size = 5120M
All that’s left is to restart Zimbra and you are good to go!
Do NOT change ANY of the other values in my.cnf unless you really, really know what you are doing!
For more detailed reading on the subject, please see the blog at Percona.
P.S. According to this post in the Zimbra forums, Zimbra 9 should be shipping with MariaDB.
Hope that helps,