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

This article will explain in detail the three tips for optimizing MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

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

1. 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.

two。 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 an index is always a top 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:

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

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

Be careful!

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

Sudo service mysql restart

There are more scientific ways to optimize these parameters, but these can be used as a general guideline that will improve the performance of your MySQL server.

This is the end of this article on "three Tips for optimizing MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.