Horton

Re: WIP: Avoid creation of the free space map for small tables

John Naylor <jcnaylor@gmail.com>

View thread View on postgr.es
From: John Naylor <jcnaylor@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-10-23T13:42:02Z
Lists: pgsql-hackers

Attachments

I wrote:

> Once this is in shape, I'll do some performance testing.

On second thought, there's no point in waiting, especially if a
regression points to a design flaw.

I compiled patched postgres with HEAP_FSM_CREATION_THRESHOLD set to
32, then ran the attached script which populates 100 tables with
varying numbers of blocks. I wanted a test that created pages eagerly
and wrote to disk as little as possible. Config was stock, except for
fsync = off. I took the average of 10 runs after removing the slowest
and fastest run:

# blocks	master		patch
4			36.4ms		33.9ms
8			50.6ms		48.9ms
12			58.6ms		66.3ms
16			65.5ms		81.4ms

It seems under these circumstances a threshold of up to 8 performs
comparably to the master branch, with small block numbers possibly
faster than with the FSM, provided they're in shared buffers already.
I didn't bother testing higher values because it's clear there's a
regression starting around 10 or so, beyond which it helps to have the
FSM.

A case could be made for setting the threshold to 4, since not having
3 blocks of FSM in shared buffers exactly makes up for the 3 other
blocks of heap that are checked when free space runs out.

I can run additional tests if there's interest.

-John Naylor

Commits

  1. Improve code comments in b0eaa4c51b.

  2. During pg_upgrade, conditionally skip transfer of FSMs.

  3. Add more tests for FSM.

  4. Doc: Update the documentation for FSM behavior for small tables.

  5. Make FSM test portable.

  6. Avoid creation of the free space map for small heap relations, take 2.

  7. Move page initialization from RelationAddExtraBlocks() to use, take 2.

  8. Avoid creation of the free space map for small heap relations.

  9. In bootstrap mode, don't allow the creation of files if they don't already