System FREEZE for few minutes

All installation and configuration problems and questions

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

System FREEZE for few minutes

Postby ruben23 » Wed Oct 18, 2017 12:52 pm

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

Last edited by ruben23 on Thu Oct 19, 2017 10:25 am, edited 2 times in total.
SkypeID: rlacumba
IBM x3200 Dual Core 2.4 Ghz.
4GB Ram
VERSION: 2.4-311a
BUILD: 110514-1351
© 2011 ViciDial Group
Asterisk 1.4.27-vici
Another VICI_day, same trunK, same Channel-->Transcode...
ruben23
 
Posts: 1161
Joined: Thu Jul 31, 2008 10:35 am
Location: Davao City, Philippines

Re: System FREEZE for few minutes

Postby williamconley » Wed Oct 18, 2017 3:49 pm

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).
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!)

Re: System FREEZE for few minutes

Postby ruben23 » Wed Oct 18, 2017 6:16 pm

@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
SkypeID: rlacumba
IBM x3200 Dual Core 2.4 Ghz.
4GB Ram
VERSION: 2.4-311a
BUILD: 110514-1351
© 2011 ViciDial Group
Asterisk 1.4.27-vici
Another VICI_day, same trunK, same Channel-->Transcode...
ruben23
 
Posts: 1161
Joined: Thu Jul 31, 2008 10:35 am
Location: Davao City, Philippines

Re: System FREEZE for few minutes

Postby ruben23 » Wed Oct 18, 2017 11:34 pm

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

Image
SkypeID: rlacumba
IBM x3200 Dual Core 2.4 Ghz.
4GB Ram
VERSION: 2.4-311a
BUILD: 110514-1351
© 2011 ViciDial Group
Asterisk 1.4.27-vici
Another VICI_day, same trunK, same Channel-->Transcode...
ruben23
 
Posts: 1161
Joined: Thu Jul 31, 2008 10:35 am
Location: Davao City, Philippines

Re: System FREEZE for few minutes

Postby williamconley » Thu Oct 19, 2017 11:47 am

ruben23 wrote:@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.

ruben23 wrote: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. 8-)

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.
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!)

Re: System FREEZE for few minutes

Postby mmirasol » Thu Oct 19, 2017 5:45 pm

Hi William is the lead archiving an app depoyed outside vici core or it a modified vici script?
mmirasol
 
Posts: 31
Joined: Tue May 15, 2012 11:28 pm

Re: System FREEZE for few minutes

Postby williamconley » Thu Oct 19, 2017 5:49 pm

Neither.

It's a standalone script that was not derived from a Vicidial script and it is not part of the core.
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: No registered users and 86 guests