Hello,

This morning my mt.cgi scripts kept timing out and leading to 500 Internal Server Errors. After e-mailing Dreamhost demanding the awesome speeds and service I'm used to, they replied saying that my MT MySQL database was killing their servers. They sent a nice, comprehensive note that I am pasting after this -- please excuse the length. Any ideas what the deal is or what I can do. Yes, I have tons of thousands of comments across 3 blogs. Help!

I'm one of the mysql admins with DreamHost, I was investigating a high
load on the "puss" mysql server and noticed that a lot of the load is
being caused by your database "speakup". For example here is a query
that is taking 3244 seconds to run and examines 121,000 rows of data:

# User@Host: speakup[speakup] @ carl.dreamhost.com
# Query_time: 3244 Lock_time: 0 Rows_sent: 0 Rows_examined: 121053
use speakup;
create temporary table temp_Table
select entry_id as temp_Table_id, entry_allow_comments as
temp_Table_allow_comments, entry_allow_pings as temp_Table_allow_pings,
entry_atom_id as temp_Table_atom_id, entry_author_id as
temp_Table_author_id, entry_basename as temp_Table_basename,
entry_blog_id as temp_Table_blog_id, entry_category_id as
temp_Table_category_id, entry_convert_breaks as
temp_Table_convert_breaks, entry_excerpt as temp_Table_excerpt,
entry_keywords as temp_Table_keywords, entry_pinged_urls as
temp_Table_pinged_urls, entry_status as temp_Table_status,
entry_tangent_cache as temp_Table_tangent_cache, entry_text as
temp_Table_text, entry_text_more as temp_Table_text_more, entry_title as
temp_Table_title, entry_to_ping_urls as temp_Table_to_ping_urls,
entry_week_number as temp_Table_week_number, entry_created_on as
temp_Table_created_on, entry_created_by as temp_Table_created_by,
entry_modified_on as temp_Table_modified_on, entry_modified_by as
temp_Table_modified_by, comment_created_on as temp_sort_key from
mt_entry, mt_comment where (comment_visible = '1') and (comment_blog_id =
'1') and (entry_id = comment_entry_id) order by comment_created_on desc;

I've renamed the mt_comment table to mt_comment_disabled_by_DreamHost,
first because this query is causing a 100 load on the mysql server (when
normal load is 1 to 3) and causing load for other customers, and second
this needs to be corrected as I believe even on a dedicated server it
would be incredibly inefficient. Please check in MT to see if there is
any way to disable this query, it appears to be gathering together all
comments for blog 1, that are visible. You can write me about this query
above at xxxx@dreamhost.com. Here is how many were trying to be ran at
once below. This could definitely make your site slow and the queries
taking 1000's of seconds could make your sites not load at all.

mysql> show processlist;
+--------+---------+------------------------------------+---------+------
---+------+--------------+-----------------------------------------------
-------------------------------------------------------+
| Id | User | Host | db |
Command | Time | State | Info
|
+--------+---------+------------------------------------+---------+------
---+------+--------------+-----------------------------------------------
-------------------------------------------------------+
| 509408 | speakup | carl.dreamhost.com:57420 | speakup | Query
| 1714 | Locked | insert into mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_email,
commen |
| 510083 | speakup | carl.dreamhost.com:44127 | speakup | Query
| 2166 | Sending data | create temporary table temp_Table
select entry_id as temp_Table_id, entry_allow_comments as temp_Tab |
| 510101 | speakup | carl.dreamhost.com:47993 | speakup | Query
| 2163 | Sending data | create temporary table temp_Table
select entry_id as temp_Table_id, entry_allow_comments as temp_Tab |
| 510538 | speakup | carl.dreamhost.com:55569 | speakup | Query
| 1714 | Locked | insert into mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_email,
commen |
| 510546 | speakup | carl.dreamhost.com:44212 | speakup | Query
| 1708 | Locked | insert into mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_email,
commen |
| 510866 | speakup | carl.dreamhost.com:45644 | speakup | Query
| 1558 | Locked | select comment_id, comment_author,
comment_blog_id, comment_commenter_id, comment_email, comment_ent |
| 510892 | speakup | carl.dreamhost.com:41491 | speakup | Query
| 1505 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:47:30') and (comment_ip = |
| 511153 | speakup | carl.dreamhost.com:60453 | speakup | Query
| 1307 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:50:47') and (comment_ip = |
| 511187 | speakup | carl.dreamhost.com:57162 | speakup | Query
| 1286 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:51:09') and (comment_ip = |
| 511190 | speakup | carl.dreamhost.com:58064 | speakup | Query
| 1275 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:51:19') and (comment_ip = |
| 511201 | speakup | carl.dreamhost.com:56196 | speakup | Query
| 1236 | Locked | select count(*)
from mt_comment
where (comment_visible = '1') and (comment_entry_id = '2835') |
| 511277 | speakup | carl.dreamhost.com:59899 | speakup | Query
| 1211 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:52:24') and (comment_ip = |
| 511401 | speakup | carl.dreamhost.com:33956 | speakup | Query
| 1128 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:53:46') and (comment_ip = |
| 511425 | speakup | carl.dreamhost.com:37477 | speakup | Query
| 1119 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:53:56') and (comment_ip = |
| 511436 | speakup | carl.dreamhost.com:54751 | speakup | Query
| 1107 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:54:07') and (comment_ip = |
| 511508 | speakup | carl.dreamhost.com:43181 | speakup | Query
| 1038 | Locked | select count(*)
from mt_comment
where (comment_visible = '1') and (comment_entry_id = '3562') |
| 511640 | speakup | carl.dreamhost.com:46197 | speakup | Query
| 954 | Locked | select count(*)
from mt_comment
where (comment_visible = '1') and (comment_entry_id = '3562') |
| 511801 | speakup | carl.dreamhost.com:59868 | speakup | Query
| 861 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 11:58:13') and (comment_ip = |
| 512127 | speakup | carl.dreamhost.com:56842 | speakup | Query
| 615 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:02:19') and (comment_ip = |
| 512176 | speakup | carl.dreamhost.com:58292 | speakup | Query
| 578 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:02:56') and (comment_ip = |
| 512191 | speakup | carl.dreamhost.com:52400 | speakup | Query
| 569 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:03:04') and (comment_ip = |
| 512205 | speakup | carl.dreamhost.com:56872 | speakup | Query
| 559 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:03:15') and (comment_ip = |
| 512280 | speakup | carl.dreamhost.com:59848 | speakup | Query
| 497 | Locked | select count(*)
from mt_comment
where (comment_visible = '1') and (comment_entry_id = '3562') |
| 512522 | speakup | carl.dreamhost.com:55910 | speakup | Query
| 314 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:07:16') and (comment_ip = |
| 512823 | speakup | carl.dreamhost.com:45221 | speakup | Query
| 50 | Locked | select count(*)
from mt_comment
where (comment_created_on > '2007-06-25 12:11:40') and (comment_ip = |
+--------+---------+------------------------------------+---------+------
---+------+--------------+-----------------------------------------------
-------------------------------------------------------+
26 rows in set (0.06 sec)

More information from my host. Any ideas where this query is coming from? I'm not convinced this is spam.

I've checked out the query, it seems to just be trying to gather all
comments for one specific post. Do you have a large amount of spam in
any of the posts? This is a pretty big problem with MT, and many other
software packages. If there are, for example, 10,000 spam comments in a
table then the query can get quite large and cause issues. You have a
LOT of comments in the mt_comments table:

mysql> select count(*) from mt_comment;
+----------+
| count(*) |
+----------+
| 93182 |
+----------+
1 row in set (0.00 sec)

I've tried all I can to make the query more efficient, but even at its
most efficient it sitll needs to examine 37,000 rows of data for that
blog #1. Possibly there is a feature or plugin or similar that is trying
to collect comments for a specific purpose? Unfortunately there isn't
much we can do to debug this issue beyond the indexing. You can check if
MT has an update and do that, or check with their support what the
problem is with that query and how to optimize. It looks like you
re-enabled the table, and it seems OK on the database now, but if those
queries get ran again often enough it will cause problems again.