Quickly identify and resolve database issues

Identify the database bottleneck

• Check the process list
  Run the following SQL statement to view all currently running SQL statements on PolarDB-X:

  SHOW PROCESSLIST WHERE INFO IS NOT NULL

  Typically, a statement pile-up coincides with database slowdowns. If the command output shows few statements with an execution time greater than 0, the database is likely not the problem. Otherwise, the database might be the bottleneck.

• Check the stack information
  An application communicates with a database over TCP. If the database is the bottleneck, the application may send a request through a socket but receive no response. This causes the socket to block on the read method. Examine the application's stack information to determine if the database is causing a block. The following example uses a Java application:

  1. Use the jstack command to dump the stack information.
  2. In the dumped information, search for the stack where the MySQL driver is waiting for a response. The stack trace looks like this:

     at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:171) at java.net.SocketInputStream.read(SocketInputStream.java:141) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174) - locked <0x00000002eb8f2d98> (a com.mysql.jdbc.util.ReadAheadInputStream) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3183) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3659) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3649) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4090) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:972) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2497) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2870) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2806)

  If many threads show a similar stack trace, they are likely blocked while waiting for a database response. This suggests the bottleneck is likely at the database layer. If not, investigate potential bottlenecks within the application itself.

Quickly resolve database issues

Once you determine the database is the bottleneck, use the following methods in the order listed to restore service.

If the process list shows a large pile-up of SQL statements, immediately kill all running statements. PolarDB-X provides the following command:

KILL "ALL"

This statement terminates all connections between compute nodes and data nodes, ending all running statements.

If statement pile-ups reoccur after you kill all running statements, restart the application. This prevents the application from repeatedly retrying costly SQL statements due to an incorrect state.

If restarting the application does not resolve the issue, use the CCL_RULES throttling feature of PolarDB-X.

1. Run the SHOW FULL PROCESSLIST command to find the template ID for the most frequent SQL statements.

   +----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
   | ID | USER          | HOST            | DB       | COMMAND                       | TIME | STATE | INFO                  | SQL_TEMPLATE_ID |
   +----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
   |  2 | polardbx_root | ***.*.*.*:62787 | polardbx | Query                         |    0 |       | show full processlist | NULL            |
   |  1 | polardbx_root | ***.*.*.*:62775 | polardbx | Query(Waiting-selectrulereal) |   12 |       | select 1              | 9037e5e2        |
   +----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
   2 rows in set (0.08 sec)

2. Use the template ID to throttle that type of SQL statement. For example:

   CREATE CCL_RULE IF NOT EXISTS `test` ON *.* TO 'ccltest'@'%'
   FOR SELECT
   FILTER BY TEMPLATE('9037e5e2')
   WITH MAX_CONCURRENCY=10;

If none of the previous methods work, restart the database.