Re: Performance Improvement by reducing WAL for Update Operation
От | Mike Blackwell |
---|---|
Тема | Re: Performance Improvement by reducing WAL for Update Operation |
Дата | |
Msg-id | CANPAkgvzGLxURUUoX7ZXMZWeO9r=VjMXM6T4KQ7L=nV4ZZL2BQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Performance Improvement by reducing WAL for Update Operation (Amit kapila <amit.kapila@huawei.com>) |
Ответы |
Re: Performance Improvement by reducing WAL for Update Operation
|
Список | pgsql-hackers |
I can't comment on further direction for the patch, but since it was marked as Needs Review in the CF app I took a quick look at it.
Does it need documentation for the GUC variable 'wal_update_compression_ratio'?
It patches and compiles clean against the current Git HEAD, and 'make check' runs successfully.
Does it need documentation for the GUC variable 'wal_update_compression_ratio'?
__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
On Tue, Jul 2, 2013 at 2:26 AM, Hari Babu <haribabu.kommi@huawei.com> wrote:
The patch is rebased to use the new PG LZ algorithm optimization changesOn Friday, June 07, 2013 5:07 PM Amit Kapila wrote:
>On Wednesday, March 06, 2013 2:57 AM Heikki Linnakangas wrote:
>> On 04.03.2013 06:39, Amit Kapila wrote:
>> > On Sunday, March 03, 2013 8:19 PM Craig Ringer wrote:
>> >> On 02/05/2013 11:53 PM, Amit Kapila wrote:
>> >>>> Performance data for the patch is attached with this mail.
>> >>>> Conclusions from the readings (these are same as my previous
>> patch):
>> >>>>
>>
>> The attached patch also just adds overhead in most cases, but the
>> overhead is much smaller in the worst case. I think that's the right
>> tradeoff here - we want to avoid scenarios where performance falls off
>> the cliff. That said, if you usually just get a slowdown, we certainly
>> can't make this the default, and if we can't turn it on by default,
>> this probably just isn't worth it.
>>
>> The attached patch contains the variable-hash-size changes I posted in
>> the "Optimizing pglz compressor". But in the delta encoding function,
>> it goes further than that, and contains some further micro-
>> optimizations:
>> the hash is calculated in a rolling fashion, and it uses a specialized
>> version of the pglz_hist_add macro that knows that the input can't
>> exceed 4096 bytes. Those changes shaved off some cycles, but you could
>> probably do more. One idea is to only add every 10 bytes or so to the
>> history lookup table; that would sacrifice some compressibility for
>> speed.
>>
>> If you could squeeze pglz_delta_encode function to be cheap enough
>> that we could enable this by default, this would be pretty cool patch.
>> Or at least, the overhead in the cases that you get no compression
>> needs to be brought down, to about 2-5 % at most I think. If it can't
>> be done easily, I feel that this probably needs to be dropped.
>After trying some more on optimizing pglz_delta_encode(), I found that if
we use new data also in history, then the results of compression and cpu
utilization >are much better.
>In addition to the pg lz micro optimization changes, following changes are
done in modified patch
>1. The unmatched new data is also added to the history which can be
referenced later.
>2. To incorporate this change in the lZ algorithm, 1 extra control bit is
needed to indicate if data is from old or new tuple
which got committed recently.two short fields, no change | 1232911016 | 35.1784930229187
Performance Data
-----------------
Head code:
testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, one changed | 1240322016 | 35.0436308383942
two short fields, both changed | 1235318352 | 35.4989421367645
one short and one long field, no change | 1042332336 | 23.4457180500031
ten tiny fields, all changed | 1395194136 | 41.9023628234863
hundred tiny fields, first 10 changed | 626725984 | 21.2999589443207
hundred tiny fields, all changed | 621899224 | 21.6676609516144
hundred tiny fields, half changed | 623998272 | 21.2745981216431
hundred tiny fields, half nulled | 557714088 | 19.5902800559998
pglz-with-micro-optimization-compress-using-newdata-2:
testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, no change | 1232903384 | 35.0115969181061
two short fields, one changed | 1232906960 | 34.3333759307861
two short fields, both changed | 1232903520 | 35.7665238380432
one short and one long field, no change | 649647992 | 19.4671010971069
ten tiny fields, all changed | 1314957136 | 39.9727990627289
hundred tiny fields, first 10 changed | 458684024 | 17.8197758197784
hundred tiny fields, all changed | 461028464 | 17.3083391189575
hundred tiny fields, half changed | 456528696 | 17.1769199371338
hundred tiny fields, half nulled | 480548936 | 18.81720495224(pgbench_lz_opt_compress_using_newdata-2.htm)
Observation
---------------
1. It yielded compression in more cases (refer all cases of hundred tiny
fields)
2. CPU- utilization is also better.
Performance data for pgbench related scenarios is attached in document3. There is small performance penality for single-thread (0.36~3.23),
1. Better reduction in WAL
2. TPS increase can be observed after records size is >=250
but when penality is 3.23 in single thread, for 8 threads TPS improvement
is high.
Please suggest any further proceedings on this patch.
Regards,
Hari babu.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "MauMau"Дата:
Сообщение: Re: [9.3 bug fix] backends emit hundreds of messages when statistics file is inaccessible
Следующее
От: Josh BerkusДата:
Сообщение: Re: Performance Improvement by reducing WAL for Update Operation