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. _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
