vicidial_live_agent table locks

All installation and configuration problems and questions

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

vicidial_live_agent table locks

Postby alo » Wed Jan 13, 2021 1:19 pm

We have been having some sluggish performance, mysql errors, and agents getting booted or showing paused when on a call, and I am pretty sure its related to the vicidial_live_agent table being locked.

    we have database 2x12core 2.5ghz cpu 64gb ram raid 10 SSDs with mega raid card.
    Load averages around 7 %CPU(s) 11% and IO wait is 0 or 0.2 at max.
    we have 4 webservers
    vicibox7 install SVN 3312

I noticed a bunch of forum articles about moving the table over to a memory table but I also noticed they say use caution here. We have 550 agents logged in and the closer_campaigns field has 100 characters per agent in it. do you think it would be okay, and a good ide for me to switch this to memory? I have 64gb ram and only ever appear to be using 17gb. (maybe my .cnf needs to be more aggressively tuned to use more Ram? see below for my.cnf)

Anything else that might be causing this table to lock or any other suggestions?

I am running AST_flush_DBqueue.pl --seconds=600 every 10 minutes.

Here is some below info:

When I look at MySQL process list I always see:

Waiting for table level lock. here are a few of the quires I see trying to run:

Code: Select all
SELECT count(*) from vicidial_live_agents where user='1555' and server_ip='dialserver4'
SELECT agent_log_id from vicidial_live_agents where user='542'
UPDATE vicidial_live_agents set random_id='19275099' where user='723' and server_ip='dialserver6'


I looked at the Slow queries log and see the following:
Slow queries

Code: Select all
SELECT count(*) FROM vicidial_live_agents where closer_campaigns LIKE "% closerqueue %";
# Time: 210113  9:02:34
# User@Host: cron[cron] @ localhost []
# Thread_id: 13539393  Schema: asterisk  QC_hit: No
# Query_time: 3.841758  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1610557354;
LOCK TABLES vicidial_live_agents WRITE;

SELECT agent_log_id from vicidial_live_agents where user='1467';
# User@Host: cron[cron] @  [Webserver1]
# Thread_id: 13539065  Schema: asterisk  QC_hit: No
# Query_time: 6.425308  Lock_time: 2.944348  Rows_sent: 3  Rows_examined: 3
# Rows_affected: 0

SELECT status,callerid,agent_log_id,campaign_id,lead_id,comments from vicidial_live_agents where user='1171' and server_ip='dialserver19';
# User@Host: cron[cron] @  [dialserver1]
# Thread_id: 13541746  Schema: asterisk  QC_hit: No
# Query_time: 3.166266  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0

SELECT lead_id,uniqueid,callerid,channel,call_server_ip,comments FROM vicidial_live_agents where server_ip = 'dialserver5' and user='402' and campaign_id='CLOSER' and status='QUEUE';
# User@Host: cron[cron] @ localhost []
# Thread_id: 13541502  Schema: asterisk  QC_hit: No
# Query_time: 5.347642  Lock_time: 2.256797  Rows_sent: 1  Rows_examined: 499
# Rows_affected: 0


and here in my.cnf

Code: Select all
log_bin=/var/lib/mysql/mysql-bin
binlog_format=mixed
server-id=1
slave-skip-errors = 1032,1690,1062
datadir=/var/lib/mysql
sql_mode=NO_ENGINE_SUBSTITUTION
skip-external-locking
skip-name-resolve
connect_timeout=60
long_query_time=3
slow_query_log=1
max_connections=4096
key_buffer_size=2G
max_allowed_packet=16M
table_open_cache=512
table_definition_cache=2048
open_files_limit=24576
sort_buffer_size=4M
net_buffer_length=8K
read_buffer_size=4M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=128M
join_buffer_size=1M
thread_cache_size=100
query_cache_size=0
thread_concurrency=16
default-storage-engine=MyISAM
expire_logs_days=3
concurrent_insert=2
myisam_repair_threads=2
myisam_use_mmap=1
skip-innodb
delay_key_write=ALL
max_write_lock_count=1
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby mflorell » Wed Jan 13, 2021 2:46 pm

I'd recommend changing the DBflush to run every 4 minutes:

### flush queue DB table every hour for entries older than 1 hour (changed to 4 minutes)
1,5,9,13,17,21,25,29,33,37,41,45,49,53,57 * * * * /usr/share/astguiclient/AST_flush_DBqueue.pl --seconds=240 --session-flush -q

As for vicidial_live_agents, I included instructions for changing that to MEMORY in another ticket:
ALTER TABLE vicidial_live_agents MODIFY closer_campaigns VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY external_ingroups VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY agent_territories VARCHAR(45) default '';
ALTER TABLE vicidial_live_agents ENGINE=MEMORY;

Also, consider changing vicidial_hopper to a MEMORY table, although that does depend on how many records you have in it regularly, if you hopper levels are set too high then changing to memory can actually make it slower.

You also might consider archiving your log tables every night if your DB server can handle that(something like this):
### roll logs weekly and daily on high-volume dialing systems
3 1 * * 0,1,2,3,4,5,6 /usr/share/astguiclient/ADMIN_archive_log_tables.pl --days=1 --recording-log-days=1 --wipe-closer-log --wipe-all-being-archived --did-log-days=31
mflorell
Site Admin
 
Posts: 17627
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: vicidial_live_agent table locks

Postby alo » Thu Jan 14, 2021 12:11 am

Changing live agents to memory was night and day. no more locks! I couldn't be more appreciative.

I also noticed some mention of the vicidial_managers table being moved to memory. is there any downside of that or special instructions for doing that like altering any fields?

Is there any negative repercussions that might come from running the AST_flush_DBqueue.pl script every 4 minutes instead of 10 or an hour?

also we have been using the archive logs script. its awesome. but I wonder why you keep the DID_logs for a month. is there a specific reason? Also I notice the normal archive script doesn't do recording log but you have it recommended above. is there a reason not to archive them?

Again, really appreciate it. you're the best!
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby carpenox » Thu Jan 14, 2021 8:32 am

damn i might have to try this, good to know, great shit matt, thanks for this awesome knowledge
ViciBox v9.0.3 | Version: 2.14-783a | BUILD: 210103-0856 | SVN Version: 3344 | DB Schema Version: 1615 | Asterisk 13.38.0
http://www.CyburityLLC.com -:- 844-PC-SATA-2 -:- www.contactcentersRus.com -:- Whatsapp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 935
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL

Re: vicidial_live_agent table locks

Postby mflorell » Thu Jan 14, 2021 9:31 am

You can usually get away with making the vicidial_manager table a MEMORY table if you:
- Have enough memory on your DB server
- Have set up the flush DB script to run more frequently(like every 4-6 minutes)

You shouldn't have to worry about altering any of the vicidial_manager fields to be able to change it to a MEMORY engine.

On very high-volume outbound dialing systems, the flush DB script does need to be run more frequently on all dialers in a cluster for everything to work properly.

The settings I included in my previous post are from a cluster that has peaked at running 500+ concurrent agents and placing over 2 million calls each day. They were recording every phone call, which is why they wanted to archive their recording logs. The system received relatively few inbound calls and they wanted to be able to trace them which is why we kept the DID logs for a month.
mflorell
Site Admin
 
Posts: 17627
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: vicidial_live_agent table locks

Postby carpenox » Thu Jan 14, 2021 10:43 am

damn thats a beast of a cluster, what was the cluster breakdown?
ViciBox v9.0.3 | Version: 2.14-783a | BUILD: 210103-0856 | SVN Version: 3344 | DB Schema Version: 1615 | Asterisk 13.38.0
http://www.CyburityLLC.com -:- 844-PC-SATA-2 -:- www.contactcentersRus.com -:- Whatsapp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 935
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL

Re: vicidial_live_agent table locks

Postby alo » Thu Jan 14, 2021 12:15 pm

So the database had crashed twice again today. but I do see it seems to correspond to a time when someone is running a report. But my concern here is that I do have a slave server set up and I do have all the reports in system settings set to use this slave database server. here is my thoughts:

could running a report on the slave cause issues for the master? like if the slave gets locked up the master needs to wait to write to the slave or something?
could the reports still be pulling from the Master even though the slave is set up? easy way for me to check this?
could it be coincidental and the reports aren't the root issue?

I think perhaps running that report locked some tables, which increased the number of connections over a limit that my RAM could support. So I can my main question is why did the tables on the main database get locked when the report should run from the slave. is that typical or have I misconfigured something?

So this might be less vicidial more SQL/OS questions, but: Whats the deal with max_connections in my.cnf? can I go as high as I want on that without issues? is there an easy way for me to se if I am running out of memory. I am constantly surprised that Htop makes it look like I am only using 16gb/64gb. is that right?
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby alo » Thu Jan 14, 2021 1:58 pm

After disabling reports all together it hasn't crashed again. So I am really thinking it was caused by running reports. but based on my understanding because of the slave database I didn't expect that to be an issue.

I wonder if I have done something wrong on the slave DB setup or if there is something special I need to do.

I did check the slow query log and the first entry of when the system locked up with this entry. followed by thousands of queries right after that of the live agent table (I assume because it was locked)

LOCK TABLES vicidial_live_agents WRITE;

Any advice is greatly appreciated!
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby mflorell » Thu Jan 14, 2021 3:42 pm

We've found that DB crashing is usually the result of my.cnf settings that cause MySQL to try to use too much RAM and the system either not being able to handle it, or the system starting to use SWAP as ram for MySQL causing the crash.

Looking at your my.cnf settings and comparing it to what we have on our high-volume systems, you might consider these changes:

key_buffer_size=12G
key_cache_segments=64
max_heap_table_size=512M
myisam_recover_option=DEFAULT
myisam_repair_threads=4
slave-parallel-max-queued=2M
slave-parallel-mode=optimistic
slave_parallel_threads=4
table_definition_cache=16384
table_open_cache=4096
tmp_table_size=32M
mflorell
Site Admin
 
Posts: 17627
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: vicidial_live_agent table locks

Postby alo » Fri Jan 15, 2021 12:58 am

I am excited to try these tomorrow.

One thing I did notice is the slave database has all the same cron items as the main database. does this mean it would load the hopper twice, etc?
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby mflorell » Fri Jan 15, 2021 7:40 am

If it is really a slave DB then it shouldn't have any of the VICIdial crontab entries in it.
mflorell
Site Admin
 
Posts: 17627
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: vicidial_live_agent table locks

Postby alo » Fri Jan 15, 2021 11:06 am

Seems Much worse today. vicidial_live_agent table getting locked. and eventually causes too many connections.

Is it possibly related to the webservers. they previously used combination webserver/database before moving on to separate webservers. could there be network load or communication issues between the webservers and the database that cause this table to get locked?
alo
 
Posts: 160
Joined: Wed Jun 20, 2012 10:21 am

Re: vicidial_live_agent table locks

Postby mflorell » Fri Jan 15, 2021 11:56 am

Anything is possible when you get to clusters of that size. You should try to have no more than 200 agents per webserver, and the webservers should be optimized to operate as stand-alone Apache/PHP webservers with the Linux open files set as high as they can go for the process running apache.
mflorell
Site Admin
 
Posts: 17627
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida


Return to Support

Who is online

Users browsing this forum: Google [Bot] and 69 guests