Mysql
Invision Power Board 3.4.6 - 查詢期間的 mysql 鎖定和掛起的更新
大約 2 週前，我們開始在我們的論壇上遇到隨機鎖定和減速。我們已經執行這個網站至少 6 年了，沒有任何問題。該站點和數據庫位於專用的虛擬機上。DB vm 有 1 個 vCPU 和 1GB 記憶體。我從來沒有見過盒子上的記憶體使用率超過 60%。硬碟目前是 sata （因為它已經有一段時間了）。我在程序列表中看到的內容如下。
| 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.cnf 在下面
# # 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其他可能相關的數據 目前 max_heap_table_size = 384 M 目前 tmp_table_size = 384 M 在 96487 個臨時表中，46% 是在磁碟上創建的 您有 72268 個查詢，其中連接無法正確使用索引 您有 1328825 個查詢中的 452139 個需要更長時間超過 10.000000 秒。完成平均 qps = 23
cat /proc/sys/vm/swappiness 10附帶說明一下，當鎖定發生時（比如現在（現在大約 10 分鐘）），磁碟 IO 是 100% 來自 mysqld
來自 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我們平均有 30 個使用者連接，但在活動和黃金時段可以達到 90 個。
截至昨晚，我將成員和文章轉換為 InnoDB，並將會話轉換為記憶體。我們仍然完全鎖定。在我的研究中，IPB 似乎建議 MyISAM 是更好的選擇，直到現在，除非流氓管理員對 my.cnf 做了什麼奇怪的事情。
我什至考慮過減小 tmp 文件的大小，添加額外的記憶體並創建一個 ramdisk，但老實說，為什麼現在和以前從未出現過問題？
——-更新——-添加了一些SSD空間（8G），安裝它並將tmpdir設置為它。它似乎有幫助，但它仍然是 100% IO。- 查詢（看起來都一樣）導致寫入等待表鎖定，這不可避免地導致論壇停止響應，因為“等待”全部建立並連續執行。
——-UPDATE——- 將成員、文章、主題和留言框轉換為innodb；查詢仍然存在，但我還沒有看到等待鎖定。
——-UPDATE——- 執行分析後轉換回 MyISAM。
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, | +----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+第一個查詢是文章和主題是innodb；這解決了我們的表鎖定問題。第三個查詢是與他們一起回到 myisam。當我們在高峰時段有大量使用者線上時，我們仍然會遇到表鎖定問題，但顯然 myisam 和 innodb 在這些表上存在巨大的性能差異。大概需要“分塊”，但超出了我的技能。
——————-更新—————– 基本上，我從 1 到 10 個同時。在我重新啟動 mysql 之前，我的磁碟 IO 已達到最大值，並且沒有任何效果。
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)一個人，查詢速度很快，4個人一起去的時候，每人2分鐘就可以完成。然後隨著站點的使用越來越多，這些查詢出現了。令人氣憤的是，這些對我來說都像是同一個問題！？
| 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 |
快速瀏覽會發現兩個主要問題：
Waiting for table level lock由於使用了僅表級別的鎖引擎，例如 MyISAM，舞台上的許多查詢表明存在並發問題。正如您所做的那樣，移動到 InnoDB 將主要解決問題，特別是對於您正在執行的查詢（計數器）：UPDATE topics SET views=views+2 WHERE tid=64896以前，只能同時更新一個主題。使用 Innodb，可以同時更新不同的主題。但是，由於對同一行（相同的 tid）的更新，您仍然可能遭受鎖定，並且它們不會顯示為
Waiting for table level lock狀態，而是顯示為停頓Updating。
- 您可能仍然有大量臨時表，並將您的配置設置為：
tmp-table-size = 384M max-heap-table-size = 256M如果臨時表比這個大，可能會產生相反的效果，使查詢更慢，而不是更快。您查詢：
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需要一個臨時表並沒有那麼複雜 - 我可能是錯的，這就是為什麼不看你的表結構和索引就不能可靠地說 - 通過添加適當的索引來優化它的執行可能會解決你的主要問題。為此，應該對所有查詢和資料結構進行全面檢查。