MySQL Bugs: #14127: Large deletes on large table lock queries on unrelated tables

Bug #14127	Large deletes on large table lock queries on unrelated tables
Submitted:	19 Oct 2005 1:40	Modified:	30 Nov 2005 13:16
Reporter:	Chip Yamasaki	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.18-standard-log	OS:	Linux (RHEL3 - 2.4.21-20.ELsmp)
Assigned to:		CPU Architecture:	Any

Description:
We have a large table table, the structure of which is shown below.  The table currently has 47,000,000 rows.  We have a process that deletes about 100k rows in a single batch using idx2.  This query will run for about 15 minutes in an "updating" state and everything is fine.  Then it enters an "end" state and remains there for about 6 minutes or so.  While it is in this state, any new query on other tables that might use indexes are held in a "statistics" state and go nowhere until the delete leaves the "end" state and finishes.  These other queries are very light and should finish instantly.

CREATE TABLE tbl1 (
  fld1 varchar(255) NOT NULL default '',
  fld2 double(25,12) default NULL,
  date date NOT NULL default '0000-00-00',
  fld3 varchar(80) NOT NULL default '',
  fld4 int(10) default NULL,
  fld5 int(10) default NULL,
  fld6 int(10) default NULL,
  fld7 int(10) default NULL,
  PRIMARY KEY  (fld1,date,fld3),
  UNIQUE KEY idx1 (date,fld1,fld3,fld4,fld5),
  KEY idx2 (fld3,date)
) TYPE=MyISAM;

How to repeat:
Create a large table of the structure described above (indexes may not be relevant).  Populate it with millions of rows.  You might need to issue deletes and inserts to generate enough fragmentation to cause the delays we see.  Then issue a range-based delete that removes a significant number of rows.  When this delete enters the "end" state, issue new queries and watch them freeze in a "statistics" state.

Thank you for a problem report. 

Do you have FOREIGN keys in those other tables referencing this one? Can you, please, send the results of SHOW PROCESSLIST statement that illustrates your description. You my.cnf content and hardware description may be useful too.

There are no foreign keys in this or other tables.  The processlist, my.cnf, and hardware info are on their way.

Please, note the additional information I asked you to provide in the http://bugs.mysql.com/bug.php?id=14070. You may switch this issue back to open then. 

The only thing I can recommend you on this problem now is to try long-waited 4.1.15 version now available.

I believe part of the problem with this original issue is that access to the key_buffer in 4.0 was serialized.  That appears to be the reason why the other queries were entering the "statistics" state when the original query entered the "end" state.  One question would be why the original query stayed in the "end" state so long.  I believe I read somewhere that flushing of dirty pages to disk could occur in less-than-optimal write sizes under certain conditions, but I could be wron about that.

In any case, this is no longer an issue for me, although I'm sure it remains for others.  I have a bigger issue now in 14070, in which the query never ends at all.  I'll provide more data on that one shortly.

In case you will be still interested in pinpointing this possible bug in 4.0.x, please, try to repeat it on 4.0.26. We need a repeatable test case that allows to get this delete being in the "end" state for a long time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".