[Spacewalk-list] API calls for new hosts

Wed Apr 10 21:39:20 UTC 2013

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 <jonebird at gmail.com> 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 <
> anton.pritchard-meaker at kit-digital.com> 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:* spacewalk-list-bounces at redhat.com [mailto:
>> spacewalk-list-bounces at redhat.com] *On Behalf Of *Anton Pritchard-Meaker
>> *Sent:* 10 April 2013 15:17
>>
>> *To:* spacewalk-list at redhat.com
>> *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:* spacewalk-list-bounces at redhat.com [
>> mailto:spacewalk-list-bounces at redhat.com<spacewalk-list-bounces at redhat.com>]
>> *On Behalf Of *Paul Robert Marino
>> *Sent:* 10 April 2013 15:03
>> *To:* spacewalk-list at redhat.com
>> *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 <
>> anton.pritchard-meaker at kit-digital.com> 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:* spacewalk-list at redhat.com
>> *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
>> 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/20130410/d7e1de2e/attachment.htm>