[rt-users] Performance Issues after 3.8.0 upgrade -- PostgreSQL delays

[Jesse Vincent]

On Jul 16, 2008, at 2:00 PM, Jessie Bryan wrote:

> Hello everyone,
>
> We recently upgraded from 3.6.6 to 3.8.0 and after the upgrade, we
> immediately noticed delays loading pages within RT-3.8.0 website.
> Currently, the web interface is hosted on a Gentoo Linux system
> running apache2-2.8 with modperl 2.0.3 (portage) and Postfix MTA.
> RT3.8 was built from source.
> The database server is on a separate Gentoo Linux system running
> Postgresql-8.1.11 (portage). We have roughly 6 concurrent users at any
> time using RT.
> The database server shows us the process postmaster running at 85-95%
> CPU  (5min avg load is around 3.88 - 4.5) during RT web clicks.
> Being relatively naive to postgres performance tuning, I'm not sure if
> this post is relevant to this list or not.
>
>

Are you doing automated vacuum analyzes? Do you have Postgres' slow  
query log on? If so, what does it report?


> Additional Server Details:
>
> tickets web/postfix has about 1GB RAM and is only running
> Postfix/Apache. The system load is always under 1, about 0.10 average
> RT-3.8.0 uses the following build options:
>
> #!/bin/bash
> ./configure   --with-bin-owner=root  --with-libs-owner=root
> --with-libs-group=bin  \
>             --with-db-type=Pg --with-db-host=copper.example.org
> --with-db-port=5432  \
>             --with-db-rt-host=tickets.example.org
> --with-db-dba=********  --with-db-database=rt3 \
>             --with-db-rt-user=rt --with-db-rt-pass=********** \
>             --with-web-user=apache --with-web-group=apache --with-rt- 
> group=rt \
>             --enable-graphviz --enable-gd --enable-gpg
>
>
> postgres "copper" server has 1GB RAM and is only running Postgres for
> our RT3 installation. Prior to the RT-3.8 upgrade the server load
> around 0.15
>
> Here's our postgresql.conf:
>
> max_connections = 100
> shared_buffers = 1000
> lc_messages = 'C'
> lc_monetary = 'C'
> lc_numeric = 'C'
> lc_time = 'C'
> stats_start_collector = true
> stats_row_level = true
> listen_addresses = '*'
> shared_buffers = 48000
> max_prepared_transactions = 64
> work_mem = 4096
> maintenance_work_mem = 32767
> max_stack_depth = 7168
> fsync = off
> effective_cache_size = 4000
> autovacuum = on
> autovacuum_naptime = 300
> lc_messages = 'en_US'
> lc_monetary = 'en_US'
> lc_numeric = 'en_US'
> lc_time = 'en_US'
>
> Here's a 'vmstat 1' from "copper" / postresql box:
>
> copper /var/log# vmstat 1
> procs -----------memory---------- ---swap-- -----io---- --system--  
> ----cpu----
> r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us  
> sy id wa
> 0  0  41080  50316  19004 704144    0    0     1     0    1     1   
> 4  1 95  0
> 0  0  41080  50316  19004 704144    0    0     0     0  145    59   
> 0  0 100  0
> 0  0  41080  50316  19004 704144    0    0     0     0  105    18   
> 0  0 100  0
> 1  0  41080  50316  19004 704212    0    0     0     0  617   540   
> 5  2 93  0
> 2  0  41080  50316  19012 704204    0    0     0  1228  367   330 57  
> 13 30  1
> 1  0  41080  50316  19012 704204    0    0     0     0  490   750 64  
> 11 25  0
> 1  0  41080  49944  19012 704340    0    0     0     0  387   711 69  
> 10 21  0
> 1  0  41080  49944  19012 704340    0    0     0     0  213   230 23  
> 15 62  0
> 1  0  41080  49944  19012 704340    0    0     0     0  211   217 25  
> 15 59  0
> 1  0  41080  49944  19020 704332    0    0     0   584  253   259 25  
> 17 58  0
> 0  0  41080  49884  19020 704400    0    0    60     0  205   219 24  
> 16 58  2
> 1  0  41080  49824  19020 704400    0    0    48     0  211   222 19  
> 22 58  0
> 1  0  41080  49764  19020 704468    0    0    72     0  197   198 28  
> 13 58  1
> 0  0  41080  49704  19020 704604    0    0    56     0  217   228 18  
> 16 66  0
> 0  0  41080  49704  19028 704596    0    0     0   112  113    46   
> 0  0 100  0
> 0  0  41080  49704  19028 704596    0    0     0     0  104    22   
> 0  0 100  0
> 0  0  41080  49704  19028 704596    0    0     0     0  105    20   
> 0  0 100  0
>
> copper log /var/log#
>
>
>
> We have a 2nd pair of identical servers running a copy of the
> Postgresql RT-3.6.6 database (for testing purposes) and do not see
> these slow downs (same hardware,software).
>
> Does anyone have any suggestions where to tackle this? Is  
> downgrading an option?
>
> Thanks everyone,
>
> -Jessie
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>