Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.10
-
Windows
Description
Have two tables RLOG, RREC which basically Looks like this:
 |
CREATE TABLE rrec (
|
RREC_TIMESTAMP_DT varchar(25) NOT NULL, |
RREC_SESSION_UID varchar(70) NOT NULL, |
... (shorten) ...
|
PRIMARY KEY (RREC_SESSION_UID),
|
...
|
)
|
ENGINE = INNODB
|
AVG_ROW_LENGTH = 197 |
CHARACTER SET latin1
|
COLLATE latin1_general_cs
|
ROW_FORMAT = COMPRESSED;
|
and
CREATE TABLE rlog (
|
RLOG_ID varchar(70) NOT NULL, |
RLOG_LOGENTRY longtext DEFAULT NULL,
|
PRIMARY KEY (RLOG_ID),
|
UNIQUE INDEX IDX_UK_rlog (RLOG_ID),
|
CONSTRAINT RLOG_RREC_FK FOREIGN KEY (RLOG_ID)
|
REFERENCES rrec (RREC_SESSION_UID) ON DELETE CASCADE ON UPDATE CASCADE
|
)
|
ENGINE = INNODB
|
AVG_ROW_LENGTH = 2343 |
CHARACTER SET latin1
|
COLLATE latin1_general_cs
|
ROW_FORMAT = COMPRESSED;
|
(see attachments for full table creation SQL)
Both tables have several rows:
RLOG: 1 MIO
RREC: 1,3 MIO
From time to time i want to delete some entrys from RLOG table:
DELETE FROM RLOG WHERE RLOG_ID in (SELECT RREC_SESSION_UID FROM RREC WHERE RREC_TIMESTAMP_DT < '2016-01-01') LIMIT 1000; |
As long as at least 1000 rows are deleted everything is fine:
1000 rows deleted [0,107s] |
but if i delete less then 1000 rows the delete Operation takes very long time:
847 rows deleted [24,270s] |
and even worse if nothing is deleted it takes Long time also:
0 rows deleted [24,090s] |
Profiling of such a statement shows the following:
Step | Time | Percent |
---|
updating |51,634785 |99,86|
query end |0,033120 |0,06|
statistics |0,014102 |0,03|
end |0,009650 |0,02|
Unlocking tables |0,005223 |0,01|
freeing items |0,003979 |0,01|
updating status |0,001728 |0,00|
starting |0,001212 |0,00|
init |0,001178 |0,00|
Opening tables |0,000821 |0,00|
preparing |0,000791 |0,00|
closing tables |0,000760 |0,00|
After opening tables |0,000380 |0,00|
checking permissions |0,000288 |0,00|
Table lock |0,000227 |0,00|
cleaning up |0,000024 |0,00|
System lock |0,000020 |0,00|
optimizing |0,000013 |0,00|
If you like I can provide you my testdata (6GB).
Attachments
Issue Links
- relates to
-
MDEV-7487 Semi-join optimization for single-table update/delete statements
- Closed