vicidial.org

Hi,

I am having an issue on multi-server system. Calls are waiting for agents while agents is waiting aswell. They are getting stuck in the queue for sometime. Even the customer is complaining regarding the issue that he has to wait for the agent.

Server is running on CentOS 6.8, scratch installed from guides. VERSION: 2.14-583a , BUILD: 161226-2224, No Sangoma/Digium, same specifications as 2 x (E3-1270v3), 32g, 2x240g SSD.

I have done some research but i don't think its overloaded since they are running on average load of 0.7 with a spike of 1.00 at times. I have done mysql tuning via the shell script inside the svn. Since the server is in remote location in a reputable DC, confirmed that there firewall or routers are not choking either, the ping also seems stable and mtr also seems to be stable with zero packet loss/chokes. Agents are running Firefox as their browser. MySQL DB size is about 15g with about 3million records in vicidial_log and fairly large amount of vicidial_list. (DB was archived but nothing purdged). dial_logs,carriers_logs,call_logs,log_extended are removed on weekly basis). <0.05% i/o wait time.

Have not got hands on my-big-vici.cnf by Kumba.

Code: Select all

Uptime = 2 days 23 hrs 49 min 3 sec
Avg. qps = 649
Total Questions = 167928244
Threads Connected = 37

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 1.000000 sec.
You have 1958 out of 167928505 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 16
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 1200
Current threads_connected = 38
Historic max_used_connections = 193
The number of used connections is 16% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 6 G
Configured Max Per-thread Buffers : 31 G
Configured Max Global Buffers : 1 G
Configured Max Memory Limit : 33 G
Physical Memory : 31.22 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 5 G
Current key_buffer_size = 1 G
Key cache miss rate is 1 : 140
Key buffer fill ratio = 86.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 10 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 32.65 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 6 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 16 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 6000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 256 tables
You have a total of 226 tables
You have 836 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 128 M
Of 582392 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 4 M
Current table scan ratio = 647 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 56
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.


Probably DB overloaded, have you looked at mtop while this issue is happening?

mflorell wrote:Probably DB overloaded, have you looked at mtop while this issue is happening?

I will get back to you tomorrow on this. I felt the same way that the db is overloaded. Can you please help me purging the DB logs, 6 months and older? DB gas been crashing alot lately. Server never died or had any outage. just normal/planned restarts once a week.

# /usr/share/astguiclient/ADMIN_archive_log_tables.pl --help
allowed run time options:
[--daily] = only archives call_log, vicidial_log_extended and vicidial_dial_log tables, only last 24 hours kept
[--carrier-daily] = will also archive the vicidial_carrier_log table when --daily is run
[--vlog-daily] = will also archive the vicidial_log table when --daily is run
[--days=XX] = number of days to archive past, default is 732(2 years)
[--months=XX] = number of months to archive past, default is 24(2 years) If 'days' used then 'months' ignored
[--closer-log] = archive vicidial_closer_log records
[--queue-log] = archive QM queue_log records
[--only-trim-archive-level-one] = will not perform normal archive process, instead this will only delete records
that are older than XX months from least important log archive tables:
call_log_archive, vicidial_log_extended_archive, vicidial_dial_log_archive
[--only-trim-archive-level-two] = same as --only-trim-archive-level-one, except includes tables:
vicidial_carrier_log_archive, vicidial_api_log_archive
[--only-trim-archive-level-three] = same as --only-trim-archive-level-two, except includes tables:
vicidial_log_archive, vicidial_agent_log_archive, vicidial_closer_log_archive
[--recording-log-days=XX] = OPTIONAL, number of days to archive recording_log table only past
[--cpd-log-purge-days=XX] = OPTIONAL, number of days to purge vicidial_cpd_log table only past
[--quiet] = quiet
[--calc-test] = date calculation test only
[--test] = test

The server seems to allowed from a bunch of IP's which client allowed. changes were made in httpd.conf. 127.0.0.1 was not allowed in the list. added and restarted the service. checked tail in httpd logs and found this. Can this be an issue?

Code: Select all

[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/vdc_chat_display.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/conf_exten_check.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/vdc_email_display.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/manager_send.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/conf_exten_check.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/conf_exten_check.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/vdc_db_query.php, referer: http://136.xx.xx.xx/agc/vicidial.php
[Tue Jan 17 10:01:32 2017] [error] [client 124.xx.xx.xx] client denied by server configuration: /var/www/html/agc/vdc_db_query.php, referer: http://136.xx.xx.xx/agc/vicidial.php


That might be an issue, but it wouldn't be causing the problems you mentioned at the top of this topic.

Trimmed the DB. optimized and it is not helping. Made concurrent_insert = 2 in my.cnf and it is not helping either.
Checked agent lagged report and shows literally nothing.

Can someone get me my-big-vici.cnf by Kumba, That may be helpful for us.

Thank you.

Can someone get me my-big-vici.cnf by Kumba, That may be helpful for us.

Secondly, if you can tell me ajustments to cronjobs and my.cnf adjustment for heavy dialing and multi cluster systems, i want to try those too.

Some screenshots during the issue. This happens like once in 15 minutes. checked 2k queries.





this server is running as a cluster, 40 agents, web+db has 5 to 10 agents with other two other phone server have 20 and 15. they are reachable at 0.2ms. the server has 1400 lists (10 active, while other are inactive, vicidial_list table sized at 6g with 24 million records)

Get the full processlist and find the FIRST/OLDEST query. That's locking up your system every 15 minutes (according to your description).

Find out why, or post it and perhaps we can tell you.

williamconley wrote:Get the full processlist and find the FIRST/OLDEST query. That's locking up your system every 15 minutes (according to your description).

Find out why, or post it and perhaps we can tell you.

Can you help me regarding fetching full processlist Andd can 1500 lists with the size of 6g be an issue and slowing down? I still have not got my-big-vici.cnf. is there a link to that?

1) Big vici cnf is on every server in your svn download or zip file. Search your HD for ".cnf" (locate .cnf) and one of those should be it.
[edit - i'm not certain when it gets installed or not ... i've found servers with it and without it ... perhaps the non-standard db option during install, hm.]

I checked one of our servers and found it at:

Code: Select all

/usr/src/astguiclient/conf/my-bigvici.cnf

Note that it may be changed from version to version ...

2) show processlist ... change to "show full processlist"

/usr/src/astguiclient/conf/my-bigvici.cnf

Code: Select all

# Basic ViciBox my.cnf for a quad-core 8-GB RAM or so
# Use the dedicate my-big.cnf for 16+GB RAM and 8+ Cores
#

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

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 2M
table_open_cache = 8192
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 50
query_cache_size = 0
thread_concurrency=16
skip-name-resolve
connect_timeout=60
long_query_time=3
log_slow_queries
max_connections=2000
open_files_limit=24576
max_heap_table_size=64M
expire_logs_days=3
default-storage-engine=MyISAM
table_definition_cache=8192
table_cache=8192
myisam_recover
myisam_repair_threads=1

# This will disable networking
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
log-error       = /var/log/mysql/mysqld.log
socket          = /var/run/mysql/mysql.sock

[mysqldump]
socket          = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# 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


thank you for your quick reply. I'll get back to you on this tomorrow.

Tested out for a week and no use. One server is running db+web+phone with a 5-10 agents, while other is a phone only server with 25 agents. running at average of 8.00 dial level.

i have two questions:

1, can cronjobs be an issue? both server has similar cronjobs.
2. vicidial_agent_log seems heavy with 4m or so records about 600mb. can it be making things heavy. load average is 0.50 on both servers.

I am still unable to fix the issue. The issue remains the same. Optimized the db, changed the server. i think it can be some setting issue that i have been missing, lol

Next is to go setting by setting on the in-group modify page and confirm your settings are good. We just fixed a similar condition for a long-term client who had put a 33-second audio file in the On-Hold Prompt field. We removed it and the problem went away.

mflorell wrote:Next is to go setting by setting on the in-group modify page and confirm your settings are good. We just fixed a similar condition for a long-term client who had put a 33-second audio file in the On-Hold Prompt field. We removed it and the problem went away.

Server is a outbound server only, no ingroups

I've seen lists of 10mil run on RAID 10 SAS drives no problem.

Are you disks keeping up? Does the raid controller have write back cache type mechanism?
It shouldn't max out ssds.

Check util% of iostat -x 1 . it will max out when the DB locks up.

Vince-0 wrote:I've seen lists of 10mil run on RAID 10 SAS drives no problem.

Are you disks keeping up? Does the raid controller have write back cache type mechanism?
It shouldn't max out ssds.

Check util% of iostat -x 1 . it will max out when the DB locks up.

Ok, so its about 0.00% to 0.04% ,lol