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 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. > > > > > > > > > > > > On Wed, Apr 10, 2013 at 3:20 PM, Jon Miller <[email protected]> wrote: > >> Perhaps routine Postgres maintenance was missing? I'm not proclaiming to >> be a Postgresql expert but do recall that periodic vacuuming[1] of the >> database is required / recommended? Your act of dropping and recreating >> sounds like side stepping what could have been accomplished via >> maintenance. >> >> [1]: http://wiki.postgresql.org/wiki/VACUUM_FULL >> >> -- Jon Miller >> >> >> On Wed, Apr 10, 2013 at 8:45 AM, Anton Pritchard-Meaker < >> [email protected]> wrote: >> >>> I managed to fix this by exporting the database, dropping it in >>> postgresql, re-creating it and then re-importing. Performance seems much >>> better on the script too. Call times have halved!**** >>> >>> **** >>> >>> *From:* [email protected] [mailto: >>> [email protected]] *On Behalf Of *Anton Pritchard-Meaker >>> *Sent:* 10 April 2013 15:17 >>> >>> *To:* [email protected] >>> *Subject:* Re: [Spacewalk-list] API calls for new hosts**** >>> >>> **** >>> >>> This script works nicely for existing hosts, the problem only occurs >>> when I subscribe a new host and run the script/call. As an example, an >>> existing host with 36 updates available takes 4.7 seconds to get a result >>> from Spacewalk whereas the new host below is taking 406.**** >>> >>> **** >>> >>> *From:* [email protected] [ >>> mailto:[email protected]<[email protected]>] >>> *On Behalf Of *Paul Robert Marino >>> *Sent:* 10 April 2013 15:03 >>> *To:* [email protected] >>> *Subject:* Re: [Spacewalk-list] API calls for new hosts**** >>> >>> **** >>> >>> It means your client timed out the connection. >>> I assume this is Perl if so than you need to look at LWPs documentation >>> to tune the time out parameter. >>> >>> **** >>> >>> -- Sent from my HP Pre3**** >>> >>> **** >>> ------------------------------ >>> >>> On Apr 10, 2013 9:52 AM, Anton Pritchard-Meaker < >>> [email protected]> wrote: **** >>> >>> Not too sure why, but a fresh request has generated the following log >>> entries, but still no output and a 500 timeout returned:**** >>> >>> **** >>> >>> Api logs:**** >>> >>> **** >>> >>> [2013-04-10 14:20:02,951] INFO - REQUESTED FROM: 192.168.131.146 CALL: >>> system.listLatestUpgradablePackages(4267x7b6a0781772f903417626f29664317c0, >>> 1000010137) CALLER: (******) TIME: 406.117 seconds**** >>> >>> **** >>> >>> Tomcat:**** >>> >>> **** >>> >>> 10-Apr-2013 14:20:02 org.apache.jk.core.MsgContext action**** >>> >>> WARNING: Error sending end packet**** >>> >>> java.net.SocketException: Broken pipe**** >>> >>> at java.net.SocketOutputStream.socketWrite0(Native Method)**** >>> >>> at >>> java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)**** >>> >>> at java.net.SocketOutputStream.write(SocketOutputStream.java:153) >>> **** >>> >>> at >>> org.apache.jk.common.ChannelSocket.send(ChannelSocket.java:538)**** >>> >>> at >>> org.apache.jk.common.JkInputStream.endMessage(JkInputStream.java:127)*** >>> * >>> >>> at org.apache.jk.core.MsgContext.action(MsgContext.java:302)**** >>> >>> at org.apache.coyote.Response.action(Response.java:183)**** >>> >>> at org.apache.coyote.Response.finish(Response.java:305)**** >>> >>> at >>> org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:205)*** >>> * >>> >>> at >>> org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)**** >>> >>> at >>> org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:775)**** >>> >>> at >>> org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:704) >>> **** >>> >>> at >>> org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:897) >>> **** >>> >>> at >>> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685) >>> **** >>> >>> at java.lang.Thread.run(Thread.java:679)**** >>> >>> 10-Apr-2013 14:20:02 org.apache.jk.common.ChannelSocket processConnection >>> **** >>> >>> WARNING: processCallbacks status 2**** >>> >>> **** >>> >>> Are there any other logs I could have a look at?**** >>> >>> **** >>> >>> **** >>> >>> *From:* Anton Pritchard-Meaker >>> *Sent:* 09 April 2013 16:42 >>> *To:* [email protected] >>> *Subject:* API calls for new hosts**** >>> >>> **** >>> >>> Hi,**** >>> >>> **** >>> >>> This one was happening in 1.8 and 1.9 on my RHEL5 based Spacewalk >>> installation. I have a script to prepare update details for audit that >>> works nicely with hosts already registered, but newly registered ones don’t >>> work – returns a 500 timeout:**** >>> >>> **** >>> >>> my $systems = $client->call('system.searchByName', $session, $search); * >>> *** >>> >>> **** >>> >>> returns the name ok:**** >>> >>> **** >>> >>> [2013-04-09 16:23:52,714] INFO - REQUESTED FROM: 192.168.131.146 CALL: >>> system.searchByName(4251x1ef1784c1d24abde4de1b183305f7458, *******) CALLER: >>> (******) TIME: 0.351 seconds**** >>> >>> **** >>> >>> my $kernel = $client->call('system.getRunningKernel', $session, >>> $system->{'id'});**** >>> >>> **** >>> >>> returns the kernel ok:**** >>> >>> **** >>> >>> [2013-04-09 16:23:52,738] INFO - REQUESTED FROM: 192.168.131.146 CALL: >>> system.getRunningKernel(4251x1ef1784c1d24abde4de1b183305f7458, 1000010137) >>> CALLER: (******) TIME: 0.014 seconds**** >>> >>> **** >>> >>> The next call is **** >>> >>> **** >>> >>> my $packages = $client->call('system.listLatestUpgradablePackages', >>> $session, $system->{'id'});**** >>> >>> **** >>> >>> At this point I get a “500 read timeout”.**** >>> >>> **** >>> >>> All details are returning fine via the GUI. The script is here - >>> http://hastebin.com/tesovipoki.pl **** >>> >>> **** >>> >>> Any suggestions would be great – I don’t get anything in the tomcat logs >>> unfortunately.**** >>> >>> **** >>> >>> Cheers, **** >>> >>> **** >>> >>> *Anton Pritchard-Meaker* | Unix Engineer**** >>> >>> *KIT digital** *| York | www.kitd.com | The Future of Television**** >>> >>> **** >>> >>> _______________________________________________ >>> Spacewalk-list mailing list >>> [email protected] >>> https://www.redhat.com/mailman/listinfo/spacewalk-list >>> >> >> >> _______________________________________________ >> Spacewalk-list mailing list >> [email protected] >> https://www.redhat.com/mailman/listinfo/spacewalk-list >> > > > _______________________________________________ > Spacewalk-list mailing list > [email protected] > https://www.redhat.com/mailman/listinfo/spacewalk-list >
_______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
