MySQL performance tends to drop. Remember these tips to keep MySQL running smoothly.

Original: 7 keys to better MySQL performance
　　
Peter Zaitsev
　　
Translator: Peter
　　
Translator Note: MySQL's performance tends to drop as the size and load increase. Remember these tips to keep MySQL running smoothly.
　　
Id
　　
One way to measure an application is to look at performance. One of the performance indicators is the user experience, the popular saying is "Whether users need to wait longer time to get what they want."
　　
This indicator has been changed in different application situations. For mobile shopping apps, the response time cannot be longer than a few seconds. For the employee's human Resources page, it may take a few more seconds.
　　
There are a number of studies on how performance affects user behavior:
　　
79% of customers are unlikely to return to slow sites
　　
47% of consumers want the page to finish loading in 2 seconds or less
　　
40% of users will give up when the site loads for more than 3 seconds
　　
1-second delay in page load time may result in 7% loss, page views reduced by 11%
　　
No matter what the standard, it is necessary to maintain good application performance. Otherwise, the user complains (or worse, goes to a different application). One of the factors that affect application performance is database performance. The interaction between applications, sites, and databases is critical to building application performance.
　　
One of the core components of this interaction is how the application queries the database and how the database responds to requests. In any case, MySQL is one of the most popular database management systems. In production environments, more and more businesses 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 to minimize application performance.
　　
Here are some basic tips to help optimize the performance of your MySQL database.
　　
Optimization Tips #1: Learn how to use EXPLAIN
　　
The two most important decisions you make with any database are to design how the relationships between application entities map to tables (database schemas), and how to design your application to get the data (queries) you want in the format you want.
　　
Complex applications can have complex patterns and queries. If you want the performance and extensibility your application needs, you can't just rely on intuition to understand how to execute a query.
　　
You should learn how to use explain commands instead of random guesses and imaginations. This command shows how to execute the query and let you know what performance you expect, and how the query will scale as the data size changes.
　　
There are many tools-such as mysqlworkbench–-to visualize explain output, but you still need to understand the basics to understand it.
　　
The explain command provides output in two different formats: an old-fashioned tabular format and a more modern structured JSON document, which provides more detail (see below):
　　
Mysql> explain Format=json select AVG (k) from Sbtest1 where ID between and \g
　　
1. Row ***************************
　　
EXPLAIN: {
　　
"Query_block": {
　　
"select_id": 1,
　　
"Cost_info": {
　　
"Query_cost": "762.40"
　　
},
　　
"Table": {
　　
"table_name": "Www.yibaoyule1.com sbtest1",
　　
"Access_type": "Range",
　　
"Possible_keys": [
　　
"PRIMARY"
　　
],
　　
"Key": "PRIMARY",
　　
"Used_key_parts" www.jcx127.cn: [
　　
"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": "Www.471060.com 762.40",
　　
"Data_read_per_join": "351K"
　　
},
　　
"Used_columns": [
　　
"id",
　　
K
　　
],
　　
"Attached_condition": "Www.huachengjpt.com (' sbtest ' www.feilinyule.cn. ' Sbtest1 '. ' ID ' between + 2000)"
　　
}
　　
}
　　
}
　　
One component that should be viewed is "query cost". Query cost means that MySQL considers the costs of this particular query based on the total cost of query execution and is based on many different factors.
　　
The query cost of a simple query is typically less than 1,000. Queries that cost between 1,000 and 100,000 are considered to be medium-cost queries, and it is usually faster to run only hundreds of such queries per second (instead of tens of thousands of).
　　
Queries that cost more than 100,000 can be considered expensive. Typically, these queries will still run quickly when you are a single user on the system, but you should carefully consider how often such queries are used in interactive applications (especially as the number of users grows).
　　
These numbers are, of course, just a general representation of performance, but they show common principles. Your system may be better able to handle query workloads or worse, depending on its architecture and configuration.
　　
The main factor determining query cost is whether the query uses the index correctly. The EXPLAIN command can tell you whether the query uses an index (usually because the index is created in the database or how the query itself is designed). That's why it's so important to learn how to use EXPLAIN.
　　
Optimization Tips #2: Creating the correct index
　　
Indexes improve query efficiency by reducing the amount of data in the database that the query must scan. The indexes in MySQL are used to speed up access in the database and to help enforce database constraints such as unique and foreign KEY.
　　
A database index is much like a book index. They are stored in their own location and contain information that already exists in the primary database. They are reference methods or mappings that point to where the data is located. The index does not change any data in the database. They simply point to the location of the data.
　　
There is no index that is fully applicable to any workload. Instead, you should always view the index in the context of the query that the system is running.
　　
Well-indexed databases run faster, and even missing an index slows down the database as a snail. Use explain (as described earlier) to find the missing indexes and add them. But be careful: don't add indexes you don't need! Unnecessary indexes can slow down the database
　　
(See the Introduction to MySQL indexing best practices).
　　
Optimization tip #3: Deny use of default settings
　　
Like any software, MySQL has a number of configurable settings that can be used to modify behavior (and ultimately performance). As with any software, the administrator ignores many of these configurable settings and is eventually used in the default mode.
　　
To get the best performance from MySQL, it is important to understand the configurable MySQL settings and, more importantly, to set them to best fit your database environment.
　　
By default, MySQL is used for small-scale development installations, not production scale. You typically want to configure MySQL to use all available memory resources and allow the number of connections that your application needs.
　　
Here are three MySQL performance optimization settings that you should always check carefully:
　　
Innodb_ buffer_ pool_size: Buffer pool is used to store cached data and indexes. This is the main reason for using a system with high-capacity RAM as a database server. If you run only the InnoDB storage engine, you typically allocate 80% of the memory 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 want to reduce this value by one grade to allocate more memory for other operations.
　　
When setting the InnoDB buffer pool size, you need to make sure that you do not set it too large, or it will cause an interchange. This will definitely affect database performance. A simple way to check is to view the exchange activity in the system overview diagram in Percona monitoring and management:
　　
Id
　　
, sometimes it is possible to make some exchanges. However, if you see a swap activity that lasts 1MB or more per second, you need to reduce the buffer pool size (or other memory usage).
　　
If you don't get the value of innodb_ buffer_ Pool_ size correctly on the first visit, don't worry. Starting with MySQL5.7, you can dynamically change the size of the INNODB buffer pool without restarting the database server.
　　
Innodb_ log_ file_ Size: This is a single InnoDB log file. By default, InnoDB uses two values, so you can double this number to get the size of the loop redo log space that InnoDB uses to ensure that transactions persist. This also optimizes the application of changes to the database. Setting innodb_ log_ file_ size is a trade-off issue. The greater the amount of redo space allocated, the better performance is for write-intensive workloads, but the longer the crash is restored if the system loses power or other problems occur.
　　
How do I know if MySQL's performance is limited by the current InnoDB log file size? You can judge by looking at how much of the redo log space is actually used. The simplest 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 (represented by a red line). The size of the log file should be at least 20% larger than the space used to keep the system running optimally.
　　
Id
　　
Max_ Connections: The number of large application connections usually needs to be higher than the default value. Unlike other variables, there is no performance problem (itself) if it is not set correctly. Conversely, if the number of connections is insufficient to meet the needs of your application, your application will not be able to connect to the database (which, in your view, is like downtime). So it's important to handle this variable correctly.
　　
If you have complex applications that run multiple components on multiple servers, it is difficult to know how many connections are required. Fortunately, MySQL can easily see how many connections are used during peak operations. Typically, you want to ensure that the maximum number of connections used by your application is at least 30% from the maximum number of connections available. An easy way to see these numbers is to use the MySQL connection diagram in the MySQL overview dashboard for Percona monitoring and management. Shows a robust system with a large number of additional connections available.
　　
Id
　　
One thing to keep in mind is that if the database is running slowly, the application will typically create too many connections. In this case, you should handle the performance problems of the database, rather than simply allowing more connections. More connections can make the underlying performance problems worse.
　　
(Note: When you set the max_connections variable to significantly higher than the default value, you typically need to consider adding additional parameters, such as the size of the table cache and the number of open MySQL files.) However, this does not fall within the scope of this discussion. )
　　
Optimization Tip #4: Save the database in memory
　　
In recent years, we have seen a transition to a solid state disk (SSD). Although SSDs are much faster than spinning hard drives, they are still not comparable to data in RAM. This difference comes not only from the storage performance itself, but also from the extra work that the database must do to retrieve data from disk or SSD storage.
　　
With the latest hardware improvements, it's increasingly possible to store the database in memory, whether it's running in the cloud or managing your own hardware.
　　
The better news is that you don't need to put all your databases into memory, you get most of the performance benefits in memory. You only need to store the work data (the most frequently accessed data) set in memory.
　　
You may have seen some articles that provide specific numbers that indicate which part of the database should be kept in memory, ranging from 10% to 33%. In fact, there is no "one size fits all" number. The amount of data that fits the best performance benefits of memory is related to the workload. Rather than looking for a specific "Magnum" number, check the I/O that the database is running in its stable state (typically several hours after startup). Look at read because if the database is in memory, you can completely eliminate read. Writing always needs to happen, no matter how much memory you have available.
　　
Below, you can see I/O in the InnoDB I/O diagram in the Percona monitoring and management Innodbmetrics dashboard.
　　
In the chart above, you can see spikes 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: Using SSD storage
　　
If your database is not suitable for memory (even if it is not appropriate), you still need fast storage to handle write operations and avoid performance issues when the database heats up (after a reboot). Today, SSDs are synonymous with fast storage.
　　
For reasons of cost or reliability, some "experts" still advocate the use of rotating disks (mechanical disks). Frankly speaking, when it comes to manipulating databases, these arguments are often outdated or completely wrong. Today, SSDs offer considerable performance and reliability at a high price.
　　
However, not all SSDs are applicable. For a database server, you should use an SSD designed for server workloads that will protect data (for example, during a power outage). Avoid the use of commercial SSDs designed for desktop and notebook computers.
　　
SSDs that are connected via NVMe or Intel Optan technology provide the best performance. SSDs still have better performance than spinning disks, even if they are remotely connected as a SAN, Nas, or cloud block device.
　　
Optimization Tips #6: Scale-out
　　
Even high-performance servers have their limitations. There are two ways to expand: up and out. Vertical scaling means buying more hardware. This can be expensive, and hardware will soon become obsolete. Scaling out to handle more load has several benefits:
　　
1. You can take advantage of smaller and less expensive systems.
　　
2. Scale-out makes linear scaling faster and easier.
　　
3. Because the database is distributed across multiple physical machines, the database is not affected by a single hardware failure point.
　　
While scale-out is beneficial, it has some limitations. Extensions require replication, such as basic MySQL replication or Percona XtraDB Cluster, to enable data synchronization. But in return, you get extra performance and high availability. If you need a larger extension, use MySQL shards.
　　
You also need to ensure that applications connected to the cluster architecture can find the data they need – typically through some proxy servers and load balancers (such as Proxysql or Haproxy).
　　
Avoid scaling out prematurely when planning for scale-out. Using distributed databases is often more complex. Modern hardware and MySQL servers can be experienced with a single server. The recently released MySQL 8 candidate version indicates that it is able to process 200多万个 simple queries on a single system.
　　
#7 of optimization techniques: observability
　　
It is no exception to consider the observable-mysql when designing the best system.
　　
Once you start, run, and correctly adjust the MySQL environment, you can't just set it up and not manage it. The database environment is affected by changes in the system or workload. Be prepared to deal with accidents such as traffic spikes, application errors, and MySQL failures. These things can and will happen.
　　
When problems occur, you need to solve them quickly and efficiently. The only way to do this is to set up some kind of monitoring solution and initialize it appropriately. This enables you to see what is happening when the database environment is running in production and to analyze server data when a problem occurs. Ideally, the system allows you to prevent the problem before it occurs or before the problem develops and the user can see its impact.
　　
Monitoring tools are available such as MySQL Enterprise Monitor, Monyog and Percona monitoring and Management (PMM), which have the additional benefit of free and open source. These tools provide good operability for monitoring and troubleshooting.
　　
As more and more companies move to open source databases (especially MySQL) to manage and service their business data in large-scale production environments, they will need to focus on maintaining the optimization and optimal operational efficiency of these databases. As with all the things that are critical to your business goals, your database performance can lead to or disrupt your business goals or outcomes. MySQL is a high-quality database solution for applications and websites, but needs to be tuned to meet your needs and monitored to identify and prevent bottlenecks and performance issues.
　　
Peterzaitsev is co-founder and CEO of Percona, Percona when it comes to enterprise-class MySQL and MongoDB solutions and services providers. "High Performance MySQL", published by O ' Reilly, is one of the most popular MySQL performance books. Zaitsev often publishes blogs on perconadatabaseperformanceblog.com and speaks at conferences around the world.

MySQL performance tends to drop. Remember these tips to keep MySQL running smoothly.