REPORTS ON ARCHIVE SUPER SLOW [SOLVED]
Posted: Fri Jan 03, 2020 2:12 pmHi 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...
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
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?
I run SHOW INDEX FROM vicidial_log_archive; and again EUREKA... Cardinality on all INDEXES was NULL
So, after a quick google, it say ANALYZE should fix it, so i run
Not really sure why it didnt work, but if mysql client was laying me down, my last option was myisamchk
so
THE KEYS ARE WORKING NOW!!!! run the query again... and SOLVED
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.
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.
