about 2 weeks ago, we started experiencing random lockups and slowdowns on our forums. We've been running this site for around 6 years minimum with no issues. the site and db are on dedicated vms. The DB vm has 1 vCPU and 1GB Ram. I've never seen memory utilization exceed 60% on the box. The hard disk is currently sata (as it has been for some time). What i'm seeing in processlist is below.
| Id | User | Host | db | Command | Time | State | Info | Progress |
| 50044 | dbuser | redacted.site:45046 | syndicate_ipb | Query | 751 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50069 | dbuser | redacted.site:45098 | syndicate_ipb | Query | 728 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50084 | dbuser | redacted.site:45128 | syndicate_ipb | Query | 715 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50107 | dbuser | redacted.site:45175 | syndicate_ipb | Query | 694 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50364 | dbuser | redacted.site:45462 | syndicate_ipb | Query | 591 | Waiting for table level lock | UPDATE topics SET views=views+2 WHERE tid=64896 | 0.000 |
| 50427 | dbuser | redacted.site:45594 | syndicate_ipb | Query | 540 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50543 | dbuser | redacted.site:45833 | syndicate_ipb | Query | 493 | Waiting for table level lock | UPDATE topics SET views=views+7 WHERE tid=64878 | 0.000 |
| 50560 | dbuser | redacted.site:45867 | syndicate_ipb | Query | 480 | Waiting for table level lock | UPDATE topics SET views=views+7 WHERE tid=16817 | 0.000 |
| 50930 | dbuser | redacted.site:46630 | syndicate_ipb | Query | 258 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
| 50932 | dbuser | redacted.site:46635 | syndicate_ipb | Query | 255 | Copying to tmp table | SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title FROM posts p LEFT JOIN topics t ON ( t.tid=p.topic_id ) WHERE t.approved=1 AND t.forum_id IN (18,17,31,32,60,47,34,110,143,25,26,58,29,155,67,81,59,90,165,170,192,80,164,213,19,99,28,24,43,39,37,107,44,73,77,38,55,40,61,62,20,41,84,46,78,68,69,70,91,111,112,113,114,152,144,57,50,51,53,176,83,79,149,8,12,214,132,11,182,30,23,177,212,178,179,180,183,186,210,215,217,222,216,218,219,220,225,226) AND queued=0 ORDER BY p.post_date DESC LIMIT 0,5 | 0.000 |
My my.cnf is below
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# General #
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
low_priority_updates = 1
skip-external-locking
read_buffer_size = 1M
thread_concurrency = 2
# MyISAM $
key-buffer-size = 128M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
sql-mode =
sysdate-is-now = 1
# DATA STORAGE #
datadir = /var/lib/mysql
# BINARY LOGGING #
gtid-domain-id = 1
server_id = 1
log-basename = db-1
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 3
max_binlog_size = 100M
binlog-format = mixed
sync-binlog = 1
# REPLICATION #
#read-only = 1
#skip-slave-start = 1
#relay-log = /var/lib/mysql/relay-bin
#slave-net-timeout = 60
#sync-master-info = 1
#sync-relay-log = 1
#sync-relay-log-info = 1
# CACHES AND LIMITS #
tmp-table-size = 384M
max-heap-table-size = 256M
query-cache-type = 0
query-cache-size = 32M
max-connections = 50
thread-cache-size = 50
#open-files-limit = 32767
table-definition-cache = 2048
table-open-cache = 2048
join_buffer_size = 512K
sort_buffer_size = 1M
# INNODB #
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb-flush-method = O_DIRECT
innodb_log_buffer_size = 8M
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 256M
innodb_additional_mem_pool_size = 20M
innodb_lock_wait_timeout = 50
innodb_io_capacity = 200
innodb_read_io_threads = 32
innodb_write_io_threads = 32
# LOGGING #
log-error = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
# SSL #
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Other data that may be pertinent Current max_heap_table_size = 384 M Current tmp_table_size = 384 M Of 96487 temp tables, 46% were created on disk You have had 72268 queries where a join could not use an index properly You have 452139 out of 1328825 that take longer than 10.000000 sec. to complete Avg. qps = 23
cat /proc/sys/vm/swappiness
10
As a side note, when the lock ups occur (like right now (about 10 minutes now)) the disk IO is 100% from mysqld
from iotop
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
19070 be/4 mysql 1398.88 K/s 47.15 K/s 0.00 % 99.99 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --~log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306
19085 be/4 mysql 1587.49 K/s 70.73 K/s 0.00 % 99.15 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --~log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306
19053 be/4 mysql 974.50 K/s 180.75 K/s 0.00 % 99.02 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --~log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306
19081 be/4 mysql 1336.01 K/s 133.60 K/s 0.00 % 98.83 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --~log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306
We have an average of 30 users connected, but can reach 90 during events and prime-time.
As of last night, i converted members and posts to InnoDB and sessions to memory. We're still locking up completely. In my research IPB seems to suggest MyISAM is the better option, and it has been until now unless something screwy was done to the my.cnf by a rogue admin.
I've even considered reducing the tmp-file size, adding additional memory and creating a ramdisk, but honestly, why is the problem here now and never before?
Aucun commentaire:
Enregistrer un commentaire