pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum performance on insert
5+ messages / 3 participants
[nested] [flat]
* vacuum performance on insert
@ 2010-08-05 17:49 Sean Chen <zyschen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sean Chen @ 2010-08-05 17:49 UTC (permalink / raw)
To: pgsql-performance
Hi, I'm curious -- does "vacuum analyze e.g. table1" improve
performance on "insert into table1 ...". I understand the vacuum
analyze helps out the query -- select, etc., but just not quite sure
on insert.
Specifically, I'm doing the following.
1, delete records ...
2, insert records ...
if I add "vacuum analyze" in-between this two steps, will it help on
the performance on the insert?
More importantly, If so, why?
Thanks,
Sean
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuum performance on insert
@ 2010-08-05 17:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Sean Chen <zyschen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2010-08-05 17:56 UTC (permalink / raw)
To: Sean Chen <zyschen@gmail.com>; pgsql-performance
Sean Chen <zyschen@gmail.com> wrote:
> 1, delete records ...
> 2, insert records ...
>
> if I add "vacuum analyze" in-between this two steps, will it help
> on the performance on the insert?
Assuming there are no long-running transactions which would still be
able to see the deleted rows, a VACUUM between those statements
would allow the INSERT to re-use the space previously occupied by
the deleted rows, rather than possibly needing to allocate new space
from the OS.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuum performance on insert
@ 2010-08-05 18:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-08-05 18:11 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Sean Chen <zyschen@gmail.com>; pgsql-performance
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Sean Chen <zyschen@gmail.com> wrote:
>> 1, delete records ...
>> 2, insert records ...
>>
>> if I add "vacuum analyze" in-between this two steps, will it help
>> on the performance on the insert?
> Assuming there are no long-running transactions which would still be
> able to see the deleted rows, a VACUUM between those statements
> would allow the INSERT to re-use the space previously occupied by
> the deleted rows, rather than possibly needing to allocate new space
> from the OS.
But on the other side of the coin, the ANALYZE step is probably not very
helpful there. Better to do that after you've loaded the new data.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuum performance on insert
@ 2010-08-07 04:32 Sean Chen <zyschen@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Sean Chen @ 2010-08-07 04:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
hi, thank you for the reply.
I ran a number of tests to try to make sense of this.
When I ran with or without vacuum, the number of disk io operations,
cache operations etc. gathered from pg_stat table for the insertions
are pretty much the same.
So I don't see vacuum reduce disk io operations.
Now from what you mentioned below, do you know what's the cost of
postgres requesting new disk space from OS?
I'm seeing a big performance difference with vacuum, but I need a
proof to show it's the requesting new space operation that was the
problem, not disk io, etc. since I would think disk could be expensive
as well.
Thanks,
Sean
On Thu, Aug 5, 2010 at 2:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Sean Chen <zyschen@gmail.com> wrote:
>>> 1, delete records ...
>>> 2, insert records ...
>>>
>>> if I add "vacuum analyze" in-between this two steps, will it help
>>> on the performance on the insert?
>
>> Assuming there are no long-running transactions which would still be
>> able to see the deleted rows, a VACUUM between those statements
>> would allow the INSERT to re-use the space previously occupied by
>> the deleted rows, rather than possibly needing to allocate new space
>> from the OS.
>
> But on the other side of the coin, the ANALYZE step is probably not very
> helpful there. Better to do that after you've loaded the new data.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuum performance on insert
@ 2010-08-09 14:03 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Sean Chen <zyschen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2010-08-09 14:03 UTC (permalink / raw)
To: Sean Chen <zyschen@gmail.com>; +Cc: pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Sean Chen <zyschen@gmail.com> wrote:
> Now from what you mentioned below, do you know what's the cost of
> postgres requesting new disk space from OS?
Depending on your OS and its version, your file system, your mount
options, and your disk subsystem (and its firmware revision), there
could be various effects -- the one likely to be biting you is write
barriers. When you allocate additional space from the OS, and it
extends a file or creates a new file, there might be a write barrier
to ensure that the file system catalog entries are persisted. This
could cause all writes (and possibly even reads) to pause until the
data is written to disk.
That's just a guess, of course. If you have a profiler you can run
you might be able to pin it down with that.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-08-09 14:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-05 17:49 vacuum performance on insert Sean Chen <zyschen@gmail.com>
2010-08-05 17:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-05 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-07 04:32 ` Sean Chen <zyschen@gmail.com>
2010-08-09 14:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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