1

UPDATE: finally remembered to look at munin, and it seems that one of the HDDs is the root of the problem after all, unless I'm totally reading this wrong. Here's the munin output: http://imagizer.imageshack.com/img536/1070/9d5c62.png . Am I right in thinking that one of my HDDs probably needs replacing, or could something else account for those I/O spikes?



I've got a pretty powerful server (quad core i7, 32GB RAM) running a Drupal 7 website (pixelscrapper.com) on a LAMP stack, utilizing Varnish, Memcached, APC, CloudFlare. We get about 20,000 pageviews a day, and typically the site has been loading in 3-4 seconds for authenticated users, with total server time accounting for about .5 seconds of that, split fairly evenly between php and mysql (so about .2 seconds in mysql per request)... all as reported via New Relic (lite, so I can't see db traces).

The 3-4 second page load time, with .2 seconds in mysql has been going like this for several months, consistently.

Then a few days ago the site started taking a lot longer to load, and looking at New Relic, I notice that mysql is now taking about 2 seconds per request, instead of .2 seconds. This is without any major changes to the site at all... just regular site usage.

So my question is, what might suddenly cause mysql to be spending 2 seconds per request instead of .2 seconds, with no changes deployed to the site, and no major changes in traffic?

The best instinct I have is that this might be caused by:

  1. Some table growing too large, or the total db growing too large, based on current memory allocations in my.cnf, so what used to be in memory is now thrashing the HDDs.
  2. Some hardware issue, like a problem with one of the HDDs causing disk access to be slow (I have two HDDs in RAID).

The thing is, based on looking at mysqltuner output, the memory allocations seem to be okay, and based on an initial drive test, the HDDs don't seem to be broken... so...

Any thoughts on what might cause something like this to happen, how I should go about troubleshooting this?

Here's mysqltuner output (mysql running for 98 days):

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28-0ubuntu0.12.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 88B (Tables: 10)
[--] Data in InnoDB tables: 1G (Tables: 582)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 148

-------- Performance Metrics -------------------------------------------------
[--] Up for: 98d 2h 0m 34s (1B q [156.130 qps], 14M conn, TX: 3596B, RX: 225B)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 5.2G global + 3.1M per thread (300 max threads)
[OK] Maximum possible memory usage: 6.1G (19% of installed RAM)
[OK] Slow queries: 0% (32K/1B)
[OK] Highest usage of available connections: 37% (113/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.4M
[OK] Key buffer hit rate: 100.0% (35M cached / 545 reads)
[OK] Query cache efficiency: 71.4% (820M cached / 1B selects)
[!!] Query cache prunes per day: 43794
[OK] Sorts requiring temporary tables: 0% (15K temp sorts / 63M sorts)
[!!] Joins performed without indexes: 85938
[OK] Temporary tables created on disk: 1% (302K on disk / 17M total)
[OK] Thread cache hit rate: 99% (113K created / 14M connections)
[!!] Table cache hit rate: 13% (2K open / 19K opened)
[OK] Open file limit used: 0% (69/33K)
[OK] Table locks acquired immediately: 99% (529M immediate / 529M locks)
[OK] InnoDB data size / buffer pool: 1.5G/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 1G) [see warning above]
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_cache (> 16384)

Here's my.cnf:


#
# 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
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
#
# 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      = 128M
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        = 300
table_cache     = 16384
max_heap_table_size = 256M
join_buffer_size    = 512K

#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 4M
query_cache_size        = 1024M
#
# * 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 3
#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!
#
innodb_file_per_table
innodb_buffer_pool_size = 4G 
innodb_flush_method = O_DIRECT

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# Lock wait timeout. See http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded
innodb_lock_wait_timeout = 120

# * 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/

Here's my slow query log:


https://gist.github.com/JordanMagnuson/68e3ab30ff9b6ccc5a96

3
  • So what is the I/O, CPU and RAM usage of that server? And mysqltuner tells you to run OPTIMIZE TABLE -- did you do that?
    – faker
    Jul 18, 2014 at 16:45
  • Thanks for taking the time, @faker . Please see the update at the top of my post, and my Munin logs here: imagizer.imageshack.com/img536/1070/9d5c62.png Jul 18, 2014 at 16:50
  • Something is definitely thrashing your sdb drive. I think that's the key to resolving the problem.
    – Nathan C
    Jul 18, 2014 at 18:29

1 Answer 1

0

Tried to run an HDD check on sdb, which wouldn't finish... (initially I thought I had run an HDD check that finished, but I incorrectly thought I was testing sdb when I was actually testing sda).

So in this case the answer to my problem was to check Munin, notice the thrashing (failing) disk, and replace it.

Everything's as it was now.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .