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?
——-UPDATE——-
added some SSD space (8G), mounted it and set the tmpdir to it. it appears to be helping, but it's still 100% IO. — the queries (which all look the same) caused writes to wait for a table lock which inevitably caused the forums to stop responding as the "waits" all built up and executed consecutively.
——-UPDATE——-
converted members, posts, topics, and shoutbox to innodb; the queries are still there but i havent seen a waiting for lock yet.
——-UPDATE——-
converted back to MyISAM after running profiling.
MariaDB [syndicate_ipb]> show profiles;
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 64.17895398 | 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,58,29,155,67,27,81,59,90,165,170,192,80,164,213,19,99,28,24,43,148,49,139,187,167,87,133,135,137,156,40,61, |
| 2 | 0.02750801 | set profileing=0 |
| 3 | 0.05086369 | 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,58,29,155,67,27,81,59,90,165,170,192,80,164,213,19,99,28,24,43,148,49,139,187,167,87,133,135,137,156,40,61, |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The first query is while posts and topics are innodb; this fixed our table lock issue. the 3rd query is with them back to myisam. we still have the table lock issue when we have mass users online during peak hours, but obviously there's a huge performance difference between myisam and innodb on these tables. Presumably "chunking" is needed but beyond my skills.
——————-update—————–
basically, i get anywhere from 1 to 10 of these at the same time. my disk IO maxes out and nothing works anymore until i restart mysql.
MariaDB [syndicate_ipb]> explain 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;
+------+-------------+-------+-------+---------------------------------------------------+---------------+---------+---------------------------+-------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------------------------------------------+---------------+---------+---------------------------+-------+--------------------------------------------------------+
| 1 | SIMPLE | t | range | PRIMARY,last_post,forum_id,last_x_topics,approved | last_x_topics | 3 | NULL | 43187 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | topic_id,queued | topic_id | 5 | syndicate_ipb.t.tid,const | 13 | |
+------+-------------+-------+-------+---------------------------------------------------+---------------+---------+---------------------------+-------+--------------------------------------------------------+
2 rows in set (0.11 sec)
alone, the query is pretty fast, when 4 of them are going together, they can each take 2minutes to complete. then as the site is used more and more of these queries appear. The infuriating thing is that these all look like the same query to me!?
| 2186 | webserv | services-1.joinsg.net:52852 | syndicate_ipb | Query | 55 | 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 |
| 2188 | webserv | services-1.joinsg.net:52856 | syndicate_ipb | Query | 53 | 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 |
| 2189 | webserv | services-1.joinsg.net:52858 | syndicate_ipb | Query | 53 | 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 |
| 2190 | webserv | services-1.joinsg.net:52860 | syndicate_ipb | Query | 53 | 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 |
| 2194 | webserv | services-1.joinsg.net:52868 | syndicate_ipb | Query | 51 | 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 |
| 2198 | webserv | services-1.joinsg.net:52876 | syndicate_ipb | Query | 49 | 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 |
| 2199 | webserv | services-1.joinsg.net:52878 | syndicate_ipb | Query | 49 | 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 |
| 2200 | webserv | services-1.joinsg.net:52882 | syndicate_ipb | Query | 48 | 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 |
| 2201 | webserv | services-1.joinsg.net:52885 | syndicate_ipb | Query | 48 | 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 |
| 2213 | webserv | services-1.joinsg.net:52909 | syndicate_ipb | Query | 35 | 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 |
| 2215 | webserv | services-1.joinsg.net:52913 | syndicate_ipb | Query | 34 | 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 |
Best Answer
A quick look shows 2 main problems:
Many queries on the stage
Waiting for table level lockindicates a concurrency problem due to the usage of a table-level-only lock engine, such as MyISAM. Moving, as you did, to InnoDB will mostly solve the problem, specially for the queries you are doing (counters):Before, only one topic could be updated at the same time. With Innodb, different topics can be updated at the same time. However, you can still suffer from locks due to updates to the same row (same tid), and they will not be show as
Waiting for table level lockbut as stalls in the stateUpdating.You still have probably a high number of temporary tables, and setting your config to:
may have the opposite effect, making the queries slower, not faster, if the temporary tables are larger than that. You query:
is not that complex to require a temporary table -I may be wrong, that is why cannot be said reliably without looking at your table structure and indexes- optimizing its execution by adding the appropriate indexes may solve your main problems. In order to do that, a full check of all your queries and data structure should be performed.