Shulou(Shulou.com)05/31 Report--

This article shows you what are the three simple adjustments to optimize MySQL, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

If you do not change the default configuration of MySQL, the performance of your server is like a Ferrari in the first gear.

I don't expect to be an expert DBA, but when I optimize MySQL, I advocate the 80Accord 20 principle, which makes it clear that you can squeeze up to 80% performance improvement by simply adjusting a few configurations. Especially at a time when server resources are getting cheaper.

Warning

No two databases or applications are identical. It is assumed that the database we are adjusting is for a "typical" Web site, giving priority to fast queries, a good user experience, and handling a large amount of traffic.

Before you optimize the server, please make a database backup!

1. Use the InnoDB storage engine

If you're still using the MyISAM storage engine, it's time to switch to InnoDB. There are many reasons why InnoDB has an advantage over MyISAM, and if you focus on performance, let's take a look at how they make use of physical memory:

MyISAM: keep the index in memory only.

InnoDB: holds indexes and data in memory.

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

Here are the commands to convert 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 is always a priority.

2. Let InnoDB use all the 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 allowed for InnoDB on your server.

For this (assuming your server is only running MySQL), the accepted "rule of thumb" is to set it to 80% of your server's physical memory. After ensuring that the operating system needs enough memory to function properly without swapping partitions, allocate as much physical memory to MySQL as possible.

Therefore, if your server's physical memory is 32 GB, you can set that parameter to up to 25 GB.

Innodb_buffer_pool_size = 25600m

* Note: (1) if your server memory is small and less than 1 GB. In order to apply the method of this article, you should upgrade your server. (2) if your server has a lot of memory, for example, it has 200 GB, then, according to common sense, you don't need to reserve up to 40 GB of memory for the operating system. *

3. Let InnoDB run multi-tasking

If the configuration of the parameter innodb_buffer_pool_size on the server is greater than 1 GB, the buffer pool of the InnoDB is divided into multiple according to the setting of the parameter innodb_buffer_pool_instances.

The benefits of having more than one buffer pool are:

Bottlenecks may be encountered when multiple threads access the buffer pool at the same time. You can minimize this contention by enabling multiple buffer pools:

The official recommendation for the number of buffer pools is:

In order to achieve the effect of * *, the settings of innodb_buffer_pool_instances and innodb_buffer_pool_size should be taken into account to ensure that each instance has at least 1 GB buffer pool.

So, in our example, on a server with 32 GB physical memory with the parameter innodb_buffer_pool_size set to 25 GB. An appropriate setting is 25600m / 24 = 1.06 GB

Innodb_buffer_pool_instances = 24 attention!

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

The above content of sudo service mysql restart is to optimize what are the three simple adjustments of MySQL? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.