Thanks a lot!
2013/4/11 Jeremy Maes <[email protected]> > 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 <[email protected]> > >> 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:* [email protected] [ >> [email protected]] on behalf of Paul Robert Marino [ >> [email protected]] >> *Sent:* 10 April 2013 22:39 >> >> *To:* [email protected] >> *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 [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
