vicidial.org

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 sufficient

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.

Everyone who is interested and think that can help us to fix the problem is welcomed to write us

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

1) WHY would you use the multi-server manual published by poundteam when you are installing with the Vicibox.com .iso installer which fully automates the process of clustering? Please tell me that you did not use the "express" install method and then manually cluster. That would be awkward. Do note that adding new servers does not require "using the same method" to cluster new servers as you used to cluster the old servers. Either they are properly clustered or not, the method to arrive at this is not relevant.

2) You described an overloading Vicidial server. The only way to determine the cause of the overload is to check the Database during the time of the problem(s) and see if there is a long-running query, and determine from that query where the problem lies.

3) Often the slow query log will be helpful in determining the problem after the fact.

4) Common problems are: too many leads (archive or delete some). too many log entries (archive or delete again). Of course, if you have any non-archive tables with more than 10M records, that could be a red flag right there.

5) An often used solution is to add a reporting/replication server which can offload the bulk of the "select" queries from the live bottleneck database.

Hey William, thanks for the reply.
Yes you guessed right. At the beginning we installed the main server with the express installation. After seeing some problems i decided to install another server as a dialer but i used an awkward way since i'm totally a noob. I googled for the clustering installation and the multi-server manual published by poundteam seemed to me at that moment as the way to go for the solution. So i followed it and made the installation manually and it worked. But as you said the installation method from one server to another isn't relevant right?

Correct. For the future, don't use "express" use the standard iso installer and when it asks, say yes to expert mode (which really just asks you a string of questions about how this server will be used). This makes adding a new server to a cluster much easier (thanks to Kumba's excellent installation software). Even if you have several different install methods on all the prior servers. Just remember that each new install requires a unique IP address to avoid a collision in admin->servers (last time I checked).