Обсуждение: Reduce WAL logging of INSERT SELECT

We currently have reduced WAL logging for wal_level = minimum for these
commands:
CREATE TABLE ASCREATE INDEXCLUSTERCOPY into tables that were created or truncated in the sametransaction

One thing we don't optimize is INSERT ... SELECT when the table is
created or truncated in the same transaction.  Seems we could.

We optimize CREATE TABLE AS which is effectively SELECT ... INTO using a
different syntax.  Is this a TODO?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:
> One thing we don't optimize is INSERT ... SELECT when the table is
> created or truncated in the same transaction.  Seems we could.

> We optimize CREATE TABLE AS which is effectively SELECT ... INTO using a
> different syntax.  Is this a TODO?

Considering that SELECT INTO is deprecated, I don't think we should be
expending effort to encourage people to use it.
        regards, tom lane

On 08/04/2011 04:55 PM, Tom Lane wrote:
> Bruce Momjian<bruce@momjian.us>  writes:
>> One thing we don't optimize is INSERT ... SELECT when the table is
>> created or truncated in the same transaction.  Seems we could.
>> We optimize CREATE TABLE AS which is effectively SELECT ... INTO using a
>> different syntax.  Is this a TODO?
> Considering that SELECT INTO is deprecated, I don't think we should be
> expending effort to encourage people to use it.
>
>             


Right, but the original point about INSERT ... SELECT seems reasonable, no?

cheers

andrew

Andrew Dunstan wrote:
> 
> 
> On 08/04/2011 04:55 PM, Tom Lane wrote:
> > Bruce Momjian<bruce@momjian.us>  writes:
> >> One thing we don't optimize is INSERT ... SELECT when the table is
> >> created or truncated in the same transaction.  Seems we could.
> >> We optimize CREATE TABLE AS which is effectively SELECT ... INTO using a
> >> different syntax.  Is this a TODO?
> > Considering that SELECT INTO is deprecated, I don't think we should be
> > expending effort to encourage people to use it.
> >
> >             
> 
> 
> Right, but the original point about INSERT ... SELECT seems reasonable, no?

Right.  I brought up SELECT INTO because you could make the argument
that INSERT ... SELECT is not a utility command like the other ones and
therefore can't be done easily, but CREATE TABLE AS is internal SELECT
INTO and implemented in execMain.c, which I think is where INSERT ...
SELECT would also be implemented.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Thu, Aug 4, 2011 at 10:46 PM, Bruce Momjian <bruce@momjian.us> wrote:

> Right.  I brought up SELECT INTO because you could make the argument
> that INSERT ... SELECT is not a utility command like the other ones and
> therefore can't be done easily, but CREATE TABLE AS is internal SELECT
> INTO and implemented in execMain.c, which I think is where INSERT ...
> SELECT would also be implemented.

What you should be asking is whether the optimisation would be
effective for INSERT SELECT, or even test it.

My observation is that the optimisation is only effective for very
large loads that cause I/O. As RAM sizes get bigger, I'm inclined to
remove the optimisation and make it optional since it is ineffective
in many cases and negative benefit for smaller cases.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs wrote:
> On Thu, Aug 4, 2011 at 10:46 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 
> > Right. ?I brought up SELECT INTO because you could make the argument
> > that INSERT ... SELECT is not a utility command like the other ones and
> > therefore can't be done easily, but CREATE TABLE AS is internal SELECT
> > INTO and implemented in execMain.c, which I think is where INSERT ...
> > SELECT would also be implemented.
> 
> What you should be asking is whether the optimisation would be
> effective for INSERT SELECT, or even test it.
> 
> My observation is that the optimisation is only effective for very
> large loads that cause I/O. As RAM sizes get bigger, I'm inclined to
> remove the optimisation and make it optional since it is ineffective
> in many cases and negative benefit for smaller cases.

I am confused how generating WAL traffic that is larger than the heap
file we are fsync'ing can possibly be slower.  Are you just throwing out
an idea to try to make me prove it?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Thu, 2011-08-04 at 17:46 -0400, Bruce Momjian wrote:
> Right.  I brought up SELECT INTO because you could make the argument
> that INSERT ... SELECT is not a utility command like the other ones and
> therefore can't be done easily, but CREATE TABLE AS is internal SELECT
> INTO and implemented in execMain.c, which I think is where INSERT ...
> SELECT would also be implemented.

The above statement is a little confusing, so let me start from the
beginning:

How could we avoid WAL logging for INSERT ... SELECT?

The way we do it for CREATE TABLE AS is because nobody would even *see*
the table if our transaction doesn't commit. Therefore we don't need to
bother logging it. Same can be said for SELECT INTO.

INSERT ... SELECT is just an insert. It needs just as much logging as
inserting tuples any other way. For instance, it will potentially share
pages with other inserts, and better properly record all such page
modifications so that they return to a consistent state.

Regards,Jeff Davis

Jeff Davis wrote:
> On Thu, 2011-08-04 at 17:46 -0400, Bruce Momjian wrote:
> > Right.  I brought up SELECT INTO because you could make the argument
> > that INSERT ... SELECT is not a utility command like the other ones and
> > therefore can't be done easily, but CREATE TABLE AS is internal SELECT
> > INTO and implemented in execMain.c, which I think is where INSERT ...
> > SELECT would also be implemented.
> 
> The above statement is a little confusing, so let me start from the
> beginning:
> 
> How could we avoid WAL logging for INSERT ... SELECT?
> 
> The way we do it for CREATE TABLE AS is because nobody would even *see*
> the table if our transaction doesn't commit. Therefore we don't need to
> bother logging it. Same can be said for SELECT INTO.
> 
> INSERT ... SELECT is just an insert. It needs just as much logging as
> inserting tuples any other way. For instance, it will potentially share
> pages with other inserts, and better properly record all such page
> modifications so that they return to a consistent state.

It would act like COPY, meaning the table would have to be truncated or
created in the same transaction.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Thu, Aug 4, 2011 at 8:55 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Jeff Davis wrote:
>> On Thu, 2011-08-04 at 17:46 -0400, Bruce Momjian wrote:
>> > Right.  I brought up SELECT INTO because you could make the argument
>> > that INSERT ... SELECT is not a utility command like the other ones and
>> > therefore can't be done easily, but CREATE TABLE AS is internal SELECT
>> > INTO and implemented in execMain.c, which I think is where INSERT ...
>> > SELECT would also be implemented.
>>
>> The above statement is a little confusing, so let me start from the
>> beginning:
>>
>> How could we avoid WAL logging for INSERT ... SELECT?
>>
>> The way we do it for CREATE TABLE AS is because nobody would even *see*
>> the table if our transaction doesn't commit. Therefore we don't need to
>> bother logging it. Same can be said for SELECT INTO.
>>
>> INSERT ... SELECT is just an insert. It needs just as much logging as
>> inserting tuples any other way. For instance, it will potentially share
>> pages with other inserts, and better properly record all such page
>> modifications so that they return to a consistent state.
>
> It would act like COPY, meaning the table would have to be truncated or
> created in the same transaction.

It seems to me that, if we know the relation was created or truncated
in the current transaction, and if wal_level = minimal, then we don't
need to WAL-log *anything* until transaction commit (provided we fsync
at commit).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, 2011-08-04 at 20:55 -0400, Bruce Momjian wrote:
> It would act like COPY, meaning the table would have to be truncated or
> created in the same transaction.

Well, in that case it could work for any INSERT. No need for a SELECT to
be involved. For that matter, why not make it work for DELETE and
UPDATE, too?

However, I think this is all just a workaround for not having a faster
loading path. I don't object to applying this optimization to inserts,
but I think it might be more productive to figure out if we can support
loading data efficiently -- i.e. also set hint bits and frozenxid during
the load.

Regards,Jeff Davis

On 05.08.2011 04:23, Jeff Davis wrote:
> On Thu, 2011-08-04 at 20:55 -0400, Bruce Momjian wrote:
>> It would act like COPY, meaning the table would have to be truncated or
>> created in the same transaction.
>
> Well, in that case it could work for any INSERT. No need for a SELECT to
> be involved. For that matter, why not make it work for DELETE and
> UPDATE, too?

Yep. If we are to expand it, we should make it work for any operation.

However, for small operations it's a net loss - you avoid writing a WAL 
record, but you have to fsync() the heap instead. If you only modify a 
few rows, the extra fsync (or fsyncs if there are indexes too) is more 
expensive than writing the WAL.

We'd need a heuristic to decide whether to write WAL or fsync at the 
end. For regular INSERTs, UPDATEs and DELETEs, you have the planner's 
estimate of number of rows affected. Another thing we should do is move 
the fsync call from the end of COPY (and other such operations) to the 
end of transaction. That way if you do e.g one COPY followed by a bunch 
of smaller INSERTs or UPDATEs, you only need to fsync the files once.

> However, I think this is all just a workaround for not having a faster
> loading path. I don't object to applying this optimization to inserts,
> but I think it might be more productive to figure out if we can support
> loading data efficiently -- i.e. also set hint bits and frozenxid during
> the load.

Yeah, that would make a much bigger impact in practice.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Thu, Aug 4, 2011 at 11:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Simon Riggs wrote:
>> On Thu, Aug 4, 2011 at 10:46 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> > Right. ?I brought up SELECT INTO because you could make the argument
>> > that INSERT ... SELECT is not a utility command like the other ones and
>> > therefore can't be done easily, but CREATE TABLE AS is internal SELECT
>> > INTO and implemented in execMain.c, which I think is where INSERT ...
>> > SELECT would also be implemented.
>>
>> What you should be asking is whether the optimisation would be
>> effective for INSERT SELECT, or even test it.
>>
>> My observation is that the optimisation is only effective for very
>> large loads that cause I/O. As RAM sizes get bigger, I'm inclined to
>> remove the optimisation and make it optional since it is ineffective
>> in many cases and negative benefit for smaller cases.
>
> I am confused how generating WAL traffic that is larger than the heap
> file we are fsync'ing can possibly be slower.

I'm telling you what I know to be true as an assistance to you.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Thu, 2011-08-04 at 18:07 -0400, Bruce Momjian wrote:
> I am confused how generating WAL traffic that is larger than the heap
> file we are fsync'ing can possibly be slower.  Are you just throwing out
> an idea to try to make me prove it?

That's worded in a slightly confusing way, but here is the trade-off:

1. If you are using WAL, then regardless of what your transaction does,
only the WAL needs to be fsync'd at commit time. Conveniently, that's
being written sequentially, so it's a single fairly cheap fsync; and all
the data page changes are deferred, collected together, and fsync'd at
checkpoint time (rather than commit time). The cost is that you
double-write the data.

2. If you aren't using WAL, you need to fsync every data file the
transaction touched, which are probably not localized with other
activity. Also, the _entire_ data files needs to be sync'd, so perhaps
many other transactions have made changes to one data file all over, and
it may require _many_ seeks to accomplish the one fsync. The benefit is
that you don't double-write the data.

So, fundamentally, WAL is (in the OLTP case, where a transaction is much
shorter than a checkpoint interval) a big performance _win_, because it
allows us to do nice sequential writing in a single place for all
activities of all transactions; and defer all those random writes to
data pages until the next checkpoint. So we shouldn't treat WAL like a
cost burden that we want to avoid in every case we can.

But in the data load case (where many checkpoints may happen during a
single transaction anyway), it happens that avoiding WAL is a
performance win, because the seeks are not the dominant cost.

Regards,Jeff Davis

Jeff Davis wrote:
> On Thu, 2011-08-04 at 18:07 -0400, Bruce Momjian wrote:
> > I am confused how generating WAL traffic that is larger than the heap
> > file we are fsync'ing can possibly be slower.  Are you just throwing out
> > an idea to try to make me prove it?
> 
> That's worded in a slightly confusing way, but here is the trade-off:
> 
> 1. If you are using WAL, then regardless of what your transaction does,
> only the WAL needs to be fsync'd at commit time. Conveniently, that's
> being written sequentially, so it's a single fairly cheap fsync; and all
> the data page changes are deferred, collected together, and fsync'd at
> checkpoint time (rather than commit time). The cost is that you
> double-write the data.
> 
> 2. If you aren't using WAL, you need to fsync every data file the
> transaction touched, which are probably not localized with other
> activity. Also, the _entire_ data files needs to be sync'd, so perhaps
> many other transactions have made changes to one data file all over, and
> it may require _many_ seeks to accomplish the one fsync. The benefit is
> that you don't double-write the data.
> 
> So, fundamentally, WAL is (in the OLTP case, where a transaction is much
> shorter than a checkpoint interval) a big performance _win_, because it
> allows us to do nice sequential writing in a single place for all
> activities of all transactions; and defer all those random writes to
> data pages until the next checkpoint. So we shouldn't treat WAL like a
> cost burden that we want to avoid in every case we can.
> 
> But in the data load case (where many checkpoints may happen during a
> single transaction anyway), it happens that avoiding WAL is a
> performance win, because the seeks are not the dominant cost.

Well, if the table is created in the same transaction (which is the only
case under consideration), no other sessions can write to the table so
you are just writing the entire table on commit, rather than to the WAL.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On 06.08.2011 06:32, Gokulakannan Somasundaram wrote:
>>
>> However, for small operations it's a net loss - you avoid writing a WAL
>> record, but you have to fsync() the heap instead. If you only modify a few
>> rows, the extra fsync (or fsyncs if there are indexes too) is more expensive
>> than writing the WAL.
>>
>> We'd need a heuristic to decide whether to write WAL or fsync at the end.
>> For regular INSERTs, UPDATEs and DELETEs, you have the planner's estimate of
>> number of rows affected. Another thing we should do is move the fsync call
>> from the end of COPY (and other such operations) to the end of transaction.
>> That way if you do e.g one COPY followed by a bunch of smaller INSERTs or
>> UPDATEs, you only need to fsync the files once.
>
>
> Have you thought about recovery, especially when the page size is greater
> than the disk block size(>  4K ). With WAL, there is a way to restore the
> pages to the original state, during recovery, if there are partial page
> writes. Is it possible to do the same with direct fsync without WAL?

The point of the optimization is that you can only skip WAL when it's 
been created (or truncated) in the same transaction. In that case, if 
the transaction aborts because of a crash, you don't care about the 
contents of the table anyway.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Sat, Aug 6, 2011 at 4:16 AM, Bruce Momjian <bruce@momjian.us> wrote:

> Well, if the table is created in the same transaction (which is the only
> case under consideration), no other sessions can write to the table so
> you are just writing the entire table on commit, rather than to the WAL.

Below a certain point, skipping WAL is slower and over an intermediate
range there is no benefit. So small amounts of data on large servers
goes slower.

heap_fsync() requires a scan of shared buffers, which may not be cheap.

There is a difficulty because you would need to calculate the cut-off
is for a particular database, and then predict ahead of time whether
the number of rows that will be handled by the statement is low enough
to warrant using the optimisation. Both of which I call a hard
problem.

I think we should remove the COPY optimisation because of this and
definitely not extend INSERT SELECT to perform it automatically.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 06.08.2011 13:13, Simon Riggs wrote:
> I think we should remove the COPY optimisation because of this and
> definitely not extend INSERT SELECT to perform it automatically.

It can be very helpful when loading a lot of data, so I'm not in favor 
of removing it altogether. Maybe WAL-log the first 10000 rows or such 
normally, and skip WAL after that. Of course, loading 10001 rows becomes 
the worst case then, but something along those lines...

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Sat, Aug 6, 2011 at 11:05 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 06.08.2011 13:13, Simon Riggs wrote:
>>
>> I think we should remove the COPY optimisation because of this and
>> definitely not extend INSERT SELECT to perform it automatically.
>
> It can be very helpful when loading a lot of data, so I'm not in favor of
> removing it altogether. Maybe WAL-log the first 10000 rows or such normally,
> and skip WAL after that. Of course, loading 10001 rows becomes the worst
> case then, but something along those lines...
>

why 10000 rows? maybe the right solution is move towards make a normal
table unlogged and viceversa... probably that's harder to do but we
will have better control and less odd heuristics

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

Heikki Linnakangas  wrote:
> On 06.08.2011 13:13, Simon Riggs wrote:
>> I think we should remove the COPY optimisation because of this and
>> definitely not extend INSERT SELECT to perform it automatically.
> 
> It can be very helpful when loading a lot of data, so I'm not in
> favor of removing it altogether.
Yeah, it can currently help a lot.  Of course, if WAL-logging could
in any way facilitate hint bit and frozen xmin setting during bulk
loads, I'm sure the WAL-logged version would win easily.
-Kevin

Jaime Casanova <jaime@2ndquadrant.com> writes:
> On Sat, Aug 6, 2011 at 11:05 AM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com> wrote:
>> It can be very helpful when loading a lot of data, so I'm not in favor of
>> removing it altogether. Maybe WAL-log the first 10000 rows or such normally,
>> and skip WAL after that. Of course, loading 10001 rows becomes the worst
>> case then, but something along those lines...

> why 10000 rows?

Yeah; any particular number is wrong.  Perhaps it'd be better to put the
behavior under user control.  In the case of COPY, where we already have
a place to stick random options, you could imagine writing something
like
COPY ... WITH (bulk)

to cue the system that a lot of data is coming in.  But I don't see any
nice way to do something similar for INSERT/SELECT.  I hesitate to
suggest a GUC, but something like "SET bulk_load = on" would be pretty
straightforward to use in pg_dump for instance.
        regards, tom lane

On Fri, 2011-08-05 at 23:16 -0400, Bruce Momjian wrote:
> Well, if the table is created in the same transaction (which is the only
> case under consideration), no other sessions can write to the table so
> you are just writing the entire table on commit, rather than to the WAL.

The transaction can still write to many tables that way, and that could
mean many fsyncs.

Also, there may be a bunch of other transactions trying to write to the
WAL that have to wait as your transaction has to seek out to fsync the
data file and then seek back to the WAL.

Regards,Jeff Davis

Simon Riggs wrote:
> On Sat, Aug 6, 2011 at 4:16 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> > Well, if the table is created in the same transaction (which is the only
> > case under consideration), no other sessions can write to the table so
> > you are just writing the entire table on commit, rather than to the WAL.
>
> Below a certain point, skipping WAL is slower and over an intermediate
> range there is no benefit. So small amounts of data on large servers
> goes slower.
>
> heap_fsync() requires a scan of shared buffers, which may not be cheap.
>
> There is a difficulty because you would need to calculate the cut-off
> is for a particular database, and then predict ahead of time whether
> the number of rows that will be handled by the statement is low enough
> to warrant using the optimisation. Both of which I call a hard
> problem.
>
> I think we should remove the COPY optimisation because of this and
> definitely not extend INSERT SELECT to perform it automatically.

I ran some tests and Simon was correct and I was wrong.  There is a
measurable overhead to the optimization of avoiding WAL traffic for
small tables.

I tested git head with COPY and created the table inside and outside the
COPY transaction, with the attached script.  It ran in 11 seconds
without the optimization, and 12 seconds with the CREATE TABLE inside
the COPY transaction.

With these results, I withdraw my idea of adding this optimization to
other commands.  I think COPY is usually used in bulk mode, but the
other commands are often used in smaller batches that would be worse
with this optimization.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
:

. traprm

echo 1 > $TMP/1

echo "DROP TABLE copytest;" > $TMP/0
for X in $(jot 1000)
do
    cat >> $TMP/0 <<END
-- move the next line into the transaction block to test the optimization overhead
CREATE TABLE copytest(x int);
BEGIN WORK;
COPY copytest FROM '$TMP/1';
COMMIT;
DROP TABLE copytest;
END
done
time sql test < $TMP/0 > /dev/null