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]<mailto:[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]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Anton Pritchard-Meaker Sent: 10 April 2013 15:17 To: [email protected]<mailto:[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]> [mailto:[email protected]] On Behalf Of Paul Robert Marino Sent: 10 April 2013 15:03 To: [email protected]<mailto:[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]<mailto:[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]<mailto:[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<http://www.kitd.com/> | The Future of Television _______________________________________________ Spacewalk-list mailing list [email protected]<mailto:[email protected]> https://www.redhat.com/mailman/listinfo/spacewalk-list _______________________________________________ Spacewalk-list mailing list [email protected]<mailto:[email protected]> https://www.redhat.com/mailman/listinfo/spacewalk-list
_______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
