dCache/dcache — pull request #395 by gbehrmann (2014-02-04 12:56:28+00:00)
Title: chimera: Optimized creation time column changeset

After extensive tests, I found a way to significantly improve the time it takes
to add the creation time column.

The key problem is not adding the column but to update every row in the
t_inodes table to populate the new column. Due to the MVCC nature of postgresql
tables, updating a row doesn't replace it - instead a new copy of the row is
added while the existing row is kept until the transaction within which the
update executes is committed. Since every row in the entire table is updated
in a single transaction, the table effectively doubles in size during this
process. In general, when a row is updated, any index pointing to the row needs
to be updated too. This was the source of the high amount of I/O observed on
our postgresql instance.

Postgresql has a trick to reduce the cost of updates: Heap-only tuples (HOT)
allow the updated row to be stored within the same logical block as the
existing row. In such cases the index doesn't need to be updated, as the index
really points to blocks, not individual rows. HOT updates however require that
there is enough free space in blocks to accomodate additional copies of rows.
During regular operation, deleted rows leave such free space. Also updated rows
once the transaction is committed leave the old copy free to be reclaimed.

In this particular update we however update every record in the entire table
within a single transaction. This means that to avoid expensive index updates,
we need enough free space in blocks to duplicate every row. This is exactly
what this patch achieves: It changes the fillfactor (percentage of a block
filled with regular inserts, leaving the rest available for HOT updates) to
45%, leaving plenty of room for adding an additional copy of every row. After
the update the fillfactor is raised to 75% to avoid wasting too much disk space
in the future. The default fillfactor in postgresql is 100%, which is fine for
mostly static data, but not fine if rows are updated frequently (we update
inodes frequently due to atime updates and due to link count changes for
directories). The addition of the new column is altered too: We now add the
non-null constraint with a computed default value right away, forcing a table
rewrite (which will use the new fill factor). Once the transaction is
committed, old copies of rows can be reallocated, so there will again be 55%
free space in the table. Note that after raising the fillfactor to 75%, we do
*not* rewite the table to reclaim the space. Postgresql will use the space when
creating new inodes, but an admin can manually reclaim the space by running
vacuum full on t_inodes (only recommended with Postgresql 9.0 or higher - use
cluster on older versions). Unless tight on disk space, this should not be
necessary.

The difference of this change on our test system is tremendous (similar hardware
to our production system, except significantly less RAM). The old changeset took
approximately 41 hours to complete (for around 48 million inodes - the same
changeset took approx 5 hours on our production hardware). The updated changesets
complete in approx. 40 minutes (half of that is for adding the column; the other
half for update the creation time).

The changeset has been written such that it will not run if the column was already
added to t_inodes (as is the case at NDGF).

Target: trunk
Request: 2.8
Require-notes: no
Require-book: no
Acked-by: Tigran Mkrtchyan
Acked-by: Paul Millar
Patch: http://rb.dcache.org/r/6530/
(cherry picked from commit b25f8dfbc214d09ae62be3da0a8f16f1c283c612)