Performance and WAL on big inserts/updates

If a transaction will do large updates or inserts, why don't we just log
the parsed statements in the WAL instead of the individual data blocks

UPDATE table SET col = random();

"Marty" == Marty Scholes <marty@outputservices.com> writes:

Marty> Why have I not seen this in any database?
Marty> There must be a reason.

For ARIES-style systems, logging parsed statements (commonly called
"logical" logging) is not preferred compared to logging data items
("physical" or "physiological" logging).

A major reason for this is that logical logs make recovery contingent
on being able to execute the "parsed statements". This execution
might, however, not be possible if the system is itself not in a
consistent state .. as is normally the case during recovery.

What if, for instance, it's the catalog tables that were hosed when
the system went down ? It may be difficult to execute the parsed
statements without the catalogs.

For this reason, a major goal of ARIES was to have each and every data
object (tables/indexes) individually recoverable. So ARIES follows
page-oriented redo logging.

Having said that, page-oriented undo logging can be a pain when B-tree
pages split. For higher concurrency, ARIES uses logical undo
logging. In this case, the logs are akin to your "parsed statement"
idea.

In any case, the only place that parsed statements are useful, imo are
with searched updates that cause a large number of records to change
and with "insert into from select" statements.

Then, there is also the case that this, the "parsed statements"
approach, is not a general solution. How would you handle the "update
current of cursor" scenarios ? In this case, there is some application
logic that determines the precise records that change and how they
change.

Ergo, it is my claim that while logical redo logging does have some
benefits, it is not a viable general solution.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

Marty Scholes <marty@outputservices.com> writes:

My experience with Oracle (and now limited experience with Pg) is that
the major choke point in performance is not the CPU or read I/O, it is
the log performance of big update and select statements.

If your load is primarily big update statements, maybe so...

Essentially, the data is written twice: first to the log and then the
data files. This would be ok except the transaction is regularly frozen
while the log files sync to disk with a bunch of tiny (8KB for Oracle
and Pg) write requests.

I don't think I buy that claim. We don't normally fsync the log file
except at transaction commit (and read-only transactions don't generate
any commit record, so they don't cause an fsync). If a single
transaction is generating lots of log data, it doesn't have to wait for
that data to hit disk before it can do more stuff.

But having said that --- on some platforms our default WAL sync method
is open_datasync, which could result in the sort of behavior you are
talking about. Try experimenting with the other possible values of
wal_sync_method to see if you like them better.

If a transaction will do large updates or inserts, why don't we just log
the parsed statements in the WAL instead of the individual data blocks
that have changed?

As already pointed out, this would not give enough information to
reproduce the database state.

Some informal testing suggests that we get a factor of 8 improvement in
speed here if we completely disable fsync() in large updates under Pg.

That probably gets you into a situation where no I/O is really happening
at all, it's just being absorbed by kernel disk buffers. Unfortunately
that doesn't have a lot to do with the performance you can get if you
want to be sure you don't lose data ...

BTW, one thing you can do to reduce the WAL I/O volume in Postgres is
to increase the inter-checkpoint interval (there are two settings to
increase, one time-based and one volume-based). The first write of a
given data page after a checkpoint dumps the whole page into WAL, as a
safety measure to deal with partial page writes during power failures.
So right after a checkpoint the WAL volume goes way up. With a longer
interval between checkpoints you don't pay that price as often.

regards, tom lane

I can see that and considered it.

The seed state would need to be saved, or any particular command that is
not reproducible would need to be exempted from this sort of logging.

Again, this would apply only to situations where a small SQL command
created huge changes.

Marty

Rod Taylor wrote:

On Thu, 2004-03-11 at 21:04, Marty Scholes wrote:

I can see that and considered it.

The seed state would need to be saved, or any particular command that is
not reproducible would need to be exempted from this sort of logging.

Again, this would apply only to situations where a small SQL command
created huge changes.

I would say a majority of SQL queries in most designed systems
(timestamp). Not to mention the fact the statement itself may use very
expensive functions -- perhaps even user functions that don't repeatably
do the same thing or depend on data from other tables.

Consider a successful write to table X for transaction 2, but an
unsuccessful write to table Y for transaction 1. Transaction 1 calls a
function that uses information from table X -- but it'll get different
information this time around.

Anyway, it really doesn't matter. You're trying to save a large amount
of time that simply isn't spent in this area in PostgreSQL. fsync()
happens once with commit -- and on a busy system, a single fsync call
may be sufficient for a number of parallel backends.

A major reason for this is that logical logs make recovery contingent
on being able to execute the "parsed statements". This execution
might, however, not be possible if the system is itself not in a
consistent state .. as is normally the case during recovery.

I am not sure I follow you here. The logs should (IMHO) save both types
of data: physical pages (what happens now), or the SQL statement if it
is small and generates a bunch of changes.

If the DB state cannot be put back to a consistent state prior to a SQL
statement in the log, then NO amount of logging will help. The idea is
that the state can be put back to what it was prior to a particular log
entry, be it raw datafile blocks or a SQL statement.

What if, for instance, it's the catalog tables that were hosed when
the system went down ? It may be difficult to execute the parsed
statements without the catalogs.

See above. If this cannot be resolved prior to re-executing a statement
in the log, then the problem is beyond ANY subsequent logging.

Having said that, page-oriented undo logging can be a pain when B-tree
pages split. For higher concurrency, ARIES uses logical undo
logging. In this case, the logs are akin to your "parsed statement"
idea.

Yes, my experience exactly. Maybe we are the only company on the planet
that experiences this sort of thing. Maybe not.

In any case, the only place that parsed statements are useful, imo are
with searched updates that cause a large number of records to change
and with "insert into from select" statements.

Yes. Correlated UPDATE, INSERT INTO with subselects AND mass DELETE on
heavily indexed tables. Index creation... The list goes on and on. I
have experienced and live it all on a daily basis with Oracle. And I
despise it.

The difference is, of course, I can't even have this kind of discussion
with Oracle, but I can here. ;-)

Then, there is also the case that this, the "parsed statements"
approach, is not a general solution. How would you handle the "update
current of cursor" scenarios ? In this case, there is some application
logic that determines the precise records that change and how they
change.

Ergo, it is my claim that while logical redo logging does have some
benefits, it is not a viable general solution.

Agreed, this is not a general solution. What it is, however, is a
tremendous improvement over the current situation for transactions that
do massive changes to heavily indexed datasets.

I am working on an application right now that will require current
postal information on EVERY address in the U.S. -- street name, street
address, directional, subunit, 5 digit zip, 3 digit zip, city, state,
delivery point barcode, carrier route, lattitude, longitude, etc. Most
of these fields will need to be indexed, because they will be searched
in real time via a web application several thousand times per day.

To keep the address current, we will be updating them all (150+ million)
on a programmed basis, so we will go through and update several
million addresses EVERY DAY, while needing to ensure that the address
updates happen atomically so that they don't disrupt web activity.

Maybe this is not a "traditional" RDBMS app, but I am not in the mood to
write my own storage infrastructure for it.

Then again, maybe I don't know what I am talking about...

Marty

Sailesh Krishnamurthy wrote:

If your load is primarily big update statements, maybe so...

It is. Maybe we are anomalous here.

I don't think I buy that claim. We don't normally fsync the log file
except at transaction commit (and read-only transactions
don't generate
any commit record, so they don't cause an fsync). If a single
transaction is generating lots of log data, it doesn't have
to wait for
that data to hit disk before it can do more stuff.

I have glanced at the code, and I agree that reads do not generate
fsync() calls. Since I watched my mirrored RAID 5 arrays hit 2,000 iops
with an average request of 4 KB on a recent batch update with Pg, I
still think that Pg may be fsync()-ing a bit too often.

Since I haven't digested all of the code, I am speaking a bit out of turn.

But having said that --- on some platforms our default WAL sync method
is open_datasync, which could result in the sort of behavior you are
talking about. Try experimenting with the other possible values of
wal_sync_method to see if you like them better.

I will have to check that. I am running Sparc Solaris 8.

That probably gets you into a situation where no I/O
is really happening
at all, it's just being absorbed by kernel disk
buffers.

Few things would please me more.

Unfortunately
that doesn't have a lot to do with the performance you can get if you
want to be sure you don't lose data ...

I am not sure these are as mutually exclusive as it looks here.

BTW, one thing you can do to reduce the WAL I/O volume in Postgres is
to increase the inter-checkpoint interval (there are two settings to
increase, one time-based and one volume-based). The first write of a
given data page after a checkpoint dumps the whole page into WAL, as a
safety measure to deal with partial page writes during power failures.
So right after a checkpoint the WAL volume goes way up. With a longer
interval between checkpoints you don't pay that price as often.

I did that and it helped tremendously. Without proper tuning, I just
made the numbers pretty large:

shared_buffers = 100000
sort_mem = 131072
vacuum_mem = 65536
wal_buffers = 8192
checkpoint_segments = 32

Thanks for your feedback.

Sincerely,
Marty

Tom Lane wrote:

Anyway, it really doesn't matter. You're trying to save a large amount
of time that simply isn't spent in this area in PostgreSQL. fsync()
happens once with commit -- and on a busy system, a single fsync call
may be sufficient for a number of parallel backends.

I think you may be right. I suspect that most "busy" installations run
a large number of "light" update/delete/insert statements.

In this scenario, the kind of logging I am talking about would make
things worse, much worse.

Marty

Rod Taylor wrote:

(Just a note: my comments are not pg-specific .. indeed I don't know
much about pg recovery).

"Marty" == Marty Scholes <marty@outputservices.com> writes:

Marty> If the DB state cannot be put back to a consistent state
Marty> prior to a SQL statement in the log, then NO amount of
Marty> logging will help. The idea is that the state can be put
Marty> back to what it was prior to a particular log entry, be it
Marty> raw datafile blocks or a SQL statement.

The point is that with redo logging, you can just blindly apply the
log to the data pages in question, without even really restarting the
database.

Note that in ARIES, recovery follows: (1) analysis, (2) redo
_everything_ since last checkpoint, (3) undo losers.

So logging carefully will indeed help get the system to a consistent
state - actually after phase (2) above the system will be in precisely
the state during the crash .. and all that's left to do is undo all
the live transactions (losers).

BTW, logging raw datafile blocks would be pretty gross (physical
logging) and so ARIES logs the changes to each tuple in "logical"
fashion .. so if only one column changes only that value (before and
after images) are logged. This is what's called "physiological
logging".

Marty> See above. If this cannot be resolved prior to
Marty> re-executing a statement in the log, then the problem is
Marty> beyond ANY subsequent logging.

Not true ! By applying the log entries carefully you should be able to
restore the system to a consistent state.

Having said that, page-oriented undo logging can be a pain when
B-tree pages split. For higher concurrency, ARIES uses logical
undo logging. In this case, the logs are akin to your "parsed
statement" idea.

Marty> Yes, my experience exactly. Maybe we are the only company
Marty> on the planet that experiences this sort of thing. Maybe

Well, logical undo is still at a much lower level than parsed
statements. Each logical undo log is something like "delete key 5 from
index xyz".

Marty> Maybe this is not a "traditional" RDBMS app, but I am not
Marty> in the mood to write my own storage infrastructure for it.

I agree that your app has a lot of updates .. it's just that I'm not
convinced that logical logging is a clean solution.

I also don't have a solution for your problem :-)

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

The point is that with redo logging, you can just blindly apply the
log to the data pages in question, without even really restarting the
database.

I also am not a recovery expert, but I have watched it happen more than
once.

You bring up a good point. My (perhaps false) understanding with
recovery/redo was that the last checkpointed state was recreated, then
log changes that finished with a commit were applied and the rest discarded.

Actually, this approach would not be ideal, since the last checkpointed
state would be unavailable if any data file writes took place between
the checkpoint and the crash.

Further, post-checkpoint log entries would surely contain multiple
copies of the same data block, and there is no point in applying any but
the last log entry for a particular block.

Ok. To recap:

* Logging parsed statements don't apply to light updates and most
installations live mostly on light updates
* Logging parsed statements would not work if the checkpoint state could
not be recreated, which is likely the case.

So, this soluition won't work, and even if it did, would not apply to
the vast majority of users.

Hmmm... No wonder it hasn't been implemented yet. ;-)

Thanks again,
Marty

Sailesh Krishnamurthy wrote:

Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:

(Just a note: my comments are not pg-specific .. indeed I don't know
much about pg recovery).
...
BTW, logging raw datafile blocks would be pretty gross (physical
logging) and so ARIES logs the changes to each tuple in "logical"
fashion .. so if only one column changes only that value (before and
after images) are logged. This is what's called "physiological
logging".

What PG currently uses is sort of a hybrid approach. The basic WAL
entry normally gives tuple-level detail: a tuple image and location
for an INSERT, a new tuple image and old and new locations for UPDATE,
a tuple location for DELETE, etc. This might be a bit bulkier than
necessary for UPDATEs that change few columns, but it's consistent and
easy to replay.

However, as I mentioned to Marty, the very first change to any disk page
after a checkpoint will dump an entire (post-change) image of that page
into the log, in place of the tuple image or other detail that would
allow us to redo the change to that page. The purpose of this is to
ensure that if a page is only partially written during a power failure,
we have the ability to recreate the entire correct page contents from
WAL by replaying everything since the last checkpoint. Replay is thus
a write-only operation as far as the data files are concerned --- we
only write full pages or modify previously written pages.

(You may be thinking "well, what about a partial write to WAL" --- but
if that happens, that's where we stop replaying WAL. The checksums on
WAL entries allow us to detect the invalid data before we use it.
So we will still have a consistent valid state on disk, showing the
effects of everything up through the last undamaged WAL record.)

BTW this is a one-directional log. We do not care about UNDO, only
replay. There is nothing that need be undone from a failed transaction;
it can only have littered the database with dead tuples, which will
eventually be reclaimed by VACUUM.

Having said that, page-oriented undo logging can be a pain when
B-tree pages split.

We don't bother to undo index page splits either ...

regards, tom lane

sailesh@EECS.Berkeley.EDU writes:

- Re uni-directional logs

Of course. I forgot about PG's non-in-place update mechanisms and the
use of VACCUUM .. with versioning there are really no undo logging
necessary. I guess that means that during VACCUUM you might have to
significant work in indexes ? I'm assuming that you never merge index
pages.

Yes, VACUUM has to delete dead index entries as well as dead heap
tuples, and there are some fine points about making sure that happens
in a safe order.

I believe the current state of index space recovery is

* btree: recycles emptied index pages via a freelist; can return empty
pages to the OS if they're at the end of the index file, but will not
move pages around to make it possible to return more empty pages.
(This is all new behavior as of 7.4, before we didn't do anything about
reclaiming dead space in btrees.) Does not try to merge partially-full
pages (this is a possible future improvement but there are concurrency
problems that would need to be solved).

* hash: recycles empty pages via a freelist, never returns them to OS
short of a REINDEX. I think it does merge partially-empty pages within
each bucket chain. No provision for reducing the number of buckets
if the index population shrinks (again, short of REINDEX).

* rtree, gist: no page recycling that I know of, but I've not looked
carefully.

regards, tom lane