I recently moved from standard shared hosting to Digital Ocean.
While I've thoroughly enjoyed the experience - especially learning to manage some (relatively) large DB's - I've run into one major issue.
My Droplet on Digital Ocean has 2GB RAM and 2 CPU's. The only thing I'm running on the server is a fairly low-profile PHP app. However, one page in this app contains a rather extensive query that, at the moment, returns around 10k rows. There are a few joins involved (not all of them indexed).
Whenever I hit this page, it takes a good 60 seconds to load. Mysql's CPU usage shoots up to the server max:
I've used SQL Tuner, converted the tables to InnoDB (per a recommendation I read, though I have at most 25 concurrent users), made and tested a huge number of changes to my.cnf, and spent a significant number of hours trying to decipher this issue. However, I'm still getting the same results (and some frustrated users).
Is this problem 100% the fault of the query? Or, are there further changes I could make to my.cnf (or anywher else) that could help decrease the CPU load and increase the loading speed?
Thanks for any assistance!
MySQL Version information:
The my.cnf, including portions generated by Percona and recommendations made by SQL Tuner:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 50
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 250M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 1000M
join_buffer_size = 250M
# LOGGING #
log_error = /var/log/mysql/error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
Edit - In hindsight... I'm assuming it's this generated query. It takes 42s to run in PHPMyAdmin.
SELECT x.*,
if(fielding_rating_pos2=0,'-',if(TRUNCATE((fielding_rating_pos2+40)/40,
0)<1,1,
0)))) as fielding_rating_pos2, if(TRUNCATE((fielding_rating_pos2+40
)/40,
0)>5, 5,TRUNCATE((fielding_rating_pos2+40)/40,
if(fielding_rating_pos3=0,'-',if(TRUNCATE((fielding_rating_pos3+40)/40,
0)<1,1,
0)))) as fielding_rating_pos3, if(fielding_rating_pos4=0,
'-',
if(TRUNCATE((fielding_rating_pos3+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos3+40)/40,
0
)<1,1,
0)))) as fielding_rating_pos4, if(TRUNCATE((fielding_rating_pos4+40)/40,
if(fielding_rating_pos5=0,'-',if(TRUNCATE((fielding_rating_pos5+40)/40,
0)<1,1,
0
)>5, 5,
0)))) as fielding_rating_pos5, if(fielding_rating_pos6=0,'-',
if(TRUNCATE((fielding_rating_pos6+40)/40, 0)<1,1,if(TRUNCATE((fielding_rating_pos6+40)/40,
0
)>5,
5,TRUNCATE((fielding_rating_pos6+40)/40,0)))) as fielding_rating_pos6,
TRUNCATE((fielding_rating_pos4+40)/40, 5,TRUNCATE((fielding_rating_pos5+40)/40,
if(fielding_rating_pos7=0,'-',if(TRUNCATE((fielding_rating_pos7+40)/40,
0)<1,1,
0)))) as fielding_rating_pos7, if(TRUNCATE((fielding_rating_pos5+40
)/40,
0
)>5,
'-',
0)>5, 5,TRUNCATE((fielding_rating_pos8+40)/40,0)))) as fielding_rating_pos8,
if(fielding_rating_pos9=0,'-',if(TRUNCATE((fielding_rating_pos9+40)/40,
if(TRUNCATE((fielding_rating_pos7+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos7+40)/40,
0)<1,1,
0)))) as fielding_rating_pos9, if(TRUNCATE((fielding_rating_pos4+40)/40,
if(fielding_rating_pos8=0, if(fielding_ratings_catcher_arm=0,
'-',
0
)<1,1,
0)>5,5,TRUNCATE((fielding_ratings_catcher_arm+40)/40, 0)))) as fielding_ratings_catcher_arm,
if(fielding_ratings_infield_arm=0, '-',if(TRUNCATE((fielding_ratings_infield_arm+40)/40,
if(TRUNCATE((fielding_rating_pos9+40)/40,0)>5, 5,TRUNCATE((fielding_rating_pos9+40)/40,
0)<1,1,if(TRUNCATE((fielding_ratings_infield_arm+40)/40, 0)>5,
5,TRUNCATE((fielding_ratings_infield_arm+40)/40,0)))) as fielding_ratings_infield_arm,
if(fielding_ratings_outfield_arm=0,'-',if(TRUNCATE((fielding_ratings_outfield_arm+40)/40,
0)<1,1,if(TRUNCATE((fielding_ratings_outfield_arm+40)/40,0)>5,
5,TRUNCATE((fielding_ratings_outfield_arm+40)/40,0)))
) as fielding_ratings_outfield_arm FROM players_fielding as pf,
if(TRUNCATE((fielding_ratings_catcher_arm+40)/40, if(TRUNCATE((fielding_rating_pos8+40)/40,
( if(TRUNCATE((fielding_rating_pos8+40)/40, 0)<1,1,if(TRUNCATE((fielding_ratings_catcher_arm+40)/40,
SELECT px.player_id,first_name,last_name,position,role,age,porder,
slot,batting_ratings_talent_contact,batting_ratings_talent_gap,
batting_ratings_talent_power,batting_ratings_talent_eye,
batting_ratings_talent_strikeouts,running_ratings_speed,
running_ratings_stealing,
if(TRUNCATE((batting_ratings_talent_contact+40)/40, 0)<1,
1,
0)>5,5,TRUNCATE((batting_ratings_talent_contact+40)/40,
0))) as batting_ratings_talent_contact,if(TRUNCATE((batting_ratings_talent_gap+40
)/40,
0)<1,1,
0)>5,5,TRUNCATE((batting_ratings_talent_gap+40)/40,0))
) as batting_ratings_talent_gap,
if(TRUNCATE((batting_ratings_talent_power+40)/40,0)<1, 1,if(TRUNCATE((batting_ratings_talent_power+40)/40,
0)>5, running_ratings_baserunning FROM ( SELECT player_id, 5,
TRUNCATE((batting_ratings_talent_power+40)/40,0))) as batting_ratings_talent_power,
if(TRUNCATE((batting_ratings_talent_eye+40)/40,0)<1,1, if(TRUNCATE((batting_ratings_talent_contact+40)/40,
if(TRUNCATE((batting_ratings_talent_eye+40)/40,0)>5,5, TRUNCATE((batting_ratings_talent_eye+40)/40,
0))) as batting_ratings_talent_eye, if(TRUNCATE((batting_ratings_talent_strikeouts+40)/40,
if(TRUNCATE((batting_ratings_talent_gap+40)/40,
0)<1,1,
0)>5,5,TRUNCATE((batting_ratings_talent_strikeouts+40)/40,
0))) as batting_ratings_talent_strikeouts FROM players_batting) as ps RIGHT JOIN ( SELECT first_name,
last_name,position,role,age,
0)<1,1,
0))) as running_ratings_speed, if(TRUNCATE((batting_ratings_talent_strikeouts+40
)/40,
if(TRUNCATE((running_ratings_stealing+40)/40,0)<1,1,if(TRUNCATE((running_ratings_stealing+40)/40,
0)>5,5,TRUNCATE((running_ratings_stealing+40)/40,0))
) as running_ratings_stealing,
if(TRUNCATE((running_ratings_baserunning+40)/40,0)<1,
if(TRUNCATE((running_ratings_speed+40)/40, 1,if(TRUNCATE((running_ratings_baserunning+40)/40,
0
)>5,
5,
0))
) as running_ratings_baserunning, pp.* FROM players as p,
( SELECT dp2.* FROM if(TRUNCATE((running_ratings_speed+40)/40,
0
)>5, 5,
SELECT dp1.player_id,
porder) as porder, slot FROM TRUNCATE((running_ratings_speed+40)/40,
( TRUNCATE((running_ratings_baserunning+40)/40, ( if(porder IS NULL,
999999, SELECT dp.player_id,slot FROM ootp_sql_draft_pool as dp LEFT JOIN amateur_draft as ad ON dp.player_id=ad.player_id
WHERE dp.league_id=101
AND ad.player_id IS NULL) as dp1
LEFT JOIN ootp_sql_draft_list as dl ON dp1.player_id=dl.player_id
WHERE human_manager_id IN (3,-999,NULL)
ORDER BY porder) as dp2
GROUP BY player_id) as pp
WHERE p.player_id=pp.player_id
AND position!=1) as px ON ps.player_id=px.player_id
) as x
WHERE pf.player_id=x.player_id
ORDER BY batting_ratings_talent_contact DESC,batting_ratings_talent_power DESC,
batting_ratings_talent_eye DESC,last_name,first_name
LIMIT 0,100
