1

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 |
0

A quick look shows 2 main problems:

  • Many queries on the stage Waiting for table level lock indicates 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):

    UPDATE topics SET views=views+2 WHERE tid=64896
    

    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 lock but as stalls in the state Updating.

  • You still have probably a high number of temporary tables, and setting your config to:

    tmp-table-size                  =       384M
    max-heap-table-size             =       256M
    

    may have the opposite effect, making the queries slower, not faster, if the temporary tables are larger than that. You query:

    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 (lots, of, in, values) AND 
          queued=0 
    ORDER BY p.post_date DESC 
    LIMIT 0,5
    

    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.

  • I also thought the InnoDB change would help, but we still run into an issue where so many queries into tmp tables launch, we max IO and the db basically dies. Since we're using IPB i dont really have any way to optimize the queries (i lack the skillset to modify a package like this) I guess what is most surprising is that the issue only recently started.. Are you saying reduce tmp-table and heap-table or increase it? How would i go about doing this index check? – driz Feb 9 '15 at 16:44
  • That table needs a tmp because of the ORDER BY. First it does the JOIN and filters on the WHERE. This is written to a tmp table so it can be sorted (ORDER BY). Then the first 5 rows are delivered. About the only way to avoid a tmp is for a single index to cover the entire WHERE and the ORDER BY; but this is impossible since the fields are in different tables. – Rick James Feb 10 '15 at 6:08
  • I'll partially take my comment back -- If the optimizer chose to use INDEX(post_date) (does such exist??), it would scan through p in order and filter on the WHERE. This is also the only way it could stop after (approximately) 5 rows, rather than gathering everything first. – Rick James Feb 10 '15 at 6:09
0

Those settings are too high for 1GB of RAM! You may be swapping all the time, hence high I/O. Get more RAM, or change the settings down -- far down.

Moving a table into MEMORY -- bad idea if you need persistence.

Big value for tmp_table_size because of lots of on-disk tmp tables -- It would be better to figure out why the queries are so inefficient.

Lots of queries taking > 10 sec -- again, let's look at them and try to improve them. Please find a couple, provide EXPLAIN SELECT... and SHOW CREATE TABLE.

  • I initially thought the same regarding memory, but looking at the historical data of the system, memory utilization hasn't exceeded 60% which led me to think it was something else. I believe the inefficiencies are related to a lack of/poor indexing but it's IPB, i'm not qualified to modify it nor woudl i want to.. – driz Feb 11 '15 at 1:58
  • Do you have INDEX(post_date)? What does EXPLAIN SELECT ... say? And SHOW CREATE TABLE for each of the tables in the query? – Rick James Feb 11 '15 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.