MySQL performance optimization

MySQL is one of the most popular relational database management systems used to power the majority of websites on the Internet. The speed of the site’s work depends on the speed of writing and receiving data from the tables, in general, since if it takes more than a second for one request, it will slow down the work of php, and as a result, so many requests will soon accumulate that the server will not be able to process them.
***

Optimization without analytics is pointless. Before moving on to optimization, let’s see how the database works now, if there are queries that are very slow. All settings for your mysql service are located in the /etc/my.cnf file. To enable the display of slow queries add the following lines to my.cnf, in the [mysqld] section:
log-slow-queries = / var / log / mariadb / slow_queries.log
long_query_time = 5

***

For speed testing and is used more for debugging code and creating tables correctly. Then restart the database server and see the log:
systemctl restart mariadb
tail -f /var/log/mariadb/slow-queries.log

We can see that there are requests that take more than 10 seconds to complete.

Here, too, time is measured, and we see the result – three seconds. This is a lot. And nothing else, if such requests rarely come, if your site is constantly under load, then you will not get off in three seconds, the number of unprocessed requests will grow, and the response speed will increase up to several minutes. You can go in two ways – optimize the code, remove complex queries, or you need to optimize mysql on the server.

List of recommendations for customization MYSQL server.All parameters need to be added to /etc/my.cnf.
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=16
skip-name-resolve=1
innodb_buffer_pool_size=800M
innodb_log_file_size=200M

The innodb log file size should be 25% of the buffer size. In the case of 800 megabytes, this would be 200M. But there is one problem here. To change the size of the log, you need to perform several steps. Since we have changed all the necessary parameters, we will proceed to reboot the server. For our log, you need to stop the service:
systemctl stop mariadb
Then move the log files to / tmp:

mv / var / lib / mysql / ib_logfile [01] / tmp
systemctl start mariadb

When the size of the log changes, the service sees a damaged log, issues an error and does not start. Therefore, you must first delete the old one.

***

As you can see, optimization of mysql is quite simple due to this rule, but at the same time, such an operation can help a lot, especially for highly loaded projects. The only thing that can speed things up even better is the optimization of mysql queries. Remember to check the parameters from time to time to make sure everything is in order.