0

I am using mysql server v5.1.73 on a Centos 6.4 64bit operating system.I have a table with about 17m records that it's size is about 10GB. mysql engine for this table is innodb.
This table has 10 columns and one of them is 'date' which its type is datetime. I want to delete records of a specific date with mysql 'delete' command.

delete
from table
where date(date) = '2015-06-01'
limit 1000

but when I run this command, i get an error 'the total number of locks exceeds lock table size'. I had this problem before and when i change innodb_buffer_poolsize, it would fix the problem but this time even increasing this amount, problem still exits.
I tried many tricks like changing limit value to 100 or even 1 record, but it doesn't work. I even increased innodb-buffer-poolsize to 20GB but nothing changed.
I also read these links:
"The total number of locks exceeds the lock table size" Deleting 267 Records and
The total number of locks exceeds the lock table size
but they didn't solve my problem. my server has 64GB RAM.
in the other hand, I can delete records when not using a filter on a specific date:

delete
from table
limit 1000

and also I can select records of the day without any problem.
can anyone help me with this? I would appreciate any help to fix the problem.

Community
  • 1
  • 1
farzad
  • 368
  • 1
  • 3
  • 15

2 Answers2

1

Don't use date(date), it cannot use INDEX(date). Instead simply use date and have an index beginning with date.

More ways to do a chunking delete.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

date(date) in the WHERE clause requires the database to calculate the value of that column for all 17m rows in the database at run time - the DB will create date(date) row by row for 17m rows, and then table scan (there is no index) those 17m rows to work out your result set. This is where you are running out of resources.

You need to remove the usage of the calculated column, which can be solved a couple of different ways.

Rather than doing date(date), change your comparison to be: WHERE date >= '2015-06-01 00:00:00' AND date <= '2015-06-01 23:59:59'

This will now hit the index on the date column directly (I'm assuming you have an index on this column, it just won't be used by your original query)

The other solution would be to add a column to the table of type DATE, and permanently store the DATE of each DATETIME in that column (and obviously add an index for the new DATE column). That would allow you to run any query you like that just wants to examine the DATE portion only, without having to specify the time range. If you've got other queries currently using date(date), having a column with just the date specifically in it might be a preferred solution than adding the time range to the query (adding the time range is fine for a straight index comparison in a SELECT or DELETE like here, but might not be a usable solution for other queries involving JOIN, GROUP BY, etc).

Peter Barton
  • 577
  • 1
  • 4
  • 12