Reports Slowing Down System

[covarrubiasgg]

Hello Guys, sorry to bother with this simple question but it is really giving me a headache.

Im running a Vicidial Cluter with the following versions:

Code: Select all

VERSION: 2.14-872a
BUILD: 230117-0838
Installation Method: Vicibox 10.0.2

Hardware: (Dedicated Database Only, other servers are irrelevant )   

Dual Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz
64 Gb RAM
LSI MegaRAID  1+0 /  8 SSD


This cluster has been up and running since 2017, recently we updated everything: Vicibox and Vicidial Version. I did a backup without archive data and after restore it I created the empty archive tables.

I have been running the archive process every night. I running every week a process to archive vicidial_list.

Curretly vicidial_list has 10 million records and as I mentioned all other logs tables are archived every night.

Everytime someone run a Export Call Report, the system hangs. This is what I see on the mysql_slow log:

Code: Select all

# Time: 230331 10:15:06
# User@Host: cron[cron] @  [10.72.106.2]
# Thread_id: 5241982  Schema: asterisk  QC_hit: No
# Query_time: 195.926636  Lock_time: 0.000817  Rows_sent: 198116  Rows_examined: 19715097
# Rows_affected: 0  Bytes_sent: 45951143
# Tmp_tables: 3  Tmp_disk_tables: 1  Tmp_table_sizes: 47067392
# Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    PRIMARY vi      ALL     PRIMARY NULL    NULL    NULL    10058285        10058285.00     100.00  100.00  Using temporary; Using filesort
# explain: 1    PRIMARY vl      ref     lead_id,call_date       lead_id 4       asterisk.vi.lead_id     1       0.90    59.00   2.19    Using where
# explain: 1    PRIMARY vu      eq_ref  user    user    62      asterisk.vl.user        1       1.00    100.00  100.00 
# explain: 1    PRIMARY <derived3>      ALL     NULL    NULL    NULL    NULL    1198    1198.00 100.00  0.08    Using where; FirstMatch(vu); Using join buffer (flat, BNL join)
# explain: 3    DERIVED NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
#


The reports are usually from the day before (which is already archived) or from the present day. So I am not sure why the database hast to request all 10M leads on the vicidial_list.

I know you recommend having Less than 2M on the vicidial_list, but before the update they used to have 50M records and running reports were not a problem before.

Also i know at this point having a Slave for reports is the best idea, but i have never been able to run a Slave Server this big without it getting out of sync every week.

Is there any suggestion on how can I fix this?

[streetboy]

Run mtop and see which query is taking a long time or check under the slow query log file.

and yes setting up slave is the best thing to do for you right now, considering the amount of data you have.

[mflorell]

Other than archiving leads and getting a slave DB server for reporting, rolling your logs to _archvie tables(using the ADMIN_archive_log_tables.pl script) more frequently and for shorter time periods will also help.

[covarrubiasgg]

Other than archiving leads and getting a slave DB server for reporting, rolling your logs to _archvie tables(using the ADMIN_archive_log_tables.pl script) more frequently and for shorter time periods will also help.

As I mentioned we run the archive every night with --days=1 , i don't think we can go as often and shorter.

What is really bugging me is Operations saying "All this didn't happened before the upgrade"

I suggested already to get Slave DB and need to "justify" why we didn't need and Slave server before.

[ccabrera]

@covarrubiasgg,

This response may came in late, but I've seen similar cases before.

My guess is that your lead_id index is a big enough number that MySQL doesn't know how to properly index it when running inside single quotes, thus causing the issue.

I've reported this issue in the Mantis tracker a long time ago: http://www.vicidial.org/VICIDIALmantis/view.php?id=1138

So the matter is not wether there are too many leads inside the table, but rather how big is the lead_id number in order to make a difference by enclosing it (or not) inside the ' ' quotes.

If you can, try to replace all the '$lead_id' for $lead_id inside the Call Export Report file to see if this solves the issue.

Regards,