1

Topic: Peculiar Query

Our forums have recently been taking a performance hit. This may be completely unrelated to PunBB, but in any case, I went digging. I soon discovered that an extraordinary number of temporary tables were being created. Not only that, but they were nearly all disk based (even with an ample amount of memory allocated to MySQL's temporary tables). I finally found the culprit to be this query within viewtopic.php:

'SELECT DISTINCT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.idle=0) WHERE p.topic_id='.$id.' ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts']

Even on my tiny test bed forum, every execution of this creates a new disk-based temporary table. Messing with indexes seems to do nothing, but removing the DISTINCT fixes things up. Looking at the query I can't really figure out why the DISTINCT is even needed. Making the query, um, non-distince doesn't seem to change the results either.

I'm still wondering why I hadn't noticed this before/why performance hadn't dragged before. It makes me think our problems aren't necessarily related to this, but creating a temporary table for every viewtopic.php seems bad. Unless, of course I've screwed up my files or databases, but I'm fairly sure my test bed is running a stock configuration.

Hard work may not kill you, but why take chances?

Re: Peculiar Query

maybe since you run a MUCH bigger and more active site it(performance hit due to the "junk") showed up and was more apparent? just a thought.

~James
FluxBB - Less is more

Re: Peculiar Query

I will look into it when I get back from work. Thanks for the debugging smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

4

Re: Peculiar Query

Well, our forums have been running a day without the DISTINCT and it's been all peachy. No complaints of screwed up topics, and the server is much happier.

But semi-related, the extern.php is full of evil queries as well. A few months ago I tried to use it to show topics on our front page, but it immediately made MySQL go nuts, and slowed the load time for the front page way down. I soon discovered that the main query extern.php was calling was taking around 2 seconds to execute every time. I've finally got around to debugging it, and I think I have a solution. It's rather strange, but it seems to work. The culprit it seems is sorting the results based on the last_post or posted columns. I tried combined indexes, and all that jazz, but those only seemed to marginally increase speed. Strangely enough, it seems like using only a newly created index on the column your sorting by (ie last_post) returns near instant results. The main caveat being that MySQL doesn't want to use that index by default, so you have to force it. All very strange...

SELECT t.id, t.subject FROM topics AS t FORCE INDEX(topics_last_post_idx) INNER JOIN forums AS f ON f.id=t.forum_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL AND f.id IN(4,2,5,29,10,15,16) ORDER BY t.last_post DESC LIMIT 10;

The FORCE INDEX and the associated index being the only part I actually added. That consitently returns results within 0.00-0.01 seconds, compared to the 1-3 seconds previously. It seems like combined indexes would help with at least t.moved_to and t.last_post, but those seemed to have performance problems as well. I think. This all has me slightly confuzzled at this point, as I've been staring at this for too long.

I'm also wondering if you even need a to join the forums table onto this query, since you could easily join the forum_perms table using only t.forum_id. Switching doesn't seem to effect performance at all. Although, actually now that I think about it, since the last_post index is now being used on the topics table, it's probably wiser to join it as a separate table, so you can utilize its own index when restricting forums.

Another oddity was that the INNER JOIN of the forums table seemed to be creating the temporary tables. Switching it to a LEFT JOIN no longer generated temporary tables, but I think it ended up overall slower. Humph. I quit. wink

Anyway, I'll mess with this some more and clean it up later this week, if you don't beat me to it, and I'll let you know what I find out.

Hard work may not kill you, but why take chances?

5

Re: Peculiar Query

What version of MySQL you have?

6

Re: Peculiar Query

Hm, looks like 4.0.18. Which reminds me that earlier today, I was testing this on my local test server with the latest version of 4.1, and I thought I had discovered a much more elegant solution than all this forcing index crap. Although I think that might have had to do with not restricting which forums the topics belong to. Bah. My brain be fried. I'll tackle this again later this week.

Hard work may not kill you, but why take chances?

7

Re: Peculiar Query

Related to your slowdown woes. I pay the odd visit to Macaddict and noticed the posts about slow performance. The strange thing is that for me the pages were loading at pretty much the rate I would expect i.e. about the same speed as here. On the other hand, I was only looking at "forum forum".

8

Re: Peculiar Query

Yeah, it was never as bad for me as everybody else was whining about. But occasionally I would experience some slow downs and MySQL was definitely not completely happy. Since fixing the viewtopic thing, it has been snappier, and MySQL has been acting great.

However, a fair number of temporary tables are still being generated by extern.php, but since we aren't being hammered with those requests, it's certainly manageable. Although, in its current state, extern.php isn't usable on the main website, since with that many hits, it really kills the server.

Hard work may not kill you, but why take chances?

9

Re: Peculiar Query

I think you may be right. I just visited and it does seem more responsive. Viewtopic seems to be loading consistently in under a second at the moment.

BTW: The jump menu seems broken - WinXP IE6.

Re: Peculiar Query

I was planning on having a closer look at this today. I do remember adding that DISTINCT a long time ago and I doubt I would add it if it didn't do any good. I could be wrong though smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Peculiar Query

I added the DISTINCT in 1.2 and I can't for the life of me figure out why smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

12

Re: Peculiar Query

Anything to do with sqlite support?

Re: Peculiar Query

Hmm. I doubt it. I think someone reported a bug that caused me to add it, but I can't seem to find it.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Peculiar Query

Argh! I can't seem to find it. I've been able to establish that I added it sometime between 2005-11-24 and 2005-11-27, but other than that, I really have no idea. I'm positive it's there for a reason. On the other hand, I haven't been able to reproduce any problems with it taken out.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Peculiar Query

i don't see how that query can ever not be DISTINCT anyway?

Re: Peculiar Query

Me neither smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

17

Re: Peculiar Query

there are situation in administration.
for example - querying of all posts users/ ips
there will be a lot of rows, because there will be a lot messages from one IP by one user.

Re: Peculiar Query

but thats not this query tongue

Re: Peculiar Query

GUI: I can't seem to replicate your problems with extern.php. Then again, I don't have the massive amount of posts you do to play with. I will attempt to import a very large (3 million posts) vbulletin database sometime this weekend. Hopefully, that will allow me to do some proper performance testing.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

20

Re: Peculiar Query

If it would be easier, I could hook you with an SQL dump of whatever stuff you needed. Unless, you had planned on importing the vBulletin database anyway. Whatever is easiest for you.

Although, I would think extern.php would still generate temporary tables on smaller boards, it's just that the effect wouldn't be as noticeable. But who knows how that crazy MySQL engine works, or if I screwed up indexing somewhere along the way.

Hard work may not kill you, but why take chances?

21

Re: Peculiar Query

Somebody just posted about guest posts showing up multiple times in 1.2.5
http://punbb.org/forums/viewtopic.php?pid=39728#p39728
Is this related to the absence of the "Distinct" query?

Re: Peculiar Query

I guess it must be, same thing is happening in the test forum here :-/

23

Re: Peculiar Query

I don't know if it's any help, but I can't duplicate it on my own setup which is running a dump of PunBB.org.
WinXP Pro
PHP 5.02
mySQL 4.0.21
No acceleration

Is it mySQL 4.1 maybe?

Re: Peculiar Query

I knew it! Oh well, it'll have to wait until tonight. I'm off to work now.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

25

Re: Peculiar Query

This DISTINCT is not useless.. sometimes guests messages got doubled. I don't know the reason...
So, I've changed it back..