Geek asks and answers

Still seeing a wait wait lock when deleting from large tables using the primary key

I have a distributed application that registers millions of records in MySQL. Sometimes it's a million per day or week, depending on the user.

I recently rewrote a “cleanup” system that automatically deletes obsolete entries. It works every 12 hours and cleans up the data in accordance with the rules set by the user. Since a database can often contain more than 50 million records on average, I designed a query to use primary key allocation .

Each delete request only checks a limited number of rows with its primary key. In my opinion, this reduces the number of locks needed to “keep” another when conditions are present. The next delete request is completed in a few seconds.

However, many of our users still see “wait wait locks,” and they always indicate cleanup requests.

DELETE FROM prism_data WHERE prism_data.id >= 7564001 AND prism_data.id < 7568001 AND prism_data.epoch <= '1388566847'

Here is part of the engine status report:

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 458, OS thread handle 0x7efed0c62700, query id 779832 localhost 127.0.0.1 prism updating
DELETE FROM prism_data WHERE prism_data.id >= 7564001 AND prism_data.id < 7568001 AND prism_data.epoch <= '1388566847'
------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 606 n bits 1272 index `epoch` of table `prism`.`prism_data` trx id 208A7E lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 52d7d976; asc R  v;;
 1: len 4; hex 00000001; asc     ;;

Just for you to have it, here is the table layout:

CREATE TABLE IF NOT EXISTS `prism_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `epoch` int(10) unsigned NOT NULL,
  `action_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `world_id` int(10) unsigned NOT NULL,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `z` int(11) NOT NULL,
  `block_id` mediumint(5) DEFAULT NULL,
  `block_subid` mediumint(5) DEFAULT NULL,
  `old_block_id` mediumint(5) DEFAULT NULL,
  `old_block_subid` mediumint(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `epoch` (`epoch`),
  KEY `location` (`world_id`,`x`,`z`,`y`,`action_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

, , , , , . mysql. -, , , , - ?

:

:

[13:43:47 INFO]: [Prism]: Database connection error: Lock wait timeout exceeded; try restarting transaction
[13:43:47 WARN]: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
[13:43:47 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[13:43:47 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
[13:43:47 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
[13:43:47 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
[13:43:47 WARN]:        at me.botsko.prism.actionlibs.ActionsQuery.delete(ActionsQuery.java:346)
[13:43:47 WARN]:        at me.botsko.prism.purge.PurgeTask.run(PurgeTask.java:84)

SHOW FULL PROCESSLIST, :

http://i.imgur.com/DxL4Rk1.png

,

+3
2

, DELETE, INFORMATION_SCHEMA.LOCK_WAITS INNODB_TRX.

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;

. http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema.html#innodb-information-schema-examples " 14.2 ".

:

blocking_query NULL, , , .

, - .

COMMIT ROLLBACK, . .

: , , pt-archiver. :

$ pt-archiver h=localhost,D=mydatabase,t=prism_data
    --purge --bulk-delete --commit-each --limit 1000 --where "epoch <= 1388566847"

, , 1000- , .

+6

( ):

1.

sql :

DELETE FROM prism_data WHERE prism_data.epoch <= '1388566847' limit 10000

, ROW_COUNT() is > 0, ROW_COUNT() <= 0 , 10000 ... (, mysql while , , 10,20, ). , .

2. - , "",

, ... ( , 50M - , ), , ...

, temp ( ), .

, prism_data prism_data_tmp, // prism_data.

PURGE

DELETE FROM prism_data_tmp WHERE prism_data_tmp.epoch <= '1388566847';

RENAME table prism_data_tmp to prism_data_sw, prism_data to prism_data_tmp, prism_data_sw to prism_data;

/* switch the tables and reRun the query on the newly temp table to be up to date*/

DELETE FROM prism_data_tmp WHERE prism_data_tmp.epoch <= '1388566847';

, , tmp, hevily... ( )

12 ... repeat

+1

More articles:


All Articles