Vicidial 8.0.0 how to install Master Slave mysql DB

[manos]

Hello folks!
I'm facing some problems with Mysql database performance. It delays sometimes to close the call, sometimes enter two calls simultaneously, some calls don't have the audio record.

The slow query log every day logs significantly a number of queries per day as slow, like for example:

Code: Select all

SET timestamp=1553342283;
DELETE from live_inbound where uniqueid IN('1553342276.124472','1553342276.124472') and server_ip='192.168.2.221';
# User@Host: cron[cron] @  [192.168.2.221]
# Thread_id: 102  Schema: asterisk  QC_hit: No
# Query_time: 5.692322  Lock_time: 0.275294  Rows_sent: 0  Rows_examined: 4
# Rows_affected: 1
# Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  vicidial_manager        range   serverstat      serverstat      49      NULL    5       1.00    100.00  100.00  Using where; Using filesort
#
SET timestamp=1553342283;
UPDATE vicidial_manager set status='QUEUE' where server_ip = '192.168.2.221' and status = 'NEW' order by man_id limit 1;
# User@Host: cron[cron] @  [192.168.2.221]
# Thread_id: 1602138  Schema: asterisk  QC_hit: No
# Query_time: 5.663125  Lock_time: 0.000037  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  vicidial_list   range   PRIMARY PRIMARY 4       NULL    1       1.00    100.00  100.00  Using where
#
SET timestamp=1553342283;
UPDATE vicidial_list set status='AB' where lead_id = '6774253';
# User@Host: cron[cron] @ localhost []
# Thread_id: 1602245  Schema: asterisk  QC_hit: No
# Query_time: 5.552035  Lock_time: 0.000043  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  vicidial_auto_calls     range   callerid        callerid        63      NULL    4       1.00    100.00  100.00  Using where


The installation of the cluster is made following the instructions of VICIdial® Multi-Server (Cluster) Manual - PoundTeam Incorporated.
The calls made on the peak are 400 current active calls sometimes less, sometimes more.
I think the main server in which is installed the MySql is pretty powerful but maybe the my.cnf is not configured properly.

Code: Select all

[mysqld]
log_bin=/var/lib/mysql/mysql-bin
relay_log=/var/lib/mysql/mysql-relay-bin
binlog_format=mixed
datadir   = /srv/mysql/data
server-id   = 1
slave-skip-errors = 1032,1690,1062
slave_parallel_threads=2
slave-parallel-mode=optimistic
port = 3306
socket = /var/run/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION
skip-external-locking
skip-name-resolve
connect_timeout=60
long_query_time=3
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysqld-slow.log
log-slow-verbosity=query_plan,explain
max_connections=4096
key_buffer_size = 16G
max_allowed_packet = 16M
table_definition_cache=16192
table_open_cache = 4096
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
query_cache_size=64M
query_cache_type=1
thread_handling=pool-of-threads
thread_pool_size=8
default-storage-engine=MyISAM
expire_logs_days = 3
concurrent_insert = 2
myisam_repair_threads = 8
myisam_recover_option=DEFAULT
tmpdir = /tmp/
thread_cache_size=200
join_buffer_size=1M
myisam_use_mmap=1
open_files_limit=24576
max_heap_table_size=256M
tmp_table_size=256M
key_cache_segments=64


I have made a script in bash which tries to maintain the DB as clean as possible and runs once in every 3 days but still its not suffincent

Code: Select all

#!/bin/bash

del_time=$(date -d @$(( $(date +%s) - $(( 86400 * 5)) )) '+%Y-%m-%d 10:00:00')  # Create the del_time with 5 days before on 10:00:00
echo $del_time

log_file="/var/log/maintain_DB/$(date '+%Y%m%d_cron.log')"
touch $log_file  # Create the log file with the execution day date

query_0="DELETE FROM vicidial_log WHERE call_date < '$del_time'"
query_1="DELETE FROM call_log WHERE start_time < '$del_time'"
query_2="DELETE FROM vicidial_log_extended WHERE call_date < '$del_time'"
query_3="DELETE FROM vicidial_drop_log WHERE drop_date < '$del_time'"
query_4="DELETE FROM vicidial_dial_log WHERE call_date < '$del_time'"
query_5="DELETE FROM vicidial_api_log WHERE api_date < '$del_time'"
query_6="DELETE FROM vicidial_api_urls WHERE api_date < '$del_time'"
query_7="DELETE FROM vicidial_carrier_log WHERE call_date < '$del_time'"
query_8="DELETE FROM vicidial_log WHERE call_date < '$del_time'"
query_9="DELETE FROM vicidial_closer_log WHERE call_date < '$del_time'"
query_10="DELETE FROM vicidial_xfer_log WHERE call_date < '$del_time'"
query_11="DELETE FROM vicidial_rt_monitor_log WHERE monitor_start_time < '$del_time'"
query_12="DELETE FROM server_performance WHERE start_time < '$del_time'"
query_13="DELETE FROM vicidial_url_log WHERE url_date < '$del_time'"
query_14="DELETE FROM vicidial_dtmf_log WHERE dtmf_time < '$del_time'"
query_15="DELETE FROM vicidial_agent_log WHERE event_time < '$del_time'"
query_16="DELETE FROM vicidial_lead_search_log WHERE event_date < '$del_time'"
query_17="DELETE FROM vicidial_agent_function_log WHERE event_time < '$del_time'"
query_18="DELETE FROM vicidial_outbound_ivr_log WHERE event_date < '$del_time'"
query_19="DELETE FROM recording_log WHERE start_time < '$del_time'"
query_20="DELETE FROM vicidial_did_log WHERE call_date < '$del_time'"

i=0
while [ $i -le 20 ]
do
   execute="query_${i}"
   mysql -D asterisk -e "${!execute}" >> $log_file
   i=$(( $i + 1 ))
done


In this moment that we are working for example we have:
-----------------------------------------------------------------------------------------
Vicidial list has: 8491111 records.
DIALABLE LEADS: 79395
542 current active calls 539 calls ringing
-----------------------------------------------------------------------------------------

DO WE LOAD TO MUCH THE SERVER OR IS THE my.cnf THAT IS NOT CONFIGURED PROPERLY.

The solution that i think for this problem is to setup a Master Slave mysql DB.
Can someone please send me a link or a source with a manual or steps for the installation.
Does the master slave mysql setup improve the mysql performance?

Thank you in advance!



Main Server specs:
CPU - Intel(R) Xeon(R) CPU E5-2650L 0 @ 1.80GHz 32 cores
RAM: 64Gb
Asterisk: Asterisk 11.25.1-vici
VERSION: 2.14-694a
BUILD: 181005-1738
Vicibox 8.0.0 iso

Installed inside the full vicidial server capability. Web, Dialler and Mysql server

Secondary server specs: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz 16 cores
only Asterisk is used from it.

RAM: 16GB
Asterisk: Asterisk 11.25.1-vici
VERSION: 2.14-694a
BUILD: 181005-1738
Vicibox 8.0.0 iso

[thephaseusa]

Let’s see what Matt and William say on this, but it seems to me that if you used the DB server just for mysql, and added 4 asterisk servers to the 1 you already have, and add 1 web server you could make the 500 to 600 concurrent calls you need to make.

[manos]

Hi, and thanks for the reply.
Well in fact it is one main server that has- Full vicidial capability in it, Web-Asterisk-Mysql and another server in which is only asterisk installed in and the servers are connected together.
Dial-calls are mainly made from this second server, and the web navigation adn mysql database is in the Main server.

[thephaseusa]

http://download.vicidial.com/iso/vicibo ... nstall.pdf

Read the paragraph Hardware Recommendations and Dimensioning please.
1 asterisk server should give you 25 agents at 4:1 or 50 inbound with 100 concurrent calls.

1 asterisk server = 100 calls
2 asterisk servers = 200 calls
3 asterisk servers = 300 calls
Etc etc

You are making almost 600 concurrent calls?

[manos]

Hello again!!
I have a question, to the article at the hardware specs it says:
Quad-Core CPU 2.0+Ghz
4+ GB RAM
160+ GB RAID-1 Hard Drive setup

what about our second server which has these specs:
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz 16 cores
16 GB RAM
120 GB Hard Drive no RAID.

Should I follow still this kind of setup suggestion, One dialer per 100 concurrent calls need?
1 asterisk server should give you 25 agents at 4:1 or 50 inbound with 100 concurrent calls.

And i repeat to this server its only asterisk in use.. + records of calls stored in it

Thanks in advance

[williamconley]

i have no idea what you are asking. the suggested minimums are listed. you have a server showing better specs. now: is there a question in there somewhere?

[manos]

Hi William the question is even if i have better specs, should i still follow this kind of setup
1 asterisk server should give you 25 agents at 4:1 or 50 inbound with 100 concurrent calls.

Or lets say, since this server has a 16 core processor it is capable to handle lets say 300 concurrent calls or its kinda mandatory to have two ore more asterisk servers if the calls become more than 100 concurrent calls in asterisk server. This was what i tried to say. Sorry for my bad English.

[williamconley]

I'd love to give you "this cpu count = this many agents", but it's NEVER that easy. Get the best you can reasonably afford (cpu count, cpu speed, front side bus speed, RAM, memory speed, HD size, RAID level, HD count ...) based on what you have available. The ONLY server that can't be duplicated is the DB. The rest you just add a new server whenever the ones you have overload.

[manos]

Hello again William, thanks for the info and hope to not disturb you with another question

#!/bin/bash
ps -C $1 -O rss | gawk '{ count ++; sum += $2 }; END {count --; print "Number of processes =",count; print "Memory usage per process =",sum/1024/count, "MB"; print "Total memory usage =", sum/1024, "MB" ;};'

The script above shows how much RAM is mysql utilizing from your server.
ex - Number of processes = 1
Memory usage per process = 3880.13 MB
Total memory usage = 3880.13 MB

From what i read the main things that are responsible for mysql performance are RAM and Disk storage speed, next it comes the cpu.
Mysql seems like is barely using the server resources. From 64GB it use 4GB and the cpu load goes at max 3.smth

What further changes should I make from my.cnf file to enable mysql to use more server resources to get better performance.

MYSQL my.cnf
----------------------------------------------------------------------------------------------------------
max_connections=4096
key_buffer_size = 16G
max_allowed_packet = 16M
table_definition_cache=16192
table_open_cache = 4096
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
query_cache_size=64M
query_cache_type=1
thread_handling=pool-of-threads
thread_pool_size=8
default-storage-engine=MyISAM
expire_logs_days = 3
concurrent_insert = 2
myisam_repair_threads = 8
myisam_recover_option=DEFAULT
tmpdir = /tmp/
thread_cache_size=200
join_buffer_size=1M
myisam_use_mmap=1
open_files_limit=24576
max_heap_table_size=256M
tmp_table_size=256M
key_cache_segments=64
-----------------------------------------------------------------------------------------------------------

[williamconley]

Mysql will always fill the memory to utilize as many "repeatable" requests as possible (IE: If it's still in memory, why pull it from the HD, right?). But it also marks that memory as "available for other processes" when it's not mandatory for it to be available to mysql (the select has pulled the data), so it's just a cache situation.

If there were universally "better" vs "worse" settings, they'd be in use already. In fact, The Vicidial Group has included an "enterprise" settings file that can be used in place of the regular my.cnf (MyBigVici.cnf? I forget the name of the file).

And the main "things" that are responsible for msyql performance are ... the entire system. Front side bus. CPU speed. Core count. Speed of CPU. Speed of memory. The entire physical system is used to perform each query. Bottlenecks are of course "the thing". Thus more cores is cool. We have several 40 core servers for DBs for larger installations.

Another serious concern, however, is Indexing. The Vicidial Group can not put an index in for every occasion, as that would bog down ALL servers. So some reports and processes that you may use more heavily than other call centers may benefit from having an extra index or three added to speed up results. This is what the slow query log is for. Anything that stick around long enough to land in the slow query log is worth checking out. If the query is using a where or having clause that is not indexed, look into indexing the table to match the query. Speedup results for that are often astounding (10% or less time required, astounding: seriously, a two second query dropping to 0.2 seconds is worthy of investigation).

Summary: my.cnf doesn't have 'good' or 'bad' settings as much as it has "what works for you". To determine what works for you: mysql slow query log will rat out offenders. As will "show processlist" during moments of slow mysql response or system instability. The oldest process running that's not "waiting for ..." something is usually your culprit. Find out why that query is running, what it does, and whether it's both necessary and properly indexed to run.

[manos]

William, thank you so much for the detailed reply. You're the man!