MySQL Optimization

The best places to look for optimizing MySQL are:

  • Configuring for proper memory environment
  • Disabling unused engines
  • Setting up a slow query log to determine inefficient queries
  • Enable query caching

Slow Queries

Configuring MySQL to log slow queries and ones that are not using indexes will quickly point out where some bottlenecks are. Configure the server to log them, and then use maatkit to analyze the logs.

Add to my.cnf to log queries that take more than one second, slow administrative commands, and queries that don't use indexes on lookups:

long_query_time=1
log-error=/var/log/mysql/slow.log
log-slow-admin-statements
log-queries-not-using-indexes

Be sure to setup log rotation since the logs can grow quickly. Also be sure to secure them properly, as queries can contain sensitive data.

Database Engines

Disable any engines that the database is not using. You can see which ones are enabled globally, and which ones are being used specifically on tables:

Display engines:

mysql -e "SHOW ENGINES;"

Display which engines are in use:

mysql -Be "SELECT DISTINCT ENGINE FROM information_schema.TABLES;"

Display which tables are using an engine you'd like to disable:

mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where ENGINE = 'InnoDB';"

Disable engines in my.cnf and restart server:

skip-innodb
skip-bdb

Query Caching

MySQL query caching enables cache on queries that are the same, and return the same results. It is optimal for tables that are queried more than updated. Query caching is disabled by default in MySQL.

Some notes on how query cache works:

  • Only SELECT statements are cached
  • Queries must be exactly the same in syntax and case (SELECT foo != select foo)
  • Only full queries are cached (no subselects, inline views, parts of the UNION)
  • Caching is transparent to the application
  • Query cache performs lookup in the cache first, avoiding the query parsing if possible
  • Queries must be absolutely the same
  • Does not support prepared statements and cursors
  • Queries using non-deterministic functions such as UUID, RAND, etc. will not use cache

Clear Cache

You can clear the query cache if you like:

mysql -e "RESET QUERY CACHE;"

Configuration Variables

query_cache_size

The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.

Optimization

To use more memory, change the buffer variables.

Raise the query_cache_limit and query_cache_size to increase caching. Cache limit sets the limit of how big a query is cached. Cache size is how much cache to have.

Configuration (5.1) small medium large default ubuntu
innodb_buffer_pool_size 128M
key_buffer_size 16k 16M 256M 8M 16M
max_allowed_packet 1M 1M 1M 1M 16M
table_cache (5.0) 4 64 256 64
table_open_cache (5.1) 4 64 256 64
sort_buffer_size 64K 512K 1M 2M
read_buffer_size 256K 256K 1M 128K
read_rnd_buffer_size 256K 512K 4M 256K
net_buffer_length 2K 8K 16K
myisam_sort_buffer_size 8M 64M 8M
thread_stack (x86) 128K 192k 192k
thread_stack (x86_64) 128K 256k 192k
thread_cache_size 8 0 8
query_cache_limit 1M
query_cache_size 16M 0 16M