Operations/Server Documentation/Configuration/MySQL

From Habari Project

Jump to: navigation, search


Contents

Abstract

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.

Configuration Files

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.

Database Engine

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.

Encoding

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.

Memory Optimization

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

Table Cache

# 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

Max Connections

# 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

References

Personal tools