[rt-users] postgres slowdown

[Bryan Gibson]

Hi all,

We've been running RT for a year now, and we're trying to upgrade from
RT 3.0.4/apache-1.3.27/mod_perl1/postgresql-7.2.4 to
RT 3.0.11/apache-2.0.49/fastCGI/postgresql-7.4.2 on a dual PIII 1.9 Ghz
machine with 1 Gig RAM and SCSI drives.
This is all that's running on this box.

I imported the database from the old instance, and applied the commands
from the UPGRADING file. I'm running into a problem where the initial
login, or going to the user "Home" page, is very slow.  The slowdown
appears to happen when the user's requested tickets are loaded.  This
happens for any user.  Most of the postgres statements take from <1 to
200 ms.  This search takes ~48000 ms.  I've tried tweaking the
postgresql settings and this is as fast as I can get it.

I noticed other people were getting slowdowns do to the search parsing
through attachments, but removing the attachments from the db didn't
help either.  Don't know if that would have any effect anyway.

Has anyone else had this problem or know what I might do to speed it up?


The log of the statement I'm talking about is here:
-------------------------------------
Jun 15 20:46:48 [hostname] postgres[8198]: [2003-1] LOG:
duration: 48442.259 ms
statement:
     SELECT DISTINCT main.* FROM Tickets main , Groups Groups_1,
CachedGroupMembers CachedGroupMembers_2, Users Users_3 WHERE
((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
((main.Type = 'ticket')) AND ( ( ( (Users_3.EmailAddress ILIKE
'root at localhost')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(main.id
ILIKE Groups_1.Instance)AND(Groups_1.Type = 'Requestor')AND(Groups_1.id
ILIKE CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId
ILIKE Users_3.id) ) ) AND ( (main.Status = 'new')OR(main.S
tatus = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10
-------------------------------------

The relevant FastCGI config stuff is here:
-------------------------------------
LoadModule fastcgi_module                modules/mod_fastcgi.so
FastCgiServer /opt/rt3/bin/mason_handler.fcgi -idle-timeout 3600
-listen-queue-depth 200 -priority 10

<VirtualHost [hostip] >
     ServerName [hostname]
     DocumentRoot /opt/rt3/share/html
     AddDefaultCharset UTF-8

     ScriptAlias / /opt/rt3/bin/mason_handler.fcgi/

     <Directory /opt/rt3/bin>
         AllowOverride None
         Order allow,deny
         Allow from all
         Options +ExecCGI
     </Directory>

</VirtualHost>
-------------------------------------

The postgres config changes I've made are here:
-------------------------------------
tcpip_socket = true
max_connections = 100
shared_buffers = 15826
sort_mem = 4096
vacuum_mem = 126615
effective_cache_size = 92852
random_page_cost = 2
-------------------------------------

Thanks in advance,
Bryan Gibson
gibsonb at umich.edu
CPD