Re: Improving the Performance of Full Table Updates

Gokulakannan Somasundaram wrote:
> Hi Tom/ Heikki,
> Thanks for the suggestion. After profiling i got similar results.
> So i am thinking of a design like this to get the performance improvement.
>
> a) We can get one page for insert(during update) and we will hold the write
> lock on it, till the page gets filled. In this way,
> RelationGetBufferForTuple will get called only once for one page of inserts.

The locking actually isn't that expensive once you have the page pinned.
For starters, keep the page pinned over calls to heap_update, and just
relock it instead of calling RelationGetBufferForTuple. Unsurprisingly,
this is not a new idea:
http://archives.postgresql.org/pgsql-patches/2007-05/msg00499.php.

> b) Do you think if we can optimize the XlogInsert in such a way, it will
> write a page instead of writing all the records in the page. I think we
> need to write a recovery routine for the same. Currently the page gets
> flushed to the WAL, if it gets modified after the checkpoint. So i still
> need to understand those code pieces. But do you think it is wise to
> continue working on this line?

It's going to be very difficult at least. There's a lot of race
conditions lurking if you try to coalesce multiple updates to a single
WAL record.

That said, making XLogInsert faster would help a lot of use cases, not
only full-table udpates. Most of the time is spent calculating the CRC,
but it has some other non-trivial overhead as well. Profiling XLogInsert
in more detail, and figuring out how to make it faster would be very nice.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com