Save HDD space removing the old logs

If your HDD is almost full, you can remove the old logs data and optimize the MySQL disk utilization. 

Introduction

To perform the following queries and commands, you can use the mysql command line or any MySql client such as WorkBench or SQLyog, etc..

Depending on the database size, the following procedure can take some minutes and have an impact on the server performance.

We suggest you to execute the DB optimization when your callcenter is closed or during low traffic time interval.

In any case, the optimization procedure needs to lock the tables so we recommend to stop all callcenter activities (xCally could not write any data inside the DB, during the optimization phase). 

First of all, backup the data!

Performing a database dump is not a complex operation, but you need to know what exactly is the database size because you will need enough space to save the dump file.

You can get the DB size by running the following query

SELECT table_schema "Data Base Name", 
SUM( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB"
FROM information_schema.TABLES 
GROUP BY table_schema ;

Once you know how many hdd space you need to save your dump file, you can proceed to backup the database. In this example we use the MySQL command mysqldump. 

mysqldump -u root -p xcally > xcally_dump.sql

and enter the MySQL root password.

When the dump is completed, you'll find a file like the following one:

-rw-r--r-- 1 root root  16084420 Nov  7 12:19 xcally_dump.sql

Delete the old records

To reduce the size of the database, we need to remove the old records from the tables.

In the xCally database, the tables with the larger amount of records are:

agent_log

cdr

call_log

queue_log

To check the size for each table you can just run the following SQL command using a MySQL client or the MySQL command line

 mysql -u root -p

and enter the root password.

mysql> show table status from xcally;

or check the database info, for example in the SQLyog

In the example below, we will delete the records older than January 2016.

mysql> use xcally;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete from agent_log where rtdtm < "2016-01-01";

Example, we want to delete only 10.000 records at the time (use the "order by id" to be sure to delete the older records and not the latest)

mysql> delete from agent_log order by id limit 10000;

or both conditions:

mysql> delete from agent_log where rtdtm < "2016-01-01" order by id limit 10000;

Optimize the table

Now we are ready to complete the table optimization.

We need just to run a single command: optimize table tablename;

Example:

Our queue_log table is about 8 MB (we know it's a ridiculous size but it's just an example)

We remove the old record

mysql> delete from queue_log where time < '2016-01-01';
Query OK, 24181 rows affected (0.68 sec)

and run the optimization

mysql> optimize table queue_log;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| xcally.queue_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| xcally.queue_log | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1.08 sec)

Now, the new size is: