Page 1 of 1
High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 8:47 am
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
Is there a way I can disable the geo lookup? Or can anyone suggest the best practice for such a situation?
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 3:12 pm
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?
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 4:17 pm
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
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 4:30 pm
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.
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 4:40 pm
by imadsani
will try that in the morning. The vps on the other hand has 1GB ram.
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 4:44 pm
by mikeshinn
128M should be fine, but you'll have to see what else is using memory you dont want mysql thrashing.
Re: High mysql load on a high traffic server
Posted: Thu Jan 29, 2015 4:58 pm
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.
Re: High mysql load on a high traffic server
Posted: Fri Jan 30, 2015 3:23 am
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.
