Horton

Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

Ashutosh Sharma <ashu.coek88@gmail.com>

View thread View on postgr.es
From: Ashutosh Sharma <ashu.coek88@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Greg Nancarrow <gregn4422@gmail.com>, Andres Freund <andres@anarazel.de>, Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-12-07T00:52:34Z
Lists: pgsql-hackers 
Thanks Robert for sharing your thoughts.

On Mon, Dec 6, 2021 at 11:16 PM Robert Haas <robertmhaas@gmail.com> wrote:

> On Mon, Dec 6, 2021 at 9:23 AM Ashutosh Sharma <ashu.coek88@gmail.com>
> wrote:
> > One last point - If we try to clone a huge database, as expected CREATE
> DATABASE emits a lot of WALs, causing a lot of intermediate checkpoints
> which seems to be affecting the performance slightly.
>
> Yes, I think this needs to be characterized better. If you have a big
> shared buffers setting and a lot of those buffers are dirty and the
> template database is small, all of which is fairly normal, then this
> new approach should be much quicker. On the other hand, what if the
> situation is reversed? Perhaps you have a small shared buffers and not
> much of it is dirty and the template database is gigantic. Then maybe
> this new approach will be slower. But right now I think we don't know
> where the crossover point is, and I think we should try to figure that
> out.
>

Yes I think so too.


>
> So for example, imagine tests with 1GB of shard_buffers, 8GB, and
> 64GB. And template databases with sizes of whatever the default is,
> 1GB, 10GB, 100GB. Repeatedly make 75% of the pages dirty and then
> create a new database from one of the templates. And then just measure
> the performance. Maybe for large databases this approach is just
> really the pits -- and if your max_wal_size is too small, it
> definitely will be. But, I don't know, maybe with reasonable settings
> it's not that bad. Writing everything to disk twice - once to WAL and
> once to the target directory - has to be more expensive than doing it
> once. But on the other hand, it's all sequential I/O and the data
> pages don't need to be fsync'd, so perhaps the overhead is relatively
> mild. I don't know.
>

So far, I haven't found much performance overhead with a few gb of data in
the template database. It's just a bit with the default settings, perhaps
setting a higher value of max_wal_size would reduce this overhead.

--
With Regards,
Ashutosh Sharma.

Commits

  1. When using the WAL-logged CREATE DATABASE strategy, bulk extend.

  2. Avoid using a fake relcache entry to own an SmgrRelation.

  3. Fix data-corruption hazard in WAL-logged CREATE DATABASE.

  4. initdb: When running CREATE DATABASE, use STRATEGY = WAL_COPY.

  5. Simplify a needlessly-complicated regular expression.

  6. In 020_createdb.pl, change order of command-line arguments.

  7. Add new block-by-block strategy for CREATE DATABASE.

  8. Fix replay of create database records on standby

  9. Refactor code for reading and writing relation map files.

  10. Replace RelationOpenSmgr() with RelationGetSmgr().

  11. Refactor the fsync queue for wider use.