Cleanup tool to prune tbllog_register table
-
bluesteam shared this idea 7 years ago
- Other
- 17 Comments
143
Votes
Login to Vote
This table gets EXTREMELY large in row size.
When performing certain clean up tasks by going to Utilities -> System -> System Cleanup we have tools that can clean up the following:
FileSystem - Prune Old Attachments
FileSystem - Empty Template Cache
tblactivitylog - Prune Client Activity Logs
tblemails - Prune Saved Emails
tblgatewaylog - Empty Gateway Log
tblticketmaillog - Empty Ticket Mail Import Log
tblwhoislog - Empty WHOIS Lookup Log
but there is nothing for the tbllog_register table. On a clean install of WHMCS, the table already starts out at +-2300 records and over time this grows immensely. My own table is sitting on 206 782 rows as we speak and it's clear that this table needs to be included in the list of cleanup tools as it can hinder restores when it needs to insert hundreds of thousands of rows. having a large table can also reduce performance and thus we should have the abilitiy to clean it up.
When performing certain clean up tasks by going to Utilities -> System -> System Cleanup we have tools that can clean up the following:
FileSystem - Prune Old Attachments
FileSystem - Empty Template Cache
tblactivitylog - Prune Client Activity Logs
tblemails - Prune Saved Emails
tblgatewaylog - Empty Gateway Log
tblticketmaillog - Empty Ticket Mail Import Log
tblwhoislog - Empty WHOIS Lookup Log
but there is nothing for the tbllog_register table. On a clean install of WHMCS, the table already starts out at +-2300 records and over time this grows immensely. My own table is sitting on 206 782 rows as we speak and it's clear that this table needs to be included in the list of cleanup tools as it can hinder restores when it needs to insert hundreds of thousands of rows. having a large table can also reduce performance and thus we should have the abilitiy to clean it up.
Merged Ideas
DB tbllog_register, this function requires automatic cleaning (Needs a cleanup feature.).
HiDB, tbllog_registerThis function requires automatic cleaning (Needs a cleanup feature.).It only takes a few months for it to become big data/size.Thanks
HiDB, tbllog_registerThis function requires automatic cleaning (Needs a cleanup feature.).It only takes a few months for it to become big data/size.Thanks
28 Comments
Login to post a comment.
tbllog_register 582269 98464 KB
Total Rows: 708294 - Total Size: 137308.29 KB
As you can see, my database is HUGE because of that table. It means I can no longer backup the database or upgrade due to timeouts because the database is so huge.
I should not be forced to manually go in an look at databases and manually delete stuff from databases.
It is the biggest table filled with info that is not useful beyond a few days.
This is causing timeouts on backups and should be easy to fix
Enable scheduler:
SET GLOBAL event_scheduler = ON;
Create the event, we clear all events > 90 days:
CREATE EVENT
whmcs_prune_tbllogON SCHEDULE EVERY 1 DAY STARTS '2023-01-01 01:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clear WHMCS tbllog_register over 90 days' DO DELETE FROMtbllog_registerWHERE created_at <= ( CURDATE() - INTERVAL 90 DAY );We also made a similar schedule for clearing tblactivitylog:
CREATE EVENT
whmcs_prune_tblactivitylogON SCHEDULE EVERY 1 DAY STARTS '2023-01-01 01:05:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clear WHMCS tblactivitylog over 90 days' DO DELETE FROMtblactivitylogWHERE date <= ( CURDATE() - INTERVAL 90 DAY );Verify your events with:
SHOW EVENTS;
This is a nice solution
Created a WHMCS support ticket and they said:
"This is used for the Automation Status page in the admin area. We do not recommend to manually edit to this table. However, you could use a MySQL query to delete entries older than a specific date like so:
DELETE FROM
tbllog_registerWHERE created_at <= ( CURDATE() - INTERVAL 365 DAY );We need to be able to control our database from getting too large, so pruning of this table inside WHMCS would be ideal.