MariaDB - 10,000+ select statement /sec

[doublepositive]

We have a vicidial 7 installation with separate web, asterisk and db servers. The db runs on openSUSE 42.1 and the db is Mariadb is 10.1.14. We currently have about 100 agents. We use zenoss to monitor our environment and recently added this server environment. At once we noticed off-business hours that the db server was at a load of 1 and there were 10,000+ select statements running per second. During business hours the load is 1.5 and the select statements are 12,000/sec. I checked with phpmyadmin and it shows the same. Is there a "fix" for this? Could it be an optimized my.cnf file or indexes that we may be missing? The db is a new HP proliant DL360 Gen9 with 128GB or RAM, dual 2.4GHz with 56 cores and 2 Raid 1 400GB SSD (total of 4 drives) where the /var/lib/mysql directory is on a separate RAID 1.

[mflorell]

What do you want a "fix" for exactly?

How many dialer servers are you using?

How many calls per day are being placed?

What kind of RAID card are you using?

What are your my.cnf settings?

[doublepositive]

The "fix" is for the 10,000+ select statements per second. If no one is dialing and no agents are logged in, then what is the system doing? I realize there are cron jobs running, but 10,000 statements per sec.?
We have 3 dialer servers actively being used and 4 more for expansion. The 4 are on but are not doing anything now.
We are making about 80,000 calls per day.
The RAID card is the HP p440 (no software RAID)
Here is our my.cnf file:

[client]
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=2048
key_buffer_size=64G
max_allowed_packet=16M
table_open_cache=8192
table_definition_cache=2048
open_files_limit=24576
sort_buffer_size=8M
net_buffer_length=8K
read_buffer_size=4M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=128M
join_buffer_size=1M
thread_cache_size=100
query_cache_size=0
thread_concurrency=8
default-storage-engine=MyISAM
expire_logs_days=3
concurrent_insert=2
myisam_repair_threads=2
myisam_use_mmap=1
skip-innodb
delay_key_write=ALL
max_write_lock_count=1

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

[myisamchk]
key_buffer = 1G
sort_buffer = 384M
read_buffer = 512M
write_buffer = 256M

!includedir /etc/my.cnf.d

[mflorell]

I don't know why you would have 10,000 QPS happening after-hours. Have you looked at the queries that are being run?

[williamconley]

sounds like a brute force attack on a login page to me.

are we whitelisted? have we looked at these queries?

[doublepositive]

We do use a whitelist so only our remote agents can access the webpages. I turned on the general log in mysql for about 2 seconds using this statement;

Code: Select all

SET GLOBAL general_log = 'ON';

and then off with this one;

Code: Select all

SET GLOBAL general_log = 'OFF';

There are 25,567 entries in that log file! About 99.9% of them are the below statement;

119634 Query SELECT SQL_NO_CACHE auto_dial_level,campaign_id from asterisk.vicidial_campaigns where dial_method='RATIOAUTO' and active='Y'

[mflorell]

That's not from VICIdial, looks like you have some custom stuff running on that system.

[doublepositive]

Yup, that is it. I searched through all of the developer scripts and code and found that statement in a cron job script set to run with all asterisks.
Thanks for your help!!