We have a Vicidial cluster that has been working since 2015, for many years we only run the archive scripts once every 6 moths, but at some point our call center grew so much, that we had to start running every night the archive script even using the daily option of the archive script.
A few moths ago the reports started going super slow when we used the "use archive" option, and since the vicidial_log table was going archived every night, all the reports was super super slow.
An example, was a report from a small campaign with only 411 records took 5 minutes to generate :/
I though since the archive table had records since 2015 it was going to be huge, and indeed, it was a 28Gb table. So i made a backup, and delete all old records, everything older than Jan 01, 2019, was deleted.
I tried again to generate the report, and STILL WAS SUPER SLOW, even though, I deleted 60% of the records on all archive tables.
I know, my first approach was terrible, but this time i was going to do it smart.
I look into the slow queries for the query of the report and found the problematic query So I runned the query from mysql and...
- Code: Select all
SELECT vl.call_date,vl.phone_number,vl.status,vl.user,vu.full_name,vl.campaign_id,vi.vendor_lead_code,vi.source_id,vi.list_id,vi.gmt_offset_now,vi.phone_code,vi.phone_number,vi.title,vi.first_name,vi.middle_initial,vi.last_name,vi.address1,vi.address2,vi.address3,vi.city,vi.state,vi.province,vi.postal_code,vi.country_code,vi.gender,vi.date_of_birth,vi.alt_phone,vi.email,vi.security_phrase,vi.comments,vl.length_in_sec,vl.user_group,vl.alt_dial,vi.rank,vi.owner,vi.lead_id,vl.uniqueid,vi.entry_list_id from vicidial_users vu,vicidial_log_archive vl,vicidial_list vi where vl.call_date >= '2019-12-26 00:00:00' and vl.call_date <= '2019-12-27 23:59:59' and vu.user=vl.user and vi.lead_id=vl.lead_id and vl.campaign_id IN('AUSENTE') order by vl.call_date limit 1000000;
411 rows in set (1 min 22.59 sec)
1:22 minutes, was terrible, but it was worse when it took 5 min...
So I decide to performan an EXPLAIN and EUREKA... vicidial_log_archive WAS NOT USING ANY INDEX so it was retrivig ALL RECORDS FROM THE TABLE
- Code: Select all
MYSQL#
MariaDB [asterisk]> EXPLAIN SELECT vl.call_date,vl.phone_number,vl.status,vl.user,vu.full_name,vl.campaign_id,vi.vendor_lead_code,vi.source_id,vi.list_id,vi.gmt_offset_now,vi.phone_code,vi.phone_number,vi.title,vi.first_name,vi.middle_initial,vi.last_name,vi.address1,vi.address2,vi.address3,vi.city,vi.state,vi.province,vi.postal_code,vi.country_code,vi.gender,vi.date_of_birth,vi.alt_phone,vi.email,vi.security_phrase,vi.comments,vl.length_in_sec,vl.user_group,vl.alt_dial,vi.rank,vi.owner,vi.lead_id,vl.uniqueid,vi.entry_list_id from vicidial_users vu,vicidial_log_archive vl,vicidial_list vi where vl.call_date >= '2019-12-26 00:00:00' and vl.call_date <= '2019-12-27 23:59:59' and vu.user=vl.user and vi.lead_id=vl.lead_id and vl.campaign_id IN('AUSENTE') order by vl.call_date limit 1000000;
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
| 1 | SIMPLE | vl | | NULL | NULL | 0 | NULL | 89123855 | |
| 1 | SIMPLE | vu | eq_ref | user | user | 62 | asterisk.vl.user | 1 | |
| 1 | SIMPLE | vi | eq_ref | PRIMARY | PRIMARY | 4 | asterisk.vl.lead_id | 1 | |
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
So I run DESCRIBE TABLE on it to verify if it hast indexes, and indeed there were a primary key (useless for this query), lead_id and call_date, so why this was not using the index?
- Code: Select all
MYSQL# describe vicidial_log_archive;
+---------------+------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------------------------------------------------------------------+------+-----+---------+-------+
| uniqueid | varchar(20) | NO | PRI | NULL | |
| lead_id | int(9) unsigned | NO | MUL | NULL | |
| list_id | bigint(14) unsigned | YES | | NULL | |
| campaign_id | varchar(8) | YES | | NULL | |
| call_date | datetime | YES | MUL | NULL | |
| start_epoch | int(10) unsigned | YES | | NULL | |
| end_epoch | int(10) unsigned | YES | | NULL | |
| length_in_sec | int(10) | YES | | NULL | |
| status | varchar(6) | YES | | NULL | |
| phone_code | varchar(10) | YES | | NULL | |
| phone_number | varchar(18) | YES | | NULL | |
| user | varchar(20) | YES | | NULL | |
| comments | varchar(255) | YES | | NULL | |
| processed | enum('Y','N') | YES | | NULL | |
| user_group | varchar(20) | YES | | NULL | |
| term_reason | enum('CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','NONE','SYSTEM') | YES | | NONE | |
| alt_dial | varchar(6) | YES | | NONE | |
| called_count | smallint(5) unsigned | YES | | 0 | |
+---------------+------------------------------------------------------------------------------+------+-----+---------+-------+
I run SHOW INDEX FROM vicidial_log_archive; and again EUREKA... Cardinality on all INDEXES was NULL
- Code: Select all
MYSQL# show index from vicidial_log_archive;
+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| vicidial_log_archive | 0 | PRIMARY | 1 | uniqueid | A | 89123855 | NULL | NULL | | BTREE | | |
| vicidial_log_archive | 1 | lead_id | 1 | lead_id | A | NULL | NULL | NULL | | BTREE | | |
| vicidial_log_archive | 1 | call_date | 1 | call_date | A | NULL | NULL | NULL | YES | BTREE | | |
+----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
So, after a quick google, it say ANALYZE should fix it, so i run
- Code: Select all
ANALYZE table vicidial_log_archive
Not really sure why it didnt work, but if mysql client was laying me down, my last option was myisamchk
so
- Code: Select all
LINUX# service mysql stop
LINUX# myisamchk --analyze /src/mysql/mysql-files/asterisk/*MYI
A few hours later....
LINUX# service mysql start
MYSQL#
MariaDB [asterisk]> EXPLAIN SELECT vl.call_date,vl.phone_number,vl.status,vl.user,vu.full_name,vl.campaign_id,vi.vendor_lead_code,vi.source_id,vi.list_id,vi.gmt_offset_now,vi.phone_code,vi.phone_number,vi.title,vi.first_name,vi.middle_initial,vi.last_name,vi.address1,vi.address2,vi.address3,vi.city,vi.state,vi.province,vi.postal_code,vi.country_code,vi.gender,vi.date_of_birth,vi.alt_phone,vi.email,vi.security_phrase,vi.comments,vl.length_in_sec,vl.user_group,vl.alt_dial,vi.rank,vi.owner,vi.lead_id,vl.uniqueid,vi.entry_list_id from vicidial_users vu,vicidial_log_archive vl,vicidial_list vi where vl.call_date >= '2019-12-26 00:00:00' and vl.call_date <= '2019-12-27 23:59:59' and vu.user=vl.user and vi.lead_id=vl.lead_id and vl.campaign_id IN('AUSENTE') order by vl.call_date limit 1000000;
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
| 1 | SIMPLE | vl | range | lead_id,call_date | call_date | 6 | NULL | 37034 | Using index condition; Using where |
| 1 | SIMPLE | vu | eq_ref | user | user | 62 | asterisk.vl.user | 1 | |
| 1 | SIMPLE | vi | eq_ref | PRIMARY | PRIMARY | 4 | asterisk.vl.lead_id | 1 | |
+------+-------------+-------+--------+-------------------+-----------+---------+---------------------+-----------+------------------------------------+
THE KEYS ARE WORKING NOW!!!! run the query again... and SOLVED
- Code: Select all
SELECT vl.call_date,vl.phone_number,vl.status,vl.user,vu.full_name,vl.campaign_id,vi.vendor_lead_code,vi.source_id,vi.list_id,vi.gmt_offset_now,vi.phone_code,vi.phone_number,vi.title,vi.first_name,vi.middle_initial,vi.last_name,vi.address1,vi.address2,vi.address3,vi.city,vi.state,vi.province,vi.postal_code,vi.country_code,vi.gender,vi.date_of_birth,vi.alt_phone,vi.email,vi.security_phrase,vi.comments,vl.length_in_sec,vl.user_group,vl.alt_dial,vi.rank,vi.owner,vi.lead_id,vl.uniqueid,vi.entry_list_id from vicidial_users vu,vicidial_log_archive vl,vicidial_list vi where vl.call_date >= '2019-12-26 00:00:00' and vl.call_date <= '2019-12-27 23:59:59' and vu.user=vl.user and vi.lead_id=vl.lead_id and vl.campaign_id IN('AUSENTE') order by vl.call_date limit 1000000;
411 rows in set (0.02 sec)
Now we are talking... from 1:22 minutes to 0:00:02
To be honest i am not sure why the indexes were lost, but i will add checking indexes to my activities during maintenance
Hope this help.
