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

Detailed steps for remote deployment of MySQL database on Linux

Linux remote deployment of MySQL database, for yo...

CSS scroll bar style modification code

CSS scroll bar style modification code .scroll::-...

MySQL/MariaDB Root Password Reset Tutorial

Preface Forgotten passwords are a problem we ofte...

SQL Get stored procedure return data process analysis

This article mainly introduces the analysis of th...

Why not use UTF-8 encoding in MySQL?

MySQL UTF-8 encoding MySQL has supported UTF-8 si...

How to manually deploy war packages through tomcat9 on windows and linux

The results are different in Windows and Linux en...

How to configure nginx to limit the access frequency of the same IP

1. Add the following code to http{} in nginx.conf...

WeChat applet development practical skills: data transmission and storage

Combining the various problems I encountered in m...

How to Fix File System Errors in Linux Using ‘fsck’

Preface The file system is responsible for organi...

MySQL controls the number of attempts to enter incorrect passwords

1. How to monitor MySQL deadlocks in production e...

What are your principles for designing indexes? How to avoid index failure?

Table of contents Primary key index Create indexe...

JavaScript to show and hide the drop-down menu

This article shares the specific code for JavaScr...

The pitfalls encountered when learning Vue.js

Table of contents Class void pointing ES6 Arrow F...

MySQL inspection script (must read)

As shown below: #!/usr/bin/env python3.5 import p...

How to view Linux ssh service information and running status

There are many articles about ssh server configur...