catalog

    • Short link storm
      • First deal with threads that occupy connections but do not work
      • Reduce the cost of connection process
    • Slow query performance problems
      • The index is not well designed
      • The sentence is not written well
      • Choose the wrong index
    • QPS surge problem

Short link storm

Normal short connection: disconnects when few SQL statements are executed and reconnects when needed. The procedure for setting up a connection in MySQL is costly, including the three-way handshake for network connection, login permission judgment, and data read and write permission for connection. There is a risk with the short-connection model: once the database is processed more slowly, the number of connections skyrockets. There are two ways to deal losslessly with short link storms

First deal with threads that occupy connections but do not work

If the number of connections, you can preferentially disconnect connections that are idle outside of a transaction for too long. If this is not enough, consider disconnecting connections that have been idle for too long within a transaction. When a client is in sleep state and its connection is voluntarily disconnected by the server, the client is not immediately aware of this. Instead, the client will receive an error when it makes the next request:

ERROR 2013 (HY000): Lost connection to MySQL server during query
Copy the code

Some applications do not reconnect when they receive this error, but retry the query with this handle, which causes MySQL to remain unrecovered from the application.

Reduce the cost of connection process

Let the database skip the permission validation phase.

Method: Restart the database and start it with the -skip-grant-tables parameter. MySQL skips all permission validation stages, including connection and statement execution.

The risk is extremely high. In MySQL8.0, if -skip-grant-tables is enabled, the –skip-networking parameter is turned on by default, indicating that the database can only be connected by local clients.

Slow query performance problems

The index is not well designed

Resolve this by creating an index urgently.

Execute the ALTER TABLE statement directly.

Assuming that the service is now one master and one standby, the master library A and the standby library B, the general flow of the solution is as follows:

1, run set SQL_log_bin = off on standby database B, do not write binlog, then run alter table, add index

2. Perform an active/standby switchover

3, The primary database is B, the standby database is A. Set SQL_log_bin = off on A, then execute ALTER TABLE, add index

The sentence is not written well

In a previous article, I mentioned that sometimes errors can cause statements not to use an index.

The query_rewrite function allows you to rewrite one type of input statement into another.

What if we started with:

select * from t where id + 1 = 10000;
Copy the code

You can add a statement rewriting rule with the following code.

insert into query_rewrite.rewrite_rules(pattern,replacement,pattern_databas) values("select * from t where id + 1 = ?" ,"db1");call query_rewrite.flush_rewrite_rules();
Copy the code

call query_rewrite.flush_rewrite_rules()Is to let the new rule inserted take effect.

Choose the wrong index

Contingency: add force index to statement;

QPS surge problem

The QPS Query Per Second rate sometimes spikes due to business peaks or application bugs.

The best thing to do is ask the business to disable this feature and the service will be restored.

You can disable a feature as follows:

1, if it is a new business bug, and if the business side that goes to go down this function, then you can directly remove the whitelist from the database side

2. If this feature uses a separate database user, delete the user with the administrator account and disconnect the existing connection.

3. If this function and the main function are deployed together, we can only limit this by processing statements. You can use the query rewrite function to rewrite the most stressed SQL statements directly as “SELECT 1” to return.

The side effects of disabling a feature are as follows:

1, if the SQL statement template is used in other functions, there will be error

2. Many businesses do not rely on a single statement to complete the logic, so returning a single statement as select 1 May cause the subsequent business logic to fail.