Shulou(Shulou.com)06/01 Report--

This article takes a look at seven tips for improving the performance of MySQL. There is a certain reference value, friends in need can refer to, hope to help you.

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

This indicator has changed in different applications. For mobile shopping applications, the response time cannot exceed a few seconds. It may take a few more seconds for the employee's human resources page.

There is a lot of research on how performance affects user behavior:

79% of customers are unlikely to return to slow websites.

47% of consumers want the web page to be loaded in 2 seconds or less.

40% of users give up when the loading time of the site exceeds 3 seconds.

A 1-second delay in page loading time may result in a 7% loss and an 11% reduction in page views

No matter what kind of standard is adopted, good application performance must be maintained. Otherwise, users will complain (or worse, switch to a different application). One of the factors that affect application performance is database performance. The interaction between applications, websites, and databases is critical to establishing application performance.

A core component 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 a 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 minimizes application performance.

Here are some basic tips to help optimize MySQL database performance.

Optimization tip # 1: learn how to use EXPLAIN

The two most important decisions made with any database are to design how relationships between application entities map to tables (database schemas), and how to design applications to get the data they need in the desired format (queries).

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

Instead of guessing and imagining, you should learn how to use the EXPLAIN command. This command shows how to execute the query and gives you an idea of the expected performance 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: the older table format 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\ Gmail * 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 that should be viewed is "query cost". Query cost means that MySQL considers the cost 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 usually less than 1000. Queries with overhead between 1000 and 100000 are considered moderately expensive, and it is usually faster to run only a few hundred such queries per second (instead of tens of thousands).

Queries that cost more than 100000 can be considered expensive. In general, these queries 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).

Of course, these numbers are only a rough indication of performance, but they show general principles. Depending on its architecture and configuration, your system may handle the query workload better or worse.

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

Optimization tip # 2: create the correct index

The index improves the 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 to 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 saved in their own location and contain information that already exists in the main database. They are reference methods or mappings that point to the location of the data. The index does not change any data in the database. They just point to the location of the data.

An index that is not fully applicable to any workload. Instead, you should always view the index in the context of the query that the system is running.

A well-indexed database not only runs faster, but even the lack of an index makes the database as slow as a snail. Use EXPLAIN (as described earlier) 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

(see an introduction to MySQL indexing best practices.)

Optimization tip # 3: refuse to use default settings

Like any software, MySQL has a number of configurable settings that can be used to modify behavior (and final performance). As with any software, administrators ignore 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, to set them to best suit your database environment.

By default, MySQL is used for small-scale development installations, not on a production scale. You usually want to configure MySQL to use all available memory resources and allow the number of connections your application needs.

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

Innodb_ buffer_ pool_size: buffer pools are used to hold 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, 80% of the memory is typically allocated to the buffer pool. If you are running very complex queries, or if you have a large number of concurrent database connections, or a large number of tables, you may need to lower this value by one notch to allocate more memory for other operations.

When setting the size of the InnoDB buffer pool, you need to make sure that it is not set too large, otherwise it will result in swapping. This will definitely affect database performance. A simple way to check is to look at the exchange activity in the system overview diagram in Percona Monitoring and Management:

As shown in the figure, it is sometimes possible to make some exchanges. However, if you see continuous 1MB or more swap activity per second, you need to reduce the buffer pool size (or other memory usage).

Don't worry if you don't get the value of innodb_ Buffer_ pool_ size correctly on your first visit. 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 the size of a single InnoDB log file. By default, InnoDB uses two values, so you can double that number to get the size of the circular redo log space that InnoDB uses to ensure transaction persistence. This also optimizes applying changes to the database. Setting up innodb_ log_ file_ size is a tradeoff. The more redo space allocated, the better performance for write-intensive workloads, but the longer it takes for crash recovery if the system is powered off or other problems occur.

How do I know if the performance of MySQL is limited by the current InnoDB log file size? You can tell by looking at how much redo log space is actually used. The easiest way is to look at the Percona Monitor and Management InnoDB Metrics dashboard. In the following figure, the size of the InnoDB log file is not large enough because the space used is very close to the available redo log space (represented by red lines). The size of the log file should be at least 20% larger than the space used to keep the system running optimally.

MAX_ Connections: the number of connections for large applications is usually higher than the default value. Unlike other variables, if it is not set correctly, there will be no performance problem (itself). Conversely, if there are not enough connections to meet your application's needs, your application will not be able to connect to the database (which looks like downtime to your users). So it's important to handle this variable correctly.

If you are running complex applications with 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. In general, 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 MySQL connection diagrams in the MySQL overview dashboard monitored and managed by Percona. The following figure shows a robust system with a large number of additional connections available.

One thing to keep in mind is that if the database is slow, the application usually creates too many connections. In this case, you should deal with the performance of the database rather than simply allowing more connections. More connections will make the underlying performance problems worse.

(note: when you set the max_Connections variable to significantly higher than the default value, you usually need to consider increasing other parameters, such as the size of the table cache and the number of MySQL files open. However, this is outside the scope of this article.)

Optimization tip # 4: save the database in memory

In recent years, we have seen a transition to solid state disks (SSD). Although SSD is much faster than rotating hard drives, they are still not comparable to the data in RAM. This difference comes not only from 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 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 the databases in memory to get most of the performance benefits in memory. You only need to store the set of working data (the most frequently accessed data) in memory.

You may have seen some articles that provide specific figures on which part of the database should be kept in memory, ranging from 10% to 33%. As a matter of fact, there is no across-the-board figure. The amount of data suitable for the best performance advantage of memory is related to the workload. Instead of looking for a specific "one-size-fits-all" number, check that the database is running in its steady state (usually a few hours after startup). Look at READ, because if the database is in memory, READ can be completely eliminated. Writing always needs to happen, no matter how much memory you have available.

Next, you can see it in the InnoDB I Dash O chart in the InnoDBMetrics dashboard monitored and managed by Percona.

In the chart above, you can see a peak of up to 2000 Icano operations per second, indicating that (at least for some parts of the workload) the database working set is not suitable for memory.

Optimization Tip # 5: using SSD Storage

If your database is not suitable for memory (even if it does not), you still need fast storage to handle writes and avoid performance problems when the database heats up (after reboot). Today, SSD is synonymous with fast storage.

For reasons of cost or reliability, some "experts" still advocate the use of rotating disks (mechanical disks). Frankly, these arguments are often outdated or completely wrong when it comes to manipulating databases. Today, SSD provides considerable performance and reliability at a higher price.

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

SSD connected through NVMe or Intel OpTan technology provides the best performance. Even as a remote connection to a SAN, NAS, or cloud block device, SSD still has better performance than rotating disks.

Optimization Tip # 6: scale out

Even high-performance servers have their limitations. There are two ways to expand: up and out. Scale-up means buying more hardware. This can be expensive, and the hardware will soon be out of date. Scaling out to handle more load has several benefits:

1. Smaller and lower-cost systems can be utilized. two。 Through scale-out, linear expansion is faster and easier. 3. Because databases are distributed across multiple physical machines, databases are not affected by a single point of hardware failure.

Although horizontal expansion is beneficial, it also has some limitations. The extension requires replication, such as basic MySQL replication or Percona XtraDB Cluster, for data synchronization. But in return, you can get additional performance and high availability. If you need a larger extension, use MySQL sharding.

You also need to ensure that applications connected 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 scale-out, avoid expanding prematurely. Using distributed databases is often more complex. Modern hardware and MySQL servers can get a good experience with only one server. The recently released candidate version of MySQL 8 shows that it can handle more than 2 million simple queries on a single system.

Optimization tip # 7: observability

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

Once you have started, run, and properly tuned the MySQL environment, you cannot just set it up without managing it. The database environment is affected by system or workload changes. Be prepared for surprises 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 effectively. The only way to do this is to set up some kind of monitoring solution and initialize it properly. This enables you to see what is happening to the database environment while it is running in production and analyze server data when problems occur. Ideally, the system allows you to prevent a problem before it occurs or before the user can see its impact.

Monitoring tools include things like MySQL Enterprise Monitor, Monyog, and Percona Monitoring and Management (PMM), which have the added advantage of being free and open source. 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 mass production environments, they will need to focus on maintaining the optimization and optimal operational efficiency of these databases. Like all things that are critical to your business goals, your database performance can lead to or undermine your business goals or outcomes. MySQL is a high-quality database solution for applications and websites, but needs to be tailored to meet your needs and monitored to identify and prevent bottlenecks and performance problems.

PeterZaitsev is the co-founder and CEO of Percona and Percona is the provider of enterprise-class MySQL and MongoDB solutions and services. High Performance MySQL, published by O'Reilly, is one of the most popular MySQL performance books. Zaitsev regularly blogs on PerconaDatabasePerformanceBlog.com and speaks at conferences around the world.

These are the details of the seven tips to improve the performance of MySQL. Do you have anything to gain after reading them? If you want to know more about it, welcome to the industry information!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.