Articles
MySQL Performance
Author: nathacof
Published: Saturday 17th of January 2009
Unfortunately, optimizing MySQL for performance can be a daunting task. Since we do not have the knowledge of our clients setup to determine what server configurations will garner performance gains the task is left up to the database administrator for VPS and Dedicated servers.
Configuration Directives
Here you can find a complete list of directives for all versions of MySQL!
Query Cache
The query cache will temporarily store query results, and can dramatically increase the speed of frequently performed queries. MySQL.com has an in depth article on the query cache.
You should not just start enabling Query Caching without knowing what you are doing. It can be a great tool but it can also completely ruin an app that needs to query distinct or new data constantly. There is a way to cache things within the actual SQL statement or to pull un-cached data the same way.
To check if the query cache is enabled run the following:
mysql> SHOW VARIABLES LIKE 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ mysql>
- query_cache_limit - The maximum size of queries that will be stored in the Query Cache.
- query_cache_min_res_unit - the minimum size for blocks allocated by the cache (kinda like inodes).
- query_cache_size - The amount of RAM to designate to the Query Cache.
- query_cache_type - This can be set to:
- A value of 0 or OFF prevents caching or retrieval of cached results.
- A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
- A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
- query_cache_wlock_invalidate - causes the query cache to invalidate any query in the cache if an object it uses has a write lock executed against it.
To see the usage statistics for your query cache run the following command:
mysql> SHOW STATUS LIKE "Qc%"; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 7 | | Qcache_free_memory | 26074680 | | Qcache_hits | 10219 | | Qcache_inserts | 463 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 11421 | | Qcache_queries_in_cache | 12 | | Qcache_total_blocks | 35 | +-------------------------+----------+ 8 rows in set (0.02 sec) mysql>
Table/Index Health
Optimize
Ongoing maintenance is very important in improving performance on your database. It is wise to OPTIMIZE any tables that change frequently:
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
For MyISAM tables, OPTIMIZE TABLE works as follows:
- If the table has deleted or split rows, repair the table.
- If the index pages are not sorted, sort them.
- If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.
Analyze
Also if you are using JOIN statements in your Queries, ANALYZEing your tables can improve performance dramatically:
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM. For InnoDB the table is locked with a write lock. This statement works with MyISAM, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.
Once you've OPTIMIZEd and ANLYZEd those tables that change most frequently you should see a notable increase in performance.
Memory
MySQL loves memory; This article describes how to calculate how much memory total MySQL will use, http://www.itslot.com/calculate_mysql_memory
A good preliminary article on configuring MySQL for low memory constraints can be found here, http://emergent.urbanpug.com/?p=61. A good read for anyone who wants to understand a bit more about MySQL's memory management as well.
Slow Query Log
The Slow Query Log alone does not help to improve performance. On the contrary writing text to a file for every query that takes longer than X seconds is going to increase the time it takes for the server to complete it's task.However this will assist you to identify troublesome queries. Typically you would disable the slow-query-log once your analysis has been completed.
Do not leave this enabled for long periods of time. It has a tendency to grow rapidly.
Table Locks
If one query seems to be preventing other queries from running it's likely due to a locking condition. The database server locks any data that is going to be changed to prevent data corruption. This can be particularly painful for user's who only use MyISAM tables, as it only supports page level locking. That is the whole table is locked for the duration of a query that causes a lock.
You can see the currently running queries with the following command:
mysql> SHOW FULL PROCESSLIST\G
To mitigate these problems you should have the customer update their tables to use InnoDB. InnoDB supports row level locking, which is much better than MyISAM in this regards, it also provides transaction support, as well as being fully ACID compliant
On a shared server, we recommend that the customer break up their data into multiple tables, this will reduce the chances of a lock occurring, without the need to use InnoDB.
Reference: http://dev.mysql.com/doc/refman/5.1/en/table-locking.html
Poor SQL Queries
If at this point you still have not seen improvements then the problem is most likely due to poorly coded SQL, that does not leverage the performance features provided by the database server. To find out more information about a particular query you can run the EXPLAIN command:
The output of the EXPLAIN command will show you exactly how MySQL will be using the indexes available to it and an estimation on the number of rows returned (It's estimated based off of the index for that row).
EXPLAIN SELECT film.title FROM actor, film, film_actor
WHERE actor.actor_id = film_actor.actor_id
AND film.film_id = film_actor.film_id
AND actor.last_name = 'Walken'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sakila.actor.actor_id
rows: 26
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sakila.film_actor.film_id
rows: 1
Extra:
3 rows in set (0.00 sec)
You'll probably see a lot of queries that are not using indexes. Here's an article going over index creation, Creating Indexes .
More Info
Here are more optimization techniques from our friends at MySQL.com, http://dev.mysql.com/doc/refman/5.0/en/optimization.html.
Article Search
Blag, blag, balg...
Social Networks
