[Spacewalk-list] Spacewalk 1.6 Database problem

[Scott Worthington]

On Tuesday, January 10, 2012 10:20:53 AM, John Hodrien wrote:
> On Tue, 10 Jan 2012, Scott Worthington wrote:

[...]

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

[...]

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

John,

If you do a bit of of additional tweaking and tinkering with your 
Spacewalk 1.6,
and you  do find out what is bogging down your Spacewalk with 
postgresql
database backend & successfully repair it, then please let us all know 
since
it may help someone else with a similar issue.

I've here are some other changes that I made in 
/var/lib/pgsql/data/postgresql.conf
that may (or may not) help you...

I ensured that 'autovacuum = on' was uncommented, and I also  
uncommented the
autovacuum block that starts with 'autovacuum_max_workers' through
'autovacuum_vacuum_cost_limit' on my postgresql 8.4.9 on CentOS 6.2.

Such as:
autovacuum_max_workers = 5              # max number of autovacuum 
subprocesses
autovacuum_naptime = 1min               # time between autovacuum runs
autovacuum_vacuum_threshold = 50        # min number of row updates 
before
                                        # vacuum
autovacuum_analyze_threshold = 50       # min number of row updates 
before
                                        # analyze
autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before 
vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of table size before 
analyze
autovacuum_freeze_max_age = 200000000   # maximum XID age before forced 
vacuum
                                        # (change requires restart)
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

Also:

synchronous_commit = off   # I believe this is originally is set to on

These are changes that I made after much searching other sites / 
mailling lists
to find out what others suggested and tried to get their own postgresql
server to behave in their environment (may & may not have been
Spacewalk implementations).  YMMV.

--ScottW