Shulou(Shulou.com)06/01 Report--

Today, Xiaobian will share with you the three tips of MySQL optimization. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you will gain something after reading this article. Let's find out together.

warning

1. No two databases or applications are identical. Assume that the database we are tuning is for a "typical" Web site, with priorities for fast queries, good user experience, and handling large amounts of traffic.

2. Before you optimize your server, make a database backup!

1. Use InnoDB storage engine

If you are still using MyISAM storage engine, it is time to switch to InnoDB. There are many reasons why InnoDB has an advantage over MyISAM, but if you're concerned about performance, let's take a look at how they utilize physical memory:

MyISAM: Save indexes only in memory.

InnoDB: Holds indexes and data in memory.

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

Here is how to convert storage engine commands on your table:

ALTER TABLE table_name ENGINE=InnoDB;

Note: You've created all the proper indexes, right? For better performance, creating indexes is always the first 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 InnoDB is allowed to use on your server.

The accepted "rule of thumb" for this (assuming your server runs only MySQL) is to set it to 80% of your server's physical memory. Allocate as much physical memory as possible for MySQL after ensuring that the operating system has enough memory to function properly without swapping partitions.

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

innodb_buffer_pool_size = 25600M

Note: (1) If your server memory is small and less than 1 GB. To apply the methods in this article, you should upgrade your server. (2) If your server memory is particularly large, say, it has 200 GB, then, according to common sense, you don't need to reserve as much as 40 GB for your operating system.

3. Let InnoDB multitask

If the parameter innodb_buffer_pool_size on the server is configured to be greater than 1 GB, the buffer pool of InnoDB will be divided into multiple buffer pools 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 simultaneously. You can minimize this contention by enabling multi-buffer pooling:

The official recommendation for the number of buffer pools is:

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

So, in our example, set the parameter innodb_buffer_pool_size 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

note

After modifying my.cnf file, MySQL needs to be restarted to take effect:

sudo service mysql restart

The above is "MySQL optimization of the three tips is what" all the content of this article, thank you for reading! I believe everyone has a great harvest after reading this article. Xiaobian will update different knowledge for everyone every day. If you want to learn more knowledge, please pay attention to 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.