Optimization Mysql:3 a simple small adjustment

I don't expect to be an expert DBA, but when I optimize MySQL, I'm advocating the 80/20 principle, which makes it clear that by simply tweaking some configurations, you can squeeze up to 80% performance gains. Especially at a moment when server resources are getting cheaper.

Warning

1. No two databases or applications are exactly the same. This assumes that the database we are adjusting is for a "typical" web site, with a high priority of fast queries , a good user experience , and the processing of large amounts of traffic .

2. Before you optimize the server, please do a good job of database backup !

1. Using the InnoDB storage engine

If you're still using the MyISAM storage engine, it's time to switch to InnoDB.

There are many reasons to suggest that InnoDB is more advantageous than MyISAM, and if you focus on performance, let's look at how they utilize physical memory:

MyISAM: The index is saved only in memory.
InnoDB: saves the index and data in memory .
Conclusion: The content of memory is faster than on disk.

Here's how to convert the storage engine's commands 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 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 that is allowed InnoDB on your server.

For this (assuming that your server only runs MySQL), the accepted "rule of thumb" is set to 80% of the physical memory of your server.

Allocate as much physical memory as possible to MySQL after ensuring that the operating system does not use the swap partition for sufficient memory to function properly.

Therefore, if your server's physical memory is a maximum of GB, you can set that parameter to up to.

innodb_buffer_pool_size = 25600M

Attention:

(1) If your server memory is small and less than 1 GB. In order to apply the method in this article, you should upgrade your server.

(2) If your server memory is particularly large, for example, it has a GB, then, according to general knowledge, you do not need to reserve up to five GB of memory for the operating system.

3, let InnoDB multi-task operation

If the configuration of the parameter innodb_buffer_pool_size on the server is greater than 1 GB, the buffer pool for InnoDB is divided into multiple innodb_buffer_pool_instances based on the settings of the parameter.

The benefits of having more than one buffer pool are:

You may encounter bottlenecks when accessing the buffer pool simultaneously in multiple threads. You can minimize this race condition by enabling the multi-buffer pool:

The official recommendations for the number of buffer pools are:

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

Therefore, in our example, the parameter innodb_buffer_pool_size is set to be on a server that has up to GB of physical memory. A suitable setting is 25600M/24 = 1.06 GB

innodb_buffer_pool_instances = 24

Attention!

A restart of MySQL is required after modifying the my.cnf file to take effect:

sudo service mysql restart

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

Optimization Mysql:3 a simple small adjustment