MySQL Performance Optimization
The most important thing when it comes to Database Performace Optimization is to pick the right one. You should decide whether your application needs a Relational or Non-Relational database. Even inside one type, you will have various options to choose from. As in the relational database, you may find Oracle, MySQL, SQL Server, and PostgreSQL. On the other hand, The non-relational database introduces MongoDB, Cassandra, and CouchDB. You may expect me to suggest using the non-relational database for faster read/write performance. However, with some refinements and tuning, you can push a relational database beyond its known limitation. So in this article, I am going to introduce you some tips in order to make your MySQL database much faster. If you wonder why MySQL in particular, then the answer is easy because it’s free, open source and so popular in the PHP community where Oracle is not widely used because it’s expensive. And the other options are not favored as much as MySQL.
- MySQL Server Configuration:
Well, first you should know where the configuration file is located depending on your operating system. In Linux systems, it is located in “/etc/mysql/my.cnf”.
Now it is time to pick your engine InnoDB vs. MyISAM. To make the choice easier, you should know that InnoDB became the default engine starting of MySQL 5.5 as it supports “row level locking, foreign keys and transactions” and MyISAM doesn’t support any of the features mentioned which make it rarely useful in modern applications.
After picking the right engine, it is time for setting some configuration variables in my.cnf file.
max_connection Variable:
The max_connection variable represents how many connections are allowed from your application. The default value is 151 connection, however, you can easily increase this number if you got an error says ‘MySQL Error, Too many connections…’
max_connections = 170
innodb_buffer_pool_size Variable:
To speed up things, MySQL will cache data in your server memory, and this variable is telling MySQL how many GigaBytes it can use. This variable is very useful if you save big blobs in your database. You can set it to 80–90% of your server memory. So if your server memory is 16GB, you can set it to 14GB.
innodb_buffer_pool_size = 14GB
innodb_io_capacity Variable:
This variable tells MySQL how many Inputs/Outputs operation it can use and it depends on your disk. For example, a single 7200 RPM drive is limited to 200 I/O and an enterprise SSD disk is 50,000 I/O. You can easily find inputs/outputs values by command line on your operating system and set the variable to 90% of the available I/O, so MySQL will never use too many I/O operations.
innodb_io_capacity = 21000
query_cache_limit and query_cache_size Variables:
Well, MySQL caches data in memory too, however, we can’t rely on it as a caching system since every time your application writes to a database table, MySQL will reset the query cache for the entire table. So, if your application has a high rate load, the MySQL cache will be completely useless, and in that case, it would be better to set these two variables to zero saving the overhead of managing the MySQL cache, and instead you can use something like Redis to manage the cache.
query_cache_limit = 0
query_cache_size = 0
Slow Query Log:
The slow query log will show you which queries are taking longer than the threshold you defined without the need to guess which query is slower.
First, you have to enable slow_query_log in your configuration file. On Linux server, open “/etc/mysql/my.cnf” or equivalent on your system.
and add:
slow_query_log = 1
long_query_time = 1
Well, these two options will enable the slow query log and will log any query that takes more than one second. If you prefer to see your logs in a table instead of a file, you may add:
log_output = ‘TABLE’
Then you can find your logs in a table called ‘slow_log,’ where you can see information about all the slow queries that take more than one second to execute. The information includes when exactly the query is executed and how many rows are affected by the query and which user executed it.
Query Optimization:
After you got all the slow queries, you need to find a way to optimize them and make them faster. So you can add the word “explain” before the query to get detailed information about the query, e.g: “explain select * from users where active=1;”
The “explain” keyword can help you to define which indexes are hit by your query and number of rows that have been queried in order to get your data. This information can tell you if you need to create more indexes or restructure your database tables.
Denormalization and Constraints:
The denormalization is the process of improving the read performance by adding redundant data or grouping them. For example, if you have a “product” table and a “category” table and every time you query the “products” table you also need to get the “category_name” of each product. In this case, you may use “join” to retrieve the “category_name.” However, this means that every time a user will open a product page, a complicated join query will be executed. So you may consider adding the “category_name” in the “product” table. In spite of the redundant data, the improvement in the read performance makes it worth.
The denormalization approach may cause an out-dated “category_name” in the “product” table. So you need to define a “foreign key” constraint, but you need to know that a “foreign key” will make the write performance a little bit slower because MySQL needs to check the constraints before writing the data. So it will always be your task to determine what the best options are to improve your database performance.
More to Read:
- Write clean code and get rid of code smells with real life examples
- Software Architecture: Architect Your Application with AWS
- Software Architecture — The Difference Between Architecture and Design
- Advanced Coding Skills, Techniques, and Ideas
