Versions Compared

Old Version 5

changes.mady.by.user Valerio Ciotta

Saved on

compared with

New Version Current

changes.mady.by.user Valerio Ciotta

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Excerpt

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

 

Table of Contents

Introduction

 

Warning

This guide include 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 a 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 in a during low traffic time periodinterval.

AnywayIn 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!

Perform Performing a database dump is not a complex operation, but you need to know which what exactly is exactly 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

Code Block
languagesql
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. 

Code Block
languagebash
themeEmacs
mysqldump -u root -p xcally > xcally_dump.sql

and enter the MySQL root password.

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

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

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

Code Block
 mysql -u root -p

and enter the root password.

Code Block
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.

Code Block
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";
Note
titleImportant

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)

Code Block
mysql> delete from agent_log order by id limit 10000;

or both conditions:

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

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

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

 

and run the optimization

Code Block
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:

 

 

 

Info
titleNote

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

 

 

Page Properties
hiddentrue
Related issues