Performance Analysis: Hash index causes Delete to slow

Front-end time, the application reported a performance problem: in the production environment, the database runs slowly every morning, and some event runs fail, which results in some application features being abnormal.

Troubleshoot the database based on the time period provided by the application.

First, the host CPU, IO, database connection monitoring historical data analysis, confirm the failure timeline, reduce the time range.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/7F/CA/wKiom1csX_Hwe3CkAAFsjRVIpjA679.jpg "style=" float: none; "title=" 11.jpg "alt=" Wkiom1csx_hwe3ckaafsjrvipja679.jpg "/>

From the view of 0:30, the database activity connection increased from 0 to 200,1:09 active connections to 400 +, the database connection abnormally increased, need to further analyze the database this time what to do.

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/7F/C7/wKioL1csYOvDsqE6AApty0izgRg355.jpg "style=" float: none; "title=" 22.jpg "alt=" Wkiol1csyovdsqe6aapty0izgrg355.jpg "/>

Analysis of captured historical data (the host deploys a shell monitoring script): At 0:30, the database is doing a delete operation on table _1030, and other threads are waiting for the table lock.

Integrated above, comb out the failure time line:

Monitoring data display, 0:30 table _1030 The delete operation, the operation at about 1:15 minutes to complete, the operation ran 40+ minute or so, the period table _1030 the select operation is blocked, resulting in a database connection from 0 to 200, up to 400, the application of the exception:

The SQL that caused the blocking is:

DELETE from _1030 WHERE _1030.f05 <= name_const (' _current_date ', _latin1 ' 2016-01-17 00:30:00 ' COLLATE ' latin1_swedish _ci ')

Combined with the above, there are 2 questions:

Why does the DELETE statement produce a table lock?

Why is the DELETE statement so slow? Can it be optimized?

([email protected]) [(None)]> Show create TABLE s11._1030 \g

1. Row ***************************

Table: _1030

Create table:create Table ' _1030 ' (

' F01 ' int (ten) unsigned not NULL auto_increment,

' F02 ' char () is not NULL,

' F03 ' datetime not NULL,

' F04 ' int (ten) unsigned DEFAULT NULL,

' F05 ' datetime not NULL,

' F06 ' varchar (+) not NULL,

' F07 ' varchar (+) 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)

By looking at the discovery that the table is a heap, the heap table data is in memory and the heap performance should be fast, why is the DELETE statement so slow?

In the test environment to test, DELETE _1030 50w data volume needs 58s, slow unreasonable. After you delete the index of the table, delete 1s completes. This basically confirms that the index maintenance cost is too large to cause.

Add Btree index, test again, delete within 1s. The confirmation is caused by a hash index.

Optimization scenarios:

1. Change the delete to a full table delete or truncate without a Where condition (the table data is cached data).

2. Change the hash index to Btree index.

Note: Because the Btree index occupies a large amount of memory (tested, the Btree index occupies more than 6 times times the hash index), the database host is memory-intensive, so the priority is to use scenario 1.

This article is from "Poetry and Distance" blog, please be sure to keep this source http://elison.blog.51cto.com/11547624/1771144

Performance Analysis: Hash index causes Delete to slow