Optimize MySQL with 3 simple tweaks

I don't expect to be an expert DBA, but when I optimize MySQL, I believe in the 80/20 rule, which specifically states that by simply adjusting some configurations, you can squeeze out up to 80% performance improvement. Especially now that server resources are becoming increasingly cheaper.

warn

1. No two databases or applications are exactly the same. Here we assume that the database we are tuning is serving a "typical" Web site, where the priorities are fast queries, a good user experience, and handling large amounts of traffic.

2. Before you optimize the server, please back up the database!

1. Use InnoDB storage engine

If you are still using the MyISAM storage engine, it is time to switch to InnoDB. There are many reasons why InnoDB is more advantageous than MyISAM. If you are concerned about performance, let's take a look at how they use physical memory:

• MyISAM: Stores indexes only in memory.
• InnoDB: Stores indexes and data in memory.

Conclusion: Content stored in memory can be accessed faster than on disk.

Here is how to switch the storage engine on your table:

ALTER TABLE table_name ENGINE=InnoDB;

NOTE: You have created all the appropriate indexes, right? For better performance, creating indexes should always be the first priority.

2. Let InnoDB use all memory

You can edit your MySQL configuration in the my.cnf file. Use the innodb_buffer_pool_size parameter to configure the amount of physical memory InnoDB is allowed to use on your server.

The generally accepted "rule of thumb" for this (assuming your server is only running MySQL) is to set it to 80% of your server's physical memory. After ensuring that the operating system has enough memory to run properly without using swap partitions, allocate as much physical memory as possible to MySQL.

So, if your server has 32 GB of physical memory, you can set that parameter to as much as 25 GB.

innodb_buffer_pool_size = 25600M

*Note: (1) If your server memory is small and less than 1 GB. In order to use the methods in this article, you should upgrade your server. (2) If your server has a large amount of memory, for example, 200 GB, then, based on common sense, you do not need to reserve up to 40 GB of memory for the operating system. *

3. Let InnoDB run in multiple tasks

If the parameter innodb_buffer_pool_size on the server is configured to be larger than 1 GB, the InnoDB buffer pool will be divided into multiple instances according to the setting of the parameter innodb_buffer_pool_instances.

The benefits of having more than one buffer pool are:

Bottlenecks may occur when multiple threads access the buffer pool simultaneously. You can minimize this contention by enabling multiple buffer pools:

The official recommendation for the number of buffer pools is:

For best results, consider the settings of innodb_buffer_pool_instances and innodb_buffer_pool_size to ensure that each instance has at least 1 GB of buffer pool.

So, in our example, the parameter innodb_buffer_pool_size is set to 25 GB on a server with 32 GB of physical memory. A suitable setting is 25600M / 24 = 1.06 GB

innodb_buffer_pool_instances = 24

Notice!

After modifying the my.cnf file, you need to restart MySQL for the changes to take effect:

sudo service mysql restart

There are more scientific ways to optimize these parameters, but these points can be applied as a general guideline to make your MySQL server perform better.

Summarize

The above are 3 simple adjustments to optimize MySQL that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!