vicidial.org

Hello there!

What is the recommended course of action for rotating/archiving the vicidial_list, vicidial_did_log and vicidial_log tables?

The question arises because i see some queries with a query_time of 13+" in the mysql-slow.log logfile.

I know there is a script to archive some tables (ADMIN_archive_log_tables), but what can i do for vicidial_list and vicidial_did_log?

Thanks!

Some examples:

# Thread_id: 88016061 Schema: asterisk QC_hit: No
# Query_time: 14.211249 Lock_time: 0.000081 Rows_sent: 1 Rows_examined: 6322950
# Rows_affected: 0
SET timestamp=1546955659;
SELECT count(*) FROM vicidial_list FORCE INDEX(list_id) where called_since_last_reset='N' and status IN('NEW') and ((list_id IN('900','902','903','904'))) and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.50','8.25','8.00','7.75','7.50','7.25','7.00','6.75','6.50','6.25','6.00','5.75','5.50','5.25','5.00','4.75','4.50','4.25','4.00','3.75','3.50','3.25','3.00','2.75','2.50','2.25','2.00','1.75','1.50','1.25','1.00','0.75','0.50','0.25','0.00','-0.25','-0.50','-0.75','-1.00','-1.25','-1.50','-1.75','-2.00','-2.25','-2.50','-2.75','-3.00','-3.25','-3.50','-3.75','-4.00','-4.25','-4.50','-4.75','-5.00','-5.25','-5.50','-5.75','-6.00','-6.25','-6.50','-6.75','-7.00','-7.25','-7.50','-7.75','-8.00','-8.25','-8.50','-8.75','-9.00','-9.25','-9.50','-9.75','-10.00','-10.25','-10.50','-10.75','-11.00','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') ) );

# Thread_id: 87074897 Schema: asterisk QC_hit: No
# Query_time: 3.141908 Lock_time: 0.000047 Rows_sent: 0 Rows_examined: 2057277
# Rows_affected: 0
SET timestamp=1546954470;
select uniqueid from vicidial_did_log where did_id='0';


====
ViciDial VERSION: 2.12-561a BUILD: 160708-0745 installed with ViciBox 7.0.2, cluster mode.

- 4 telephony nodes, Xorcom XE3000: 4 GB RAM, 500 GB HDD linux software RAID 1
- 1 Database node, Dell R720: 32 GB RAM, 500 GB HDD hardware RAID 10.
- 2 CentOS 7 hypervisors runnning KVM: 16 GB RAM, 500 GB HDD linux software RAID 1, containing
- 2 web nodes: 4 GB RAM, 50 GB HDD
- 1 load balancer running HAProxy: 1 GB RAM, 50 GB HDD, running in one of the hypervisors.
- 1 archive node.
====

You can manually archive leads in the vicidial_list table by using a set of MySQL queries like these:


CREATE TABLE vicidial_list_archive LIKE vicidial_list;
ALTER TABLE vicidial_list_archive MODIFY lead_id INT(9) UNSIGNED NOT NULL;

select count(*) from vicidial_list where list_id IN('8881106');

INSERT INTO vicidial_list_archive SELECT * from vicidial_list where list_id IN('8881106');
select count(*) from vicidial_list_archive where list_id IN('8881106');
DELETE from vicidial_list where list_id IN('8881106');
optimize table vicidial_list;



As for the DID log, in the ADMIN_archive_log_tables.pl script there is an option for DID archiving:
[--did-log-days=XX] = OPTIONAL, number of days to archive vicidial_did_log table only past

But if your system is not running recent code, you may not have that option, in which case I would recommend upgrading to the most recent svn/trunk revision.

http://catalog.poundteam.com/product_in ... ts_id=1385

http://catalog.poundteam.com/product_in ... ts_id=1386

If you don't want to mysql it every time. Also allows "bring them back" for reporting purposes.