If your HDD is almost full, you can remove the old logs data and optimize the MySQL disk utilization.
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
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.
and enter the MySQL root password.
When the dump is completed, you'll find a file like the following one:
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:
To check the size for each table you can just run the following SQL command using a MySQL client or the MySQL command line
and enter the root password.
or check the database info, for example in the SQLyog
In the example below, we will delete the records older than January 2016.
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)
or both conditions:
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;
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
and run the optimization
Now, the new size is:
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.