1 Topic by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Topic: Punbb search performance tweak

Hi, I am managing a quite big punbb forum (+- 500 000 posts). We have had recently some real performance problems, forum hits sometimes taking anormally long.

We searched a lonnnng time for the cause of the problem, tuning our fastcgi configuration, php environment vars, before stating the problem was the SQL server. I got help from some guys working at skyblog.com (understand: far more qualified than I am), and they stated the error was coming from the search queries.

After investigating the problem some time, they gave me that patch: http://www.skyblog.com/search.php.patch

Before yelling that it may break searches in some cases: we know it! As fas as I understood it, it may display inappropriate search results for posts that have been removed, moved, changed, whatever. But, still, have a look at the performance change:

http://waste.mandragor.org/mawu_cpu.png
http://waste.mandragor.org/mawu_slow.png
http://waste.mandragor.org/mawu_load.png

The patch was applied at 16h.

So, let's think about it: would there be one way to push that join out of the search query? Deleting that join would dramatically improve punbb's performance.

Anyway, even if some queries aren't as accurate as before and if the patch introduces some wrong-true results, I'm so happy these performances problems have ended, I wanted to share that with you!

Yann's Website

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Punbb search performance tweak

You could always try the MySQL fulltext search mod wink

3 Reply by Xarkam (edited by Xarkam 2006-09-22 15:48)

  • Registered: 2005-05-04
  • Posts: 28

Re: Punbb search performance tweak

Hi.
do not use this patch. After several tests, it makes results false according to search criteria's given.

for the moment we carry out investigations to solve this problem.

Xarkam.

  • Registered: 2005-05-04
  • Posts: 28

Re: Punbb search performance tweak

All right, the patch work fine smile

5 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Up... Rickard, could we have your advice on this?

Yann's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Punbb search performance tweak

Looking into this is on my todo list.

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

Rickard's Website

  • From: France
  • Registered: 2004-11-06
  • Posts: 695

Re: Punbb search performance tweak

I'm guessing a homemade search tool, using PHP (aka interpreted language) will always be slower than using a true SQL search. It would be quite useful if 1.3 would allow real SQL search (using MySQL fulltext for example), or at least be able to use it if one add a plugin/extension/whatever for it without hacking the main sources.

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website

8 Reply by hcgtv

  • hcgtv
  • hcgtv
  • Senior Member
  • Offline
  • From: Charlotte, NC
  • Registered: 2004-06-25
  • Posts: 1,991

Re: Punbb search performance tweak

Jeremie, MySQL fulltext indexing is in the todo list for PunBB 1.3.

PHPCrossRef . TXP Make . TXP Themes . TXP Tags . TXP Planet . We Love TXP

hcgtv's Website

  • From: VnOSS
  • Registered: 2004-09-04
  • Posts: 121

Re: Punbb search performance tweak

hcgtv wrote:

Jeremie, MySQL fulltext indexing is in the todo list for PunBB 1.3.

MySQL fulltext supports UTF-8 ? I mean MySQL 5.x

[no signature]

10 Reply by hcgtv

  • hcgtv
  • hcgtv
  • Senior Member
  • Offline
  • From: Charlotte, NC
  • Registered: 2004-06-25
  • Posts: 1,991

Re: Punbb search performance tweak

vnpenguin wrote:

[MySQL fulltext supports UTF-8 ? I mean MySQL 5.x

I believe it does:
http://dev.mysql.com/tech-resources/art … icode.html
http://textpattern.net/wiki/index.php?t … de_Support

PHPCrossRef . TXP Make . TXP Themes . TXP Tags . TXP Planet . We Love TXP

hcgtv's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Punbb search performance tweak

http://dev.punbb.org/changeset/599

The preg_match check in the original patch isn't needed. Thanks to whoever noticed the unnecessary join at skyblog.com and thanks to you Yann for telling us smile

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

Rickard's Website

12 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Can it be that the join is in unused in other queries, like for example this one (search.php):

   
                        if ($show_as == 'topics')
                        {
                                $result = $db->query('SELECT t.id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.id IN('.implode(',', $search_ids).')'.$forum_sql.' GROUP BY t.id', true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());


Deleting that join resulted in 70% less disk access, as I had a lot of slow_queries coming from that request.
Was it safe? tongue

Yann's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Punbb search performance tweak

What join did you delete there (I assume the join with topics)?

14 Reply by Yann (edited by Yann 2007-01-04 01:32)

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Yep, the same as earlier (hey, some people still awake?? tongue)
So I deleted that part: INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id

Yann's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Punbb search performance tweak

How did you get the forum ID for forum_perms then?

16 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Yeah that broke the search, in fact... But, this query is killing the server's disk.. Is there no way to improve it?

Yann's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Punbb search performance tweak

Well, you could split it up into 2 queries:

$result = $db->query('SELECT t.id FROM '.$db->prefix.'posts AS p WHERE p.id IN('.implode(',', $search_ids).')') or error('Unable to fetch topic IDs', __FILE__, __LINE__, $db->error());

$tids = array();
while (list($tid) = $db->result($result))
{
    $tids[] = $tid;
}

$result = $db->query('SELECT t.id FROM '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id IN('.implode(',', $tids).')'.$forum_sql, true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

I removed the group by statement from the last query because even if there are duplicate IDs within the IN statement, MySQL will only return 1 row for the ID. I think without the group by you should be fine.

18 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

These lines don't work... Error: Unable to fetch topic IDs.
Are you to be find tomewhere on IRC?

Yann's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Punbb search performance tweak

I'm in PunBB's room on Quakenet

And here's code that should work

$result = $db->query('SELECT p.topic_id FROM '.$db->prefix.'posts AS p WHERE p.id IN('.implode(',', $search_ids).')', true) or error('Unable to fetch topic IDs', __FILE__, __LINE__, $db->error());

$tids = array();
while (list($tid) = $db->result($result))
{
    $tids[] = $tid;
}

$result = $db->query('SELECT t.id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id IN('.implode(',', $tids).')'.$forum_sql, true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

20 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

MMmhh, could the following scenario be possible?
- Someone start a search that take long, let's say, 30secs - it locks the topics table in write, so that read is still available, but no posting as long as the search is not ended...
- Someone posts: as the topic table is locked in write, it waits for the search to be finished, and until that, locks the table in read too...
- Someone tryes to display the forum: Not possible due to lock of the table, has to wait for the search to be ended.

And that way, the forum lags.

Is that possible? Are there solutions? Would moving from myisam to innodb change things (the row locking may prevent that?) Moving the search to another server so that the tables get not locked?

Yann's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Punbb search performance tweak

Yann: You would have to implement transactions in the MySQL dblayer

22 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

I'll try to move the search to another server... sad

PS: just as a comparison i wanted to look how was search in SMF, and tried a request a bit complex like "forum -php -bug" and got a sql error... the search pain is not only punbb specific tongue

Yann's Website

23 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Btw, Am I the only one with these bugs? I can't understand how bigger forums manage it...

Yann's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Punbb search performance tweak

Yann: In a different topic, you asked if the fulltext search from 1.3 would be backported to 1.2. I doubt it. I can only imagine you're asking this because you will not be updating to 1.3 when the time comes. If that's the case, why not try out a mysql fulltext mod?

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

Rickard's Website

25 Reply by Yann

  • Yann
  • Senior Member
  • Offline
  • Registered: 2004-04-16
  • Posts: 125

Re: Punbb search performance tweak

Rickard > Smartyes told me it was buggy and searched in forum where users have no access wink

Btw, i'm continuing to investigate the issue... And I think I should apologize for my complaints, the search was what
caused my server to read/write so much on the disk, but I think it was because of a too small key_buffer_size ...

Sorry for that.

Yann's Website