Optimize MySQL three simple minor adjustments and mysql adjustments

Optimize MySQL three simple minor adjustments and mysql adjustments

I don't want to be an expert DBA, But when I optimize MySQL, I respect the 80/20 principle, specifically by simply adjusting some configurations, you can squeeze out performance improvements of up to 80%. Especially when server resources are getting cheaper and cheaper.

Warning

1. No two databases or applications are identical. Here, we assume that the database we want to adjust is a "typical" Web site service. The priority is fast query, good user experience, and massive traffic processing.

2. Back up your database before optimizing your server!

1. Use the InnoDB Storage Engine

If you are still using the MyISAM storage engine, it is time to convert it to InnoDB. There are many reasons why InnoDB is more advantageous than MyISAM. If you are concerned about performance, let's take a look at how they use physical memory:

• MyISAM: stores indexes only in memory.
• InnoDB: stores indexes and data in the memory.

Conclusion: The access speed of content stored in the memory is faster than that on the disk.

The following is a command to convert the storage engine to 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 the top priority.

2. Enable InnoDB to 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 number of physical memories allowed by InnoDB on your server.

In this case (assuming your server only runs MySQL), the "rule of thumb" is generally set to 80% of the physical memory of your server. After ensuring that the operating system does not use swap partitions and the required memory is sufficient for normal operation, allocate as much physical memory as possible for MySQL.

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 smaller than 1 GB. To apply this method, you should upgrade your server. (2) If your server memory is very large, for example, it has 200 GB, then according to general knowledge, you do not need to reserve up to 40 GB memory for the operating system. *

3. Multi-task InnoDB operation

If the parameters on the serverinnodb_buffer_pool_size The configuration is greater than 1 GB. The innodb_buffer_pool_instances parameter is set to divide the InnoDB buffer pool into multiple ones.

The advantages of having more than one buffer pool are:

A bottleneck may occur when multiple threads access the buffer pool simultaneously. You can minimize this contention by enabling multi-buffer pools:

The official suggestions for the number of buffer pools are as follows:

To achieve the best effect, we should consider innodb_buffer_pool_instances and innodb_buffer_pool_size to ensure that each instance has a buffer pool of at least 1 GB.

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

innodb_buffer_pool_instances = 24

Note!

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

sudo service mysql restart

There are more scientific methods to optimize these parameters, but these can be used as a general principle, which will make your MySQL Server better performance.

Summary

The above is a small Editor to introduce you to the optimization of MySQL three simple small adjustments, I hope to help you, if you have any questions, please leave a message, the small editor will reply to you in a timely manner. Thank you very much for your support for the help House website!