[postgresql] BIG troubles with active forum and search

XuMiX · 2005-12-13 11:25

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Topic: [postgresql] BIG troubles with active forum and search

So, my AthlonXP 1800+, 512Mb RAM, 1gb SWAP, FreeBSD 6, PostgreSQL 8.1 gets fully stuck when smb searches forum because of this query:
SELECT t.id FROM pun_posts AS p INNER JOIN pun_topics AS t ON t.id=p.topic_id INNER JOIN pun_forums AS f ON f.id=t.forum_id LEFT JOIN pun_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 p.id IN(40258,57627,82690,17797,76558,.......!!!30_pages_of_numbers_here!!!..........88788,88772,89720,88784,50439,63671,29725,87854,71274,62780,29053) AND t.forum_id = 10 GROUP BY t.id
-----
So, is there a nice solution for this(except manually flushing search words cache ?

Rickard · 2005-12-13 12:08

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

Well, this is the problem with the current search system. It can be solved by running a subselect instead of the huge IN query, but this feature would be MySQL5/PostgreSQL specific. Right now, I don't have a solution for you.

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

XuMiX · 2005-12-13 12:20

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

Hmmm, mysql 4.1.x(mb mysql 4.x) already has subqueries
could you please rewrite search a bit, mb just add a check for mysql version ?

XuMiX · 2005-12-13 12:27

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

BTW: include/search_idx.php
function strip_search_index($post_ids)

DELETE FROM '.$db->prefix.'search_words WHERE id IN(SELECT word_id FROM '.$db->prefix.'search_matches WHERE word_id IN(SELECT word_id FROM '.$db->prefix.'search_matches WHERE post_id IN('.$post_ids.') GROUP BY word_id) GROUP BY word_id HAVING COUNT(word_id)=1)

Here is check for db type, it would be logical to add the same to search.php

Rickard · 2005-12-13 12:47

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

Yes, but it's a bigger change for search.php. It's not just one or two lines that need to be changed.

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

XuMiX · 2005-12-13 12:59

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

>Yes, but it's a bigger change for search.php. It's not just one or two lines that need to be changed.
Really sad Are you going to look it through in near future or its better to think a bit and make it myself?
Honestly, I have no desire to do this ) Just have no time, but this bug is really annoying

Rickard · 2005-12-13 14:56

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

I will try to have a look at it tonight. I have a bunch of other things to do though, so I won't make any promises.

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

XuMiX · 2005-12-14 06:37

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

Thx a lot

Rickard · 2005-12-14 08:41

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

Sorry, I just didn't have the time yesterday. I will try again tonight.

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

XuMiX · 2005-12-17 17:02

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

Hi,
is there any progress ?

XuMiX · 2005-12-22 08:53

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

desided to use postgresql's Tsearch2 full-text search engine, and to adapt punbb so

XuMiX · 2005-12-23 09:03

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

so. done by myself, 6 Kb of code from search.php was removed. Searching of 3 words through ~110K messages and ~13K topics takes ~5-10 sec(~100 users online)
Adapting has taken ~8 hours

Rickard · 2005-12-23 09:57

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

I'm sorry once again for not being able to help you with this. Nice to hear you solved it yourself. Using the native fulltext search indexing is probably a good idea. I only wish there was some kind of standard. MySQL does it in a different way and I don't think SQLite supports it at all.

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

XuMiX · 2005-12-23 10:32

XuMiX
Senior Member
Offline

From: Russia.Novosibirsk.ToStreet()
Registered: 2004-05-17
Posts: 130

Re: [postgresql] BIG troubles with active forum and search

No matter Anyway, if you want your forum to be more scalable you should think of rewriting search.php
BTW i don't know insiders of phpBB's search, but there are no such troubles there

Rickard · 2005-12-30 12:35

Rickard
PunBB Developer
Offline

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

Re: [postgresql] BIG troubles with active forum and search

XuMiX wrote:

BTW i don't know insiders of phpBB's search, but there are no such troubles there

PunBB's search feature is based on phpBB's, so I would think it suffers from the same scalability problem.

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

[postgresql] BIG troubles with active forum and search

Posts: 15

1 Topic by XuMiX 2005-12-13 11:25 (edited by XuMiX 2005-12-13 12:30)

Topic: [postgresql] BIG troubles with active forum and search

2 Reply by Rickard 2005-12-13 12:08

Re: [postgresql] BIG troubles with active forum and search

3 Reply by XuMiX 2005-12-13 12:20 (edited by XuMiX 2005-12-13 12:21)

Re: [postgresql] BIG troubles with active forum and search

4 Reply by XuMiX 2005-12-13 12:27

Re: [postgresql] BIG troubles with active forum and search

5 Reply by Rickard 2005-12-13 12:47

Re: [postgresql] BIG troubles with active forum and search

6 Reply by XuMiX 2005-12-13 12:59 (edited by XuMiX 2005-12-13 13:02)

Re: [postgresql] BIG troubles with active forum and search

7 Reply by Rickard 2005-12-13 14:56

Re: [postgresql] BIG troubles with active forum and search

8 Reply by XuMiX 2005-12-14 06:37

Re: [postgresql] BIG troubles with active forum and search

9 Reply by Rickard 2005-12-14 08:41

Re: [postgresql] BIG troubles with active forum and search

10 Reply by XuMiX 2005-12-17 17:02

Re: [postgresql] BIG troubles with active forum and search

11 Reply by XuMiX 2005-12-22 08:53

Re: [postgresql] BIG troubles with active forum and search

12 Reply by XuMiX 2005-12-23 09:03 (edited by XuMiX 2005-12-23 09:04)

Re: [postgresql] BIG troubles with active forum and search

13 Reply by Rickard 2005-12-23 09:57

Re: [postgresql] BIG troubles with active forum and search

14 Reply by XuMiX 2005-12-23 10:32

Re: [postgresql] BIG troubles with active forum and search

15 Reply by Rickard 2005-12-30 12:35

Re: [postgresql] BIG troubles with active forum and search

Posts: 15