I’m struggling to clean up the AsteriskCDRDB that’s having slow MYSQL Performance. I have reviewed the post below but still struggling. Any ideas would be appreciated.
ERROR 1206 (HY000): The total number of locks exceeds the lock table size.
I think the problem is that the isolation level requires eventtime for the whole table to locked to prevent any eventtime field being changed to all into the range after it has been passed. Furthermore, a quick documentation search suggest that, unlike MS SQL Server, row locks can’t be escalated to a table lock in an Innodb database.
I suspect that using a range, rather than a pattern match may help here.
If not, you could reduce the isolation level, assuming you are sure that no records can get created in the range.
Finally, it might be possible to force a table lock. It wasn’t clear whether this is an option for Innodb, on a quick search of the documentation.
(If the query optimiser is failing to realise that this is an indexed range, in disguise, the range is also likely to run a lot faster. If you have a pattern that has a range element, but cannot be reduced to just a range, including the containing range, explicitly, may well help the query optimiser get a better strategy.)
What does SELECT eventtime FROM cel WHERE eventtime LIKE '2020-11-16 %' return, is it huge?
You limit the previous query to one and then illogically try and delete all that match without a LIMIT, ( I am saying that you can DELETE with LIMITs, even in innodb, but even then the on-disk size of the database won’t decrease until you export,delete,flush,re-import as stated).
Perhaps rather than wildcards which is making the db figure things out you just get concise and see if it helps…
DELETE FROM cel WHERE eventtime BETWEEN '2020-11-16 00:00:00' AND '2020-11-16 23:59:59'
1 Like
The OP has already told you how many records it would return:
Which is the concrete form of what I was suggesting, except that I would add some decimal places, or include midnight on the next day (unlikely to compromise the intended use).
No he hasn’t, he said what that query qualified with LIMIT 1 returns, unsurprisingly, it returned 1 
mysql returns dates and times as strings , you can qualify further by hours ,minutes and seconds, using decimal subdivision although possible would be byzantine and unnecessary here
There is no LIMIT on the query and the result looks like 5 to me!
Wrong query under discussion here, that was the return from another COUNT(*) query. .
I wasn’t aware that, by default, MySQL violates the standard, by default. However, I’d still suggest it better not to rely on the database making times be discrete.
The other one wasn’t a count at all, although it does show that performance hit from using a pattern match. The order by one gets the earliest record and is done on the index. The count gets all for the day, and is clearly done by a complete scan of the table.
Unix epoch time is always ‘discrete’ even if subdivided by micro seconds.
Let me just suggest that the original
DELETE FROM cel WHERE eventtime LIKE ‘2020-11-16 %’
might more concisely be achieved by 24 versions of
DELETE FROM cel WHERE eventtime LIKE ‘2020-11-16 01%’
.
.
DELETE FROM cel WHERE eventtime LIKE ‘2020-11-16 23%’
or a limited range less than 24 hours as @jfinstrom intimated.
If my analysis is right, each of those partial deletes will fail (maybe the ones that select nothing might succeed, but probably not). Using a range is the right (and efficient) way of doing this.
You could get probably get the best of both worlds, with:
DELETE FROM cel WHERE eventtime BETWEEN '2020-11-16 00:00:00' AND '2020-11-17 00:00:00' AND eventtime LIKE ‘2020-11-16 %’
Thanks everyone! It worked now…
Database changed
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime LIKE ‘2020-11-16 %’;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime BETWEEN ‘2020-11-16 00:00:00’ AND ‘2020-11-17 00:00:00’ AND eventtime LIKE ‘2020-11-16 %’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘-11-16 %’’ at line 1
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime BETWEEN ‘2020-11-16 00:00:00’ AND ‘2020-11-17 00:00:00’;
Query OK, 5 rows affected (0.01 sec)
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime LIKE ‘2020-11-16 %’;
^CCtrl-C – query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime BETWEEN ‘2020-11-16 00:00:00’ AND ‘2020-12-17 00:00:00’;
Query OK, 48165 rows affected (1.29 sec)
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.