[Spacewalk-list] API calls for new hosts

Thu Apr 11 18:49:23 UTC 2013

Hello,
Thanks for the info. I'll modify my maintenance script to add these
commands.
Should I call reindexdb after vacuumdb command?

To the spacewalk team: wouldn't it be useful to add a wiki page around
postgresql: pgtune, backup/restore, vacuumdb and reindexdb commands?

Pierre

2013/4/11 Paul Robert Marino <prmarino1 at gmail.com>

> the vacuumdb command and reindexdb command both come with PostgreSQL and
> are not vendor specific.
>
> those art the commands I was alluding to when I said this
> "
> Finally there are command line tools for vacuum 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.
> "
>
>
>
> On Thu, Apr 11, 2013 at 11:33 AM, Anton Pritchard-Meaker <
> anton.pritchard-meaker at kit-digital.com> wrote:
>
>>  Cheers – also available in RHEL5.****
>>
>> ** **
>>
>> *From:* spacewalk-list-bounces at redhat.com [mailto:
>> spacewalk-list-bounces at redhat.com] *On Behalf Of *Pierre Casenove
>> *Sent:* 11 April 2013 15:40
>>
>> *To:* spacewalk-list at redhat.com
>> *Subject:* Re: [Spacewalk-list] API calls for new hosts****
>>
>> ** **
>>
>> Thanks a lot!****
>>
>> ** **
>>
>> 2013/4/11 Jeremy Maes <jma at schaubroeck.be>****
>>
>> Op 11/04/2013 10:25, Pierre Casenove schreef:****
>>
>>  Hello, ****
>>
>> I have a setup with postgresql 8.4 and 150 clients.****
>>
>> My DB dump (using pg_dump, with -Fc option) is about 1.1 GB.****
>>
>> I still haven't noticed slower operations.****
>>
>> Should I planify VACUUM ANALYZE operations once a month? Could it lower
>> the size of the dump?****
>>
>> Would the script be like:****
>>
>> spacewalk-service stop****
>>
>> su --command "psql -c 'VACUUM VERBOSE ANALYZE;' -d spaceschema" postgres*
>> ***
>>
>>   spacewalk-service start****
>>
>> ?****
>>
>> ** **
>>
>> Thanks in advance for your help,****
>>
>> ** **
>>
>> Pierre****
>>
>> ** **
>>
>> ** **
>>
>>  On CentOS 6 there's also a command /usr/bin/*vacuumdb * 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+)
>>
>> 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.
>>
>> For the full vacuum you'd stop spacewalk and add --full. (Or add "FULL"
>> to the sql command you mentioned)
>>
>> Regards,
>> Jeremy****
>>
>>
>>
>>
>> ****
>>
>> ** **
>>
>> 2013/4/11 Anton Pritchard-Meaker <anton.pritchard-meaker at kit-digital.com>
>> ****
>>
>> Thanks I really appreciate this, I'll definitely look into these actions.
>> Downtime is not an issue for my Spacewalk implementation.****
>>
>>  ****
>>
>> I'm pretty new to PostreSQL, so I was completely unaware of all of
>> maintenance tools available which actually sound quite necessary.****
>>
>>  ****
>>
>>  ****
>>
>> Anton Pritchard-Meaker | Unix Engineer****
>>    ------------------------------
>>
>> *From:* spacewalk-list-bounces at redhat.com [
>> spacewalk-list-bounces at redhat.com] on behalf of Paul Robert Marino [
>> prmarino1 at gmail.com]
>> *Sent:* 10 April 2013 22:39 ****
>>
>>
>> *To:* spacewalk-list at redhat.com
>> *Subject:* Re: [Spacewalk-list] API calls for new hosts****
>>
>> ** **
>>
>> 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****
>>
>> 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.****
>>
>> There are two kinds of vacuuming a lazy vacuum and a full vacuum.****
>>
>> There are also two other table maintenance task which need to be done
>> periodically as well. ****
>>
>>
>> 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.****
>>
>> 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.
>>
>> ****
>>
>> NOTE: a dump and load has the same effect as a full vacuum****
>>
>> ANALYZE****
>>
>> 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.****
>>
>> 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.****
>>
>> 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 .
>>
>> NOTE: a dump and load does not do an ANALYZE on the tables.
>>
>> ****
>>
>> REINDEX****
>>
>> 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.****
>>
>> NOTE: a dump and load has the same effect as a REINDEX.
>>
>> ****
>>
>> 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.****
>>
>> 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.****
>>
>> **** DISCLAIMER ****
>> http://www.schaubroeck.be/maildisclaimer.htm****
>>
>> ** **
>>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>>
>
>
> _______________________________________________
> 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/20130411/7211f17f/attachment.htm>