System FREEZE for few minutes

[ruben23]

VERSION: 2.14-627a
BUILD: 170828-2105
© 2017 ViciDial Group
Asterisk 1.8
Ubuntu Server 12.04.5 LTS 64bit
1Large Sole Database (16 Core CPU Xeon) MegaRAID10 SSD
1 Web Server SATA Storage
4 Vicidial/asterisk SATA Storage

Hi guys system Freeze for few minutes during operation it happens randomly on time seeing the CPU core of Large database Spikes
Our Database Server is using SSD Storage already and i run Archive_log script to archive up to 1 month old

see this SLow log queries, any idea whats happening guys.?

Code: Select all

/usr/sbin/mysqld, Version: 5.5.56-MariaDB-1~precise (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 171018  7:01:20
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 18.548771  Lock_time: 0.000034  Rows_sent: 2  Rows_examined: 10625057
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
use asterisk;
SET timestamp=1508335280;
select distinct phone_code from vicidial_list;
# Time: 171018  7:07:27
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 12.785265  Lock_time: 0.000036  Rows_sent: 1  Rows_examined: 10625055
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508335647;
select count(*) from vicidial_list where phone_code='57';
# Time: 171018  7:07:40
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 13.080513  Lock_time: 0.021004  Rows_sent: 1  Rows_examined: 10625055
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508335660;
select count(*) from vicidial_list where phone_code='57'  and (gmt_offset_now != '-5' or gmt_offset_now IS NULL);
# Time: 171018  7:07:52
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 11.842386  Lock_time: 0.015773  Rows_sent: 1  Rows_examined: 10625055
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508335672;
select count(*) from vicidial_list where phone_code='57';
# Time: 171018  7:08:05
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 12.848366  Lock_time: 0.006858  Rows_sent: 1  Rows_examined: 10625055
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508335685;
select count(*) from vicidial_list where phone_code='57'  and (gmt_offset_now != '-5' or gmt_offset_now IS NULL);
# Time: 171018  7:08:17
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
# Query_time: 12.355262  Lock_time: 0.001307  Rows_sent: 1  Rows_examined: 10625055
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508335697;
select count(*) from vicidial_list where phone_code='57';
# Time: 171018  7:08:30
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 199372  Schema: asterisk  QC_hit: No
SET timestamp=1508335710;
select count(*) from vicidial_list where phone_code='57'  and (gmt_offset_now != '-5' or gmt_offset_now IS NULL);
/usr/sbin/mysqld, Version: 5.5.56-MariaDB-1~precise (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 171018  9:19:55
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 825  Schema: asterisk  QC_hit: No
# Query_time: 171.723711  Lock_time: 0.009785  Rows_sent: 2000  Rows_examined: 10949148
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: Yes  Merge_passes: 4
use asterisk;
SET timestamp=1508343595;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='A') and (( (gmt_o$
# Time: 171018  9:22:36
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 981  Schema: asterisk  QC_hit: No
# Query_time: 160.170054  Lock_time: 0.007792  Rows_sent: 2000  Rows_examined: 10949541
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: Yes  Merge_passes: 4
SET timestamp=1508343756;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='A') and (( (gmt_o$
# Time: 171018  9:22:38
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 1048  Schema: asterisk  QC_hit: No
# Query_time: 161.594773  Lock_time: 0.008912  Rows_sent: 2000  Rows_examined: 10949532
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: Yes  Merge_passes: 4
SET timestamp=1508343758;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='A') and (( (gmt_o$
# Time: 171018  9:23:20
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 1139  Schema: asterisk  QC_hit: No
# Query_time: 40.420433  Lock_time: 0.935813  Rows_sent: 0  Rows_examined: 0
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508343800;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='A') and (( (gmt_o$
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 1438  Schema: asterisk  QC_hit: No
# Query_time: 40.486598  Lock_time: 1.001922  Rows_sent: 0  Rows_examined: 0
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1508343800;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='A') and (( (gmt_o$
/usr/sbin/mysqld, Version: 5.5.56-MariaDB-1~precise (mariadb.org binary distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 171018  9:29:51
# User@Host: cron[cron] @  [10.110.64.24]
# Thread_id: 260  Schema: asterisk  QC_hit: No
# Query_time: 168.199016  Lock_time: 0.009030  Rows_sent: 2000  Rows_examined: 10950227
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes



[williamconley]

Archiving logs does not reduce the size of the "vicidial_list" table. You can manually archive that table by moving leads you don't need to be available right now to "vicidial_list_archive" or just delete them, then optimize the table, and if you reduce your table size below 5M records you'll probably get your speed back.

We have a product that does this, and an upgraded version that will do it overnight after you've chosen Archive and UNarchive records during the day. That's all it does: It moves records from "vicidial_list" to "vicidial_list_archive" (or back) based on list_id or campaign_id. The only difference between the versions is that one performs the move when you hit the button for the Campaign (all leads in one campaign are moved "now") or Submit (all checked lists are moved "now") in the original version. Of course, if the vicidial_list table is large, this can cause disruption and essentially "down" the dialer until completion. In the upgraded version, neither of those actions results in any action "now". They mark the lists for archiving or unarchiving, and a script runs at 3AM (or some other "overnight" time). It can then run an optimize command (or you can move the 1AM optimize script to a later time after this script is done).

[ruben23]

@william,

Can this upgraded version be implemented to our existing setup without any sort of rebuilding.? we just add up the script, as far as per checking we updated our vicidial version and we are at 2.14 ver. with latest DB schema, let me kknow

[ruben23]

Hi,

i also have this recently on my monitoring which is pretty weird should this process really runs from time to time.? this is mainly causing the freezing at the moment on our operation, this runs from time to time

[williamconley]

@william,

Can this upgraded version be implemented to our existing setup without any sort of rebuilding.? we just add up the script, as far as per checking we updated our vicidial version and we are at 2.14 ver. with latest DB schema, let me kknow

Yep. It's a simplistic script that has no effect on Vicidial itself. It just moves records from that one table to an archive table. You will not be able to run reports on the missing records until you UNarchive them, of course.

Hi,

i also have this recently on my monitoring which is pretty weird should this process really runs from time to time.? this is mainly causing the freezing at the moment on our operation, this runs from time to time

1) you didn't show the queries. that makes it moderately challenging to say whether those queries should be running periodically.

2) Based on what little I can see of the queries: Yep. Those will run periodically. Of course, if you are using any filters or AreaCode CID settings, turning those off may reduce those queries and improve performance.

[mmirasol]

Hi William is the lead archiving an app depoyed outside vici core or it a modified vici script?

[williamconley]

Neither.

It's a standalone script that was not derived from a Vicidial script and it is not part of the core.