Changes between Initial Version and Version 1 of MySQLTuning

Dec 1, 2006, 9:49:22 AM (16 years ago)

Added some advice on MySQL tuning


  • MySQLTuning

    v1 v1  
     1= Tuning MySQL for Maia Mailguard =
     3== MySQL Versions ==
     5For 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.
     7== Engine/Table Types ==
     9MySQL'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.
     11The 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:
     17If 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.
     20ALTER TABLE <table> ENGINE=InnoDB;
     23Bear 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.
     26== Bayes Storage and MySQL ==
     28In your [wiki:SAConfigFile] 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.
     30If you're using MySQL 4.0:
     33bayes_store_module    Mail::SpamAssassin::BayesStore::SQL
     36If you're using MySQL 4.1 or later:
     39bayes_store_module    Mail::SpamAssassin::BayesStore::MySQL
     43== Performance Parameters ==
     45The following parameters can be set in your /etc/my.cnf file to fine-tune your MySQL server.  You can verify their current values with:
     51'''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.
     53'''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 [wiki:SizeLimit size limits] for a more detailed explanation of how to set this parameter.
     55'''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.
     57'''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.
     59'''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.
     61'''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.
     63'''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.
     65'''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.
     67'''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.
     69'''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).
     71'''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.
     73'''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.
     75'''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.
     77'''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:
     80Total Memory Used = key_buffer_size + max_connections * (join_buffer_size + read_buffer_size + sort_buffer_size + thread_stack + tmp_table_size)
     84== Additional Sources of Information ==
     86[ High Performance MySQL] by Jeremy Zawodny
     88* [ The MySQL Manual: Tuning Server Parameters]
     93[wiki:FAQ Back to FAQ]