-----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 helps. > > 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/en/order-by-optimization.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----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
