REPORTS ON ARCHIVE SUPER SLOW [SOLVED]

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

REPORTS ON ARCHIVE SUPER SLOW [SOLVED]

Postby covarrubiasgg » Fri Jan 03, 2020 2:12 pm

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.
covarrubiasgg
 
Posts: 420
Joined: Thu Jun 10, 2010 10:20 am
Location: Tijuana, Mexico

Re: REPORTS ON ARCHIVE SUPER SLOW [SOLVED]

Postby williamconley » Sun Feb 09, 2020 9:41 am

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! :)
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to Support

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 89 guests