[Spacewalk-list] Spacewalk 1.6 Database problem

[Scott Worthington]

On Tuesday, January 10, 2012 7:37:20 AM, John Hodrien wrote:
> On Thu, 5 Jan 2012, John Hodrien wrote:
>
>>> From a clean start of the services, I visit the overview, systems, then
>> channels.  I've done nothing else.  After about a minute or two I start getting
>> stuck SQL queries.  Over time these build up until I run out of connections to
>> postgres.
>
> Is the real problem here the sheer number of 'idle in transaction'
> connections?
>
> For now I've just put a very crufty and wrong cron hack in that at least might
> stop me having to restart spacewalk on a daily basis:
>
> *  *  *  *  * root /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | *  /usr/bin/wc \-l ` \-gt 20 && /usr/bin/pkill \-o \-f 'idle in transaction'
>
> But this is really just a big pile of wrongness.  Anyone got any tips on how I
> debug *why* I'm getting so many transactions that aren't being completed in a
> timely fashion?
>
> How many 'idle in transaction' processes are other people seeing with 1.6 on
> Postgres?
>
> jh

Sitting idle, my postgresql has 3 processes 'idle in transaction'.  
This is after being up for 3 days after a kernel update & reboot.

I have 4GB of system memory on CentOS 6.2.  Using pgtune, I have 
tweaked my /var/lib/pgsql/data/postgresql.conf with the following at 
the end of the that file:

default_statistics_target = 50 # pgtune wizard 2011-09-05
constraint_exclusion = on # pgtune wizard 2011-09-05
checkpoint_completion_target = 0.9 # pgtune wizard 2011-09-05
checkpoint_segments = 16 # pgtune wizard 2011-09-05
maintenance_work_mem = 240MB # pgtune wizard 2011-09-13
effective_cache_size = 2816MB # pgtune wizard 2011-09-13
work_mem = 24MB # pgtune wizard 2011-09-13
wal_buffers = 8MB # pgtune wizard 2011-09-13
shared_buffers = 960MB # pgtune wizard 2011-09-13
max_connections = 89 # pgtune wizard 2011-09-13

And I have also added to my /etc/sysctl.conf:

kernel.shmmax=1040990208

The above was calculated using pgtune.

I do stop and reinitialize jabber  & osa-dispatcher every morning at 
6am:

# Restart jabber everyday @6am
00 06 * * *  /sbin/service jabberd stop > /dev/null 2>&1 ; 
/sbin/service osa-dispatcher stop > /dev/null 2>&1 ; rm -Rf 
/var/lib/jabberd/db/* > /dev/null 2>&1 ; /sbin/service jabberd start > 
/dev/null 2>&1 ; /sbin/service osa-dispatcher start > /dev/null 2>&1

After installing "spacewalk-report" rpm and executing 
'/usr/bin/spacewalk-report channel-packages | wc -l' returns 42725.
Or 42,725 packages loaded into the spacewalk channel system.

Spacewalk 1.6 and postgresql both seem to work nicely in my production 
environment.