MariaDB - 10,000+ select statement /sec

Any and all non-support discussions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

MariaDB - 10,000+ select statement /sec

Postby doublepositive » Thu Dec 01, 2016 2:12 pm

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.
doublepositive
 
Posts: 10
Joined: Wed Nov 23, 2016 1:22 pm

Re: MariaDB - 10,000+ select statement /sec

Postby mflorell » Thu Dec 01, 2016 2:49 pm

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?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: MariaDB - 10,000+ select statement /sec

Postby doublepositive » Thu Dec 01, 2016 4:10 pm

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
doublepositive
 
Posts: 10
Joined: Wed Nov 23, 2016 1:22 pm

Re: MariaDB - 10,000+ select statement /sec

Postby mflorell » Thu Dec 01, 2016 10:26 pm

I don't know why you would have 10,000 QPS happening after-hours. Have you looked at the queries that are being run?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: MariaDB - 10,000+ select statement /sec

Postby williamconley » Thu Dec 01, 2016 10:36 pm

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

are we whitelisted? have we looked at these queries?
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: MariaDB - 10,000+ select statement /sec

Postby doublepositive » Fri Dec 02, 2016 9:40 am

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'
doublepositive
 
Posts: 10
Joined: Wed Nov 23, 2016 1:22 pm

Re: MariaDB - 10,000+ select statement /sec

Postby mflorell » Fri Dec 02, 2016 11:54 am

That's not from VICIdial, looks like you have some custom stuff running on that system.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: MariaDB - 10,000+ select statement /sec

Postby doublepositive » Fri Dec 02, 2016 12:56 pm

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!!
doublepositive
 
Posts: 10
Joined: Wed Nov 23, 2016 1:22 pm


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 71 guests