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.
