MySQL tuning

On a SQL server, the data tables sit on disk. Indexes provide a means for the server to find a particular row of data in the table without having to search the entire table. When the entire table has to be searched, it's called a table scan. Most often, you want only a small subset of the data in the table, so a full table scan wastes a lot of disk I/O and therefore time.This problem is compounded when data must be joined, because many more rows must be compared between the two sides of the join.

Of course, table scans aren't always a sign of a problem; sometimes it's more efficient to read the whole table than it is to pick through it (making these decisions is the job of the query planner in the server process). Inefficient use of indexes, or not being able to use indexes at all, slows the queries, and this issue becomes more pronounced as the load on the server and the size of the tables increases. Queries that take more than a given amount of time to execute are called slow queries.

You can configure mysqld to log slow queries in the aptly named slow query log. Administrators then look at this log to help them determine which parts of the application need further investigation.

This  shows the configuration required in my.cnf to enable the slow query log:

[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

These three settings, used together, log any queries that take longer than 5 seconds and any queries that don't use indexes. Note the caveat about log-queries-not-using-indexes: You must have MySQL 4.1 or newer. The slow query log is in your MySQL data directory and is called hostname-slow.log. If you'd rather use a different name or path, you can do so with log-slow-queries = /new/path/to/file in my.cnf.

Reading through the slow query log is best done with the mysqldumpslow command. Specify the path to the logfile, and you're given a sorted list of the slow queries, along with how many times they're found in the log. One helpful feature is that mysqldumpslow removes any user-specified data before collating the results, so different invocations of the same query are counted as one; this helps point out queries in need of the most work.

I recently came across this script for testing and tuning MySQL performance:

mysql_tuning-primer

I'll post a review on this later on