Re: Performance Improvement by reducing WAL for Update Operation

On Thursday, October 25, 2012 11:04 PM Amit kapila wrote:
> On Thursday, October 25, 2012 5:43 AM, Noah Misch wrote:
> On Tue, Oct 23, 2012 at 08:21:54PM -0400, Noah Misch wrote:
> >> -Patch- -tps(at)-c1- -tps(at)-c2- -tps(at)-c8- -WAL(at)-c8-
> >> HEAD,-F80 816 1644 6528 1821 MiB
> >> xlogscale,-F80 824 1643 6551 1826 MiB
> >> xlogscale+lz,-F80 717 1466 5924 1137 MiB
> >> xlogscale+lz,-F100 753 1508 5948 1548 MiB
> >
> >> Those are short runs with no averaging of multiple iterations; don't
> >> put too much faith in the absolute numbers.
>
> > I decided to rerun those measurements with three 15-minute runs. I
> > removed the -F100 test and added wal_update_changes_v2.patch (delta
> > encoding version) to the mix. Median results:
>
> > -Patch- -tps(at)-c1- -tps(at)-c2- -tps(at)-c8- -WAL(at)-c8-
> > HEAD,-F80 832 1679 6797 44 GiB
> > scale,-F80 830 1679 6798 44 GiB
> > scale+lz,-F80 736 1498 6169 11 GiB
> > scale+delta,-F80 841 1713 7056 10 GiB
>
> > The numbers varied little across runs. So we see the same general
> > trends as with the short runs; overall performance is slightly higher
> > across the board, and the fraction of WAL avoided is much higher. I'm
> > suspecting the patches shrink WAL better in these longer runs because
> > the WAL of a short run contains a higher density of full-page images.
>
> I have fixed all the review comments raised in delta encoding approach
> raised by you (for needs toast, for now I have kept the code as it will
> not go in patch of encoding for it. however it can be changed.).
> I have also fixed the major comment about this patch by Heikki and Tom
> [use memcmp to find modified columns].
> The patch containing review comments fixed for delta encoding method is
> attached with this mail.
>
> The readings with modified patch are as below, the detailed
> configuration used in the file attached:
>
> -Patch- -tps(at)-c1- -tps(at)-c2- -tps(at)-
> c8-
> scale,-F80 834 1451
> 2701
> scale+lz,-F80 659 1276
> 4650
> scale+delta+review_fixed,-F80 873 1704 5509
>
>
> The results are similar to your findings except for 8 client result.
> I have one doubt that my m/c is 4core m/c on which I am taking data
> whereas yours is 8 core.
>
> So tommorow I shall post the results with 1,2,4,8 clients as well.
> Any further suggestions?

The data with 1,2,4,8 clients is as below:

-Patch- -tps(at)-c1- -tps(at)-c2- -tps(at)-c4- -tps(at)-c8-
HEAD,-F80 874 1350 2129 2733
scale,-F80 884 1393 2106 2671
scale+lz,-F80 722 1342 2646 4568
scale+delta,-F80 892 1670 3323 5481

From the above readings, it is clear that delta encoding approach has better
performance and scaling.
Do you see the need of any further investigation from myside?

With Regards,
Amit Kapila.