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