[rt-users] postgres slowdown

[Jesse Vincent]

On Tue, Jun 15, 2004 at 04:46:06PM -0500, Bryan Gibson wrote:
> 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?

Try the latest prerelease DBIx::SearchBuilder.

> 
> 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
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> RT Developer and Administrator training is coming to LA, DC and Frankfurt 
> this spring and summer.
> http://bestpractical.com/services/training.html
> 
> Sign up early, as class space is limited. 

--