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

[Jessie Bryan]

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.


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