Hi,

I'm using phorum [1] and made some custom queries against their
database. My query looks like this:

SELECT
message_id, subject, datestamp, forum_id, thread
FROM
phorum_messages
WHERE
forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND
thread != 0 AND status = 2 AND closed = 0
ORDER BY datestamp DESC LIMIT 3

The table phorum_message is about 500MB in size. The problem is that
such a query often starts to "hang" in the "Sorting result" phase. This
can take up to minutes and during this time problems really start: more
and more such queries are coming in, each of them "hanging" for the same
reason too and after a few minutes the maximum of connections are
reached (currently 170) and everything is dead. Only killing the queries
manually s.

My guess is that the filesort is problematic and so I tried to avoid it
with the following things.

When I use explain on the query I get back the following:

id: 1
select_type: SIMPLE
table: phorum_messages
type: range
possible_keys: thread_message, thread_forum, status_forum,

list_page_float, list_page_flat, dup_check,
last_post_time, forum_max_message, post_count
key: post_count
key_len: 9
ref: NULL
rows: 1311
Extra: Using where; Using filesort

When I remove the ORDER BY statements, the query is *not* using
filesort. However, as you can guess, it is necessary. The goal of the
query is to get the top-most posters in the selected forums.

The MySQL documentation [2] says that under certain cases it should be
possible to create appropriate keys so that even an ORDER BY can take
advantage of, but I was unable to come up with such an.

Is there a recommendation how to go for it?

thanks,
- Markus

[1] http://www.phorum.org/
[2] http://dev.mysql.com/doc/refman/5.0/...imization.html

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

I've learned a bit about the environment this server is running in. It's
VMware with root NFS and storage NFS mount points for MySQL. I've been
told the throughput over NFS for my Server is from 20 to 30 MB/s.

The server has 3GB ram. I'm not sure about it's CPU performance, but the
information I've is that when connections start hanging and the effect
basically multiplies, the CPU load goes aup.

Does this sound like a possible bottleneck?

thanks,
- - Markus

Markus Fischer wrote:
> Hi,
>
> I'm using phorum [1] and made some custom queries against their
> database. My query looks like this:
>
> SELECT
> message_id, subject, datestamp, forum_id, thread
> FROM
> phorum_messages
> WHERE
> forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND
> thread != 0 AND status = 2 AND closed = 0
> ORDER BY datestamp DESC LIMIT 3
>
> The table phorum_message is about 500MB in size. The problem is that
> such a query often starts to "hang" in the "Sorting result" phase. This
> can take up to minutes and during this time problems really start: more
> and more such queries are coming in, each of them "hanging" for the same
> reason too and after a few minutes the maximum of connections are
> reached (currently 170) and everything is dead. Only killing the queries
> manually s.
>
> My guess is that the filesort is problematic and so I tried to avoid it
> with the following things.
>
> When I use explain on the query I get back the following:
>
> id: 1
> select_type: SIMPLE
> table: phorum_messages
> type: range
> possible_keys: thread_message, thread_forum, status_forum,
>
> list_page_float, list_page_flat, dup_check,
> last_post_time, forum_max_message, post_count
> key: post_count
> key_len: 9
> ref: NULL
> rows: 1311
> Extra: Using where; Using filesort
>
> When I remove the ORDER BY statements, the query is *not* using
> filesort. However, as you can guess, it is necessary. The goal of the
> query is to get the top-most posters in the selected forums.
>
> The MySQL documentation [2] says that under certain cases it should be
> possible to create appropriate keys so that even an ORDER BY can take
> advantage of, but I was unable to come up with such an.
>
> Is there a recommendation how to go for it?
>
> thanks,
> - Markus
>
> [1] http://www.phorum.org/
> [2] http://dev.mysql.com/doc/refman/5.0/...imization.html
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHfCl+1nS0RcInK9ARAqEaAJ9JsofQIzoVBfCJQRKE/8X6wW1/SwCg0+en
0HDQBTAB4U87Nuua/h4pDiU=
=utDe
-----END PGP SIGNATURE-----