Re: Proposing pg_hibernate

Please find attached the updated code of Postgres Hibenator. Notable
changes since the first proposal are:

.) The name has been changed to pg_hibernator (from pg_hibernate), to
avoid confusion with the ORM Hibernate.
.) Works with Postgres 9.4
.) Uses DynamicBackgroundWorker infrastructure.
.) Ability to restore one database at a time, to avoid random-read
storms. Can be disabled by parameter.
.) A couple of bug-fixes.
.) Detailed documentation.

I am pasting the README here (also included in the attachment).

Best regards,

Postgres Hibernator
===================

This Postgres extension is a set-it-and-forget-it solution to save and restore
the Postgres shared-buffers contents, across Postgres server restarts.

It performs the automatic save and restore of database buffers, integrated with
database shutdown and startup, hence reducing the durations of
database maintenance
windows, in effect increasing the uptime of your applications.

Postgres Hibernator automatically saves the list of shared buffers to the disk
on database shutdown, and automatically restores the buffers on
database startup.
This acts pretty much like your Operating System's hibernate feature, except,
instead of saving the contents of the memory to disk, Postgres Hibernator saves
just a list of block identifiers. And it uses that list after startup to restore
the blocks from data directory into Postgres' shared buffers.

Why
--------------

DBAs are often faced with the task of performing some maintenance on their
database server(s) which requires shutting down the database. The maintenance
may involve anything from a database patch application, to a hardware upgrade.
One ugly side-effect of restarting the database server/service is that all the
data currently in database server's memory will be all lost, which was
painstakingly fetched from disk and put there in response to application queries
over time. And this data will have to be rebuilt as applications start querying
database again. The query response times will be very high until all the "hot"
data is fetched from disk and put back in memory again.

People employ a few tricks to get around this ugly truth, which range from
running a `select * from app_table;`, to `dd if=table_file ...`, to using
specialized utilities like pgfincore to prefetch data files into OS cache.
Wouldn't it be ideal if the database itself could save and restore its memory
contents across restarts!

The duration for which the server is building up caches, and trying to reach its
optimal cache performance is called ramp-up time. Postgres Hibernator is aimed
at reducing the ramp-up time of Postgres servers.

How
--------------

Compile and install the extension (you'll need a Postgres instalation and its
`pg_config` in `$PATH`):

$ cd pg_hibernator
$ make install

Then.

1. Add `pg_hibernator` to the `shared_preload_libraries` variable in
`postgresql.conf` file.
2. Restart the Postgres server.
3. You are done.

How it works
--------------

This extension uses the `Background Worker` infrastructure of
Postgres, which was
introduced in Postgres 9.3. When the server starts, this extension registers
background workers; one for saving the buffers (called `Buffer Saver`) when the
server shuts down, and one for each database in the cluster (called
`Block Readers`)
for restoring the buffers saved during previous shutdown.

When the Postgres server is being stopped/shut down, the `Buffer
Saver` scans the
shared-buffers of Postgres, and stores the unique block identifiers of
each cached
block to the disk. This information is saved under the `$PGDATA/pg_hibernator/`
directory. For each of the database whose blocks are resident in shared buffers,
one file is created; for eg.: `$PGDATA/pg_hibernator/2.postgres.save`.

During the next startup sequence, the `Block Reader` threads are
registered, one for
each file present under `$PGDATA/pg_hibernator/` directory. When the
Postgres server
has reached stable state (that is, it's ready for database connections), these
`Block Reader` processes are launched. The `Block Reader` process
reads the save-files
looking for block-ids to restore. It then connects to the respective database,
and requests Postgres to fetch the blocks into shared-buffers.

Configuration
--------------

This extension can be controlled via the following parameters. These parameters
can be set in postgresql.conf or on postmaster's command-line.

- `pg_hibernator.enabled`

Setting this parameter to false disables the hibernator features. That is,
on server startup the BlockReader processes will not be launched, and on
server shutdown the list of blocks in shared buffers will not be saved.

Note that the BuffersSaver process exists at all times, even when this
parameter is set to `false`. This is to allow the DBA to enable/disable the
extension without having to restart the server. The BufferSaver process
checks this parameter during server startup and right before shutdown, and
honors this parameter's value at that time.

To enable/disable Postgres Hibernator at runtime, change the value in
`postgresql.conf` and use `pg_ctl reload` to make Postgres re-read the new
parameter values from `postgresql.conf`.

Default value: `true`.

- `pg_hibernator.parallel`

This parameter controls whether Postgres Hibernator launches the BlockReader
processes in parallel, or sequentially, waiting for current BlockReader to
exit before launching the next one.

When enabled, all the BlockReaders, one for each database, will be launched
simultaneously, and this may cause huge random-read flood on disks if there
are many databases in cluster. This may also cause some BlockReaders to fail
to launch successfully because of `max_worker_processes` limit.

Default value: `false`.

- `pg_hibernator.default_database`

The BufferSaver process needs to connect to a database in order to perform
the database-name lookups etc. This parameter controls which database the
BufferSaver process connects to for performing these operations.

Default value: `postgres`.

Caveats
--------------

- Buffer list is saved only when Postgres is shutdown in "smart" and
"fast" modes.

That is, buffer list is not saved when database crashes, or on "immediate"
shutdown.

- A reduction in `shared_buffers` is not detected.

If the `shared_buffers` is reduced across a restart, and if the combined
saved buffer list is larger than the new shared_buffers, Postgres
Hibernator continues to read and restore blocks even after `shared_buffers`
worth of buffers have been restored.

FAQ
--------------

- What is the relationship between `pg_buffercache`, `pg_prewarm`, and
`pg_hibernator`?

They all allow you to do different things with Postgres' shared buffers.

+ pg_buffercahce:

Inspect and show contents of shared buffers

+ pg_prewarm:

Load some table/index/fork blocks into shared buffers. User needs
to tell it which blocks to load.

+ pg_hibernator:

Upon shutdown, save list of blocks stored in shared buffers. Upon
startup, loads those blocks back into shared buffers.

The goal of Postgres Hibernator is to be invisible to the user/DBA.
Whereas with `pg_prewarm` the user needs to know a lot of stuff about
what they really want to do, most likely information gathered via
`pg_buffercahce`.

- Does `pg_hibernate` use either `pg_buffercache` or `pg_prewarm`?

No, Postgres Hibernator works all on its own.

If the concern is, "Do I have to install pg_buffercache and pg_prewarm
to use pg_hibernator", the answer is no. pg_hibernator is a stand-alone
extension, although influenced by pg_buffercache and pg_prewarm.

With `pg_prewarm` you can load blocks of **only** the database
you're connected
to. So if you have `N` databases in your cluster, to restore blocks of all
databases, the DBA will have to connect to each database and invoke
`pg_prewarm` functions.

With `pg_hibernator`, DBA isn't required to do anything, let alone
connecting to the database!

- Where can I learn more about it?

There are a couple of blog posts and initial proposal to Postgres
hackers' mailing list. They may provide a better understanding of
Postgres Hibernator.

[Proposal](http://www.postgresql.org/message-id/CABwTF4Ui_anAG+ybseFunAH5Z6DE9aw2NPdy4HryK+M5OdXCCA@mail.gmail.com)

[Introducing Postrges
Hibernator](http://gurjeet.singh.im/blog/2014/02/03/introducing-postgres-hibernator/)

[Demostrating Performance
Benefits](http://gurjeet.singh.im/blog/2014/04/30/postgres-hibernator-reduce-planned-database-down-times/)

On Mon, Feb 3, 2014 at 7:18 PM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> Please find attached the pg_hibernate extension. It is a
> set-it-and-forget-it solution to enable hibernation of Postgres
> shared-buffers. It can be thought of as an amalgam of pg_buffercache and
> pg_prewarm.
>
> It uses the background worker infrastructure. It registers one worker
> process (BufferSaver) to save the shared-buffer metadata when server is
> shutting down, and one worker per database (BlockReader) when restoring the
> shared buffers.
>
> It stores the buffer metadata under $PGDATA/pg_database/, one file per
> database, and one separate file for global objects. It sorts the list of
> buffers before storage, so that when it encounters a range of consecutive
> blocks of a relation's fork, it stores that range as just one entry, hence
> reducing the storage and I/O overhead.
>
> On-disk binary format, which is used to create the per-database save-files,
> is defined as:
> 1. One or more relation filenodes; stored as r<relfilenode>.
> 2. Each realtion is followed by one or more fork number; stored as
> f<forknumber>
> 3. Each fork number is followed by one or more block numbers; stored as
> b<blocknumber>
> 4. Each block number is followed by zero or more range numbers; stored as
> N<number>
>
> {r {f {b N* }+ }+ }+
>
> Possible enhancements:
> - Ability to save/restore only specific databases.
> - Control how many BlockReaders are active at a time; to avoid I/O storms.
> - Be smart about lowered shared_buffers across the restart.
> - Different modes of reading like pg_prewarm does.
> - Include PgFincore functionality, at least for Linux platforms.
>
> The extension currently works with PG 9.3, and may work on 9.4 without any
> changes; I haven't tested, though. If not, I think it'd be easy to port to
> HEAD/PG 9.4. I see that 9.4 has put a cap on maximum background workers via
> a GUC, and since my aim is to provide a non-intrusive no-tuning-required
> extension, I'd like to use the new dynamic-background-worker infrastructure
> in 9.4, which doesn't seem to have any preset limits (I think it's limited
> by max_connections, but I may be wrong). I can work on 9.4 port, if there's
> interest in including this as a contrib/ module.
>
> To see the extension in action:
>
> .) Compile it.
> .) Install it.
> .) Add it to shared_preload_libraries.
> .) Start/restart Postgres.
> .) Install pg_buffercache extension, to inspect the shared buffers.
> .) Note the result of pg_buffercache view.
> .) Work on your database to fill up the shared buffers.
> .) Note the result of pg_buffercache view, again; there should be more
> blocks than last time we checked.
> .) Stop and start the Postgres server.
> .) Note the output of pg_buffercache view; it should contain the blocks seen
> just before the shutdown.
> .) Future server restarts will automatically save and restore the blocks in
> shared-buffers.
>
> The code is also available as Git repository at
> https://github.com/gurjeet/pg_hibernate/
>
> Demo:
>
> $ make -C contrib/pg_hibernate/
> $ make -C contrib/pg_hibernate/ install
> $ vi $B/db/data/postgresql.conf
> $ grep shared_preload_libraries $PGDATA/postgresql.conf
> shared_preload_libraries = 'pg_hibernate' # (change requires restart)
>
> $ pgstart
> waiting for server to start.... done
> server started
>
> $ pgsql -c 'create extension pg_buffercache;'
> CREATE EXTENSION
>
> $ pgsql -c 'select count(*) from pg_buffercache where relblocknumber is not
> null group by reldatabase;'
> count
> -------
> 163
> 14
> (2 rows)
>
> $ pgsql -c 'create table test_hibernate as select s as a, s::char(1000) as b
> from generate_series(1, 100000) as s;'
> SELECT 100000
>
> $ pgsql -c 'create index on test_hibernate(a);'
> CREATE INDEX
>
> $ pgsql -c 'select count(*) from pg_buffercache where relblocknumber is not
> null group by reldatabase;'
> count
> -------
> 2254
> 14
> (2 rows)
>
> $ pgstop
> waiting for server to shut down....... done
> server stopped
>
> $ pgstart
> waiting for server to start.... done
> server started
>
> $ pgsql -c 'select count(*) from pg_buffercache where relblocknumber is not
> null group by reldatabase;'
> count
> -------
> 2264
> 17
> (2 rows)
>
> There are a few more blocks than the time they were saved, but all the
> blocks from before the restart are present in shared buffers after the
> restart.
>
> Best regards,
> --
> Gurjeet Singh http://gurjeet.singh.im/
>
> EDB www.EnterpriseDB.com

--
Gurjeet Singh http://gurjeet.singh.im/

EDB www.EnterpriseDB.com