Optimize MySQL with 3 simple tweaks

Optimize MySQL with 3 simple tweaks

I don't expect to be an expert DBA, but when I optimize MySQL, I believe in the 80/20 rule, which specifically states that by simply adjusting some configurations, you can squeeze out up to 80% performance improvement. Especially now that server resources are becoming increasingly cheaper.

warn

1. No two databases or applications are exactly the same. Here we assume that the database we are tuning is serving 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 back up the database!

1. Use InnoDB storage engine

If you are still using the MyISAM storage engine, it is time to switch 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 memory.

Conclusion: Content stored in memory can be accessed faster than on disk.

Here is how to switch 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 indexes should always be 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 generally 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 properly without using swap partitions, 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 as much as 25 GB.

innodb_buffer_pool_size = 25600M

*Note: (1) If your server memory is small and less than 1 GB. In order to use the methods in this article, you should upgrade your server. (2) If your server has a large amount of memory, for example, 200 GB, then, based on common sense, you do not need to reserve up to 40 GB of memory for the operating system. *

3. Let InnoDB run in multiple tasks

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

The benefits of having more than one buffer pool are:

Bottlenecks may occur when multiple threads access the buffer pool simultaneously. 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 at least 1 GB of buffer pool.

So, in our example, the parameter innodb_buffer_pool_size is set 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

Notice!

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

sudo service mysql restart

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

Summarize

The above are 3 simple adjustments to optimize MySQL that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:
  • MySQL 4G memory server configuration optimization
  • MYSQL development performance research: optimization method for batch inserting data
  • Summary of ten principles for optimizing basic statements in MySQL
  • Some methods to optimize query speed when MySQL processes massive data
  • Optimization methods when Mysql occupies too high CPU (must read)
  • MySQL Optimization: Cache Optimization
  • MySQL Optimization: InnoDB Optimization
  • How to optimize the speed of inserting records in MySQL
  • A brief talk about MySQL optimization tool - slow query

<<:  JS native 2048 game source code sharing (the latest on the Internet)

>>:  Debian virtual machine created by VirtualBox shares files with Windows host

Recommend

Tutorial on how to quickly deploy a Nebula Graph cluster using Docker swarm

1. Introduction This article describes how to use...

MySQL REVOKE to delete user permissions

In MySQL, you can use the REVOKE statement to rem...

A brief introduction to the general process of web front-end web development

I see many novice students doing front-end develop...

How to compile the Linux kernel

1. Download the required kernel version 2. Upload...

Object-Oriented Programming with XHTML and CSS

<br />If only XHTML and CSS were object-orie...

Detailed explanation of the principle of Docker image layering

Base image The base image has two meanings: Does ...

How to use React to implement image recognition app

Let me show you the effect picture first. Persona...

Implementation of CSS text shadow gradually blurring effect

text-shadow Add a shadow to the text. You can add...

3 ways to correctly modify the maximum number of connections in MySQL

We all know that after the MySQL database is inst...

How to use Vue3 mixin

Table of contents 1. How to use mixin? 2. Notes o...

How to solve the timeout during pip operation in Linux

How to solve the timeout problem when pip is used...