<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:m =
"http://schemas.microsoft.com/office/2004/12/omml"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.17102" name=GENERATOR>
<STYLE>@font-face {
font-family: Calibri;
}
@font-face {
font-family: Tahoma;
}
@font-face {
font-family: Consolas;
}
@page WordSection1 {size: 612.0pt 792.0pt; margin: 70.85pt 70.85pt 2.0cm 70.85pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman","serif"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman","serif"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman","serif"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
PRE {
FONT-SIZE: 10pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Courier New"; mso-style-priority: 99; mso-style-link: "HTML Vorformatiert Zchn"
}
SPAN.apple-style-span {
mso-style-name: apple-style-span
}
SPAN.HTMLVorformatiertZchn {
FONT-FAMILY: Consolas; mso-style-priority: 99; mso-style-link: "HTML Vorformatiert"; mso-style-name: "HTML Vorformatiert Zchn"; mso-fareast-language: DE-AT
}
SPAN.E-MailFormatvorlage20 {
COLOR: #1f497d; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: personal-reply
}
.MsoChpDefault {
FONT-FAMILY: "Calibri","sans-serif"; mso-fareast-language: EN-US; mso-style-type: export-only
}
DIV.WordSection1 {
page: WordSection1
}
</STYLE>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=DE-AT vLink=purple link=blue>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=628055308-23092011>Hello,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=628055308-23092011>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=628055308-23092011>After added the index everything returned to
normal.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=628055308-23092011>The SQL part:</SPAN></FONT><SPAN
class=628055308-23092011><FONT face=Arial size=2> </FONT></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011><SPAN lang=EN>
<P></SPAN><A
href="http://git.fedorahosted.org/git/?p=spacewalk.git;a=blob;f=schema/spacewalk/common/tables/rhnPackageCapability.sql#l32"><U><FONT
color=#0000ff size=2><FONT color=#0000ff size=2><SPAN
lang=EN>http://git.fedorahosted.org/git/?p=spacewalk.git;a=blob;f=schema/spacewalk/common/tables/rhnPackageCapability.sql#l32</U></FONT></FONT></SPAN></A></P></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011><FONT face=Arial size=2></FONT></SPAN> </DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011><FONT face=Arial size=2></FONT></SPAN> </DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011><FONT face=Arial size=2>Regards,</FONT></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011><FONT face=Arial size=2>Nicolas</FONT></SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=628055308-23092011> </SPAN></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
</DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2><B>From:</B> spacewalk-list-bounces@redhat.com
[mailto:spacewalk-list-bounces@redhat.com] <B>On Behalf Of
</B>Gerald<BR><B>Sent:</B> vendredi 23 septembre 2011 10:30<BR><B>To:</B>
spacewalk-list@redhat.com<BR><B>Subject:</B> Re: [Spacewalk-list] Optimizing
postgresql with Spacewalk 1.5 onCentOS 6.0 x86_64<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=WordSection1>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Hi,<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">I’m
now syncing for TWO AND A HALF WEEK (spacewalk-repo-sync for centos5+6 repos)
<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">and
still not finished (spacewalk 1.5 with postgresql, centos5
x86_64).<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Especially
the larger repos from rpmforge are a massive problem.<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">With
oracle guess I synced all in max. 2 days.<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">I’ve
tried the performance settings from below, tried others, added more ram, etc.
but it still takes ages <o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">(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<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">it
gets slower and slower).<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Disabling
taskomatic and osa-dispatcher didn’t help either. Maybe there are some other
suggestions to try?<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Hope
someone can speed up this process…<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Thank
you guys,<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Gerald<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><B><SPAN lang=DE
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">Von:</SPAN></B><SPAN
lang=DE style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">
spacewalk-list-bounces@redhat.com [mailto:spacewalk-list-bounces@redhat.com]
<B>Im Auftrag von </B>Pierre Casenove<BR><B>Gesendet:</B> Mittwoch, 14.
September 2011 20:37<BR><B>An:</B> spacewalk-list@redhat.com<BR><B>Betreff:</B>
Re: [Spacewalk-list] Optimizing postgresql with Spacewalk 1.5 on CentOS 6.0
x86_64<o:p></o:p></SPAN></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal>Thanks for sharing your experience. I don't experience
problem so I think I'll keep with the default params.<o:p></o:p></P>
<DIV>
<P class=MsoNormal>Could someone from spacewalk team confirm the optimization
possibilities?<o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal>Another point about the autovacuum function: BZ 705935
address the fact that vacuum is useless. Is it still a valid
BZ?<o:p></o:p></P></DIV>
<DIV><PRE><SPAN style="FONT-SIZE: 13.5pt; BACKGROUND: white"><o:p> </o:p></SPAN></PRE><PRE><SPAN style="FONT-SIZE: 13.5pt; BACKGROUND: white">Description of problem:<o:p></o:p></SPAN></PRE><PRE><SPAN style="FONT-SIZE: 13.5pt; BACKGROUND: white">Seems like Spacewalk is keeping database transactions open which leads to<o:p></o:p></SPAN></PRE><PRE><SPAN style="FONT-SIZE: 13.5pt; BACKGROUND: white">unremovable dead rows in tables.<o:p></o:p></SPAN></PRE></DIV>
<DIV>
<P class=MsoNormal><o:p> </o:p></P></DIV>
<DIV>
<P class=MsoNormal>Thanks,<o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><o:p> </o:p></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt">Pierre<o:p></o:p></P>
<DIV>
<P class=MsoNormal>2011/9/14 Scott Worthington <<A
href="mailto:scott.c.worthington@gmail.com">scott.c.worthington@gmail.com</A>><o:p></o:p></P>
<P class=MsoNormal>Hello,<BR><BR>I'd like to share my experience with
out-of-the-box installation of<BR>Spacewalk 1.5 on CentOS 6.0 x86_64.<BR><BR>The
out-of-the-box default configuration of postgresql is abysmal.<BR>You may be
very familiar with MySQL, but postgresql is certainly not<BR>MySQL (install and
forget). You _HAVE_ to make configuration changes<BR>and tune postgresql for
optimum performance with Spacewalk.<BR><BR>While reading this, please note that
this is for the CentOS 6.0 x86_64<BR>platform. I cannot speak for Ubuntu,
Debian, or other CentOS<BR>releases, etc.<BR><BR>In my installation, I am using
4GB of server RAM as a virtualized<BR>guest on VMware. I have NOT changed
Java or Tomcat's memory heap<BR>settings (-Xms or -Xmx) and I don't believe it
is necessary. Your<BR>slowness is due to postgresql.<BR><BR>* Before
making the following changes, shutdown Spacewalk and then<BR>Postgresql.
Better yet, run Spacewalk on VMware and take a snapshot<BR>of your current
setup for rollbacks (if you're like me, you've spent<BR>hours upon hours loading
in Channels and their repo data).<BR><BR>spacewalk-service stop<BR>service
postgresql stop<BR><BR>== Default postgresql configuration ==<BR><BR>Auto vacuum
is NOT enabled. This causes the database server to crawl<BR>to a halt
during the loading of thousands of RPMs via the "rhnpush"<BR>CLI and also
through "spacewalk-repo-syncs --type yum".<BR><BR>* Turn on
autovacuum<BR><BR>Edit your /var/lib/pgsql/data/postgresql.conf and
find...<BR><BR>#autovacuum=on<BR><BR>...and uncomment
it:<BR><BR>autovacuum=on<BR><BR>In the same file, find...<BR><BR>#track_counts =
on<BR><BR>...and uncomment it:<BR><BR>track_counts = on<BR><BR>== Install pgtune
==<BR><BR>Next, tune postgresql using the pgtune package. I have found that
the<BR>package pgtune is excellent for tuning your system. The
following<BR>instructions have you download the .tar.gz file and create
an<BR>installable RPM. I prefer install RPMs, not source. You can
install<BR>source, that is your prerogative.<BR><BR>* Download and compile
pgtune (to install it as an RPM):<BR><BR>* First, ensure you have rpmbuild
installed<BR><BR>yum install rpmbuild<BR><BR># next, download, put files in the
correct directories, and build<BR>mkdir -p /usr/local/src/TARGZs/pgtune<BR>cd
/usr/local/src/TARGZs/pgtune<BR>wget <A
href="http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz"
target=_blank>http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz</A><BR>tar
xvfz pgtune-0.9.3.tar.gz<BR>cp /usr/local/src/TARGZs/pgtune/pgtune-0.9.3.tar.gz
/root/rpmbuild/SOURCES<BR>cp
/usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune.spec /root/rpmbuild/SPECS<BR>cp
/usr/local/src/TARGZs/pgtune/pgtune-0.9.3/pgtune-settingsdir.patch<BR>/root/rpmbuild/SOURCES<BR>cd
/root/rpmbuild/SPECS<BR>#the spec file has an incorrect version number, the
following command<BR>will fix it<BR>perl -npe 's/0\.9\.2/0.9.3/g' -i
/root/rpmbuild/SPECS/pgtune.spec<BR>rpmbuild -ba pgtune.spec<BR>#If your
rpmbuild fails, you may need to install dependencies, look at<BR>the output of
the rpmbuild<BR><BR>#install the RPM<BR>rpm -ivh
/root/rpmbuild/RPMS/noarch/pgtune-0.9.3-1.el6.noarch.rpm<BR><BR>After pgtune is
installed, run it on your postgresql.conf file:<BR><BR>cd
/var/lib/pgsql/data<BR>pgtune -i postgresql.conf -o
postgresql.conf.pgtune<BR><BR>* Make a backup of your existing, "working"
configuration (replace<BR>YYYYMMDD with today's date)<BR><BR>cp -p
postgresql.conf postgresql.conf-working-YYYYMMDD<BR><BR>* Replace existing
config<BR><BR>cp postgresql.conf.pgtune postgresql.conf<BR><BR>* I do not
believe that the postgresql.conf file contains the<BR>"default" parameters in
the lines that are commented out (unlike<BR>packages like postfix). I
recommend that you do not trust the<BR>postgresql.conf default file as a source
of "default" parameters.<BR><BR>* I have also edited by hand and uncommented the
entire AUTOVACUUM<BR>PARAMETERS section and increased the autovacuum_max_workers
from 3
to<BR>5:<BR><BR>#------------------------------------------------------------------------------<BR>#
AUTOVACUUM
PARAMETERS<BR>#------------------------------------------------------------------------------<BR><BR>autovacuum
= on # Enable autovacuum
subprocess? 'on'<BR>
# requires track_counts to also be
on.<BR>#log_autovacuum_min_duration = -1 # -1 disables, 0
logs all actions and<BR>
# their durations, > 0 logs only<BR>
# actions running at least this number<BR>
# of
milliseconds.<BR>autovacuum_max_workers = 5
# max number of autovacuum subprocesses<BR>autovacuum_naptime =
1min # time between autovacuum
runs<BR>autovacuum_vacuum_threshold = 50 # min number
of row updates before<BR>
# vacuum<BR>autovacuum_analyze_threshold = 50 # min
number of row updates before<BR>
# analyze<BR>autovacuum_vacuum_scale_factor = 0.2 #
fraction of table size before vacuum<BR>autovacuum_analyze_scale_factor = 0.1
# fraction of table size before analyze<BR>autovacuum_freeze_max_age =
200000000 # maximum XID age before forced vacuum<BR>
# (change requires
restart)<BR>autovacuum_vacuum_cost_delay = 20ms # default vacuum
cost delay for<BR>
#
autovacuum, in milliseconds;<BR>
# -1 means use vacuum_cost_delay<BR>autovacuum_vacuum_cost_limit =
-1 # default vacuum cost limit for<BR>
# autovacuum, -1 means use<BR>
#
vacuum_cost_limit<BR><BR>* After you have made the above changes, attempt to
restart postgresql<BR>(it will most likely fail)<BR><BR>service postgresql
restart<BR><BR>* Yes, it will FAIL. Why did it fail? Most likely
postgresql needs<BR>more memory now and the default kernel memory setup is too
low<BR>(Hopefully you didn't introduce a typo)<BR><BR>* Examine the contents of
postgresql's startup log:<BR><BR>less /var/lib/pgsql/pgstartup.log<BR><BR>* You
may see something like this at the end:<BR><BR>FATAL: could not create
shared memory segment: Invalid argument<BR>DETAIL: Failed system call was
shmget(key=5432001, size=1040752640, 03600).<BR>HINT: This error usually
means that PostgreSQL's request for a shared<BR>memory segment exceeded your
kernel's SHMMAX parameter. You can<BR>either reduce the request size or
reconfigure the kernel with larger<BR>SHMMAX. To reduce the request size
(currently 1040752640 bytes),<BR>reduce PostgreSQL's shared_buffers parameter
(currently 122880) and/or<BR>its max_connections parameter (currently
85).<BR> If the request size is already small, it's
possible that it is<BR>less than your kernel's SHMMIN parameter, in which case
raising the<BR>request size or reconfiguring SHMMIN is called for.<BR>
The PostgreSQL documentation contains more information
about<BR>shared memory configuration.<BR><BR>* You have to bump up your Linux
kernel.shmmax to the number, in the<BR>example above, the number is
"1040752640"<BR><BR>* First, non-persistently:<BR><BR>sysctl -w
kernel.shmmax=1040752640<BR><BR>* Try to restart postgresql<BR><BR>service
postgresql restart<BR><BR>* If it fails, look again inside of
/var/lib/pgsql/pgstartup.log and<BR>see if you made a mistake with the shmmax
number and fix your<BR>kernel.shmmax<BR><BR>* Finally, make the number that you
ended up using for kernel.shmmax<BR>and make it persistent between boots:<BR>cat
>> /etc/sysctl.conf << 'END_OF_FILE'<BR>#YYYY-MM-DD HHMM
(your-initials)<BR>kernel.shmmax=1040752640<BR>END_OF_FILE<BR><BR>I have found
that making those changes above makes postgresql and<BR>Spacewalk usable and
much much faster. I have not experienced the<BR>"500 server timeout" error
since.<BR><BR>If you are having problems with loading packages into your
Spacewalk,<BR>running top will usually show postgresql is sitting on a "DELETE"
or<BR>other process that is taking forever -- the above may help speed
up<BR>your postgresql.<BR><BR>I hope you have the best of luck with your
Spacewalk & postgresql<BR>deployment on CentOS 6.0 x86_64, and I hope the
above instructions may<BR>benefit
you.<BR><BR>_______________________________________________<BR>Spacewalk-list
mailing list<BR><A
href="mailto:Spacewalk-list@redhat.com">Spacewalk-list@redhat.com</A><BR><A
href="https://www.redhat.com/mailman/listinfo/spacewalk-list"
target=_blank>https://www.redhat.com/mailman/listinfo/spacewalk-list</A><o:p></o:p></P></DIV>
<P class=MsoNormal><o:p> </o:p></P></DIV></DIV>
<font bgcolor="#ffffff" color="#000000"><H5><BR><HR>
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.
<BR><HR></H5></font>
</BODY></HTML>