From Habari Project
The MySQL database server hosts several databases for services provided on hp.o. Disk access, particularly in a virtualized environment, is a major performance killer, so MySQL has been tuned to use enough RAM for caching as possible, rather than requiring disk reads for every query.
Do not edit the default my.cnf file. Future changes may be made in the MySQL package that require configuration updates. Rather than manually needing to consolidate these changes the default my.cnf file can be used without worry.
All changes should be added as overrides in /etc/mysql/conf.d/*.cnf files. There is a main habari.cnf file which contains the bulk of the server-specific configuration values, but don't be afraid to further modularize your configs.
Config File: /etc/mysql/conf.d/default-engine.cnf
The InnoDB engine is preferred when an alternate engine is not specifically required (MyISAM for a fulltext index, for instance, is required by MediaWiki).
InnoDB is the preferred default in recent builds of MySQL and offers a number of advantages. Row-level locking is of significant importance for Trac, but InnoDB also offers easier configuration for caching and nice development features like foreign keys, etc.
Using a single engine type also reduces the number of separate cache pools required and drastically simplifies the complexity of configuration.
Config File: /etc/mysql/conf.d/encoding.cnf
The UTF8 character set should be used everywhere for everything. By default MySQL assumes you'll be using a latin1 character set, so we explicitly tell it to use UTF8 and the utf8_general_ci collation. This should change the default not only for new databases but for server and client connections.
Config File: /etc/mysql/conf.d/habari.cnf
As memory optimization is highly dependent on the workload and use case involved, this section of the config is the most highly customized. The config file is well-documented, but each section is also covered here. Ideally references and additional explanations should be added here. For now it's just a copy of the config.
Key Buffer Size
# the key buffer stores MyISAM indexes - we don't use MyISAM tables, so this can be small key_buffer_size = 8M
# the table cache defines the number of tables that can be kept open across threads. if opened_tables is high, increase this # 400 should be enough for all our tables to be open at once table_cache = 400
# there's never a need for more connections, we rarely have more than 1 or 2 simultaneous connections max_connections = 100 # 100 is the default
Sort Buffer Size
# the sort buffer defines the amount of memory available for sorting query results (per thread). more than 2M can degrade performance on linux sort_buffer_size = 2M # 2M is the default
Query Cache Size
# the query cache defines the amount of memory available for caching query results query_cache_size = 64M
Query Cache Limit
# the query cache limit defines the largest query that can be cached - if we've got free memory, why not use it? query_cache_limit = 10M
InnoDB Buffer Pool Size
# the innodb buffer pool size defines the amount of memory InnoDB uses to cache data and indexes - equivalent to key_buffer and query_cache for MyISAM innodb_buffer_pool_size = 64M
Log Slow Queries
# always log queries that take a long time to this file log_slow_queries = /var/log/mysql/mysql-slow.log
Long Query Time
# queries that take a long time means... long_query_time = 3 # seconds - the default is 10
Log Queries Not Using Indexes
# logging queries that don't use indexes produces a shitload of entries because of crappy wikimedia and trac #log-queries-not-using-indexes
Thread Cache Size
# there's no point in keeping threads cached, they just eat up memory thread_cache_size = 0
Temp Table Size
# temp tables are written to disk when they exceed either tmp_table_size or max_heap_table_size (whichever is lower), so make sure those are fairly large values tmp_table_size = 32M # default is system-dependent, seems to be 32M here max_heap_table_size = 32M # 16M is the default