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

This article mainly introduces the optimization of MySQL tips, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

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!

This article mainly brings you to optimize the MySQL 3 simple small adjustments, need friends to refer to, hope to help you.

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.

Related recommendations:

Analyze and optimize the efficiency of Mysql multi-table joint query

Optimizing mysql skills on linux

A detailed explanation of the method of modifying the data page size of Innodb to optimize MySQL

Thank you for reading this article carefully. I hope the article "Tips for optimizing MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.