Wechat public number: The way to grow up

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Hello, everyone. In the previous chapters, we introduced rules optimization for locking. Today we are going to introduce some ways to improve performance of MySQL.

case

The normal short connection mode is to connect to the database, execute a few SQL statements, disconnect, and reconnect the next time you need to. If a short connection is used, the number of connections can suddenly jump during peak hours.

The process of MySQL establishing a connection is very expensive. In addition to the normal three-way handshake for network connections, you also need to determine login permissions and obtain data read and write permissions for this connection.

max_connections

This parameter controls the maximum number of connections, which will skyrocket as the database slows down. Any more than that and subsequent connections are rejected with an error message saying “Too many connections.” For a rejected connection request, the database is unavailable from a business perspective.

At high machine loads, it takes longer to process existing requests and each connection is held longer. At this point, new connections may exceed the max_connections limit.

The solution

Damage to solve

A natural thought when this happens is to increase the value of max_connections. But there are risks. Because max_connections is designed to protect MySQL, if we make it too large to allow more connections in, the system could become even more overburdened, spending a lot of resources on logic such as permission validation, which could backfire. The connected thread could not get the CPU resources to execute the SQL request of the business.

Get rid of the threads that occupy the connection but are not working.

Max_connections is calculated not by who is running, but by occupying a count position whenever connected. For those connections that do not need to be maintained, we can actively kill the connection. This behavior has the same effect as setting wait_timeout beforehand. Setting the wait_timeout parameter means that a thread will be disconnected from MySQL after it has idle wait_timeout for so many seconds.

Note, however, that in the result of show ProcessList, it might be lossy to kick the thread that shows sleep. Let’s look at the following example.

sessionA

• Start a transaction and insert a record of 1

sessionB

• Example Query the data whose ID is 1

sessionC

• State of the query

If we disable sessionA, the MySQL transaction will need to roll back the transaction performed by sessionA.

If we turn sessionB off, it doesn’t seem to affect MySQL too much

Conclusion: You should first close free connections outside the transaction, then close free connections inside the transaction, and finally close queries that do not matter.

So how do we judge a spatial connection outside of a common transaction?

By performingshow processlistDiscuss and analyze according to the output results.Session id=4 and session id=5 are both in Sleep state. Then we need to query the transaction result for each ID. Look at the execution logic within a transaction.

For details on transaction status, you can refer to the Innodb_TRx table in the Information_SCHEMA library.

In this result, trx_mysql_thread_id=4 indicates that the thread with id=4 is still in the transaction.

These two methods can determine that the link is currently executing within the transaction. So according to the priorities we summarized above. I can rule out this ID connection. This way you can check the ids that are not executing within the transaction

Then run the kill Connections ID.

ERROR 2013 (HY000): Lost Connection to MySQL server during Query ERROR 2013 (HY000): Lost connection to MySQL server during Query ERROR 2013 (HY000): Lost connection to MySQL server during Query

Disconnecting from the database can be lossy, especially if an application receives this error and does not reconnect, but simply retries the query with the invalid handle. This causes the application to look like “MySQL has never recovered”.

Reduce the cost of connection process.

Let’s look at the use of the — skip-grant-tables parameter.

To improve concurrency performance, some services apply for connections from the database in advance. In this way, the time limit of connection, verification and so on can be eliminated.

To execute the current method, restart the database with the — skip-grant-tables parameter. This skips all permission validation (including statement execution) throughout MySQL.

The pros and cons

This does solve the performance problem, but it can be very dangerous. Not if you’re exposed to the extranet.

In MySQL 8.0, if you enable the — skip-grant-tables parameter, MySQL will default to the –skip-networking parameter, which means that the database can only be connected by local clients. The security of skip-grant-tables is very important for MySQL.

The slow query

Let’s start with a few factors that affect performance

• Due to index problems, there are too many back tables and too many scans
• SQL problems causing slow
• The MySQL engine chose the wrong index. If you don’t understand here, go to my MySQL learning column to find that one

Let’s go through them one by one

The index problem

If it’s an index problem, you have to redesign the index. Play it safe because you are manipulating data structures in a production repository. I’m sure you can’t just move it.

A single library

MySQL 5.6 supports Online DDL for index creation. The most efficient way to do this is to execute the ALTER TABLE statement directly.

Many libraries

The best is to be able to execute in the standby repository first. Assume that your current service is one master and one standby, master library A and standby library B. The general process of this solution is as follows:

• Set SQL_log_bin =off on standby database B. then execute alter TABLE statement add index.
• Perform an active/standby switchover.
• In this case, the primary library is B, and the standby library is A. Execute set SQL_LOG_bin =off on A, then execute alter TABLE statement add index.

This is an “old” DDL scheme. Usually when making changes, you should consider something like GH-OST, which is more secure. But the above scheme is most efficient when emergency treatment is needed.

SQL problem

This situation is relatively easy to solve. It does not normally occur in the production repository. But there are, let’s introduce it.

Novice problem SQL words have nothing to say, more run explain it. Something a little more technical. That is the inexplicable failure of the index introduced in the previous few papers. Mainly from three aspects. Implicit conversion, implicit encoding, function operation and other failure reasons.

We can handle this by rewriting the SQL statement. MySQL 5.7 provides query_rewrite functionality that allows you to rewrite one type of input statement into another mode

For example, if the statement was incorrectly written as SELECT * from t where id + 1 = 10000, you can add a statement rewriting rule as follows

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?"."select * from t where id = ? 1. ""."db1");

call query_rewrite.flush_rewrite_rules();
Copy the code

query_rewrite.flush_rewrite_rulesThis is a stored procedure for the above insert to work. Let’s verify that it works

Rewrite success!

Choose the wrong index

The wrong index, as we’ve seen here. The optimizer selected the wrong index, causing online paralysis. This is easy to understand.

Back to the subject.

If the index is not selected as we would like, the usual way to write this is to add a force index. Bootstrap the optimizer to select the index.

In the same way, we can solve this problem in this place.

Index problems and SQL problems are still common in development scenarios. Usually MySQL doesn’t make a mistake. These two are often relatively easy to prevent.

• Before going live, in the test environment, turn on slow log and set long_query_time to 0 to ensure that each statement is logged into the slow log.
• Insert the data on the simulation line into the test table and do a regression test;
• Observe the output of each statement in the slow query log, paying particular attention to whether the Rows_examined field is as expected.

Don’t skimp on this “extra” time before going live, as it will save you a lot of troubleshooting time.

QPS

Sometimes, due to a sudden business peak or application bug, the QPS of a certain statement suddenly increases, and MySQL may be under too much pressure, affecting the service.

I’ve come across a situation before that was caused by a bug in a new feature. Ideally, of course, the business would take this feature off and the service would resume.

Dropping a function, if handled from the database side, can be done in different ways, depending on the context. Let me explain it to you again.

• One is caused by a bug in a brand new business. Assume that your DB operation and maintenance is relatively standard, that is, the whitelist is added one by one. In this case, if you are certain that the business side will disable the feature, but not as quickly, you can remove the whitelist directly from the database side.
• If this new feature uses a separate database user, you can delete this user using the administrator account and then disconnect the existing connection. In this case, the new function fails to connect, and the QPS raised by it becomes 0.
• If this new function is deployed with the main function, we can only limit it by processing statements. At this point, we can use the query rewrite feature mentioned above to rewrite the most stressed SQL statements directly as “SELECT 1”.

Of course, this operation is very risky and requires you to be very careful. It can have two side effects:

• If the SQL statement template is also used in other functions, there will be error;
• Many businesses do not rely on this statement to complete the logic, so if this statement is returned as select 1, it may cause the subsequent business logic to fail.

So, plan 3 is for hemostasis and, along with the removal of permission verification mentioned earlier, should be the lowest priority of all your options.

At the same time, you will find that both solutions 1 and 2 rely on standard O&M systems: virtualization, whitelist mechanism, and business account separation. More preparation, therefore, often means a more stable system.

conclusion

Today, I introduced several ways to optimize the use of MySQL. And common problem solving access solutions. It’s all online.

Thank you for your support. That’s all for today. What doubt can be through the public number [happy little growth of the road] to my private letter