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

On Friday, September 28, 2012 7:03 PM Amit Kapila wrote:
> > On Thursday, September 27, 2012 6:39 PM Amit Kapila wrote:
> > > On Thursday, September 27, 2012 4:12 PM Heikki Linnakangas wrote:
> > > On 25.09.2012 18:27, Amit Kapila wrote:
> > > > If you feel it is must to do the comparison, we can do it in same
> > way
> > > > as we identify for HOT?
> > >
>
>
> > Now I shall do the various tests for following and post it here:
> > a. Attached Patch in the mode where it takes advantage of history
> > tuple b. By changing the logic for modified column calculation to use
> > calculation for memcmp()
>
>
> Attached documents contain data for following scenarios for both 'a' (LZ
> compression patch) and 'b' (modified wal patch) patches:
>
> 1. Using fixed string (last few bytes are random) to update the column
> values.
> Total record length = 1800
> Updated columns length = 250
> 2. Using random string to update the column values
> Total record length = 1800
> Updated columns length = 250
>
> Observations -
> 1. With both patches performance increase is very good .
> 2. Almost same performance increase with both patches with slightly
> more for LZ compression patch.
> 3. TPS is varying with LZ patch, but if we take average it is
> equivalent to other patch.

> Other Performance tests I am planning to conduct
> 1. Using bigger random string to update the column values
> Total record length = 1800
> Updated columns length = 250
> 2. Using fixed string (last few bytes are random) to update the column
> values.
> Total record length = 1800
> Updated columns length = 50, 100, 500, 750, 1000, 1500, 1800

1. Please find the results (pgbench_test.htm) for point -2 where there is
one fixed column updation (last few bytes are random) and second column
updation is 32 byte random string. The results for 50, 100 are still going
on others are attached with this mail.
2. Attached pgbench test code for a modification of 25 and 250 bytes record
size having total record length as 1800.
For the other record size modification tests, the schema is changed
accordingly.
3. Added a random string generation for updating some column data from 250
record modification test onwards.
CREATE OR REPLACE FUNCTION random_text_md5_v2(INTEGER)
RETURNS TEXT
LANGUAGE SQL
AS $$
select upper(
substring(
(
SELECT string_agg(md5(random()::TEXT), '')
FROM generate_series(1, CEIL($1 / 32.)::integer)
),
$1)
);
$$;
4. Observations
a. When the size of updated value is less, the performance is almost
same for both the patches.
b. When the size of updated value is more, the performance with LZ patch
is better.

> 3. Recovery performance test as suggested by Noah
Still not started.

> 4. Complete testing for LZ compression patch using testcases defined for
> original patch
a. During testing of LZ patch, few issues are found related to when the
updated record contains NULLS. Working on it to fix.

Any comments/suggestions regarding performance/functionality test?

With Regards,
Amit Kapila.