[Spacewalk-list] Optimizing postgresql with Spacewalk 1.5 onCentOS 6.0 x86_64
Gerald
redhat at jerry.at
Fri Sep 23 10:25:08 UTC 2011
Hi Nicolas,
thanks for your reply.
I checked the index and this wasn't the problem.
Regards,
Gerald
Von: spacewalk-list-bounces at redhat.com
[mailto:spacewalk-list-bounces at redhat.com] Im Auftrag von pradelles nicolas
Gesendet: Freitag, 23. September 2011 10:56
An: spacewalk-list at redhat.com
Betreff: Re: [Spacewalk-list] Optimizing postgresql with Spacewalk 1.5
onCentOS 6.0 x86_64
Hello,
please check my thread "Repo Sync speed problem" the 16/08/2011, I had the
same problem on a Oracle database. An index was not created during
installation, and reposync was slower and slower.
After added the index everything returned to normal.
The SQL part:
<http://git.fedorahosted.org/git/?p=spacewalk.git;a=blob;f=schema/spacewalk/
common/tables/rhnPackageCapability.sql#l32>
http://git.fedorahosted.org/git/?p=spacewalk.git;a=blob;f=schema/spacewalk/c
ommon/tables/rhnPackageCapability.sql#l32
Regards,
Nicolas
_____
From: spacewalk-list-bounces at redhat.com
[mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Gerald
Sent: vendredi 23 septembre 2011 10:30
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Optimizing postgresql with Spacewalk 1.5
onCentOS 6.0 x86_64
Hi,
I'm now syncing for TWO AND A HALF WEEK (spacewalk-repo-sync for centos5+6
repos)
and still not finished (spacewalk 1.5 with postgresql, centos5 x86_64).
Especially the larger repos from rpmforge are a massive problem.
With oracle guess I synced all in max. 2 days.
I've tried the performance settings from below, tried others, added more
ram, etc. but it still takes ages
(at the moment I'm syncing package 6200 of 8215 and it takes 61sec per
package! In the beginning it's a bit faster and then
it gets slower and slower).
Disabling taskomatic and osa-dispatcher didn't help either. Maybe there are
some other suggestions to try?
Hope someone can speed up this process.
Thank you guys,
Gerald
Von: spacewalk-list-bounces at redhat.com
[mailto:spacewalk-list-bounces at redhat.com] Im Auftrag von Pierre Casenove
Gesendet: Mittwoch, 14. September 2011 20:37
An: spacewalk-list at redhat.com
Betreff: Re: [Spacewalk-list] Optimizing postgresql with Spacewalk 1.5 on
CentOS 6.0 x86_64
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
_____
This message may contain confidential or privileged information and is
intended only for the personal and confidential use of the designated
recipient(s) named above. If you are not one of the intended recipients of
this message you are hereby notified that any review, dissemination,
distribution or copying is strictly prohibited. This communication is for
information purposes only and should not be regarded as an official
statement of Eutelsat S.A. or any of its subsidiaries. E-mail transmission
cannot be guaranteed to be secure or error-free. Therefore, Eutelsat S.A.
does not claim that this information is complete or accurate and it should
not be treated as such.
_____
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110923/d8207b75/attachment.htm>
More information about the Spacewalk-list
mailing list