ABC of Database Server Tuning

Home Simply ABC of Database Server Tuning Downloads

The ABC of MYSQL-Server performance recommendations.

The below list is quite simple and trivial but during my many years as Database Performance Consultant my experience was that often disregarding these simple rules was the cause of performance problems.

1) Pick the right database table type

MySQL supports several different table types. Each of them has both advantages and disadvantages.
MyISAM is the default table type but for most usages InnoDB is much better suited.

  • Risk of Loosing data on server crash
    Depending on your usage this could be a problem but for many people it is not.
  • Very Good performance when used mostly READ ONLY
  • Very Good performance when used mostly WRITE ONLY
  • Very unreliable performance when used in mixed environment with many WRITES and longer READ queries.
  • Supports Fulltext seach
  • Very safe table handler. Very little risk of data loss on server crash
  • Needs more space on disk than MyISAM
  • Reliable Good performance - even in mixed READ/WRITE environments

2) Use the right Primary Keys

Usually every Table needs a Primary key. Primary keys should be SHORT. Good primary keys are INTEGER columns. CHAR columns make bad primary keys. Especially for the InnoDB table type is crutial to have good chosen primary keys. I've seen professional customers ruining their performance by using CHAR(40) as primary key.

3) Choose good Index

Some people hurt performance a lot by not using indexes. Others loose a lot performance by indexing everything. A good design will index the needed columns and not more. Learn how to use the EXPLAIN command to annalyse which indexes are used.

4) Don't misuse the database as BLOB storage

Rational database can store BLOBS of BINARY DATA. Sometimes this is very handy. But be very careful with this. BLOBS will slow down the database. If the amount of indexed data (in GigaByte) is less than the amount of space used by BLOBs consider to store them in the filesystem and to only store pointers to them in the database. The filesystem will always be faster and more efficient in this than a database. People ruin their database performance by storing videos or other blobs in the database.

5) Use the database to filter your records early

Sometimes people select ALL records from the database and filter them in the top application layer. This is of course terrible for performance - Filter your data early, at the lowest level and only transmit the needed records to the top level. If you need a filter that is not available in stock SQL - try to implement it as SQL function or use the feature of MySQL to load selfwritten functions as modules. SOUNDEX is buildin but sometimes people need more powerful compare functions e.g. LEVENSTHEIN. Not using the Levensthein in the top level but loading it into the database server will usually bring several magnitutes of performance increase.

6) Make us of LIMIT

Often LIMIT is very usefull to limit the amount of unneeded transfered records. Often you want to have both. You want to know how many records are affected in total and then you want to select a certain number from them with limit. Imagine a search where you list the amount of found record e.g. 15000 and then show the details of the first 50 records. Very often the most effective way to do this is to run two seperate queries. One query that search for the matching records. This query needs to by highly optimized - it should use as few tables as possible - Best is only to use one table. This query will return the number of total records. Often the fastest solution is to use such a simple query to fetch the primary keys of the matching rows and then to use a second query that joins all needed tables to fetch the details for the few records that should be displayed on the screen.

7) Query cache is usefull

Enabling Query cache can be very handy if your application is mostly READ only and often repeats queries. Often this is the case for webapplications. But not all queries can be cached. Use show status to check if your queries are effectively cached or not. If the query cache does not work disable it.

8) Server Settings

Often people run their database server with to small settings.
There are several dozend parameters that often make sense tuning.
But often changing a few parameters can cure performance problems.

table_cache should be big enough. A good value is number of tables *2

If you use MyISAM count the size of your indexes and set key_buffer accordingly.

If you use InnoDB then give it enough buffer memory. innodb_buffer_pool_size should be as big as your server can effort. (But never use more memory than server physically has!)