Calls waiting for AGENT

[ruben23]

VERSION: 2.12-555a
BUILD: 160508-1948
© 2016 ViciDial Group
Asterisk 1.8
Ubuntu Server 12.04.5 LTS 64bit

Hi guys any idea why this happening and any resolutions somehow.? there are many agents waiting and we get calls waiting for agent on the realtime

[mflorell]

Check your database slow query log, that's usually caused by slow queries, not archiving enough of your logs or inadequate database hardware.

Also, I would recommend upgrading to the latest svn/trunk version, since we have added several system optimizations for high-volume systems recently.

[ruben23]

@Matt,

I have updated the System to this already:

Version: 2.12b0.5
SVN Version: 2534
DB Schema Version: 1459
DB Schema Update Date: 2016-05-10 22:36:47
Password Encryption: DISABLED - S1 - C1
Auto User-add Value: 101
Install Date: 2016-05-10

How do i show the slow log query somehow.?

[ruben23]

@Matt:

Also our Hardware Specs is:

16 Core Processor Xeon
16 GB RAM
2 TeraBytes SATA II

[mflorell]

You are using a single SATA drive for your database? Really?

[ruben23]

Yes Single SATA II Drives only, Planning to upgrade to 500GB SSD somehow, this might help.?

[ruben23]

@Matt

Here is my Database LoadAverage

[mflorell]

Yes, I would strongly suggest upgrading to SSD drives as quickly as possible.

It's not the load, but the IO bottleneck that causes issues with using inadequate drives for high-volume database use. Switching to a SSD drive system should help. We recommend LSILogic MedaRAID caching RAID controllers for our highest-volume dialing clients.

[ruben23]

My Webserver resides with the same server of Database, should i separate it also on a different Server 80 Agents

Should i also used RAID for my SSD card, what can you suggest the type of RAID i will used for my SSD.?

And also does this affect also performance the internal network is setup to this, its not a physical LAN card only a virtual.? latency between Server

TESTast 10.110.64.24 (D) 255.255.255.255 4569 OK (3 ms)

or i should transfer it on a Physical LAND card like eth1.

eth0:1 Link encap:Ethernet HWaddr 00:26:6c:f1:41:68
inet addr:10.110.68.166 Bcast:10.255.255.255 Mask:255.0.0.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
Memory:c0000000-c0020000

[mflorell]

Separating web and DB to different hardware is good if you can do it.

Physical network is better than shared virtual as well.

[ruben23]

@Matt

Where do we get this or we implement this:

We recommend LSILogic MedaRAID caching RAID controllers for our highest-volume dialing clients.

can this be implemented with SSD.?

[mflorell]

https://www.google.com/search?q=lsi+logic+megaraid

All major component vendors carry LSI Logic MegaRAID cards, just make sure you get MegaRAID and not a 3ware card.

Yes, they have specific settings for SSD drives actually.

[ruben23]

Thanks a lot Matt, i ask the technical guys on my hosting company and they said they have LSI Logic MEGARAID. but they ask ask how many SSD i want what type setup of RAID.. can you help and suggest what type of RAID i will used.?

[mflorell]

Well, that all depends on how much money you want to spend

For the highest setups, two sets of four SSDs, each set up in a RAID-10, with one RAID for operating system and one RAID for MySQL/MariaDB data only.

A single 4xSSD RAID-10 would be a good place to start though.

[ruben23]

@Matt,

Thanks a lot for the information you share, i think i will start from basic, two SSD Cards separate the MariaDB application and the data on the other SSD. then check how it performs then i will upgrade to doing LSI logic Master RAID after.

now so far im running single SSD, Single database server have no issue that suddenly stops during operation when queries go high but i still got random Queue that i present to you before, is that part of the vicidial feartures now.?

that some time it queues before passing to agent.?

[mflorell]

Is anything in the slow query log on your database server?

[ruben23]

@Matt,

I got this on my Slow log Queries, any idea or suggestion for this somehow. Thanks in Advance


https://pastebin.com/DY5Kamph

[williamconley]

Code: Select all

Rows_examined: 12607094

12 million leads in your vicidial_list table? Try reducing that under 5mil.

Also: since this is obviously "support" and not "general discussion" ... moving to the proper board.

[frequency]

Are you opening the campaign to adjust dial level or anything less quite often? If yes, the data fetched for System statuses at the bottom of the campaign takes quite a bit load and that calls get stuck in queue during the time the campaign is opened even to do minor changes.

It can also happen due to bad my.cnf settings or going too aggressive in those settings.

[ruben23]

@Frequency

Any suggestion you can give us, yes most of the time the QA mostly opens the campaign to do some re adjusting. im using the config for my big my.cnf the one used for sole database

Also if i may ask would it help turning the databsed engine to InnoDB rather with Myisam. is this still an issue now, that it wont worked with innoDB. Thanks

[frequency]

@Frequency

Any suggestion you can give us, yes most of the time the QA mostly opens the campaign to do some re adjusting. im using the config for my big my.cnf the one used for sole database

Also if i may ask would it help turning the databsed engine to InnoDB rather with Myisam. is this still an issue now, that it wont worked with innoDB. Thanks

Try the my.cnf that i'm attaching, that might fix a few things up for you. Optimize tables or even restart DB server and try your luck. if the issue persists, try making a newer campaign with lesser lists.

InnoDB would not work because vicidial requires table-level locking and not row level locking. 15+ agents plus and you would be see vicidial behavior very awkward way, even double calls, calls waiting, unable to transfer or leave 3-way calls etc.

Code: Select all

# ViciBox v.7.0 config file for MariaDB 10
# Inital settings for a 4 CPU cores and 8GB of RAM
# Should be easily obtainable at this point for any modern server

### Tuning for more CPU
# Set thread concurrency equal to the total number of CPU cores that
# the OS sees. Running 'cat /proc/cpuinfo | grep processor' should help

### Tuning for more RAM/Connections
# You will want 6-8GB of memory for every 512 connections to MySQL
# with a standard ViciDial installation and database. So if you want
# to go from 512 for 2048 connections, you will want
# somewhere around 24 to 32GB of RAM in the database. This is a general
# guideline and is HIGHLY dependant upon your ACTUAL production
# load. You will
#
# TL;DR; 24-32GB total memory for 2048 max connections, YMMV

### Tuning locking and I/O issues
# More memory first, faster disks second, and log/lead purging third.
# See above note for memory, but it's cheap performance, so add plenty.
# Use good proven SSDs in a RAID-1 or RAID-10 for MySQL, NEVER RAID5/6!!!
# Archive log tables with the ADMIN_archive_log_tables.pl script in the crontab
# Try to purge/archive leads you haven't called in a while from vicidial_list

# The following options will be passed to all MariaDB clients
[client]
# password  = your_password
port       = 3306
socket     = /var/run/mysql/mysql.sock

[mysqld]
log_bin=/var/lib/mysql/mysql-bin
binlog_format=mixed
server-id=1
relay-log  = /var/lib/mysql/mysqld-relay-bin
slave-skip-errors = 1032,1690,1062
datadir=/var/lib/mysql
sql_mode=NO_ENGINE_SUBSTITUTION
port = 3306
socket = /var/run/mysql/mysql.sock
skip-external-locking
skip-name-resolve
connect_timeout=60
long_query_time=3
slow_query_log=1
slow-query-log-file=/var/log/mysql/mysqld-slow.log
max_connections=1500
key_buffer_size=2048M
max_allowed_packet=32M
table_open_cache=2048
table_definition_cache=2048
open_files_limit=24576
sort_buffer_size=6M
net_buffer_length=8K
read_buffer_size=4M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=64M
join_buffer_size=1M
thread_cache_size=16
query_cache_size=64M
thread_concurrency=8
default-storage-engine=MyISAM
expire_logs_days=3
concurrent_insert=2
myisam_repair_threads=1
myisam_use_mmap=1
skip-innodb
delay_key_write=ALL
max_write_lock_count=1


# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
# skip-networking


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
#
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
# pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql

# [mysqld2]
# port       = 3307
# datadir    = /var/lib/mysql-databases/mysqld2
# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
# user       = mysql

# [mysqld3]
# port       = 3308
# datadir    = /var/lib/mysql-databases/mysqld3
# pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
# user       = mysql

# [mysqld6]
# port       = 3309
# datadir    = /var/lib/mysql-databases/mysqld6
# pid-file   = /var/lib/mysql-databases/mysqld6/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld6/mysql.sock
# user       = mysql

[myisamchk]
key_buffer = 256M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M


!includedir /etc/my.cnf.d



[ruben23]

@Guys,

How do i expand my Large database further so i can still support further scaling somehow aside from moving my database storage to SSD and make some RAID, would MYSQL Database replication works for this type of scenario somehow.? currently when im running full blast of calls for 70 people with ration of 18, i see CPU Core spike most specially when there are Query, UPDATE and check on the vicidial list tables. Please any suggestion.?

[proper]

My testing has shown that overall mysql database size affects performance in vicidial deployments so you must archive your logs to separate database. I usually keep 15-30days of logs on live server and move overflow to archive database(usually VM). You should do the same.
Keep you vicidial_list table or your lead count as low as possible, preferably under 4million, if you absolutely have to keep larger volumes of leads, you will need to partition leads table by list_id, to speed it up.
You can also partition log tables for performance boost.
Keep individual lists under 200k leads, this helps speed up processing
Keep only lists you need in the campaign.
If you have more than one campaign, disable ones you do not use as number of calculations still run in the background for any active campaign.
Make sure to optimize your database every night.
Keep carrier log clean, if you do not want to archive it, just clean it every week. This table can kill your database performance if it grows out of control.
Run performance analytics on your database to see where you have issues.

I run one or more database slaves for clients with more than 40 agents using MaxScale, it offers significant stability and allows scaling of database as deployment grows.
However this is not something you can find a tutorial on, there are some asterisk and vicidial specific issues that need to be resolved for MaxScale to work. You probably want to focus on points i described above.

[ruben23]

HI there Proper,

Thanks for you information you shared surely it will help me but i just have challenges how to execute those procedure somehow.

like this points:

I usually keep 15-30days of logs on live server and move overflow to archive database(usually VM). You should do the same.

you will need to partition leads table by list_id, to speed it up.
You can also partition log tables for performance boost.

Keep carrier log clean, if you do not want to archive it, just clean it every week. This table can kill your database performance if it grows out of control

please guide me on this please. Thanks you so much

[williamconley]

Code: Select all

perl /usr/share/astguiclient/ADMIN_archive_log_tables.pl --help


Have you read the Vicidial Manager's Manual? (Available in free and paid versions on EFLO.net)

Based on your current situation, I highly recommend the paid version. Srsly.

[mmirasol]

Hi Guys,

I work with Ruben. Can you please review if our setup is suitable for our config and volume of calls.

We currently have:
2 Xeon 8 Core E5-2650 2.00GHz 16GB RAM - Master and Slave DB
5 Xeon Quad Core E3-1230v3 3.3 GHz 8GB RAM - Asterisk Servers and Web Server

We have setup RAID10 with the LSI Logic MegaRAID controller on our Master DB but we are still getting table locks up to 102 secs.
https://drive.google.com/file/d/0B5pCQ2 ... sp=sharing

We currently have 80 agents and our campaigns are configure to run at dial level 16 and lead order is UP TIMEZONE with lead recycling up to 10 attempts.
We have gone up to 600 calls before but in the last couple of days we have experienced downtime because of the locks several times in a day.

Our vicidial_list table currently has 10M records. Our lists have about 20k - 100k leads each.

Please advise how we can solve our issues with the table locks. Thanks.

[williamconley]

If you work with ruben ...

The likely reason for the slow queries: viewtopic.php?f=4&t=37745&p=130501#p130482

[mflorell]

Need to see actual full query, not just beginning of it.

How many campaigns do you have set to active on your system?

How many lists do you have active in each campaign?

How many leads are in the active lists in each of those campaigns?


Just as a start, we usually recommend no more than 10M leads on a system. Of course we've had clients with 62M+ leads, but they had issues with that many.

[mmirasol]

Need to see actual full query, not just beginning of it.

- we seem to have eliminated the locks after we disabled our lead recycling rules.
https://snag.gy/NMCdJv.jpg
I cannot give you the exact query now because we don't see it anymore but yesterday we saw that it was very long select query from vicidial_list and a long list of matching gmt_offset_now and last_local_call_time. I'm guessing this comes from lead recycling + our local time sorting. This was the only thing running while the other pending queries got locked and it builds up over time until it reaches up to 250+ seconds and we can't get calls anymore. here's a screenshot of the full query yesterday.
https://drive.google.com/file/d/0B5pCQ2 ... sp=sharing
- our vicidial_list table only has the following indexes. please advise if we need to add any. the last_local_call_time were all different so perhaps that's what was making it heavy/slow.
https://snag.gy/oZxFdS.jpg

How many campaigns do you have set to active on your system?

- we have about 5 campaigns with local time sorting but only one had the lead recycling rules in place and currently the one agents log into.

How many lists do you have active in each campaign?

- the main campaign has 2 active lists and 117 inactive lists.

How many leads are in the active lists in each of those campaigns?

- the 2 active lists have about 50k leads each.

[mmirasol]

@williamconley, thank you. We will consider it and reply to the other thread separately.

[mmirasol]

Also could you advise on the performance of SSD drives vs SAS drives.

Our raid10 setup uses:
4 400GB Intel DC S3610
4 port LSI 9631 RAID SAS Controller with Cachevault

Upon checking we have SSD drives. Do you know if these would perform better or less than 15k RPM SAS/SCSI hard drives?

[williamconley]

Also could you advise on the performance of SSD drives vs SAS drives.

Our raid10 setup uses:
4 400GB Intel DC S3610
4 port LSI 9631 RAID SAS Controller with Cachevault

Upon checking we have SSD drives. Do you know if these would perform better or less than 15k RPM SAS/SCSI hard drives?

SSD drives are an entire family of drives. They do not all perform the same. Essentially you just asked which is faster, a Ford or a Chevy? lol

That being said: You'll have to research the individual drives to see what they can sustain for long-term throughput. But first verify that the RAID card is a 6G/Sec RAID card. The CABLE is a 6G/Sec cable. And the HD is a 6G/sec HD. This applies equally to SSD as it does to SAS. But in the world of SAS, a 15K RPM SAS drive is such a standard that they all operate very well and pretty much the same. SSD, on the other hand, is a new standard that has a lot of players, so you'll have to check the specs for constant usage and reviews to be sure it's not ... crap.

[mflorell]

We usually recommend using Lead Recycling sparingly, if at all. It is a known issue that many recycling entries can significantly slow down your hopper processes.

[mmirasol]

May we know how it currently works so we can make the most of it without choking the database? We have currently set the delays for each one to reflect how we would normally reset the lists if it were done manually but we probably still have a lot of leads that have not yet been called. Does it do one run per status for each campaign based on the delay?

[mflorell]

Typically, we only use it for one or two statuses, B and ADC, because the customer doesn't know those attempts have happened. Aside from that, we usually recommend resetting the list for all other statuses.

[mmirasol]

Thank you for the advise, Matt.