Database Performance

[covarrubiasgg]

Hi all, i know there are tons of database perfomance threads, but this one is regarding a very specific issue.

Today i was monitoring one of my setups which is a mini Cluster with 1 DB + 3 Dialers There are less de 80 agents on that setup, but i noticed on peak hours the load of the server is really high, 4.0 or below.... and i am having an error that i specified on another thread:

viewtopic.php?f=4&t=34041&p=116064#p116064

The problem is that today there were only 23 agents connect to the server and my load was 6.0

Apache logs show this:

Code: Select all


[Sat Feb 21 06:11:51.394258 2015] [core:notice] [pid 16926] AH00094: Command line: '/usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf -D SSL -D SYSTEMD -D FOREGROUND'
[Sat Feb 21 13:47:59.004534 2015] [mpm_prefork:error] [pid 16926] AH00161: server reached MaxRequestWorkers setting, consider raising the MaxRequestWorkers setting
[Sat Feb 21 14:36:19.855605 2015] [php5:error] [pid 7282] [client 192.168.100.1:15167] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.855643 2015] [php5:error] [pid 7282] [client 192.168.100.1:15167] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.855811 2015] [php5:error] [pid 6685] [client 192.168.100.1:15034] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.855834 2015] [php5:error] [pid 6685] [client 192.168.100.1:15034] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856096 2015] [php5:error] [pid 7550] [client 192.168.100.1:15169] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856121 2015] [php5:error] [pid 7550] [client 192.168.100.1:15169] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856244 2015] [php5:error] [pid 6661] [client 192.168.100.1:15033] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856260 2015] [php5:error] [pid 6635] [client 192.168.102.6:65028] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856270 2015] [php5:error] [pid 6661] [client 192.168.100.1:15033] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:19.856280 2015] [php5:error] [pid 6635] [client 192.168.102.6:65028] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:27.569059 2015] [php5:error] [pid 6302] [client 192.168.100.1:14194] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:27.569093 2015] [php5:error] [pid 6302] [client 192.168.100.1:14194] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:27.932851 2015] [php5:error] [pid 3794] [client 192.168.100.1:15262] PHP Warning:  mysqli_connect(): (HY000/2002): Resource temporarily unavailable in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 49, referer: http://192.168.100.50/agc/vicidial.php
[Sat Feb 21 14:36:27.932878 2015] [php5:error] [pid 3794] [client 192.168.100.1:15262] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, boolean given in /srv/www/htdocs/agc/dbconnect_mysqli.php on line 52, referer: http://192.168.100.50/agc/vicidial.php

But htop showed that mysql was running consuming all the resources of the machine oO

BTW the serve specs are

Code: Select all

CPU: Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz (4 cores + HT) [I will disable the HT just to be sure it is not causing any weird behaivior)
RAM:  8GB ECC
Hard Drive: Raid 10 on 4 128 GB SSD using SI MegaRAID SAS 9240-4i  (i know is not the best card, but it has been working great for almost 2 years)
Vicidial:  VERSION: 2.10-472a  BUILD: 150120-0749
Vicibox: ViciBox Redux v.6.0-beta

My question is, what could be causing this bas perfoamce, i though it was time to split the DB and the WebServer, but today with only 23 agents i got the same error, do you have any insigths on where to begin troubleshooting this issue?

[mflorell]

How many leads are in vicidial_list?
Did you tune your my.cnf file to your database?
What is your mysql max connections set to?
What version of mysql/mariadb are you using?
Are hostname lookups disabled?
Did you raise your PHP and Apache settings to handle more connections?
Is your webserver on the same machine as your DB?

[covarrubiasgg]

Matt thank you for you response, the answer to all your questions...

How many leads are in vicidial_list?

3 Million records

Did you tune your my.cnf file to your database?

I think vicibox auto-generate my my.cnf

What is your mysql max connections set to?

# Basic ViciBox my.cnf for a quad-core 8-GB RAM or so
# Use the dedicate my-big.cnf for 16+GB RAM and 8+ Cores
#

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 640M
max_allowed_packet = 2M
table_open_cache = 8192
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 50
query_cache_size = 0
thread_concurrency=8
skip-name-resolve
connect_timeout=60
long_query_time=3
log_slow_queries
max_connections=768
open_files_limit=24576
max_heap_table_size=32M
expire_logs_days=3
default-storage-engine=MyISAM
table_definition_cache=8192
table_cache=8192
myisam_recover
myisam_repair_threads=1

What version of mysql/mariadb are you using?

erver version: 5.5.33-MariaDB-log openSUSE package

Are hostname lookups disabled?

It wasnt but already add skip-name-resolve

Did you raise your PHP and Apache settings to handle more connections?

I only raised the MaxClients in /etc/apache2/server-tuning.conf and raised the memory_limit in php.ini , not sure what else i should increase

Is your web server on the same machine as your DB?

Yes, i was planning to split the webserver when i reach 100 agents

[mflorell]

First, I would suggest turning on slow query logging in mysql if it isn't on already, then checking to see if you have any major issues happening.

Second, get the webserver off of the DB machine now.

[covarrubiasgg]

Matt i split the web into another server, and the problem with the database persist.

I remember that one week ago i had a similar issue with a smaller setup, one of the dialer were having high load 4 or 5 load after a
zypper update

I made another zypper update and there were a systemd update, after installing it, the load on that server is 0.3.

I realize that the same update is available on my database server, and i hoping it fix it... i will let you all know is that was the problem i just installed a few minutes ago.

[covarrubiasgg]

Forget about the previous post, still happening now i am having

BUG: soft lockup - CPU stuck for 23s!

I know this could be a bad CPU

[mflorell]

Phantom hardware issues are the worst. We once had a client with bad RAM in their DB server causing all kinds of problems, and it took 2 weeks to figure it out. Literally swapping out the RAM and leaving everything else the same fixed every problem.

[covarrubiasgg]

Just to let you know.

All is working now, i splatted the load into another server, moving the webserver to a new one, i was still getting even high load (100 load at some times :S)..


I enable back the HT and Turbo Boost on the server (which i disabled previously for testing) and now it is working like a charm, load is 0.4 - 0.9 and haven’t had any crash in the last 2 days after i made those changes...