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

Introduction

This guide includes some suggestions in order to keep safe and clean your xCally database.

We strongly recommend you to refer to your DBA before doing any operations on the database.

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

For any further information about the mysqldump command, please refer to the MySql official documentation.

You might also use other MySQL client in order to export the database as a csv file (one per table), specially if your database size is very big.

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";

Important

The DELETE command doesn't reduce the table size, on the contrary, the size will increase due the MySQL operation on the deleted records. So pay attention when you specify the amount of data to delete.

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;

Pay attention the DELETE statement locks the table so no other operation will be allowed on that table during the delete process

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:

Note

In this guide we've shown you how to delete the old logs file, but please always remember to backup your database before deleting any records.

Browser not supported