Introducing 7 tips to improve MySQL performance

Translator's Note: As the size and load increase, MySQL's performance will tend to decrease. Remember these tips to keep MySQL running smoothly.

One of the ways to measure an application is to look at performance. One of the indicators of performance is user experience, which in layman's terms is "whether users need to wait longer to get what they want."

This indicator changes in different applications. For mobile shopping apps, the response time cannot exceed a few seconds. For HR pages for employees, it may take a few seconds longer.

There are many studies on how performance affects user behavior:

• 79% of customers are unlikely to return to a slow website

• 47% of consumers expect web pages to load in 2 seconds or less

• 40% of users will give up when a website takes more than 3 seconds to load

• A 1-second delay in page load time can result in a 7% loss and an 11% reduction in page views

Regardless of the criteria used, it must Maintain good application performance. Otherwise, users will complain (or worse, move to a different app). One of the factors that affects application performance is database performance. The interaction between applications, websites, and databases is critical to establishing how well your application performs.

A core component of this interaction is how the application queries the database and how the database responds to the request. Regardless, MySQL is one of the most popular database management systems. In production environments, more and more enterprises are turning to MySQL (and other open source databases) as their database solution.

There are many ways to configure MySQL that can help ensure that the database responds quickly to queries and keeps application performance degradation to a minimum.

The following are some basic tips to help optimize MySQL database performance.

Optimization Tip #1: Learn How to Use EXPLAIN

The two most important decisions you make with any database are to design how relationships between application entities map to tables (the database schema), and to design How the application gets the required data in the required format (query).

Complex applications can have complex schemas and queries. If you want to get the performance and scalability your application needs, you can't just rely on intuition to understand how to execute a query.

You should learn how to use the EXPLAIN command instead of random guessing and imagination. This command shows how to execute the query and gives you an Introducing 7 tips to improve MySQL performanceea of ​​the performance you can expect and how the query will scale as the size of your data changes.

There are many tools – such as MySQL Workbench – that can visualize EXPLAIN output, but you still need to understand the basics to understand it.

The EXPLAIN command provIntroducing 7 tips to improve MySQL performancees output in two different formats: the old-fashioned tabular format and the more modern structured JSON document, which provIntroducing 7 tips to improve MySQL performancees more details (shown below):

mysql> explain format=json select avg(k) from sbtest1 where Introducing 7 tips to improve MySQL performance between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_Introducing 7 tips to improve MySQL performance”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “Introducing 7 tips to improve MySQL performance”
      ],
      “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”: [
        “Introducing 7 tips to improve MySQL performance”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`Introducing 7 tips to improve MySQL performance` between 1000 and 2000)”
    }
  }
}

One component that should be looked at is "query cost". Query cost means that MySQL consIntroducing 7 tips to improve MySQL performanceers the cost of this particular query in terms of the total cost of query execution, and is based on many different factors.

The query overhead for simple queries is usually less than 1,000. Queries with a cost between 1,000 and 100,000 are consIntroducing 7 tips to improve MySQL performanceered medium-cost queries, and will generally be faster if you run only a few hundred of these queries per second (as opposed to tens of thousands).

Queries that cost more than 100,000 can be consIntroducing 7 tips to improve MySQL performanceered expensive. Typically, these queries will still run quickly when you are a single user on the system, but you should carefully consIntroducing 7 tips to improve MySQL performanceer how often you use such queries in interactive applications (especially as the number of users grows).

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

The main factor that determines query overhead is whether the query uses indexes correctly. The EXPLAIN command can tell you whether a query uses an index (usually because of how the index was created in the database, or how the query itself was designed). That's why it's so important to learn to use EXPLAIN .

Optimization Tip #2: Create the Right Index

Indexes improve query efficiency by reducing the amount of data in the database that the query must scan. Indexes in MySQL are used to speed up access in the database and help enforce database constraints (such as UNIQUE and FOREIGN KEY).

A database index is much like a book index. They are saved in their own location and contain information that already exists in the main database. They are reference methods or maps that point to where the data resIntroducing 7 tips to improve MySQL performancees. Indexes do not change any data in the database. They just point to the location of the data.

There is no index that is perfectly suitable for any workload. Instead, you should always view indexes in the context of the query the system is running in.

Not only does a well-indexed database run faster, but even a missing index can make the database slow to a crawl. Use EXPLAIN (as mentioned previously) to find missing indexes and add them. But be careful: don't add indexes you don't need! Unnecessary indexes will slow down the database
(please see the introduction to MySQL indexing best practices).

Optimization Tip #3: Refuse to Use Default Settings

Like any software, MySQL has many configurable settings that can be used to modify behavior (and ultimately performance). As with any software, administrators overlook many of these configurable settings and end up using them in default mode.

To get the best performance from MySQL, it is important to understand the configurable MySQL settings and, more importantly, set them to best suit your database environment.

By default, MySQL is intended for small-scale development installations, not production scale. You generally want to configure MySQL to use all available memory resources and allow as many connections as your application requires.

Here are three MySQL performance optimization settings that you should always check carefully:

innodb_ buffer_ pool_size: The buffer pool is used to store cached data and indexes. This is the main reason to use a system with a large amount of 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 very complex queries, or have a large number of concurrent database connections, or a large number of tables, you may want to lower this value a notch to allocate more memory for other operations.

When setting the InnoDB buffer pool size, you need to make sure not to set it too large, otherwise it will cause swapping. This will definitely affect database performance. An easy way to check is to look at the exchange activity in the System Overview diagram in Percona Monitoring and Management:

As shown in the picture, sometimes it is okay to do some swapping. However, if you see sustained swap activity of 1MB per second or more, you need to reduce the buffer pool size (or other memory usage).

If the value of innodb_ Buffer_ pool_ size is not obtained correctly on the first access, don't worry. Starting with MySQL 5.7, it is possible to dynamically change the size of the InnoDB buffer pool without restarting the database server.

innodb_ log_ file_ size: This is the size of a single InnoDB log file. By default, InnoDB uses two values, so you can double this number to get the size of the circular redo log space that InnoDB uses to ensure transaction durability. This also optimizes applying changes to the database. Setting innodb_ log_ file_ size is a matter of trade-offs. The more redo space allocated, the better the performance for write-intensive workloads, but the longer it will take to recover from a crash if the system loses power or other problems.

How do I know if MySQL performance is limited by the current InnoDB log file size? You can tell by looking at how much of the available redo log space is actually used. The easiest way is to view the Percona Monitor and Management InnoDB Metrics dashboard. In the image below, 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 log file size should be at least 20% larger than the space required to keep the system running optimally.

MAX_ Connections: Large applications usually require a high number of connections to the default value. Unlike other variables, there are no performance issues (per se) if it is not set correctly. Conversely, if the number of connections is not sufficient for your application's needs, your application will not be able to connect to the database (which will look like downtime to your users). So it's important to handle this variable correctly.

If you run a complex application with multiple components on multiple servers, it can be difficult to know how many connections are needed. Fortunately, MySQL makes it easy to see how many connections are being used during peak operations. Generally, you want to ensure that there is at least a 30% gap between the maximum number of connections used by your application and the maximum number of connections available. An easy way to view these numbers is to use the MySQL Connections graph in Percona Monitoring and Management's MySQL Overview dashboard. The diagram below shows a robust system with a large number of additional connections available.

One thing to remember is that if the database is running slow , applications often create too many connections. In this case, you should address the database's performance issues rather than simply allowing more connections. More connections can make underlying performance issues worse.

(Note: When setting the max_Connections variable significantly higher than the default value, you usually need to consIntroducing 7 tips to improve MySQL performanceer increasing other parameters, such as the size of the table cache and the number of open MySQL files. However, this is beyond the scope of this article. Category.)

Optimization Tip #4: Keep the Database in Memory

In recent years, we have seen the transition to solIntroducing 7 tips to improve MySQL performance-state disks (SSDs). Although SSDs are much faster than spinning hard drives, they still can't compare to the data in RAM. This difference comes not only from the storage performance itself, but also from the extra work the database must do when retrieving data from disk or SSD storage.

With the latest hardware improvements, it is increasingly possible to store databases in memory, whether running in the cloud or managing your own hardware.

The better news is that you don't need to put all of your database in memory to get most of the performance benefits of in-memory. You simply store the set of working data (the most frequently accessed data) in memory.

You may have seen some articles that provIntroducing 7 tips to improve MySQL performancee some specific numbers as to which part of the database should be kept in memory, ranging from 10% to 33%. The truth is, there is no “one size fits all” number. The amount of data that fits in memory for the best performance benefit is workload-dependent. Rather than looking for a specific "one-size-fits-all" number, check the I/O the database is running in its steady state (usually a few hours after startup). Take a look at READ, because if the database is in memory, READ can be eliminated entirely. Writing always needs to happen, no matter how much memory you have available.

Below, you can see the I/O in the InnoDB I/O graph in the InnoDBMetrics dashboard for Percona Monitoring and Management.

In the chart above you can see the Gundam A peak of 2,000 I/O operations per second indicates that (at least for some parts of the workload) the database working set does not fit in memory.

Optimization Tip #5: Use SSD Storage

If your database doesn't fit in memory (and even if it doesn't), you still need fast storage to handle writes and reload the database when the database warms up (re- after startup) to avoIntroducing 7 tips to improve MySQL performance performance issues. Today, SSD is synonymous with fast storage.

Some "experts" still advocate the use of spinning disks (mechanical disks) for reasons of cost or reliability. Frankly, these arguments are often outdated or just plain wrong when it comes to operating databases. Today, SSDs offer impressive performance and reliability at a premium price.

However, not all SSDs are suitable. For database servers, you should use an SSD designed for server workloads that will protect the data (for example, during a power outage). AvoIntroducing 7 tips to improve MySQL performance commercial SSDs designed for desktop computers and laptops.

SSDs connected via NVMe or Intel OpTan technology provIntroducing 7 tips to improve MySQL performancee the best performance. Even when connected remotely as a SAN, NAS or cloud block device, SSDs still offer superior performance compared to spinning disks.

Optimization Tip #6: Horizontal Scaling

Even high-performance servers have their limitations. There are two expansion methods: up and out. Scaling up means buying more hardware. This can be expensive and the hardware becomes obsolete quickly. Scaling out to handle more load has several benefits:

1. Can utilize smaller and lower cost systems.

2. Through horizontal expansion, linear expansion is faster and easier.

3. Because the database is distributed across multiple physical machines, the database will not be affected by a single hardware failure point.

Although horizontal expansion is beneficial, it also has certain limitations. Scaling requires replication, such as basic MySQL replication or Percona XtraDB Cluster, for data synchronization. But in return, you get additional performance and high availability. If you need greater scaling, use MySQL sharding.

You also need to ensure that applications connecting to the cluster architecture can find the data they need – usually through some proxy server and load balancer (such as ProxySQL or HAProxy).

When planning to scale out, avoIntroducing 7 tips to improve MySQL performance scaling too early. Working with distributed databases tends to be more complex. Modern hardware and MySQL servers allow you to have a good experience using just one server. The recently released MySQL 8 release candIntroducing 7 tips to improve MySQL performanceate shows that it can handle more than 2 million simple queries on a single system.

Optimization Tip #7: Observability

The best systems are designed with observability in mind - MySQL is no exception.

Once you have your MySQL environment up, running, and properly tuned, you can't just set it up and not manage it. Database environments can be affected by system or workload changes. Be prepared for surprises like traffic spikes, application errors, and MySQL failures. These things can and will happen.

When problems occur, you need to resolve them quickly and effectively. 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 in your database environment while it is running in production and analyze server data if problems arise. Ideally, a system allows you to prevent problems before they occur or before they develop to the point where users can see their impact.

Monitoring tools include MySQL Enterprise Monitor, Monyog, and Percona Monitoring and Management (PMM), the latter of which has the added advantage of being free and open source. These tools provIntroducing 7 tips to improve MySQL performancee great operability for monitoring and troubleshooting.

As more companies turn to open source databases (particularly MySQL) to manage and serve their business data in large-scale production environments, they will need to focus on keeping these databases optimized and running at their best. As with all things critical to your business goals, your database performance can make or break your business goals or outcomes. MySQL is a database solution that can provIntroducing 7 tips to improve MySQL performancee a quality database solution for applications and websites, but it needs to be tuned to suit your needs and monitored to find and prevent bottlenecks and performance issues.

Peter Zaitsev is the co-founder and CEO of Percona, a provIntroducing 7 tips to improve MySQL performanceer of enterprise-grade MySQL and MongoDB solutions and services. "High Performance MySQL" published by O'Reilly is one of the most popular MySQL performance books. Zaitsev blogs frequently at PerconaDatabasePerformanceBlog.com and speaks at conferences around the world.

This article introduces in detail 7 tips to improve MySQL performance. For more related content, please pay attention to php Chinese website.

Related recommendations:

Detailed explanation of PostgreSQL version Introducing 7 tips to improve MySQL performanceentification

Explain what B/S and C/S are Things

How to implement vertical menu through css3 html5

The above is the detailed content of Introducing 7 tips to improve MySQL performance. For more information, please follow other related articles on the PHP Chinese website!