MySQL Optimization

• Optimization
• MySQL
• MySQL Configuration
• MySQL Logs
• mysqlreport
• mysqltuner

• Optimizing SELECT Statements

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

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.

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

• The MySQL Query Cache
• How the Query Cache Operates
• Query Cache SELECT Options
• Query Cache Configuration
• Query Cache Status and Maintenance

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

You can clear the query cache if you like:

mysql -e "RESET QUERY CACHE;"

query_cache_size

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

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.