Thread: Manual vacs 5x faster than autovacs?

Manual vacs 5x faster than autovacs?

From
Wayne Beaver
Date:
Hi All,

Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I
know that's old). I have seen *really* long-running autovacs eating up
system resources. While the below is not an example of *really* long,
it shows how I killed an autovac which had been running for more than
10 minutes, then ran a VAC FULL ANALYZE on same exact table in about
~2 min. Any wisdom here? Attributable to autovac_worker settings? Or
Pg version? Other?

Any insight appreciated.

wb

++++++++++++++++++++++++++

$ psql template1 -c "SELECT procpid, current_query, to_char (now() -
backend_start, 'HH24:MI:SS') AS connected_et, to_char (now() -
query_start,'HH24:MI:SS') AS query_et FROM pg_stat_activity WHERE
datname='mydb' ORDER BY query_et DESC LIMIT 1"

  procpid |                   current_query            | connected_et
| query_et
---------+--------------------------------------------+--------------+----------
     9064 | autovacuum: VACUUM ANALYZE myschema.mytable    | 00:12:07
    | 00:11:38



$ kill 9064


$ date; psql mydb -c "VACUUM FULL ANALYZE myschema.mytable"; date
Wed Nov 11 17:25:41 UTC 2009
VACUUM
Wed Nov 11 17:27:59 UTC 2009

Re: Manual vacs 5x faster than autovacs?

From
Tom Lane
Date:
Wayne Beaver <wayne@acedsl.com> writes:
> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I
> know that's old). I have seen *really* long-running autovacs eating up
> system resources. While the below is not an example of *really* long,
> it shows how I killed an autovac which had been running for more than
> 10 minutes, then ran a VAC FULL ANALYZE on same exact table in about
> ~2 min. Any wisdom here? Attributable to autovac_worker settings?

autovacuum_vacuum_cost_delay.  Is the slow autovac *really* eating
a noticeable amount of system resources?  I would think that a full
speed manual vacuum would be a lot worse.

            regards, tom lane

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Thu, Nov 12, 2009 at 7:33 AM, Wayne Beaver <wayne@acedsl.com> wrote:
> Hi All,
>
> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I know
> that's old). I have seen *really* long-running autovacs eating up system
> resources. While the below is not an example of *really* long, it shows how
> I killed an autovac which had been running for more than 10 minutes, then
> ran a VAC FULL ANALYZE on same exact table in about ~2 min. Any wisdom here?
> Attributable to autovac_worker settings? Or Pg version? Other?
>
> Any insight appreciated.

Autovac running slow is (generally) a good thing.  It reduces the load
on your IO subsystem so that other queries can still run fast.  What
resources are your long running autovacs eating up.  If top shows
500Mres and 499Mshr, then don't worry, it's not actually eating up
resources.

Re: Manual vacs 5x faster than autovacs?

From
Wayne Beaver
Date:
Hmm, looks like I've been myth-busted here.

$ top | grep -E '29343|31924|29840|PID'; echo
   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29840 postgres  15   0 2150m 203m 194m S    0  2.5   0:00.59 postmaster
29343 postgres  15   0 2137m 360m 356m S    1  4.5   0:00.92 postmaster
31924 postgres  15   0 2135m  73m  70m S    1  0.9   0:00.44 postmaster

So my claims of resource-usage appear incorrect.

I'd seen autovacs running for hours and had mis-attributed this to
growing query times on those tables  - my thought was that "shrinking"
the tables "more quickly" could make them "more-optimized", more
often. Sounds like I could be chasing the wrong symptoms, though.

wb


> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>
> Autovac running slow is (generally) a good thing.  It reduces the load
> on your IO subsystem so that other queries can still run fast.  What
> resources are your long running autovacs eating up.  If top shows
> 500Mres and 499Mshr, then don't worry, it's not actually eating up
> resources.


> Quoting Tom Lane <tgl@sss.pgh.pa.us>:
>
> autovacuum_vacuum_cost_delay.  Is the slow autovac *really* eating
> a noticeable amount of system resources?  I would think that a full
> speed manual vacuum would be a lot worse.


>> Wayne Beaver <wayne@acedsl.com> writes:
>>
>> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I
>> know that's old). I have seen *really* long-running autovacs eating up
>> system resources. While the below is not an example of *really* long,
>> it shows how I killed an autovac which had been running for more than
>> 10 minutes, then ran a VAC FULL ANALYZE on same exact table in about
>> ~2 min. Any wisdom here? Attributable to autovac_worker settings?


Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
> Hmm, looks like I've been myth-busted here.
>
> $ top | grep -E '29343|31924|29840|PID'; echo
>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 29840 postgres  15   0 2150m 203m 194m S    0  2.5   0:00.59 postmaster
> 29343 postgres  15   0 2137m 360m 356m S    1  4.5   0:00.92 postmaster
> 31924 postgres  15   0 2135m  73m  70m S    1  0.9   0:00.44 postmaster
>
> So my claims of resource-usage appear incorrect.
>
> I'd seen autovacs running for hours and had mis-attributed this to growing
> query times on those tables  - my thought was that "shrinking" the tables
> "more quickly" could make them "more-optimized", more often. Sounds like I
> could be chasing the wrong symptoms, though.

Now it is quite possible that a slow autovac is causing your queries
to run slower.  And it's that autovac isn't keeping up.  One of the
verious serious shortcomings of autovac in 8.1 (or was it 8.0?  I
think it was 8.1 as well) was that it only had one worker thread.  So,
if it has a moderate to high cost delay, then it might be able to keep
up with the job and your tables will become bloated.

The problem isn't that autovac is stealing too many resources, it's
that it's not stealing enough.

The first quick fix is 8.3 which has more efficient vacuuming code and
the ability to run > 1 thread (it defaults to 3) so you can still keep
it "detuned" to stay out of the way, but with enough threads it can
hopefully keep up.

Of course, eventually you reach the point where as the work load rises
the ability of autovac to keep up is lost, and then you need more IO
period.  Whether pgsql or any other database, running out of io
bandwidth is only really solvable by more IO bandwidth.

So, what does iostat -x 10

say about utilization?

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Thu, Nov 12, 2009 at 9:33 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>> Hmm, looks like I've been myth-busted here.
>>
>> $ top | grep -E '29343|31924|29840|PID'; echo
>>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>> 29840 postgres  15   0 2150m 203m 194m S    0  2.5   0:00.59 postmaster
>> 29343 postgres  15   0 2137m 360m 356m S    1  4.5   0:00.92 postmaster
>> 31924 postgres  15   0 2135m  73m  70m S    1  0.9   0:00.44 postmaster
>>
>> So my claims of resource-usage appear incorrect.
>>
>> I'd seen autovacs running for hours and had mis-attributed this to growing
>> query times on those tables  - my thought was that "shrinking" the tables
>> "more quickly" could make them "more-optimized", more often. Sounds like I
>> could be chasing the wrong symptoms, though.
>
> Now it is quite possible that a slow autovac is causing your queries
> to run slower.  And it's that autovac isn't keeping up.  One of the
> verious serious shortcomings of autovac in 8.1 (or was it 8.0?  I
> think it was 8.1 as well) was that it only had one worker thread.  So,
> if it has a moderate to high cost delay, then it might be able to keep
> up with the job and your tables will become bloated.

might NOT be able to keep up

>
> The problem isn't that autovac is stealing too many resources, it's
> that it's not stealing enough.
>
> The first quick fix is 8.3 which has more efficient vacuuming code and

Whoops I see you're technically running 8.3, but you're running RC2
for some reason?  I don't usually run 8.x.0 in production.  Let alone
RCs.  You should really update before some nasty bug that's been
squashed in later releases bites you.

Re: Manual vacs 5x faster than autovacs?

From
Wayne Beaver
Date:
> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>
>>> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>> I'd seen autovacs running for hours and had mis-attributed this to growing
>>> query times on those tables  - my thought was that "shrinking" the tables
>>> "more quickly" could make them "more-optimized", more often. Sounds like I
>>> could be chasing the wrong symptoms, though.
>>
>> Now it is quite possible that a slow autovac is causing your queries
>> to run slower.  So, if it has a moderate to high cost delay, then
>> it might not be able to keep
>> up with the job and your tables will become bloated.
>>
>> The problem isn't that autovac is stealing too many resources, it's
>> that it's not stealing enough.
>>
>> I see you're technically running 8.3, but you're running RC2
>> for some reason?  I don't usually run 8.x.0 in production.  Let alone
>> RCs.  You should really update before some nasty bug that's been
>> squashed in later releases bites you.


Hahaha. Yes, 8.3RC2 was latest version at time I implemented related
client app. Install is "production-like", more so than production -
non-mission-critical,  but important to some "VIP-like" users at
intervals which are not necessarily predictable. I'm long past my goal
of migrating to 8.4, actually...

My autovac settings are all at default values, so sounds like I can at
least tinker with _workers and _cost_delay. I've not yet gotten to you
iostat inquiry from your previous response...

wb

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Thu, Nov 12, 2009 at 9:58 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>
>>>> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>>> I'd seen autovacs running for hours and had mis-attributed this to
>>>> growing
>>>> query times on those tables  - my thought was that "shrinking" the
>>>> tables
>>>> "more quickly" could make them "more-optimized", more often. Sounds like
>>>> I
>>>> could be chasing the wrong symptoms, though.
>>>
>>> Now it is quite possible that a slow autovac is causing your queries
>>> to run slower.  So, if it has a moderate to high cost delay, then it
>>> might not be able to keep
>>> up with the job and your tables will become bloated.
>>>
>>> The problem isn't that autovac is stealing too many resources, it's
>>> that it's not stealing enough.
>>>
>>> I see you're technically running 8.3, but you're running RC2
>>> for some reason?  I don't usually run 8.x.0 in production.  Let alone
>>> RCs.  You should really update before some nasty bug that's been
>>> squashed in later releases bites you.
>
>
> Hahaha. Yes, 8.3RC2 was latest version at time I implemented related client
> app. Install is "production-like", more so than production -
> non-mission-critical,  but important to some "VIP-like" users at intervals
> which are not necessarily predictable. I'm long past my goal of migrating to
> 8.4, actually...

Worry far more about being out of date on 8.3. Since you're on an rc
release you'll likely need to dump and restore to safely migrate to
8.3.latest, but once there, simply shutting down, updating and
starting up is all that's usually required.

>
> My autovac settings are all at default values, so sounds like I can at least
> tinker with _workers and _cost_delay. I've not yet gotten to you iostat
> inquiry from your previous response...

Don't worry too much, just want to see if your IO system is maxed out.

Re: Manual vacs 5x faster than autovacs?

From
Dave Crooke
Date:
The autovac may have done most of the work before you killed it ...
I'm new to Postgres, but from limited subjective experience, it seems
it's a lot faster to vaccum ranges of blocks that are were recently
vacuumed (at minimum, a good chunk of table will have been brought
into buffer cache by both Postgres and the OS during the prior pass).

I've found that with very large data tables, the auto-vaccum on
default settings isn't as aggressive as I'd like ... I find running a
VACUUM ANALYZE isn't at all intrusive, though I prefer to do it once a
day at 3am.

Beware that VACUUM FULL locks an entire table at a time :-)

Cheers
Dave

On Thu, Nov 12, 2009 at 8:33 AM, Wayne Beaver <wayne@acedsl.com> wrote:
> Hi All,
>
> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I know
> that's old). I have seen *really* long-running autovacs eating up system
> resources. While the below is not an example of *really* long, it shows how
> I killed an autovac which had been running for more than 10 minutes, then
> ran a VAC FULL ANALYZE on same exact table in about ~2 min. Any wisdom here?
> Attributable to autovac_worker settings? Or Pg version? Other?
>
> Any insight appreciated.
>
> wb
>
> ++++++++++++++++++++++++++
>
> $ psql template1 -c "SELECT procpid, current_query, to_char (now() -
> backend_start, 'HH24:MI:SS') AS connected_et, to_char (now() -
> query_start,'HH24:MI:SS') AS query_et FROM pg_stat_activity WHERE
> datname='mydb' ORDER BY query_et DESC LIMIT 1"
>
>  procpid |                   current_query            | connected_et |
> query_et
> ---------+--------------------------------------------+--------------+----------
>    9064 | autovacuum: VACUUM ANALYZE myschema.mytable    | 00:12:07     |
> 00:11:38
>
>
>
> $ kill 9064
>
>
> $ date; psql mydb -c "VACUUM FULL ANALYZE myschema.mytable"; date
> Wed Nov 11 17:25:41 UTC 2009
> VACUUM
> Wed Nov 11 17:27:59 UTC 2009
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: Manual vacs 5x faster than autovacs?

From
Craig Ringer
Date:
On 13/11/2009 2:29 PM, Dave Crooke wrote:

> Beware that VACUUM FULL locks an entire table at a time :-)

... and often bloats its indexes horribly. Use CLUSTER instead if you
need to chop a table that's massively bloated down to size; it'll be
much faster, and shouldn't leave the indexes in a mess.

I increasingly wonder what the purpose of VACUUM FULL in its current
form is.

--
Craig Ringer

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Fri, Nov 13, 2009 at 8:31 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 13/11/2009 2:29 PM, Dave Crooke wrote:
>
>> Beware that VACUUM FULL locks an entire table at a time :-)
>
> ... and often bloats its indexes horribly. Use CLUSTER instead if you
> need to chop a table that's massively bloated down to size; it'll be
> much faster, and shouldn't leave the indexes in a mess.
>
> I increasingly wonder what the purpose of VACUUM FULL in its current
> form is.

There's been talk of removing it.  It's almost historical in nature
now, but there are apparently one or two situations, like when you're
almost out of space, that vacuum full can handle that dumping reload
or cluster or whatnot can't do without more extra space.

Re: Manual vacs 5x faster than autovacs?

From
Craig Ringer
Date:
On 14/11/2009 11:55 AM, Scott Marlowe wrote:
> On Fri, Nov 13, 2009 at 8:31 PM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>> On 13/11/2009 2:29 PM, Dave Crooke wrote:
>>
>>> Beware that VACUUM FULL locks an entire table at a time :-)
>>
>> ... and often bloats its indexes horribly. Use CLUSTER instead if you
>> need to chop a table that's massively bloated down to size; it'll be
>> much faster, and shouldn't leave the indexes in a mess.
>>
>> I increasingly wonder what the purpose of VACUUM FULL in its current
>> form is.
>
> There's been talk of removing it.  It's almost historical in nature
> now, but there are apparently one or two situations, like when you're
> almost out of space, that vacuum full can handle that dumping reload
> or cluster or whatnot can't do without more extra space.

Perhaps it should drop and re-create indexes as well, then? (Or disable
them so they become inconsistent, then REINDEX them - same deal). It'd
run a LOT faster, and the index bloat issue would be gone.

The current form of the command just invites misuse and misapplication.

--
Craig Ringer

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Fri, Nov 13, 2009 at 9:45 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 14/11/2009 11:55 AM, Scott Marlowe wrote:
>> On Fri, Nov 13, 2009 at 8:31 PM, Craig Ringer
>> <craig@postnewspapers.com.au> wrote:
>>> On 13/11/2009 2:29 PM, Dave Crooke wrote:
>>>
>>>> Beware that VACUUM FULL locks an entire table at a time :-)
>>>
>>> ... and often bloats its indexes horribly. Use CLUSTER instead if you
>>> need to chop a table that's massively bloated down to size; it'll be
>>> much faster, and shouldn't leave the indexes in a mess.
>>>
>>> I increasingly wonder what the purpose of VACUUM FULL in its current
>>> form is.
>>
>> There's been talk of removing it.  It's almost historical in nature
>> now, but there are apparently one or two situations, like when you're
>> almost out of space, that vacuum full can handle that dumping reload
>> or cluster or whatnot can't do without more extra space.
>
> Perhaps it should drop and re-create indexes as well, then? (Or disable
> them so they become inconsistent, then REINDEX them - same deal). It'd
> run a LOT faster, and the index bloat issue would be gone.
>
> The current form of the command just invites misuse and misapplication.

Yeah, it should be a name that when you're typing it you know you
screwed up to get where you are.  The
opleasemayihavebackthespaceilostwhilelockingmytablesandbloatingmyindexes
command.  No chance you'll run it by mistake either!

Re: Manual vacs 5x faster than autovacs?

From
Wayne Beaver
Date:
> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>
>> On Thu, Nov 12, 2009 at 9:58 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>>
>>>>> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>>>> I'd seen autovacs running for hours and had mis-attributed this to
>>>>> growing query times on those tables  - my thought was that
>>>>> "shrinking" the tables
>>>>> "more quickly" could make them "more-optimized", more often. Sounds like
>>>>> could be chasing the wrong symptoms, though.
>>>>
>>>> Now it is quite possible that a slow autovac is causing your queries
>>>> to run slower.  So, if it has a moderate to high cost delay, then it
>>>> might not be able to keep
>>>> up with the job and your tables will become bloated.
>>>>
>>>> The problem isn't that autovac is stealing too many resources, it's
>>>> that it's not stealing enough.
>>>>
>> I've not yet gotten to you iostat inquiry from your previous response...
>
> Don't worry too much, just want to see if your IO system is maxed out.


$ iostat
Linux 2.6.18.8-0.9-default (myserver)     11/16/2009

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           28.11    3.13    6.50    8.71    0.00   53.56

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             153.08      7295.23      3675.59 123127895363 62036043656\

Re: Manual vacs 5x faster than autovacs?

From
Scott Marlowe
Date:
On Mon, Nov 16, 2009 at 9:13 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>
>>> On Thu, Nov 12, 2009 at 9:58 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>>>
>>>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>>>
>>>>>> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com>
>>>>>> wrote:
>>>>>> I'd seen autovacs running for hours and had mis-attributed this to
>>>>>> growing query times on those tables  - my thought was that "shrinking"
>>>>>> the tables
>>>>>> "more quickly" could make them "more-optimized", more often. Sounds
>>>>>> like
>>>>>> could be chasing the wrong symptoms, though.
>>>>>
>>>>> Now it is quite possible that a slow autovac is causing your queries
>>>>> to run slower.  So, if it has a moderate to high cost delay, then it
>>>>> might not be able to keep
>>>>> up with the job and your tables will become bloated.
>>>>>
>>>>> The problem isn't that autovac is stealing too many resources, it's
>>>>> that it's not stealing enough.
>>>>>
>>> I've not yet gotten to you iostat inquiry from your previous response...
>>
>> Don't worry too much, just want to see if your IO system is maxed out.
>
>
> $ iostat
> Linux 2.6.18.8-0.9-default (myserver)   11/16/2009
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>          28.11    3.13    6.50    8.71    0.00   53.56
>
> Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
> sda             153.08      7295.23      3675.59 123127895363 62036043656\

That's just since the machine was turned on.  run it like:

iostat -x 10

and see what comes out after the first one.

Re: Manual vacs 5x faster than autovacs?

From
Wayne Beaver
Date:
Quoting Scott Marlowe <scott.marlowe@gmail.com>:

> On Mon, Nov 16, 2009 at 9:13 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>>
>>>> On Thu, Nov 12, 2009 at 9:58 AM, Wayne Beaver <wayne@acedsl.com> wrote:
>>>>>
>>>>> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>>>>>
>>>>>>> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com>
>>>>>>> wrote:
>>>>>>> I'd seen autovacs running for hours and had mis-attributed this to
>>>>>>> growing query times on those tables  - my thought was that "shrinking"
>>>>>>> the tables
>>>>>>> "more quickly" could make them "more-optimized", more often. Sounds
>>>>>>> like
>>>>>>> could be chasing the wrong symptoms, though.
>>>>>>
>>>>>> Now it is quite possible that a slow autovac is causing your queries
>>>>>> to run slower.  So, if it has a moderate to high cost delay, then it
>>>>>> might not be able to keep
>>>>>> up with the job and your tables will become bloated.
>>>>>>
>>>>>> The problem isn't that autovac is stealing too many resources, it's
>>>>>> that it's not stealing enough.
>>>>>>
>>>> I've not yet gotten to you iostat inquiry from your previous response...
>>>
>>> Don't worry too much, just want to see if your IO system is maxed out.
>>
>
> That's just since the machine was turned on.  run it like:
>
> iostat -x 10
>
> and see what comes out after the first one.


Duh! Sorry about that...


$ iostat -x 10
Linux 2.6.18.8-0.9-default (myserver)     11/16/2009

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           28.11    3.13    6.50    8.70    0.00   53.56

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s
avgrq-sz avgqu-sz   await  svctm  %util
sda          3.20 406.34 100.74 52.33 7293.84 3675.79  3646.92
1837.90    71.66     0.07    2.15   0.90  13.71