FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » General » Database discussions » Query taking 2 minutes (Board grinds to halt for that period of time)
Show: Today's Messages :: Unread Messages :: Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Query taking 2 minutes [message #161905] Fri, 19 February 2010 10:33 Go to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Not strictly an installation issue, but not sure where this should go.

We are experiencing a few occasions during a day where the forum grinds to a halt due to locked queries. It is down to the query below, which takes anything between 85 and 120 seconds to run.

First off, when is it run, and is it something which can be disabled via a feature? Or is there an alternative way we can solve it or get around it?

It brings back around 15,000 records from the thread table.

Thanks
P_G_

SELECT
m.*, COALESCE(m.flag_cc, u.flag_cc) AS flag_cc, COALESCE(m.flag_country, u.flag_country) AS flag_country,
t.thread_opt, t.root_msg_id, t.last_post_id, t.forum_id,
f.message_threshold, f.name,
u.id AS user_id, u.alias AS login, u.avatar_loc, u.email, u.posted_msg_count, u.join_date, u.location,
u.sig, u.custom_status, u.icq, u.jabber, u.affero, u.aim, u.msnm, u.yahoo, u.skype, u.google, u.last_visit AS time_sec, u.users_opt,
l.name AS level_name, l.level_opt, l.img AS level_img,
p.max_votes, p.expiry_date, p.creation_date, p.name AS poll_name, p.total_votes,
pot.id AS cant_vote,
r.last_view,
mm.id AS md,
m2.subject AS thr_subject,
COALESCE(g2.group_cache_opt, g1.group_cache_opt) AS group_cache_opt
FROM
fud26_msg m
INNER JOIN fud26_thread t ON m.thread_id=t.id
INNER JOIN fud26_msg m2 ON m2.id=t.root_msg_id
INNER JOIN fud26_forum f ON t.forum_id=f.id
INNER JOIN fud26_cat c ON f.cat_id=c.id


INNER JOIN fud26_group_cache g1 ON g1.user_id=2147483647 AND g1.resource_id=f.id LEFT JOIN fud26_group_cache g2 ON g2.user_id=6927 AND g2.resource_id=f.id
LEFT JOIN fud26_read r ON r.thread_id=t.id AND r.user_id=6927
LEFT JOIN fud26_users u ON m.poster_id=u.id
LEFT JOIN fud26_level l ON u.level_id=l.id
LEFT JOIN fud26_poll p ON m.poll_id=p.id
LEFT JOIN fud26_poll_opt_track pot ON pot.poll_id=p.id AND pot.user_id=6927
LEFT JOIN fud26_mod mm ON mm.forum_id=f.id AND mm.user_id=6927
WHERE
m.apr=1

AND f.id=1


AND m.post_stamp > 1235529738 AND (r.id IS NULL OR r.last_view < m.post_stamp)
AND (mm.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt) & 2) > 0)
ORDER BY
f.last_post_id ASC , t.last_post_date ASC , m.post_stamp ASC
LIMIT 0,50;
Re: Query taking 2 minutes [message #161906 is a reply to message #161905] Fri, 19 February 2010 14:58 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Can you please post an EXPLAIN for the statement?

PS: This query looks all too familiar. I've tried to "tune" it before, but was unable to get it any faster.
Re: Query taking 2 minutes [message #161907 is a reply to message #161906] Fri, 19 February 2010 15:09 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Certainly can, it is:

+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
| 1 | SIMPLE | c | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | l | system | PRIMARY | NULL | NULL | NULL | 0 | const row not found |
| 1 | SIMPLE | f | const | PRIMARY,fud26_forum_i_c | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | g1 | const | fud26_group_cache_i_ru,fud26_group_cache_i_u | fud26_group_cache_i_ru | 8 | const,const | 1 | |
| 1 | SIMPLE | g2 | const | fud26_group_cache_i_ru,fud26_group_cache_i_u | fud26_group_cache_i_ru | 8 | const,const | 0 | unique row not found |
| 1 | SIMPLE | t | ref | PRIMARY,fud26_thread_i_flm,fud26_thread_i_rmi | fud26_thread_i_flm | 4 | const | 15724 | |
| 1 | SIMPLE | r | eq_ref | fud26_read_i_tu,fud26_read_i_u | fud26_read_i_tu | 8 | cardiffonline.t.id,const | 1 | |
| 1 | SIMPLE | m | ref | fud26_msg_i_ta,fud26_msg_i_ps,fud26_msg_i_a | fud26_msg_i_ta | 8 | cardiffonline.t.id,const | 9 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.m.poster_id | 1 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.m.poll_id | 1 | |
| 1 | SIMPLE | pot | eq_ref | fud26_poll_opt_track_i_pu | fud26_poll_opt_track_i_pu | 8 | cardiffonline.p.id,const | 1 | |
| 1 | SIMPLE | mm | const | fud26_mod_i_uf | fud26_mod_i_uf | 8 | const,const | 1 | Using where |
| 1 | SIMPLE | m2 | eq_ref | PRIMARY | PRIMARY | 4 | cardiffonline.t.root_msg_id | 1 | |
+----+-------------+-------+--------+-------------------------------------- ---------+---------------------------+---------+--------------------------- --+-------+---------------------------------+
Re: Query taking 2 minutes [message #161908 is a reply to message #161907] Fri, 19 February 2010 15:54 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
OK, the slow-down is probably caused by fetching 15724 rows from fud26_thread. MySQL uses the thread_i_flm index (which is probably not selective enough).

Looking at the query, candidate keys would be:

* id
* root_msg_id
* forum_id
* last_post_date (in ORDER BY)

Existing indexes:

* PRIMARY (id)
* thread_i_flm (forum_id, moved_to) <-- use for leading col.
* thread_i_t (thread_opt)
* thread_i_rmi (root_msg_id)
* thread_i_r (replies)

Will you be able to create some indexes to see it they will help?
Re: Query taking 2 minutes [message #161910 is a reply to message #161908] Sat, 20 February 2010 07:54 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
I am able to create indexes in the database, and even modify the code if need be (maybe to force use of an index?), so if you do have any suggestions as to what to add or alter, it would be very much appreciated.

Thanks

[Updated on: Sat, 20 February 2010 07:54]

Report message to a moderator

Re: Query taking 2 minutes [message #161920 is a reply to message #161910] Sat, 20 February 2010 19:08 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
One would have to play with various combinations (trial and error). Some indexes that may be worth trying:

(forum_id, root_msg_id)
(forum_id, id)
(id, forum_id, root_msg_id)
(last_post_date)
(last_post_date, forum_id)
Etc.
Re: Query taking 2 minutes [message #161936 is a reply to message #161920] Mon, 22 February 2010 05:57 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Okay, after having a play around with a few things, I have the query down to between 10 and 18 seconds (from between 90 and 120), ending up with doing the following:

- ALTER TABLE fud26_thread ADD INDEX fud26_forum_thread (forum_id, id), and;
- adding "FORCE INDEX (fud26_forum_thread)" AFTER "INNER JOIN fud26_thread t" on line 815 of fudforum/theme/default/selmsg.php.

It's obviously a lot better, but still not perfect, so hopefully it is something you can improve on further?

I also assume that this is something called upon while in the admin panel only. What task actually runs this statement, and are we perhaps able to avoid it at all?

Cheers
P_G_
Re: Query taking 2 minutes [message #161940 is a reply to message #161936] Mon, 22 February 2010 11:06 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
P_G_ wrote:
Okay, after having a play around with a few things, I have the query down to between 10 and 18 seconds (from between 90 and 120), ending up with doing the following:


Good progress. I just wonder why MySQL cannot pick the right indexes by itself.

P_G_ wrote:
I also assume that this is something called upon while in the admin panel only. What task actually runs this statement, and are we perhaps able to avoid it at all?


Looks like the Message Navigator. However, I've never waited that long for a FUDforum screen to return, so I might be a background caching operation.
Re: Query taking 2 minutes [message #161941 is a reply to message #161940] Mon, 22 February 2010 12:18 Go to previous messageGo to next message
P_G_ is currently offline  P_G_
Messages: 9
Registered: February 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Thanks again for replying, but I'm not quite sure what you are suggesting, or if you are suggesting anything, to be honest.

You know your software better than me Razz, and I'm afraid I can't find where a 'Message Navigator' is, or how it is run. Trying to run the URL of the page I altered the SQL on doesn't work, so clearly it has a parent file it runs within.

I only noticed the query initially from the 'show processlist' command, and indeed subsequently tested these additional indexes from the MySQL command prompt.

I suppose ideally it's whether you can optimise further, or, if not, whether the command be altered or removed, either via your coding or an option in the admin panel?

Thanks

[Updated on: Mon, 22 February 2010 12:19]

Report message to a moderator

Re: Query taking 2 minutes [message #161949 is a reply to message #161941] Tue, 23 February 2010 14:19 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
I'm not making any suggestions. Unfortunately I don't know enough about MySQL to know what the best solution would be. I was actually hoping someone would jump in to help us.

PS: The Message Navigator for this forum is at http://fudforum.org/forum/index.php?t=mnav&
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Re: Query taking 2 minutes [message #163691 is a reply to message #163689] Wed, 24 November 2010 07:47 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Good heavens!
It's impossible to see what changes you've made.
Also, working on code from 2007 is not making it any easier.
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Message by Ernesto is ignored  [reveal message]  [reveal all messages by Ernesto]  [stop ignoring this user] Go to previous messageGo to next message
Re: Query taking 2 minutes [message #165791 is a reply to message #161906] Thu, 11 August 2011 10:23 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
naudefj wrote on Fri, 19 February 2010 14:58
PS: This query looks all too familiar. I've tried to "tune" it before, but was unable to get it any faster.


I've posted another thread about this.

http://fudforum.org/forum/index.php?t=msg&th=120085&unread=1&

This query is part of the "Unanswered Messages" features. I don't think you'll be able to optimize this query any further. To make matters worse, no matter how aggressively you tune MySQL's buffers, it will more than likely create a rather large tmp table on disk.
Re: Query taking 2 minutes [message #166254 is a reply to message #165791] Mon, 17 October 2011 06:03 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3757
Registered: December 2004
Karma: 27
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Unanswered messages was optimized as per:
http://sourceforge.net/apps/trac/fudforum/changeset/5395
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Cannot connect to database
Next Topic: Has anyone tried to use this w/SQL Server 2000
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 20 00:37:57 EDT 2021

Total time taken to generate the page: 0.00925 seconds