Basic Performance Tuning Concepts for MySQL

Here is the some common performance tuning concepts/process that I follow to tune the MySQL Server. This is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.

Below is the steps I follow to tune MySQL Server.

1.    Query optimization

2.    Data type Optimization

3.    MySQL Server variables optimization

4.    Hardware optimization

1.    Query optimization/Tuning

First, though probably the most important, we look at tuning queries. We check whether they are executing smoothly or not. In particular, we make sure that they’re using indexes, joining on appropriate columns and they’re running quickly. To get the list of those queries which are not using index, executing smoothly or taking more the expected time in execution, we turn on the Slow Query Log for some time, with proper setting of long query time and variables.  At the end we will got the log file with queries which have taken more than expected time in execution.  Run the resulting log through mysqldumpslow, which will produce a summary of the log. This summary will have the detail of query, query execution time, occurrence of query.   This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.

Note: - Adding a new index is overhead on I/O. Think before you are adding any index/indexes on the table.

2.    Data type Optimization

In this step we will check weather data type for all columns has been declared correctly or not. We will check it with procedure analyze function. Remember it will calculate/Suggest based on current database size/data.  Before changing it also think about future prospects.

3.    MySQL Server variables optimization

For tuning Innodb performance, the  primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.

For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.

Other variables that are worth investigating for performance tuning are:

query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.

innodb_log_file_size - Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.

sort_buffer_size - Another one that shouldn’t be set too large.

innodb_flush_log_at_trx_commit—How innoDB will commit

4.    Hardware optimization

There are a few recommendations for improving the performance of MySQL by upgrading your hardware:

·         Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM.

·         Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.

·         If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can separate your data files, OS files and log files onto different physical disks.