Since the tables may have a huge size, we recommend to stop all callcenter activities before running the
procedure for the first time in order not to impact with the agent activities.
- All the agents need to be logged out from the XCALLY server
- All the Phonebars need to be closed
- Stop the Asterisk service
# service asterisk stop
# service motion stop
After that, you can also use the procedure on a live server. Please always consider the amount of data to
delete and the system perforance can decrease during the delete procedure.
We recommend to always execute it when the services are closed.
List of the tables
All the tables with prefix report_ (eg. report_call, report_agent, report_member, report_chat_queue, etc)
Connect to the Motion DB with the root credentials. You can use the MySQL CLI on the server or your favorite MySQL client (Wotkbench, SQLJog,
HeidiSQL or the MySQL CLI)
In the following example we'll operate on the report_agent but the procedure can be applied to every report tables. Just replace d_report_agent and
report_agent with the table you need to operate with.
Create a temporary table in order to collect the ID of the records to be deleted (first time only)
CREATE TABLE d_report_agent SELECT id FROM report_agent WHERE 1=2;
Create a composite index in order to speed up the ID collection (first time only)
ALTER TABLE report_agent ADD INDEX del_id_ndx (createdAt,id);
Insert into the temp table the ID of the records to be deleted
Please remember to backup the database before proceed with the record deletion.
INSERT INTO d_report_agent SELECT id FROM (SELECT id FROM report_agent where createdAt < date_sub(now(), INTERVAL 90 DAY) ORDER BY createdAt ) A LIMIT 50000;
The WHERE clause is customizable according with your needs, in the example we use an interval of 90 days. It means we are going to
delete the records older than 90 days
The LIMIT value defines how many rows we delete at the time. If you have millions of records, you may need to limit the amount of records
to be deleted and run the procedure multiples times.
Add index into the temporary table
ALTER TABLE d_report_agent ADD PRIMARY KEY (id);
And finally the delete of the records from both tables
DELETE B.* FROM d_report_agent INNER JOIN report_agent B USING (id);
At the end, we can empty** the temporary table
TRUNCATE TABLE d_report_agent;
** just empty the table instead of delete it so you don't need to create it every time you execute the procedure
- Please note that procedure can be added into a stored procedure and scheduled using the MySQL internal scheduler or included in a cronjob according with your needs.If you decide to use a scheduler, please remember to keep a correct ratio between frequency and rows to be deleted. eg. (100 rows every 5 minutes or 500.000 rows every 24h)