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 |
Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
3
  • 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? Commented Feb 9, 2015 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. Commented Feb 10, 2015 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. Commented Feb 10, 2015 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.

Improve this answer
2
  • 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.. Commented Feb 11, 2015 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? Commented Feb 11, 2015 at 18:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.