[Spacewalk-list] Spacewalk 1.6 Database problem

[John Hodrien]

On Tue, 10 Jan 2012, Scott Worthington wrote:

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

So *something* is clearly different with my setup.  I currently have 20,
solely because I'm killing them off when I get more.

After a restart, it settles down to two 'idle in transaction', with others
coming and going regularly.  That all looks fine at that stage.  But within
just a few minutes I've got a block of 7 stuck at idle in transaction that
just aren't going anywhere.  AFAIK this is a 1.4 -> 1.5 -> 1.6 Spacewalk
install.

> 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

I'm broadly similar.  I've just added constraint_exclusion on the off chance
that makes it behave slightly differently, but I don't see that as being
likely, and indeed no, it doesn't obviously do anything.

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

I don't, but given my symptoms appear far faster than this it's clearly not
that.

> 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.

40226, so broadly similar.

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

Arggggh!  :)

jh