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

In the front-end time, the application staff reported a performance problem: in the production environment, the database runs very slowly in the early hours of every day, and some EVENT fails, resulting in abnormal functions of some applications.

Check the database according to the time period provided by the application staff.

First of all, the host CPU, IO, database connections and other monitoring historical data are analyzed to confirm the fault timeline and narrow the time range.

From the picture above, we can see that at about 0:30, the number of database active connections has increased from 0 to 2001.The number of active connections has increased to 400pm, and the database connection has increased abnormally. It is necessary to further analyze what the database is doing at this time.

Analyze the captured historical data (the host deploys a shell monitoring script): at 0:30, the database is delete table _ 1030 and other threads are waiting for the table lock.

Based on the above, sort out the fault timeline:

The monitoring data show that the delete operation is performed in table _ 1030 at 0:30, and the operation is not completed until around 1:15. The operation runs for about 40 + minutes. During this period, the select operation of table _ 1030 is blocked, causing the database connection to rise from 0 to 1030, with a maximum of 400. Application exception:

The SQL that caused the blocking is:

DELETE FROM _ 1030 WHERE _ 1030.F05 show create table S11.030\ G

* * 1. Row *

Table: _ 1030

Create Table: CREATE TABLE `_ 1030` (

`F01` int (10) unsigned NOT NULL AUTO_INCREMENT

`F02` char (45) NOT NULL

`F03` datetime NOT NULL

`F04` int (10) unsigned DEFAULT NULL

`F05` datetime NOT NULL

`F06` varchar (40) NOT NULL

`F07` varchar (40) DEFAULT NULL

PRIMARY KEY (`F01`)

UNIQUE KEY `F02` (`F02`) USING HASH

KEY `F06` (`F06`) USING HASH

KEY `F07` (`F07`) USING HASH

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Through inspection, it is found that the table is a heap table, the heap table data is in memory, and the heap performance should be very fast. Why is the delete statement so slow?

When testing in the test environment, the amount of data of DELETE _ 1030 50W takes 58s, which is unreasonably slow. After deleting the index of the table, it is completed within 1s of delete. It is basically confirmed here that the cost of index maintenance is too high.

Add btree index, test again, and finish it within delete 1s. Confirm that it is caused by the hash index.

Optimization scheme:

Change delete to full table delete or truncate without where conditions (the table data is cached data).

Change the HASH index to the BTREE index.

Note: because the btree index takes up a lot of memory space (after testing, the btree index occupies more than 6 times as much space as the hash index), the memory of the database host is tight at that time, so scheme 1 is preferred.

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.