3 simple ways to optimize MySQL

1. No two databases or applications are exactly the same. It is assumed here that the database we are tuning is to serve a "typical" Web site, where the priorities are fast queries, a good user experience, and handling large amounts of traffic.

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

This article mainly brings you 3 simple small adjustments to optimize MySQL. Friends who need it can refer to it. I hope it can help everyone.

1. Use InnoDB storage engine

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

• MyISAM: In-memory only Save the index.

• InnoDB: Save indexes and data in memory.

Conclusion: Contents stored in memory are accessed faster than those on disk.

Here's how to convert the storage engine command on your table:

ALTER TABLE table_name ENGINE=InnoDB;

Note: You have created all the appropriate indexes, right? For better performance, creating indexes is always the first priority.

2. Let InnoDB use all 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 is only running MySQL) is to set it to 80% of your server's physical memory. After ensuring that the operating system has enough memory to run normally without using the swap partition, allocate as much physical memory as possible to MySQL.

So if your server has 32 GB of physical memory, 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 methods in this article, you should upgrade your server. (2) If your server has a particularly large memory, for example, it has 200 GB, then according to common sense, you do not need to reserve up to 40 GB of memory for the operating system. *

3. Let InnoDB multi-task run

If the configuration of the parameter innodb_buffer_pool_size on the server is greater than 1 GB, the InnoDB buffer pool will be divided into multiple ones according to the setting of the parameter innodb_buffer_pool_instances.

The benefits of having more than one buffer pool are:

You may encounter bottlenecks 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 innodb_buffer_pool_instances and innodb_buffer_pool_size settings 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

Attention!

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

sudo service mysql restart

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

Related recommendations:

Analysis and optimization of Mysql multi-table joint query efficiency

Tips for optimizing mysql on Linux

Detailed example of how to modify the data page size of Innodb to optimize MySQL

The above is the detailed content of 3 simple ways to optimize MySQL. For more information, please follow other related articles on the PHP Chinese website!