At first I want to clarify that this is only a guideline for tuning your MySQL Performance, due to the fact that every MySQL Server is used for custom purposes and needs. There can be huge differences for application use, like READ-heavy or WRITE-heavy databases.
I recommend advanced knowledge of MySQL to understand most of the things explained in the following part.
This post includes some basic tips to increase your MySQL Server performance. Maybe I will write some other posts about more specific topics, like buffers or caching.
Tips for MySQL performance optimization:
- Storage Engine:
In my last post I described and explained the differences, advantages and disadvantages of the most popular MySQL storage engines: MyISAM vs. InnoDB. The choice of the storage engine that fits best to your needs is very important. If you have done the correct selection this can have huge performance enhancement and more features too!
- Table optimization:
Another common problem is table fragmentation, which can have great performance impact too. The main problem with fragmentation is bad ordering of indexes on disks, which results in slower query processing due to I/O issues.
On my github profile you will find a tool for checking and optimizing your tables: MySQL fragmentation tool.
Here is a short explanation how you can use this:
# Show table count of fragmented tables in your databases
./mysqlfragfinder.sh --user $USER --password $PASS --check
# Show fragmented tables in your databases
./mysqlfragfinder.sh --user $USER --password $PASS --check --detail
# Optimize all tables
./mysqlfragfinder.sh --user $USER --password $PASS
# Optimize all tables of one specific database
./mysqlfragfinder.sh --user $USER --password $PASS --database $DB
So for better performance you should optimize your tables regularly. But beware of using this with productive databases, because optimizing a table will lock it!
- Tools for analysis:
This section is only for advanced users, because you need experience in MySQL server configuration. There are some tools with which you can analyze your MySQL server very well. With the help of them, you are able to find possible configuration failures or optimization targets. You find them on my github account too: MySQL Tuner and MySQL Report.
MySQL Tuner itself displays tips and data in its output, whereas MySQL Report displays more detailed data. Besides MySQL Report has a really great guide in which their output is explained: Guide for MySQL Report.
Both tools are really helpful, but they will not do the configuration for you. So be sure you know what you are doing if you follow their tips!
My recommendation is taking a closer look on following topics:
- Slow queries
You should ensure that the slow query log is enabled in your configuration. If you have slow queries try to get rid of them immediately! There is nothing worse than queries, which keep your MySQL server stuck.
- Key buffer
The key buffer is very important for your indexes and you should always guarantee that there is enough free buffer space left. With the help of this the indexes of the tables are processed in memory and not on disks. So if you use them this will result in a huge performance boost.
- Query cache
The query cache is used by SELECT-statements which for example get executed very often and their result changes very rarely. If they are cached the result comes out of the memory, which is much faster than from disk. So you should set the query cache value high enough to profit from this advantage.
- InnoDB buffer pool
This is only available within the InnoDB storage engine and it should be set to the size of all InnoDB tables plus a margin, so that they can expand. The buffer pool is one huge advantage of InnoDB, because it reads additional data from memory too.
Of course most of the options and variables depend on your custom purposes and vary due to that. Because of that you need to check the special output of the tools for possible problems by yourself.
I hope this tutorial gave you some hints and tips to optimize your MySQL server. If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!
If my time permits and there is some demand, I will write some posts on more specific topics of this post too.
