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

This article shows you what MySQL optimization adjustments are, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

I don't expect to be an expert DBA, but when I optimize MySQL, I believe in the 80/20 principle, which specifically means that by simply tweaking a few configurations, you can squeeze out up to 80% performance gains. Especially when server resources are getting cheaper.

warning

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

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:

1

2

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.

1

2

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 is configured to be larger than 1 GB on the server, 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

1

2

innodb_buffer_pool_instances = 24

Attention!

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

1

2

sudo service mysql restart

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

What are the minor adjustments to MySQL optimization? Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, 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.