wiki:MySQLTuning

Version 1 (modified by rjl, 12 years ago) (diff)

Added some advice on MySQL tuning

Tuning MySQL for Maia Mailguard

MySQL Versions

For best performance, MySQL versions 4.1 and later are recommended. Maia will work fine with MySQL 4.0 as well, but with 4.1 and later you can take advantage of improved efficiencies in SpamAssassin's Bayes storage module. Given how big an impact the Bayes database performance has on the overall performance of a Maia system, this is an efficiency gain you really should not pass up.

Engine/Table? Types

MySQL's default engine type (formerly referred to as "table type") is called MyISAM. This engine is fast and lightweight, but it doesn't support transactions or rollbacks, and it only supports table-level locking. This means that to modify one row in a table, the entire table needs to be locked. This is a performance killer on busy systems, as database processes sit waiting for tables to become unlocked. For this reason MyISAM tables are not recommended for use with Maia Mailguard.

The preferred engine type for Maia Mailguard is MySQL's InnoDB engine, which is transaction-safe, offers rollback capability, and row-level locking. The database schema that ships with Maia Mailguard will create InnoDB tables by default. If you're uncertain which engine your tables are using, you can find out with:

SHOW TABLE STATUS;

If you need to convert a table from the MyISAM engine to the InnoDB engine, you can do this with an ALTER TABLE statement, e.g.

ALTER TABLE <table> ENGINE=InnoDB;

Bear in mind that the conversion may take a while to finish, especially if you have a lot of data to be converted, so you should probably do this during off-peak hours. As always with database manipulations, it's better to be safe than sorry, so for peace of mind you should backup your database first.

Bayes Storage and MySQL

In your local.cf file you can specify the storage module SpamAssassin should use for its Bayes database. The generic SQL module is fine for older versions of MySQL, but if you're using MySQL 4.1 or later you should really take advantage of the MySQL-specific storage module.

If you're using MySQL 4.0:

bayes_store_module    Mail::SpamAssassin::BayesStore::SQL

If you're using MySQL 4.1 or later:

bayes_store_module    Mail::SpamAssassin::BayesStore::MySQL

Performance Parameters

The following parameters can be set in your /etc/my.cnf file to fine-tune your MySQL server. You can verify their current values with:

SHOW VARIABLES;

thread_concurrency determines the number of threads the MySQL server should spawn in order to handle client requests. As a reasonable starting point, set this to twice the number of processor cores installed on the server host. If you guess too low, you'll experience some start-up delays whenever the MySQL server has to spawn new children to process requests; if you guess too high, you'll waste some memory by preloading more children than you need.

max_allowed_packet determines the maximum size of an INSERT or UPDATE statement, including its data. In a Maia context that means the largest mail item you're willing to store in the quarantine or cache. See the FAQ item about size limits for a more detailed explanation of how to set this parameter.

max_connections limits the number of concurrent database connections the MySQL server will allow before telling clients to wait. The default of 100 should be plenty for most Maia users, since each amavisd-maia child will use at most 3 connections, and the maintenance scripts will each use just one connection when they run. If your MySQL server is being used by other applications as well, though, you may want to increase this limit.

innodb_buffer_pool_size determines how much of your system memory will be used by the MySQL server as a read cache for the InnoDB engine. On a dedicated database server host, you can set this to 50-80% of system memory to dramatically improve the MySQL server's performance. If other applications are running on the same host, though, you'll want to reduce this to perhaps 20-30% of system memory, bearing in mind that whatever memory you allocate this way will not be available to other processes. This one setting alone has made a huge difference for many Maia users.

key_buffer_size tells the MySQL server how much system memory to allocate for caching indexes. The larger the key buffer, the faster SELECT queries will execute. On a dedicated MySQL server host, setting this to 25-50% of system memory is generally recommended. To assess the actual performance of the key buffer, use SHOW STATUS to look up Key_reads, Key_read_requests, Key_writes, and Key_write_requests. On an efficiently-tuned system, Key_reads / Key_read_requests should be less than 0.01, while Key_writes / Key_write_requests should be less than 1.

query_cache_type determines whether to enable or disable MySQL's query-caching mechanism, and how the cache should work if it's enabled. Setting this to 0 or OFF disables the query cache. Setting it to 1 or ON enables the query cache for all queries except those that begin with SELECT SQL_NO_CACHE. Setting it to 2 or DEMAND will only cache queries that explicitly request it with SELECT SQL_CACHE. Since Maia currently doesn't do much query-caching of its own, setting this to 1 or ON is a good way to improve performance.

query_cache_limit sets the maximum size of a result set that MySQL should cache for a given query. Queries that generate result sets larger than this value will not be cached. A value of 2M should be sufficient for most Maia sites.

query_cache_size sets the total amount of system memory the MySQL server should allocate for its query cache. Start with a setting of 64M, and increase this as necessary to improve performance.

table_cache tells the MySQL server how many tables it should cache in memory for faster lookups. Use SHOW STATUS to reveal the Open_tables statistic, which tells you how many tables the MySQL server is currently using. Do this again at peak times to see how large this figure grows, and use that as a guide for setting this parameter. Provided you have the memory for it, setting this to a value as high as 1500 may not be unreasonable for busy sites.

read_rnd_buffer_size is the size of the per-thread buffer for sorted query results, i.e. the results of ORDER BY queries. Since Maia uses a fair bit of these to order its various lists for display, increasing this parameter can speed things up. The rule of thumb for setting this parameter is to allocate 1 KB for every 1 MB of RAM (i.e. 1 MB per GB).

thread_cache_size lets you specify the number of threads the MySQL server should cache for reuse. The process of allocating memory and stocking it with thread information happens every time a connection request arrives at the database, and this eats up CPU time. By using a thread cache, you can relieve the CPU of this burden. Use SHOW STATUS to reveal the Connections and Threads_created statistic and compare these values to see how efficiently threads are being used. Ideally you'd expect to see fewer Threads_created than Connections if your thread cache is working properly. A starting value of anywhere from 32 to 128 is reasonable for this parameter.

sort_buffer_size determines how much memory the MySQL server allocates (per thread) for performing sorting operations (i.e. ORDER BY and GROUP BY queries). 2M is a reasonable starting point.

tmp_table_size determines how much memory the MySQL server allocates in total for storing the temporary tables it needs to construct for more complicated queries, such as GROUP BY operations. Start with a setting of 32M.

Note: Many of these performance parameters work by having the MySQL server allocate a certain amount of system memory for caches and buffers. These memory allocations are cumulative, so keep this in mind when deciding how much to use, because if you set these allocations too high your system will end up swapping out to disk, which kind of defeats the purpose of these performance optimizations! As a general guide:

Total Memory Used = key_buffer_size + max_connections * (join_buffer_size + read_buffer_size + sort_buffer_size + thread_stack + tmp_table_size) 

Additional Sources of Information

High Performance MySQL by Jeremy Zawodny


Back to FAQ