vicidial.org

Hi all, this is not a request for help, this is more like a STORY OF A SQL NIGHTMARE AND HOW WE SOLVED so get a cup of coffee and i hope it helps someone in the future.

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

aaaaaand IT DIDNT WORK

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.

Also note that merely deleting entries will NOT immediately improve speed. You must optimize the tables in question to get that speed kick.

We've also added some specific indexes to certain tables specific to individual client systems. Note that if Vicidial had indexes on everything for all circumstances and call center types for their various reports, the existence of all those indexes would bog the system down drastically.

So ... check slow queries for hints on new indexes. Be careful not to add too many since every index adds a tiny bit of time to each new or modified record. And yes: adding a well-designed index on point for a specific report will change a several minute report into a one second report.

Well done!