<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Op 11/04/2013 10:25, Pierre Casenove
schreef:<br>
</div>
<blockquote
cite="mid:CAALa77=ZFgdiHfCSjxLHeSvdyvRpE5+YG_3Mb=5aCbQYPc_RNg@mail.gmail.com"
type="cite">
<div dir="ltr">Hello,
<div style="">I have a setup with postgresql 8.4 and 150
clients.</div>
<div style="">My DB dump (using pg_dump, with -Fc option) is
about 1.1 GB.</div>
<div style="">I still haven't noticed slower operations.</div>
<div style="">Should I planify VACUUM ANALYZE operations once a
month? Could it lower the size of the dump?</div>
<div style="">Would the script be like:</div>
<div style="">spacewalk-service stop</div>
<div style="">su --command "psql -c 'VACUUM VERBOSE ANALYZE;' -d
spaceschema" postgres<br>
</div>
</div>
</blockquote>
<blockquote
cite="mid:CAALa77=ZFgdiHfCSjxLHeSvdyvRpE5+YG_3Mb=5aCbQYPc_RNg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div style="">spacewalk-service start<br>
</div>
<div style="">?</div>
<div style=""><br>
</div>
<div style="">Thanks in advance for your help,</div>
<div style=""><br>
</div>
<div style="">Pierre</div>
<div style=""><br>
</div>
</div>
<div class="gmail_extra">
<br>
</div>
</blockquote>
On CentOS 6 there's also a command /usr/bin/<b>vacuumdb </b>available
with a bunch of options that basically does that without you having
the know the exact SQL commands. (for 8.4 at least, don't know about
9+)<br>
<br>
You can run the basic (lazy) vacuum while spacewalk is running, I
run it daily in my database dump script before the dump itself.
(/usr/bin/vacuumdb --analyze -h localhost -U postgres spaceschema,
needs a .pgpass file to work if you don't add the password to the
command) My gzipped database is about 300M now for ~50 clients and
~15.500 packages in repos. Can't say if it has a big impact on size
as I configured it this way when I set up the database and I've no
further postgres experience.<br>
<br>
For the full vacuum you'd stop spacewalk and add --full. (Or add
"FULL" to the sql command you mentioned)<br>
<br>
Regards,<br>
Jeremy<br>
<br>
<blockquote
cite="mid:CAALa77=ZFgdiHfCSjxLHeSvdyvRpE5+YG_3Mb=5aCbQYPc_RNg@mail.gmail.com"
type="cite">
<div class="gmail_extra"><br>
<div class="gmail_quote">2013/4/11 Anton Pritchard-Meaker <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:anton.pritchard-meaker@kit-digital.com"
target="_blank">anton.pritchard-meaker@kit-digital.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div
style="direction:ltr;font-size:10pt;font-family:Tahoma">
<p>Thanks I really appreciate this, I'll definitely look
into these actions. Downtime is not an issue for my
Spacewalk implementation.</p>
<p> </p>
<p>I'm pretty new to PostreSQL, so I was completely
unaware of all of maintenance tools available which
actually sound quite necessary.</p>
<p> </p>
<div>
<p> </p>
<div><font><span style="FONT-SIZE:10pt">
<div>Anton Pritchard-Meaker | Unix Engineer<br>
<br>
</div>
</span></font></div>
</div>
<div style="font-size:16px;font-family:Times New Roman">
<hr>
<div style="DIRECTION:ltr"><font face="Tahoma"
color="#000000"><b>From:</b> <a
moz-do-not-send="true"
href="mailto:spacewalk-list-bounces@redhat.com"
target="_blank">spacewalk-list-bounces@redhat.com</a>
[<a moz-do-not-send="true"
href="mailto:spacewalk-list-bounces@redhat.com"
target="_blank">spacewalk-list-bounces@redhat.com</a>]
on behalf of Paul Robert Marino [<a
moz-do-not-send="true"
href="mailto:prmarino1@gmail.com"
target="_blank">prmarino1@gmail.com</a>]<br>
<b>Sent:</b> 10 April 2013 22:39
<div>
<div class="h5"><br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:spacewalk-list@redhat.com"
target="_blank">spacewalk-list@redhat.com</a><br>
<b>Subject:</b> Re: [Spacewalk-list] API calls
for new hosts<br>
</div>
</div>
</font><br>
</div>
<div>
<div class="h5">
<div>
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>In PostgreSQL 8.x auto vacuuming
was first being introduced and the
default settings weren't Ideal.
Further more it wasn't a complete
implementation so standard vacuuming
is still necessary in 8.x<br>
<br>
</div>
In PostgreSQL 9.x auto vacuuming
matured quite a bit a and manual
vacuuming is needed far less often,
but still a good idea to do
occasionally.<br>
<br>
</div>
There are two kinds of vacuuming a lazy
vacuum and a full vacuum.<br>
</div>
There are also two other table maintenance
task which need to be done periodically as
well.
<br>
</div>
<div><br>
A lazy vacuum does not require an
exclusive table lock so in many cases may
be executed while the database is actively
in use; however there tend to be tables in
spacewalk that constantly have lock which
may hang the process so its best to
schedule occasional downtime for this
operation. The good new is if you do it on
a regular basis a lazy vacuum is quick. In
addition in PostgreSQL 9.x the auto vacuum
process fairly effectively
opportunistically tries to do this for you
as needed with as little impact as
possible.<br>
<br>
</div>
A full vacuum requires an exclusive table
lock but does a few things a lazy vacuum
can't. The first thing it does is it
flattens the MVCC ( MVCC is version control
for rows it provides rollback capabilities
and allows long running queries to complete
without the results being tainted by data
added or deleted after the long running
query was started). the MVCC needs to be
occasionally flattened on high volume tables
to prevent the version numbers from wrapping
around (which can potentially cause a sort
of data corruption); however this is rare
and may databases run for years without
having to worry about this. The major
advantage is that a Full vacuum can reclaim
all of the disk space being used by old row
versions. the lazy vacuum can only mark the
space into a pool for recycling (Oracle had
the same thing literally called it the trash
bin last time I worked with it) unless they
are at the end of the last table file, also
in PostgreSQL 8.x the developers realized
the maximum size limit of recycle pool was
too small for modern databases so it was
increased significantly in 9.x.<br>
<br>
<br>
</div>
<div>NOTE: a dump and load has the same effect
as a full vacuum<br>
<br>
</div>
<div>ANALYZE<br>
<br>
</div>
<div>Analyzing updates your table statistics.
the statistics are used by the query
planner. what the query planner does is it
takes the queries you run on the tables and
re-optimizes them based on the table
structure, the fragmentation level of the
table, the types of sorts, filters the query
has, the indexes available and how efficient
they, are more. the statistics tell the
planer how efficient different types of
operations are based on a series of test
queries it executed the last time they were
updated.<br>
</div>
<div>Analyzing is a non blocking operation
however just like lazy vacuuming it can get
hung up by other queries from spacewalk
indefinitely, so its best to do it
occasionally with spacewalk offline.<br>
</div>
<div>Analyzing can be done as part of a vacuum
or independently. If done independently you
can control it to the level where you can
even tell it just to analyze a specific
column; however its usually best to do an
analyze with a vacuum for most people, only
very experienced DBAs should consider doing
more advanced versions of the ANALYZE
command .<br>
<br>
NOTE: a dump and load does not do an ANALYZE
on the tables.<br>
<br>
<br>
</div>
<div>REINDEX<br>
<br>
</div>
<div>Vacuuming cleans up the table but not
cleanup, defragment, or resort the indexes
so it is important to at least once a year
do a REINDEX on standard indexes to maintain
performance, and more often for ordered
indexes. A REINDEX can not be done as part
of a vacuum it is an independent operation.
A REINDEX is an exclusive locking operation
and as such can not be done at the same time
as any thing else is accessing the table, as
such spacewalk should be offline during this
operation. reindexing is the slowest
maintenance operation and should only be
done after a full vacuum. You should also do
an ANALYZE after a REINDEX.<br>
<br>
</div>
<div>NOTE: a dump and load has the same effect
as a REINDEX.<br>
<br>
<br>
</div>
<div>All of these operations are at the table
level except the ANALYZE which may be done
down to the column level. a REINDEX can also
be done in the specific index level I think;
however its usually most efficient to do the
whole table at once unless you have an
unusually large table.<br>
<br>
</div>
<div>Finally there are command line tools for
vaccum and reindex that can operate by
sequentially cycling through the tables in
the database; however if your disks ram and
CPU can handle it you can run these
operations in parallel on different tables
to speed things up via multiple SQL
connections.<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
<p>**** DISCLAIMER ****<br><a href="http://www.schaubroeck.be/maildisclaimer.htm">http://www.schaubroeck.be/maildisclaimer.htm</a></p>
</body>
</html>