pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedManual vacs 5x faster than autovacs?
5+ messages / 3 participants
[nested] [flat]
* Manual vacs 5x faster than autovacs?
@ 2009-11-12 14:33 Wayne Beaver <wayne@acedsl.com>
2009-11-12 14:49 ` Re: Manual vacs 5x faster than autovacs? Tom Lane <tgl@sss.pgh.pa.us>
2009-11-12 15:14 ` Re: Manual vacs 5x faster than autovacs? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Wayne Beaver @ 2009-11-12 14:33 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Manual vacs 5x faster than autovacs?
2009-11-12 14:33 Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
@ 2009-11-12 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2009-11-12 14:49 UTC (permalink / raw)
To: Wayne Beaver <wayne@acedsl.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Manual vacs 5x faster than autovacs?
2009-11-12 14:33 Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
@ 2009-11-12 15:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-11-12 16:14 ` Re: Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2009-11-12 15:14 UTC (permalink / raw)
To: Wayne Beaver <wayne@acedsl.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Manual vacs 5x faster than autovacs?
2009-11-12 14:33 Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
2009-11-12 15:14 ` Re: Manual vacs 5x faster than autovacs? Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-11-12 16:14 ` Wayne Beaver <wayne@acedsl.com>
2009-11-12 16:33 ` Re: Manual vacs 5x faster than autovacs? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Wayne Beaver @ 2009-11-12 16:14 UTC (permalink / raw)
To: pgsql-performance
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Manual vacs 5x faster than autovacs?
2009-11-12 14:33 Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
2009-11-12 15:14 ` Re: Manual vacs 5x faster than autovacs? Scott Marlowe <scott.marlowe@gmail.com>
2009-11-12 16:14 ` Re: Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
@ 2009-11-12 16:33 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2009-11-12 16:33 UTC (permalink / raw)
To: Wayne Beaver <wayne@acedsl.com>; +Cc: pgsql-performance
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-11-12 16:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-12 14:33 Manual vacs 5x faster than autovacs? Wayne Beaver <wayne@acedsl.com>
2009-11-12 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-12 15:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-11-12 16:14 ` Wayne Beaver <wayne@acedsl.com>
2009-11-12 16:33 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox