Re: One source of constant annoyance identified

> And the swapping activity is exactly the problem, isn't it?

Yupp, I guess so; once swapping is reduced, there should be not only
much more fast memory available to the queries but also more processing
time.

> In any case, we can't make much progress until we identify the query
> that is making the backend's address space grow.

This here is one of them (see full text in mail from 13:25):

> -----Ursprüngliche Nachricht-----
> Von: Markus Wollny
> Gesendet: Freitag, 28. Juni 2002 13:25
> An: pgsql-general(at)postgresql(dot)org
> Betreff: Re: [GENERAL] One source of constant annoyance identified
>
>
> Hi!
>
> It seems I found one of the queries which suck up memory as if there
> were terabytes available.
>
> If a user starts a search for e.g. "Ich brauche Mitleid" on one of our
> websites (site-ID is 43 in this example), we construct our select like
> this:
>
> select MESSAGE.BOARD_ID
> , MESSAGE.THREAD_ID
> , MESSAGE.MESSAGE_ID
> , MESSAGE.TITLE
> , MESSAGE.USER_ID
> , USERS.LOGIN
> , USERS.STATUS
> , USERS.RIGHTS
> , to_char(MESSAGE.CREATED,'DD.MM.YY
> hh24:mi') as DATUM
> , MESSAGE.COUNT_REPLY
>
> , (select count(*) from
> CT_COM_USER_THREAD_FOLLOW where USER_ID= '484387' and
> thread_id=MESSAGE.THREAD_ID) as TFUID
>
> from CT_COM_BOARD_MESSAGE MESSAGE
> , CT_COM_USER
> USERS
> , CT_COM_BOARD_RULES READRULE
> , CT_COM_SITE_BOARDS SITE
> where SITE.SITE_ID = '43'
>
> and
> (
> lower(MESSAGE.TEXT) like '%ich%'
> or lower(MESSAGE.TEXT) like 'ich%'
> or lower(MESSAGE.TEXT) like '%ich'
>
> or lower(MESSAGE.TITLE) like '%ich%'
> or lower(MESSAGE.TITLE) like 'ich%'
> or lower(MESSAGE.TITLE) like '%ich'
>
> )
>
> and
> (
> lower(MESSAGE.TEXT) like '%brauche%'
> or lower(MESSAGE.TEXT) like 'brauche%'
> or lower(MESSAGE.TEXT) like '%brauche'
>
> or lower(MESSAGE.TITLE) like '%brauche%'
> or lower(MESSAGE.TITLE) like 'brauche%'
> or lower(MESSAGE.TITLE) like '%brauche'
>
> )
>
> and
> (
> lower(MESSAGE.TEXT) like '%mitleid%'
> or lower(MESSAGE.TEXT) like 'mitleid%'
> or lower(MESSAGE.TEXT) like '%mitleid'
>
> or lower(MESSAGE.TITLE) like '%mitleid%'
> or lower(MESSAGE.TITLE) like 'mitleid%'
> or lower(MESSAGE.TITLE) like '%mitleid'
>
> )
>
> and MESSAGE.STATE_ID = 0
> and MESSAGE.USER_ID =
> USERS.USER_ID
> and USERS.STATUS > 0
> and SITE.BOARD_ID =
> MESSAGE.BOARD_ID
> and READRULE.BOARD_ID =
> MESSAGE.BOARD_ID
> and READRULE.RULE_ID = 1
> and READRULE.VALUE <= '5'
> order by MESSAGE.LAST_REPLY desc
>

[snip]

> (Markus, you don't happen to have SORT_MEM set to a large value,
> do you?)

Not at all - recommendations are 2-4% of available RAM AFAIK. On a 1GB
machine this would be 21000 to 42000KBs. Nevertheless I reduced it from
these values to a meagre sort_mem = 8192, that's 8MB, but to no avail.
Reducing this value any further doesn't really make too much sense, does
it.

Anyway, I think that one of our main problems is the lack of options for
fulltext-indexing. I'm currently trying to find out how to get FTI (from
CVS/Contrib) up and running, because I hope that this will solve 90% of
my problems if not more :)

Regards,

Markus