[Spacewalk-list] Optimizing postgresql with Spacewalk 1.5 on CentOS 6.0 x86_64
Pierre Casenove
pcasenove at gmail.com
Wed Sep 14 18:36:58 UTC 2011
Thanks for sharing your experience. I don't experience problem so I think
I'll keep with the default params.
Could someone from spacewalk team confirm the optimization possibilities?
Another point about the autovacuum function: BZ 705935 address the fact that
vacuum is useless. Is it still a valid BZ?
Description of problem:
Seems like Spacewalk is keeping database transactions open which leads to
unremovable dead rows in tables.
Thanks,
Pierre
2011/9/14 Scott Worthington <scott.c.worthington at gmail.com>
> Hello,
>
> I'd like to share my experience with out-of-the-box installation of
> Spacewalk 1.5 on CentOS 6.0 x86_64.
>
> The out-of-the-box default configuration of postgresql is abysmal.
> You may be very familiar with MySQL, but postgresql is certainly not
> MySQL (install and forget). You _HAVE_ to make configuration changes
> and tune postgresql for optimum performance with Spacewalk.
>
> While reading this, please note that this is for the CentOS 6.0 x86_64
> platform. I cannot speak for Ubuntu, Debian, or other CentOS
> releases, etc.
>
> In my installation, I am using 4GB of server RAM as a virtualized
> guest on VMware. I have NOT changed Java or Tomcat's memory heap
> settings (-Xms or -Xmx) and I don't believe it is necessary. Your
> slowness is due to postgresql.
>
> * Before making the following changes, shutdown Spacewalk and then
> Postgresql. Better yet, run Spacewalk on VMware and take a snapshot
> of your current setup for rollbacks (if you're like me, you've spent
> hours upon hours loading in Channels and their repo data).
>
> spacewalk-service stop
> service postgresql stop
>
> == Default postgresql configuration ==
>
> Auto vacuum is NOT enabled. This causes the database server to crawl
> to a halt during the loading of thousands of RPMs via the "rhnpush"
> CLI and also through "spacewalk-repo-syncs --type yum".
>
> * Turn on autovacuum
>
> Edit your /var/lib/pgsql/data/postgresql.conf and find...
>
> #autovacuum=on
>
> ...and uncomment it:
>
> autovacuum=on
>
> In the same file, find...
>
> #track_counts = on
>
> ...and uncomment it:
>
> track_counts = on
>
> == Install pgtune ==
>
> Next, tune postgresql using the pgtune package. I have found that the
> package pgtune is excellent for tuning your system. The following
> instructions have you download the .tar.gz file and create an
> installable RPM. I prefer install RPMs, not source. You can install
> source, that is your prerogative.
>
> * Download and compile pgtune (to install it as an RPM):
>
> * First, ensure you have rpmbuild installed
>
> yum install rpmbuild
>
> # next, download, put files in the correct directories, and build
> mkdir -p /usr/local/src/TARGZs/pgtune
> cd /usr/local/src/TARGZs/pgtune
> wget http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz
> tar xvfz pgtune-0.9.3.tar.gz
> cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3.tar.gz /root/rpmbuild/SOURCES
> cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune.spec
> /root/rpmbuild/SPECS
> cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune-settingsdir.patch
> /root/rpmbuild/SOURCES
> cd /root/rpmbuild/SPECS
> #the spec file has an incorrect version number, the following command
> will fix it
> perl -npe 's/0\.9\.2/0.9.3/g' -i /root/rpmbuild/SPECS/pgtune.spec
> rpmbuild -ba pgtune.spec
> #If your rpmbuild fails, you may need to install dependencies, look at
> the output of the rpmbuild
>
> #install the RPM
> rpm -ivh /root/rpmbuild/RPMS/noarch/pgtune-0.9.3-1.el6.noarch.rpm
>
> After pgtune is installed, run it on your postgresql.conf file:
>
> cd /var/lib/pgsql/data
> pgtune -i postgresql.conf -o postgresql.conf.pgtune
>
> * Make a backup of your existing, "working" configuration (replace
> YYYYMMDD with today's date)
>
> cp -p postgresql.conf postgresql.conf-working-YYYYMMDD
>
> * Replace existing config
>
> cp postgresql.conf.pgtune postgresql.conf
>
> * I do not believe that the postgresql.conf file contains the
> "default" parameters in the lines that are commented out (unlike
> packages like postfix). I recommend that you do not trust the
> postgresql.conf default file as a source of "default" parameters.
>
> * I have also edited by hand and uncommented the entire AUTOVACUUM
> PARAMETERS section and increased the autovacuum_max_workers from 3 to
> 5:
>
>
> #------------------------------------------------------------------------------
> # AUTOVACUUM PARAMETERS
>
> #------------------------------------------------------------------------------
>
> autovacuum = on # Enable autovacuum subprocess? 'on'
> # requires track_counts to also be
> on.
> #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
> and
> # their durations, > 0 logs only
> # actions running at least this
> number
> # of milliseconds.
> 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
>
> * After you have made the above changes, attempt to restart postgresql
> (it will most likely fail)
>
> service postgresql restart
>
> * Yes, it will FAIL. Why did it fail? Most likely postgresql needs
> more memory now and the default kernel memory setup is too low
> (Hopefully you didn't introduce a typo)
>
> * Examine the contents of postgresql's startup log:
>
> less /var/lib/pgsql/pgstartup.log
>
> * You may see something like this at the end:
>
> FATAL: could not create shared memory segment: Invalid argument
> DETAIL: Failed system call was shmget(key=5432001, size=1040752640,
> 03600).
> HINT: This error usually means that PostgreSQL's request for a shared
> memory segment exceeded your kernel's SHMMAX parameter. You can
> either reduce the request size or reconfigure the kernel with larger
> SHMMAX. To reduce the request size (currently 1040752640 bytes),
> reduce PostgreSQL's shared_buffers parameter (currently 122880) and/or
> its max_connections parameter (currently 85).
> If the request size is already small, it's possible that it is
> less than your kernel's SHMMIN parameter, in which case raising the
> request size or reconfiguring SHMMIN is called for.
> The PostgreSQL documentation contains more information about
> shared memory configuration.
>
> * You have to bump up your Linux kernel.shmmax to the number, in the
> example above, the number is "1040752640"
>
> * First, non-persistently:
>
> sysctl -w kernel.shmmax=1040752640
>
> * Try to restart postgresql
>
> service postgresql restart
>
> * If it fails, look again inside of /var/lib/pgsql/pgstartup.log and
> see if you made a mistake with the shmmax number and fix your
> kernel.shmmax
>
> * Finally, make the number that you ended up using for kernel.shmmax
> and make it persistent between boots:
> cat >> /etc/sysctl.conf << 'END_OF_FILE'
> #YYYY-MM-DD HHMM (your-initials)
> kernel.shmmax=1040752640
> END_OF_FILE
>
> I have found that making those changes above makes postgresql and
> Spacewalk usable and much much faster. I have not experienced the
> "500 server timeout" error since.
>
> If you are having problems with loading packages into your Spacewalk,
> running top will usually show postgresql is sitting on a "DELETE" or
> other process that is taking forever -- the above may help speed up
> your postgresql.
>
> I hope you have the best of luck with your Spacewalk & postgresql
> deployment on CentOS 6.0 x86_64, and I hope the above instructions may
> benefit you.
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110914/5e145041/attachment.htm>
More information about the Spacewalk-list
mailing list