How to Safely Purge Large Tables and Free Disk Space in My V

[ambiorixg12]

I’m trying to release some space from my disk by directly purging certain tables in the database that contain essential log information and can be safely removed. I ran this command and realized that two tables were taking up all the space on my disk:

SELECT table_schema, table_name, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_size_MB FROM information_schema.tables GROUP BY table_schema, table_name ORDER BY total_size_MB DESC LIMIT 10;

Code: Select all

table_schema   table_name   total_size_GB
asterisk   vicidial_api_urls   1.03
asterisk   vicidial_url_log   0.59
asterisk   vicidial_api_log   0.32
asterisk   vicidial_list   0.18
asterisk   call_log   0.10

So, I deleted the content of these two tables: vicidial_url_log and vicidial_api_log. I assume this is essential log information. Are there any other tables I can safely purge without harming the system?

[williamconley]

PLEASE remember to post your full Vicidial Version with Build on all posts! (at a minimum: You should also post your full installer version, and arguably your SVN level)

essential log information

I'm assuming you meant "NONessential" log information. LOL.

Code: Select all

# Clear the "daily use" mysql logs that are not used in reports after they are archived
23 22 * * *  mysql asterisk -e "TRUNCATE vicidial_dial_log_archive" > /dev/null
24 22 * * *  mysql asterisk -e "TRUNCATE vicidial_log_extended_archive" > /dev/null
26 22 * * *  mysql asterisk -e "TRUNCATE vicidial_api_log_archive" > /dev/null
27 22 * * *  mysql asterisk -e "TRUNCATE vicidial_carrier_log_archive" > /dev/null


* Note that you may need to add credentials to the mysql line depending on your mysql configuration (if the mysql "root" user has a password, for instance). You CAN alter these to truncate the NonArchive logs instead, but I recommend use of the archiving script instead. I try to avoid truncating the active/non-archive log tables directly. At least until the next day or two ... (in case they are actually needed once every year for real troubleshooting)

Have a look at the notes in the top of this script:

/usr/share/astguiclient/ADMIN_archive_log_tables.pl

There are some tables eligible for pruning. Others for archiving. You can then prune the archived tables beyond a specific date if you no longer need the information. This script will also optimize the log tables (which frees up space: deleting records does NOT unless you truncate). Managing your pruning/archiving/retention periods is a Good Practice for any database system with logs (often even for those without ... do you still need leads from 2012? LOL)

Happy Hunting!

[ambiorixg12]

Thank you for your reply. Yes, you're right, I meant 'non-essential.' Based on the code you posted with the truncate instructions, I logically assume that all tables tagged as 'log' are candidates for safe purging, unless they are needed for some kind of troubleshooting or debug information.

[williamconley]

Thank you for your reply. Yes, you're right, I meant 'non-essential.' Based on the code you posted with the truncate instructions, I logically assume that all tables tagged as 'log' are candidates for safe purging, unless they are needed for some kind of troubleshooting or debug information.

Not true.

Most REPORTS in the Vicidial system are based on Logs (ie: tables ending in _log). But some tables that end in log are not used in any reports.

[ambiorixg12]

Thank you for the clarification. For now, cleaning this table keeps the disk space stable, and there is no impact on our daily report usage.

asterisk vicidial_api_urls 1.03
asterisk vicidial_url_log 0.59
asterisk vicidial_api_log 0.32

[williamconley]

good postback. just for the sake of discussion: be sure those logs are being filled by YOU and not by an attacker. always whitelist any vicidial servers.