Seven Tips for improving MySQL performance: mysql Performance

Seven Tips for improving MySQL performance: mysql Performance

Original article: 7 keys to better MySQL performance
Author: Peter Zaitsev
Translator: Peter

Note: As the size and load increase, MySQL performance tends to decline. Remember these tips to keep MySQL running smoothly.

One of the ways to measure an application is to look at performance. One of the performance indicators is the user experience. The popular saying is "Do users need to wait longer to get what they want ".

This indicator has changed in different application scenarios. For mobile shopping applications, the response time cannot exceed several seconds. It may take several seconds for an employee's Human Resources page.

There are many studies on how performance affects user behavior:

• 79% of customers are unlikely to return to slow websites
• 47% of consumers want the webpage to be loaded in 2 seconds or less
• 40% of users give up when the website loading time exceeds 3 seconds
• The one-second delay of page loading time may cause 7% loss, and the page views are reduced by 11%.

No matter what standards are adopted, good application performance must be maintained. Otherwise, the user will complain (or worse, switch to different applications ). One of the factors that affect application performance is database performance. Interactions between applications, websites, and databases are critical to building application performance.

A core component of this interaction is how applications query databases and how databases respond to requests. MySQL is one of the most popular database management systems. In the production environment, more and more enterprises are turning to MySQL (and other open source databases) as database solutions.

There are many ways to configure MySQL to help ensure that the database responds quickly to queries and minimize application performance.

The following are some basic tips for optimizing MySQL database performance.

Optimization Tips #1: Learn How to Use EXPLAIN

The two most important decisions made by using any database are to design how the relationship between application entities maps to tables (database mode ), and how to design an application to obtain the required data (query) in the desired format ).

Complex applications can have complex modes and queries. If you want the performance and scalability required by the application, you cannot simply rely on intuition to understand how to execute the query.

You should learn how to use the EXPLAIN command instead of making guesses and imagination at will. This command shows how to execute a query and shows you the expected performance and how the query scales as the data size changes.

Many tools, such as MySQLWorkbench, can visualize the EXPLAIN output, but still need to understand the basic knowledge to understand it.

The EXPLAIN command provides two different formats for output: The old table style and the more modern structured JSON document, which provides more details (as shown below ):

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G*************************** 1. row ***************************EXPLAIN: {  “query_block”: {    “select_id”: 1,    “cost_info”: {      “query_cost”: “762.40”    },    “table”: {      “table_name”: “sbtest1”,      “access_type”: “range”,      “possible_keys”: [        “PRIMARY”      ],      “key”: “PRIMARY”,      “used_key_parts”: [        “id”      ],      “key_length”: “4”,      “rows_examined_per_scan”: 1874,      “rows_produced_per_join”: 1874,      “filtered”: “100.00”,      “cost_info”: {        “read_cost”: “387.60”,        “eval_cost”: “374.80”,        “prefix_cost”: “762.40”,        “data_read_per_join”: “351K”      },      “used_columns”: [        “id”,        “k”      ],      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”    }  }}

One component to be viewed is "query cost ". Query cost is the cost of a specific query that MySQL considers based on the total overhead of query execution, and is based on many different factors.

The query overhead of a simple query is usually less than 1,000. A query with an overhead of 1,000 to 100,000 is considered to be a medium overhead query. If you only run hundreds of such queries per second (instead of tens of thousands), it is usually faster.

Queries with an overhead of more than 100,000 can be considered expensive. Generally, when you are a single user on the system, these queries will still run quickly, however, you should carefully consider the frequency of using such queries in interactive applications (especially as the number of users increases ).

Of course, these numbers are only a rough embodiment of performance, but they demonstrate general principles. Your system may better handle query workloads, or worse, depending on its architecture and configuration.

The main factor determining the query overhead is whether the query uses the index correctly. The EXPLAIN Command tells you whether to use an index (usually because of how the index is created in the database or how the query is designed ). This is why it is so important to learn to use EXPLAIN.

Optimization Tips #2: Create the correct index

Indexes increase query efficiency by reducing the amount of data in the database that must be scanned by queries. The indexes in MySQL are used to accelerate access to the database and help execute database constraints (such as UNIQUE and foreign key ).

Database indexes are similar to book indexes. They are stored in their own locations and contain information that already exists in the primary database. They are reference methods or mappings pointing to the data location. Indexes do not change any data in the database. They only point to the data location.

No indexes are fully applicable to any workload. You should always view the index in the context of the system running query.

A database with good indexes not only runs faster, but also slows down the database even if one index is missing. Use EXPLAIN (as described earlier) to search for missing indexes and add them. But be careful: do not add indexes you don't need! Unnecessary indexes reduce the database speed.
(Please refer to the introduction to MySQL index best practices ).

Optimization Tips #3: reject the default settings

Like any software, MySQL has many configurable settings for modifying behavior (and final performance ). Like any software, administrators ignore many of these configurable settings and use them in default mode.

To get the best performance from MySQL, it is very important to understand the configurable MySQL settings, and more importantly, set them to the most suitable database environment for you.

By default, MySQL is used for small-scale development and installation, rather than for production. You usually want to configure MySQL to use all available memory resources and allow the number of connections required by the application.

The following are three MySQL performance optimization settings. You should always check carefully:

Innodb _ buffer _ pool_size: the buffer pool is used to store cache data and indexes. This is the main reason for using a system with large-capacity RAM as a database server. If you only run the InnoDB Storage engine, 80% of the memory is usually allocated to the buffer pool. If you are running a very complex query, or have a large number of concurrent database connections, or a large number of tables, you may need to lower this value by one level, to allocate more memory for other operations.

When setting the InnoDB buffer pool size, make sure that it is not set too large, otherwise it will lead to swap. This will definitely affect the database performance. A simple check method is to view the exchange activities in the Percona Monitoring and Management System Overview diagram:

, Sometimes some exchanges are acceptable. However, if you see 1 MB or more switching activities per second, you need to reduce the buffer pool size (or other memory usage ).

If the value of innodb _ Buffer _ pool _ size is not correctly obtained during the first access, do not worry. From MySQL5.7, You can dynamically change the InnoDB buffer pool size without restarting the database server.

Innodb _ log _ file _ size: the size of a single InnoDB log file. By default, InnoDB uses two values, so that you can double this number to obtain the InnoDB space used to ensure the persistent loop redo log space of the transaction. This also optimizes the application of changes to the database. Setting innodb _ log _ file _ size is a trade-off. The larger the allocated redo space, the better the performance for write-intensive workloads. However, if the system is powered off or has other problems, the longer the crash recovery time will be.

How do I know if MySQL performance is limited by the current InnoDB log file size? You can determine the number of available redo log spaces used. The easiest way is to view the Percona Monitor and Management InnoDB Metrics dashboard. In, the InnoDB log file size is not large enough, because the space used is very close to the available redo log space (indicated by the red line ). The size of the log file should be at least 20% larger than the space used to keep the system running.

MAX _ Connections: the number of Connections of large applications is usually higher than the default value. Unlike other variables, if you do not set it correctly, there will be no performance problems (itself ). On the contrary, if the number of connections is insufficient to meet your application's needs, your application will not be able to connect to the database (in your opinion, this is like the downtime ). Therefore, it is important to correctly process this variable.

If you run complex applications with multiple components on multiple servers, it is difficult to know how many connections are needed. Fortunately, MySQL can easily see how many connections are used during peak operations. Generally, you want to ensure that the maximum number of connections used by the application is at least 30% different from the maximum number of available connections. One easy way to view these numbers is to use the MySQL connection diagram in the MySQL overview dashboard monitored and managed by Percona. Shows a sound system with a large number of additional connections available.

One thing to remember is that if the database runs slowly, applications usually create too many connections. In this case, you should handle database performance issues, rather than simply allowing more connections. More connections make the underlying performance worse.

(Note: When you set max_Connections to a variable that is significantly higher than the default value, you usually need to consider adding other parameters, such as the table cache size and the number of opened MySQL files. However, this does not fall into the scope discussed in this article .)

Optimization Tips #4: Save the database in the memory

In recent years, we have seen the transition to SSD. Although SSDs are much faster than rotating hard disks, they still cannot be compared with data in RAM. This difference comes not only from the storage performance, but also from the additional work required by the database to retrieve data from the disk or SSD storage.

With the improvement of the latest hardware, whether it is running on the cloud or managing its own hardware, it is increasingly possible to store the database in the memory.

Better news is that you don't need to put all the databases into the memory, you can get most of the performance advantages in the memory. You only need to save the work data (the most frequently accessed data) set to the memory.

You may have seen some articles that provide specific numbers to indicate which part of the database should be saved in the memory, ranging from 10% to 33%. In fact, there is no one-size-fits-all number. The amount of data suitable for the memory's best performance advantages depends on the workload. Instead of looking for a specific "omnipotent" number, check the I/O that the database runs in its stable state (usually several hours after startup ). READ, because if the database is in the memory, the READ can be completely eliminated. Write always happens, no matter how much memory you have available.

You can see the I/O in the InnoDB I/O diagram in the InnoDBMetrics dashboard monitored and managed by Percona.

In the above chart, you can see a peak of up to 2,000 I/O operations per second, which indicates that (at least for some parts of the workload) The database working set is not suitable for memory.

Optimization Tips #5: SSD Storage

If your database is not suitable for memory (even if not), you still need to store it quickly to handle write operations and avoid performance problems when the database is warmed up (after restart. Today, SSD is synonymous with fast storage.

For cost or reliability reasons, some "experts" still advocate the use of Rotating Disks (mechanical disks ). Frankly speaking, when it comes to database operations, these arguments are often outdated or totally wrong. Today, SSD provides considerable performance and reliability at a high price.

However, not all SSDs are applicable. For database servers, you should use an SSD designed for server workloads, which protects data (for example, during power outages ). Avoid using commercial SSDS designed for desktop computers and laptops.

SSD connected through NVMe or Intel OpTan technology provides optimal performance. Even if you remotely connect to a SAN, NAS, or cloud block device, SSD delivers superior performance compared to a rotating disk.

Optimization Tips #6: horizontal scaling

Even high-performance servers have their own limitations. There are two extension methods: up and out. Vertical Scaling means buying more hardware. This may be expensive and the hardware will soon become obsolete. There are several advantages for horizontal scaling to handle more loads:

1. small and low-cost systems can be used. 2. linear expansion is faster and easier through horizontal expansion. 3. Because databases are distributed across multiple physical machines, the database will not be affected by a single hardware fault point.

Although horizontal scaling is advantageous, it also has some limitations. Replication is required for expansion, such as basic MySQL replication or Percona XtraDB Cluster, to achieve data synchronization. However, in return, additional performance and high availability are available. If you need more extensions, use MySQL sharding.

You also need to ensure that applications connected to the cluster architecture can find the required data-typically through some proxy servers and load balancers (such as ProxySQL or HAProxy ).

Avoid Premature scaling when planning horizontal scaling. Distributed databases are often more complex. Modern hardware and MySQL servers can have a good experience by using only one server. The latest MySQL 8 candidate version shows that it can process more than 2 million simple queries on a single system.

Optimization Tips #7: Observability

The observability should be taken into account when designing the best system-MySQL is no exception ..

Once you have started, run, and correctly adjusted the MySQL environment, you cannot just set and manage it. The database environment will be affected by system or workload changes. Prepare for incidents such as traffic peaks, application errors, and MySQL faults. These things can and will happen.

When problems occur, you need to solve them quickly and effectively. The only way to do this is to set a monitoring solution and initialize it properly. This allows you to see what is happening in the database environment during production and analyze server data when a problem occurs. Ideally, the system allows you to prevent problems before they occur or before they become visible to users.

Monitoring tools include MySQL Enterprise Monitor, Monyog, and Percona Monitoring and Management (PMM). The latter has additional free and open-source advantages. These tools provide good operability for monitoring and troubleshooting.

As more and more companies turn to open-source databases (especially MySQL) to manage and serve their business data in a large-scale production environment, they will need to focus on optimizing these databases and optimizing their operational efficiency. As with all things that are critical to your business goals, your database performance may cause or damage your business goals or achievements. MySQL is a database solution that can provide quality services for applications and websites. However, it needs to be adjusted to meet your needs and be monitored to detect and prevent bottlenecks and performance problems.

Peter Zaitsev is the co-founder and CEO of Percona and a provider of enterprise-level MySQL and MongoDB solutions and services at Percona. High Performance MySQL, published by o'reilly, is one of the most popular MySQL Performance books. Zaitsev often posts blogs on PerconaDatabasePerformanceBlog.com and speaks at conferences around the world.