Description:
What about adding a reference in this doc page to using RENAME technique to delete many rows without locking too long.
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
proppy: is there a way to get around this error ?
proppy: ERROR 1206 (HY000): The total number of locks exceeds the lock table size
proppy: I'm trying to delete 20M row from a table
ebergen: an innodb table?
proppy: ebergen: yep
proppy: trying to set innodb_buffer_pool_size in my.cnf
ebergen: how many rows are in the table?
proppy: ebergen: 40 000 000
proppy: ebergen: I want to delete 20 000 000 rows
ebergen: hm
ebergen: you have basically two choices
ebergen: 1 delete the rows in small transactions
ebergen: 2. create a new table with the rows you want and do an atomic rename
ebergen: innodb simply wasn't built to delete 20mil rows in a single transaction
proppy: ebergen: atomic swap you mean ?
proppy: rename would likely fail if the table exist ?
ebergen: proppy rename table a to b, b to a;
ebergen: err
ebergen: a to b, c to a
proppy: ebergen: what if there are request to a in the between ?
ebergen: rename table locks all the tables before renaming them
ebergen: that's why it's an atomic rename
proppy: oh you mean renaming the both table is atomic ?
ebergen: there is an example at the top of: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
proppy: ebergen: thanks a lot
How to repeat:
read http://dev.mysql.com/doc/refman/6.0/en/delete.html
no reference to RENAME except in the comment
(note that TRUNCATE is referenced as an alternative way to delete a whole table)
