vicidial_live_agent table locks

[alo]

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

[mflorell]

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

[alo]

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!

[carpenox]

damn i might have to try this, good to know, great shit matt, thanks for this awesome knowledge

[mflorell]

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.

[carpenox]

damn thats a beast of a cluster, what was the cluster breakdown?

[alo]

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]

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!

[mflorell]

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

[alo]

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?

[mflorell]

If it is really a slave DB then it shouldn't have any of the VICIdial crontab entries in it.

[alo]

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?

[mflorell]

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.

[alo]

Been running much better lately since I removed most of the webservers! I think some delay in communications to the DB caused locks?

I noticed the agent log table gets locked heavy later in the day when the table gets bigger. (archiving every night) Specifically when there are a lot of inbound calls waiting in queue.

I noticed inbound calls sometimes causing more locks then outbound calls, and I wonder if there is something in the ingroup or DID I can set to be less Heavy? for example disabling No agent No Queuing might result in one les check of the live table or something along those lines.

Anyone know if things I can disable in the DIDs or ingroups to improve performance?

Thanks.

[mflorell]

How many inbound calls are you handling a day?

We have a client that has hit peak daily inbound traffic over 500,000 inbound calls and the system is able to handle it without issues.

[geilt]

I can tell you from experience, that using RANKS on ingroups, causes more locks than just using longest wait time. Doing pure longest wait time I can get the most performance.