Before setting up cron for automated deletion of CDR, i tried to delete CDR by following these ;
mysql
use asteriskcdrdb;
DELETE from cdr WHERE calldate < ‘2018-06-05 00:00:00’;
Everything worked perfectly, as expected . before setting up cron job for automated job, i tried to repair as well optimize database as suggested in this thread but after executing these ,
repair table cdr;
repair table cel;
optimize table cdr;
optimize table cel;
| asteriskcdrdb.cdr | repair | note | The storage engine for the table doesn’t support repair | ,
I am getting this message . I tried to lookup into things and it seems InnoDB storage type is used , we can’t use repair or optimize for InnoDB type .
To follow this script either i have to convert to MyISAM to use repair or optimize command , or should i leave it to default InnoDB and ignore repair or optimize ?
This is an old topic but it got me thinking as I needed to clean the cdr tables on live system. Deleting and reorganizing large tables is slow and resource-intensive. I created a script that actually copies most recent data to new tables then drops old large tables. This creates new optimized tables and runs under 10 minutes. The script came out quite large and I put the script on GitHub for everyone to use. Hope you find this helpful.
I’m having issues on one server and can’t clean up the cel table…
Any ideas? I’ve narrowed the search down to 1 hour instead of a month or year.
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime BETWEEN ‘2020-10-25 00:00:00’ AND ‘2020-10-25 01:00:00’;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MariaDB [asteriskcdrdb]> optimize table cel;
±------------------±---------±---------±------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±------------------±---------±---------±------------------------------------------------------------------+
| asteriskcdrdb.cel | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| asteriskcdrdb.cel | optimize | status | OK |
±------------------±---------±---------±------------------------------------------------------------------+
2 rows in set (1 hour 46 min 20.38 sec)
MariaDB [asteriskcdrdb]> DELETE FROM cel WHERE eventtime BETWEEN ‘2020-10-25 00:00:00’ AND ‘2020-10-25 01:00:00’;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MariaDB [asteriskcdrdb]>
@erobertus This post should have been in a new thread in tips and tricks as opposed to necro-posting to 6 year old thread.
I and others in support have done similar operations to cdr/cel tables over the years, and would use commands like this
CREATE TABLE new_cdr LIKE cdr;
INSERT INTO new_cdr SELECT * FROM cdr WHERE calldate >= CURDATE() - INTERVAL 3 MONTH;
RENAME TABLE cdr TO cdr_old, cdr_new TO cdr;
which are certainly simpler and more fault tolerant in case the tables on the system have been altered from what the script is expecting.
No. There probably are huge systems that might benefit from that, but as a practical matter Ive not seen cases where generating cdr reports are too slow.
Lol, that’s plain and simple, sure. It will work fine but I don’t know how atomicity of those tables is crucial for asterisk. So,my point was to make sure I do not lock and do not lose any records.
