Re: [WIP] Performance Improvement by reducing WAL for Update Operation

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Amit Kapila
Sent: Wednesday, August 22, 2012 8:34 AM
From: Jesper Krogh [mailto:jesper(at)krogh(dot)cc]
Sent: Wednesday, August 22, 2012 1:13 AM
On 21/08/12 16:57, Amit kapila wrote:

>>Test results:

>>>1. The pgbench test run for 10min.
>>> 2. The test reult is for modified pgbench (such that total row size is
1800 and updated columns are of length 300) tpc-b testcase.
>>> The result and modified pgbench code is attached with mail.

>>> 3. The performance improvement shown in the m/c I have tested is quite
good (more than 100% for sync commit = off).

>> I cannot comment on completeness or correctness of the code, but I do
think a relevant test would be
>> to turn synchronous_commit on as default.

>> Even though you aim at an improved performance, it would be nice to see
the reduction in WAL-size
>>as an effect of this patch.

> Yes, I shall take care of doing both the above tests and send the report.

The data for WAL reduction is as below:

The number of transactions processed are 16000 by doing update only of size
250 bytes with an record size of 1800.

I had made sure no full_page_write happens by making checkpoint interval and
checkpoints segments longer.

Original code - 1.8G Modified code - 1.1G Diff - 63% reduction, incase
of fill factor 100.
Original code - 1.6G Modified code - 1.1G Diff - 45% reduction, incase
of fill factor 80.

I am still in process of collecting synchronous commit mode on data.

Please let me know what more kind of data will be helpful to indicate the
benefits of this implementation.

With Regards,

Amit Kapila.