mysql maintainance

[Op3r]

Im thinking about rolling back the call_log tables as 1 of my database is now like 4 gb now

any way to optimize and roll back the logs created by vicidial? How do you do it?

[spaquet]

As far as I understand this is more a MySQL issue.

I would first go for a MySQL Dump. This is a way to backup your data (check MySQL documentation for more information on mysqldump command)

Once you have your backup done, you can try deleting part of the log.

It is always safe to perform a backup before deleting rows in a database, particularly when you do not know your future needs.

Regarding optimization, I'm using the tool provided by either phpMyAdmin or MySQL GUI tools.
There are tools in both to optimize,check, repair tables and databases.

[mflorell]

We've discussed log rolling a few times on the forums. What we usually do is create an "archive" table for call_log, vicidial_log and vicidial_agent_log that we roll the existing logs into so them we can purge the active log tables.

First you create a new table(removing AUTO_INCREMENT if there is one), then you run something like the following:

SELECT count(*) from call_log;
SELECT count(*) from call_log_archive;
INSERT IGNORE INTO call_log_archive SELECT * from call_log;
DELETE FROM call_log WHERE start_time < '2009-05-01 01:00:00';
optimize table call_log;
optimize table call_log_archive;