100's of duplicate database queries? postgresql

Hi,

I’m presently trying to determine why our version of RT is running so slow. At
first we thought it was because the system it was running on was
underpowered, however I’ve just moved it across to a new high powered machine
and it’s still running dog slow.

Debian (woody, with some updates to sarge as required)
RT 2.0.14
Postgres 7.1.2

I’ve checked the indexes on the database, they seem fine. I’ve run VACUUM and
so forth, no effect. I notice that when a page is loaded postgresql takes up
about 80% of the CPU. I turned on debugging to try and determine what
postgresql is doing, and it appears to be making duplicate queries, in the
order of 100’s of them, for a simple “view ticket” click. The duplicates look
like this :

SELECT * FROM Watchers WHERE lower(Type) = ‘cc’ AND lower(Scope) = 'queue’
AND Value = ‘2’ AND Owner = ‘13’

The database in question holds maybe 5500 tickets in total, and at present
takes about 9 seconds to display a ticket, which just doesn’t seem right for
a dual P-III 1.1ghz, 1.8gb ram system running scsi/raid.

Any suggestions?

t
GPG: http://n12turbo.com/tarragon/public.key

Tarragon Allen tarragon@onthe.net.au writes:

Hi,

I’m presently trying to determine why our version of RT is running so slow. At
first we thought it was because the system it was running on was
underpowered, however I’ve just moved it across to a new high powered machine
and it’s still running dog slow.

did you look in the mail archives? Or the faq? they both have some suggestions.

seph

Tarragon Allen tarragon@onthe.net.au writes:

Hi,

I’m presently trying to determine why our version of RT is running so
slow. At first we thought it was because the system it was running on was
underpowered, however I’ve just moved it across to a new high powered
machine and it’s still running dog slow.

did you look in the mail archives? Or the faq? they both have some
suggestions.

Yes, and yes. As I said, I’ve looked at the indexes, they appear fine, and
yes, I’ve looked at the hardware running the show, however it seems to me
that for the size of our database, compared to other discussions where RT is
running slow, we should not be seeing these sorts of slowdowns.

It seemed strange to me that RT is making 100’s of apparently duplicate
queries for a single “view ticket”. Is this normal behaviour, and if so,
wouldn’t it be a lot more efficient to do one query and cache it?

I came to this list as a last resort after spending quite a lot of time trying
to determine what was wrong myself.

t
GPG: http://n12turbo.com/tarragon/public.key

how many users do you have in the system?
how many watchers on the ticket? you’re initial mail didn’t say.

seph

Tarragon Allen tarragon@onthe.net.au writes:> On Tuesday 07 January 2003 15:16, seph wrote:

Tarragon Allen tarragon@onthe.net.au writes:

Hi,

I’m presently trying to determine why our version of RT is running so
slow. At first we thought it was because the system it was running on was
underpowered, however I’ve just moved it across to a new high powered
machine and it’s still running dog slow.

did you look in the mail archives? Or the faq? they both have some
suggestions.

Yes, and yes. As I said, I’ve looked at the indexes, they appear fine, and
yes, I’ve looked at the hardware running the show, however it seems to me
that for the size of our database, compared to other discussions where RT is
running slow, we should not be seeing these sorts of slowdowns.

It seemed strange to me that RT is making 100’s of apparently duplicate
queries for a single “view ticket”. Is this normal behaviour, and if so,
wouldn’t it be a lot more efficient to do one query and cache it?

I came to this list as a last resort after spending quite a lot of time trying
to determine what was wrong myself.

t

GPG: http://n12turbo.com/tarragon/public.key

I found a similar problem, but I have about 3000 tickets, and 31 users
and a relative complex acl. My response time is about 6 and 10 seconds.
During debug querys I count about 40 times (cut … | sort | uniq -c ),
but wait for have time to debug code.

I don’t have time to finish this work, but supose problems around on
sucessive query’s on isa tree
Queue/Ticket -> ACE -> EasySearch -> ACL -> CurrentUser -> User ->
_HasRight…

If have time to verify, debug the hashkey and expire (10 seconds) on
Users.pm.

PS: I don’t finish this debug, but now my new project use
DBIx::SearchBuilder ( Thank’s Jesse ! It’s a great work ! )

Marco Antonio

seph wrote: