High mysql load on a high traffic server

Customer support forums for Atomic Protector (formerly Atomic Secured Linux). There is no such thing as a bad question here as long as it pertains to using Atomic Protector. Newbies feel free to get help getting started or asking questions that may be obvious. Regular users are asked to be gentle. :-)
imadsani
Forum Regular
Forum Regular
Posts: 112
Joined: Mon Sep 16, 2013 10:10 am
Location: Lahore

High mysql load on a high traffic server

Unread post by imadsani »

Hey,

I've recently noticed that ASL took 15 minutes to load on a high traffic server. Htop shows multiple threads of mysql running at 100%. I ran show full processlist in mysql and this is what is constantly running.
Note: The IP keeps changing everytime I run the command

Code: Select all

mysql> show full processlist;
+----+--------+-------------------------------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User   | Host                          | db     | Command | Time | State        | Info                                                                                          | Rows_sent | Rows_examined |
+----+--------+-------------------------------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------+-----------+---------------+
|  1 | tortix | server:44717                  | tortix | Query   |    0 | Sending data | SELECT code FROM aslw_geo_range WHERE inet_aton('217.165.134.43') BETWEEN ip_start AND ip_end |         0 |             0 |
|  4 | root   | localhost                     | NULL   | Query   |    0 | init         | show full processlist                                                                         |         0 |             0 |
+----+--------+-------------------------------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------+-----------+---------------+
This is where the db stands at

Image

Is there a way I can disable the geo lookup? Or can anyone suggest the best practice for such a situation?
jgodwin
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 39
Joined: Mon Sep 14, 2009 12:15 pm

Re: High mysql load on a high traffic server

Unread post by jgodwin »

Couple of questions:

What does your mysql config look like?
What does your system load look like when no ASL Web is open?
imadsani
Forum Regular
Forum Regular
Posts: 112
Joined: Mon Sep 16, 2013 10:10 am
Location: Lahore

Re: High mysql load on a high traffic server

Unread post by imadsani »

I couldn't run the tests on the main server so i moved the db to a vps with nothing else on it, but on the main server the load usually is at 40-50%. I've changed ASL's dbhost IP to this VPS to troubleshoot.

Here's the config on the vps:

Code: Select all

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
and this is the config i use on the main server:

Code: Select all

[mysqld]
local-infile=0
log-warnings=2
query_cache_size=600M
binlog-ignore-db=tortix

datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql
### MyISAM ###
#key_buffer_size = 600M # keep it low if no myisam data
myisam_recover_options = FORCE,BACKUP
### SAFETY #
#bind_address = 127.0.0.1
local_infile = 0
#innodb = force
max_allowed_packet = 600M
max_connect_errors = 100000
skip_name_resolve
### CACHES AND LIMITS #
back_log = 2000
interactive_timeout = 600
wait_timeout = 600
sort_buffer_size = 1M
read_buffer_size = 1M
#read_rnd_buffer_size = 12M
join_buffer_size = 4M
tmp_table_size = 600M
#max_heap_table_size = 128M
#query_cache_limit = 4M
max_connections = 500
thread_cache_size = 64
open_files_limit = 65535
table_definition_cache = 2048
table_open_cache = 4096

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
User avatar
mikeshinn
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 4149
Joined: Thu Feb 07, 2008 7:49 pm
Location: Chantilly, VA

Re: High mysql load on a high traffic server

Unread post by mikeshinn »

query_cache_size=600M
Right off the bat, this setting is probably going to cause you problems in general with mysql. Really large query caches can have an counter intuitive inverse effect on performance, as they get really large. In general, you probably dont want to go beyond 128MB. So you may want to lower that to 128M and see how your system performs.

On the VPS, I didnt notice you had a query cache setup, so I'd recommend you setup one there, it will certainly help mysql to have some query cache. Without knowing how much ram your VPS has I couldnt tell you what that should be though.
imadsani
Forum Regular
Forum Regular
Posts: 112
Joined: Mon Sep 16, 2013 10:10 am
Location: Lahore

Re: High mysql load on a high traffic server

Unread post by imadsani »

will try that in the morning. The vps on the other hand has 1GB ram.
User avatar
mikeshinn
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin
Posts: 4149
Joined: Thu Feb 07, 2008 7:49 pm
Location: Chantilly, VA

Re: High mysql load on a high traffic server

Unread post by mikeshinn »

128M should be fine, but you'll have to see what else is using memory you dont want mysql thrashing.
imadsani
Forum Regular
Forum Regular
Posts: 112
Joined: Mon Sep 16, 2013 10:10 am
Location: Lahore

Re: High mysql load on a high traffic server

Unread post by imadsani »

I've got 64GB's on the main server so that shouldn't be a problem and there's nothing else running on the vps so shouldn't be a problem.
imadsani
Forum Regular
Forum Regular
Posts: 112
Joined: Mon Sep 16, 2013 10:10 am
Location: Lahore

Re: High mysql load on a high traffic server

Unread post by imadsani »

I've tried it with query_cache_size=128M but no joy. the mysql threads still hit near 100% cpu usage and stick there for a good 15-20minutes.
Post Reply