Bug #26929 DELETE statement takes too long
Submitted: 7 Mar 2007 18:54 Modified: 8 Mar 2007 13:20
Reporter: Mario Batista Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version: 5.0.27 OS:Linux (Redhat 9)
Assigned to: Heikki Tuuri CPU Architecture:Any

[7 Mar 2007 18:54] Mario Batista
Description:
Our DB has a large amount of disk space and we insert lots of rows. Seven million rows per day. After a month we start to delete a whole day (time column is indexed). We notice that some DELETE commands stall others not. After a DELETE that stalls we shutdown the DB (only by executing kill -9) and start storing new records from zero. InnoDB monitor produces some logs reporting 
"WARNING: over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index".
We have a DB with the following config:

innodb_data_file_path = /db3/lib/ibdata3:60000M;/db2/lib/ibdata4:10000M;/traces1/lib/ibdata5:30000M
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=20M

Printout of the time where the DELETE was running for 241984 seconds:

[root@smarteye root]# mysqladmin processlist
+--------+------+-----------+------+---------+--------+----------+---------------------------------------------------------------------+
| Id     | User | Host      | db   | Command | Time   | State    | Info                                                                
+--------+------+-----------+------+---------+--------+----------+---------------------------------------------------------------------+
| 76437  | root | localhost | cdrs | Killed  | 241984 | updating | DELETE FROM cdr_j20 where recordOpeningTime < "2007-02-07 00:00:00" |
| 184659 | root | localhost |      | Query   | 0      |          | show processlist                                                    |

Size of the database:

mysql> show table status from cdrs;
+---------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+
| Name    | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                  |
+---------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+
| cdr_j20 | InnoDB |      10 | Compact    | 188005697 |            344 | 64832421888 |               0 |  22651863040 |         0 |           NULL | 2007-02-05 18:12:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 13715456 kB |
+---------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+
1 row in set (1.65 sec)

How to repeat:
We get behavior every time the DB gets +/- 80% full.
[7 Mar 2007 19:18] Valeriy Kravchuk
Thank you for a problem report. Please, send your entire my.cnf, SHOW INNODB STATUS results next time you will have a long running DELETEs, and the results of:

uname -a
free

Linux commands.
[8 Mar 2007 12:33] Mario Batista
[user@hostname user]$ uname -a
Linux hostname 2.4.20-8smp #1 SMP Thu Mar 13 17:45:54 EST 2003 i686 i686 i386 GNU/Linux

I will send the free command output when I check that a DELETE is taking too long.

I uploaded to the FTP Server the file with the INNODB Monitor output when the problem happened and the my.cnf file.
ftp://ftp.mysql.com/pub/mysql/upload/bub_id_26929_my.cnf
ftp://ftp.mysql.com/pub/mysql/upload/bub_id_26929_mysqld.log.zip
[8 Mar 2007 13:20] Heikki Tuuri
Mario,

you should make innodb_buffer_pool bigger. You have 100 GB of data files, but only 100 MB of buffer pool!

When you do a massive delete, the row lock table of InnoDB may exhaust the buffer pool.

Regards,

Heikki