เว็บถูกปิดเพราะมีการใช้ MySQL Query สูงมากจนค้างไป 2 รอบ

[takato]

เจ้าของบอกมาแบบนี้ครับ

"แจ้งระงับบริการเว็บ 64blog.com ชั่วคราวเรื่องจากเว็บนองท่านมีการใช้ MySQL Query สูงมากจนค้างไป 2 รอบทำให้ Server เรา Service Down ไปครับ จึงต้องปิดไว้ก่อนครับ"

mysql> show processlist;
+---------+--------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id      | User         | Host      | db         | Command | Time | State          | Info                                                                                                 |
+---------+--------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 8881760 | blogcom_blog | localhost | blogcom_wp | Query   |  264 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881762 | blogcom_blog | localhost | blogcom_wp | Query   |  294 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881764 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881765 | blogcom_blog | localhost | blogcom_wp | Query   |  269 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881767 | blogcom_blog | localhost | blogcom_wp | Query   |  269 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881768 | blogcom_blog | localhost | blogcom_wp | Query   |  271 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881770 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881771 | blogcom_blog | localhost | blogcom_wp | Query   |  271 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881772 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881773 | blogcom_blog | localhost | blogcom_wp | Query   |  266 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881774 | blogcom_blog | localhost | blogcom_wp | Query   |  266 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881775 | blogcom_blog | localhost | blogcom_wp | Query   |  285 | Locked         | SELECT COUNT(ID) FROM wp_80_posts WHERE post_status = 'publish' and post_type = 'post'               |
| 8881776 | blogcom_blog | localhost | blogcom_wp | Query   |  295 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881777 | blogcom_blog | localhost | blogcom_wp | Query   |  269 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881778 | blogcom_blog | localhost | blogcom_wp | Query   |  294 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881779 | blogcom_blog | localhost | blogcom_wp | Query   |  288 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881780 | blogcom_blog | localhost | blogcom_wp | Query   |  280 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881781 | blogcom_blog | localhost | blogcom_wp | Query   |  263 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881782 | blogcom_blog | localhost | blogcom_wp | Query   |  269 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881783 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881784 | blogcom_blog | localhost | blogcom_wp | Query   |  337 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881785 | blogcom_blog | localhost | blogcom_wp | Query   |  270 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881786 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | UPDATE `wp_80_posts` SET `post_modified` = '2011-07-13 07:00:35', `post_modified_gmt` = '2011-07-13  |
| 8881801 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT SQL_CALC_FOUND_ROWS  wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_ |
| 8881824 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881827 | blogcom_blog | localhost | blogcom_wp | Query   |  341 | Locked         | SELECT ID FROM wp_80_posts WHERE post_name LIKE 'anime-summer-2011-updated%' AND YEAR(post_date) = 2 |
| 8881830 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881831 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881832 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881834 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881836 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881898 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881899 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881900 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881904 | blogcom_blog | localhost | blogcom_wp | Query   |  267 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8881905 | blogcom_blog | localhost | blogcom_wp | Query   |  375 | Sorting result | SELECT post_modified_gmt FROM wp_80_posts WHERE post_status = 'publish' AND post_type IN ('post', 'p |
| 8881907 | blogcom_blog | localhost | blogcom_wp | Query   |  268 | Locked         | SELECT   wp_80_posts.* FROM wp_80_posts   JOIN wp_80_icl_translations t ON wp_80_posts.ID = t.elemen |
| 8882091 | blogcom_blog | localhost | blogcom_wp | Query   |  380 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882097 | blogcom_blog | localhost | blogcom_wp | Query   |  383 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882100 | blogcom_blog | localhost | blogcom_wp | Query   |  383 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882106 | blogcom_blog | localhost | blogcom_wp | Query   |  383 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882108 | blogcom_blog | localhost | blogcom_wp | Query   |  383 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882110 | blogcom_blog | localhost | blogcom_wp | Query   |  382 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882115 | blogcom_blog | localhost | blogcom_wp | Query   |  376 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882114 | blogcom_blog | localhost | blogcom_wp | Query   |  368 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882117 | blogcom_blog | localhost | blogcom_wp | Query   |  362 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882118 | blogcom_blog | localhost | blogcom_wp | Query   |  355 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882119 | blogcom_blog | localhost | blogcom_wp | Query   |  381 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882120 | blogcom_blog | localhost | blogcom_wp | Query   |  378 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882121 | blogcom_blog | localhost | blogcom_wp | Query   |  378 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882122 | blogcom_blog | localhost | blogcom_wp | Query   |  377 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882123 | blogcom_blog | localhost | blogcom_wp | Query   |  379 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882125 | blogcom_blog | localhost | blogcom_wp | Query   |  380 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882126 | blogcom_blog | localhost | blogcom_wp | Query   |  380 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882127 | blogcom_blog | localhost | blogcom_wp | Query   |  378 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882128 | blogcom_blog | localhost | blogcom_wp | Query   |  377 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882130 | blogcom_blog | localhost | blogcom_wp | Query   |  376 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882129 | blogcom_blog | localhost | blogcom_wp | Query   |  379 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882131 | blogcom_blog | localhost | blogcom_wp | Query   |  376 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882132 | blogcom_blog | localhost | blogcom_wp | Query   |  359 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882134 | blogcom_blog | localhost | blogcom_wp | Query   |  377 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882135 | blogcom_blog | localhost | blogcom_wp | Query   |  374 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882136 | blogcom_blog | localhost | blogcom_wp | Query   |  378 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882139 | blogcom_blog | localhost | blogcom_wp | Query   |  372 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882140 | blogcom_blog | localhost | blogcom_wp | Query   |  373 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882141 | blogcom_blog | localhost | blogcom_wp | Query   |  367 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882142 | blogcom_blog | localhost | blogcom_wp | Query   |  368 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882144 | blogcom_blog | localhost | blogcom_wp | Query   |  366 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882147 | blogcom_blog | localhost | blogcom_wp | Query   |  352 | Sending data   | SELECT SQL_CALC_FOUND_ROWS  wp_98_posts.* FROM wp_98_posts  WHERE 1=1  AND YEAR(wp_98_posts.post_dat |
| 8882777 | da_admin     | localhost | NULL       | Query   |    0 | NULL           | show processlist

ผมจะป้องกันและแก้ไขอย่างไรดีครับ

[ayeweb]

เดี๋ยวนี้ทำเว็บ ขาดไม่ได้ครับ ระบบแคช ผมแทบได้รื้อระบบเพื่อให้ไม่ไปยุ่งกะsqlมาก แต่ก็กินพื้นที่อีก 

[vii]

wordpress แค่เว็บเดียวก็แทบอ้วกแล้ว อืดเป็นเต่าเลย ไม่รู้จะกินทรัพยากรอะไรมากมาย

ทางออก
1ใช้ cache
2เขียนสคริปเอง เน้นทำงานตรงตามจุดประสงค์ เบา โหลดเร็ว
3ย้ายไปใช้ vps, หรืออะไรที่ดีกว่านี้

[tdelphi]

table นั้น ถูกกระหน่าเขียน จนมัน lock (wp_80_posts)

ลองเปลี่ยน type เป็น innodb ดูครับ

[CommonSun]

ดูจำนวน Record ว่ามีจำนวนมากไปหรือเปล่า หากมากเกินไป ให้ลบๆ ออกบ้าง ตารางชื่อ wp_80_posts แล้วเพิ่ม index ที่ฟิลล์ post_modified ด้วย

จากนันให้หมั่น ซ่อมแซมตาราง หาไม่มีเวลาก็เขียน Script ให้มันซ่อมตารางทุกวันตอน ตีห้าหรือ 6 โมงเช้าก็ได้