Re: WIP patch for hint bit i/o mitigation

On Thu, Dec 13, 2012 at 6:36 PM, Hari Babu <haribabu(dot)kommi(at)huawei(dot)com>wrote:

> On Thu, Dec 7, 2012 at 7:56 PM, Hari babu
> <haribabu(dot)kommi(at)Huawei(dot)com> wrote:****
>
> >>On Thu, Dec 6, 2012 at 8:52 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:****
>
> >>Thanks for that -- that's fairly comprehensive I'd say. I'm quite ****
>
> ** **
>
> >>interested in that benchmarking framework as well. Do you need help ***
> *
>
> >>setting up the scripts?****
>
> ** **
>
> >Presently I am testing with pgbench custom query option & taking IO & VM
> statistics in parallel. ****
>
> >Following way I had written the test script for case -1.****
>
> ** **
>
> >./psql -f bench_test_1_init.sql postgres iostat -t 1 -d >
> hint.test1.iostat.reading_3.txt & vmstat -n 1 >
> >hint.test1.vmstat.reading_3.txt & ./pgbench -f bench_test_1.sql -T 300 -c
> 8 -j 8 -n postgres killall -s SIGINT iostat >killall -s SIGINT vmstat****
>
> ** **
>
> >Where the sql files are as follows:****
>
> >-- bench_test_1.sql****
>
> >select count(*) from bench where f1 is not null;****
>
> ** **
>
> >--bench_test_1_init.sql****
>
> >drop table if exists bench;****
>
> >create table bench(f0 int primary key, f1 char(50)); insert into bench
> values (generate_series(1, 100000), 'a'); insert >into bench values
> (generate_series(100001, 200000), 'a'); ...****
>
> >insert into bench values (generate_series(9800001, 9900000), 'a'); insert
> into bench values (generate_series(9900001, >10000000), 'a'); checkpoint;*
> ***
>
> ** **
>
> >I will provide the test results later.****
>
> ** **
>
> Please find the review of the patch.
>
> *Basic stuff:*
> ------------
> - Patch applies with offsets.
> - Compiles cleanly with no warnings
> - Regression Test pass.
>
> *Code Review:*
> -------------
> 1. Better to set the hint bits for the tuples in a page, if the
> page is already dirty.
>
> *Test cases:*
> *----------*
> * *Test cases are already described in the following link.
>
> http://archives.postgresql.org/message-id/00d301cdd398$6e3fff30$4abffd90$@kapila@huawei.com
>
> *Test Results:*
> *------------*
> Machine details:
> CPU cores : 4
> RAM : 24GB
> OS : Suse Linux 10 SP2
>
> ****
>
> Configuration:
> shared_buffers = 500MB
> autovacuum = off
> checkpoint_segments = 256
> checkpoint_timeout = 10min
>
> Following result are average of 3 runs each run is of 5min through pgbench
> custom query.
>
> *Test case - 1**:*
> Init:
> None
> Run:
> create temp table atri1 as select v from
> generate_series(1,1000000) v;
> select * from atri1;
> VACUUM atri1;
> DROP TABLE atri1;
>
> *Test case - 2**:*
> Init:
> create table atri1 as select v from
> generate_series(1,1000000) v;
> Run:
> select * from atri1;
>
> *Test case - 3**: (without pgbench)*
> connect two sessions s1, s2
> s1 : start transaction;
> s2 :
> create table atri1 as select v from
> generate_series(1,1000000) v;
> \timing
> select * from atri1;
> VACUUM atri1;
>
> *Results:*
> 9.3devel(Tps) HintbitIO(Tps)
> ---------------------------------------------------
> Test case - 1: 1.762946 1.922219
> Test case - 2: 4.038673 4.044356
>
> *Pgbench without vacuum scale factor 75, 8 threads & 8 client. (refer
> reports attached)*
> Default tables select : 64980.736149 64550.118693
> Unlogged tables select: 64874.974334 64550.118693
>
> *Multiple transaction bulk inserts: Select performance (refer script -1 &
> 2 which attached)*
> sequential scan: 6.492680 6.382014
> Index scan: 1.386851 1.36234
>
> *Single transaction bulk inserts: Select performance (refer script - 3 &
> 4 which attached)*
> sequential scan: 6.49319 6.3800147
> Index scan: 1.384121 1.3615277
>
> *Long transaction open then Vacuum & select performance in milli seconds.
> (refer reports output)*
> Testcase - 3:
> Single Vacuum Perf : 128.302 ms 181.292 ms
> Single select perf : 214.107 ms 177.057 ms
> Total : 342.409 ms 358.349 ms
>
> I was not able to find the reason why in some of cases results are low so
> please use the attached scripts to validate the same.
>
> I was not able to provide the IO statistics as IOSTAT & VMSTAT was giving
> the current snapshot
> but not the cumulative from start to end of test execution.
>
> *Documentation:*
> *-------------*
> No user visible changes, so no documentation is need to update.
>
>
> ****
>
> Regards,****
>
> Hari babu.****
>

Thanks for the review and tests.

The remarkable difference between 9.3devel and Hint Bit IO is present only
in test -3,right? I have the feeling that the original case we
discussed(vacuum setting the hint bits) is taking place here and hence the
decrease in performance.

Atri

--
Regards,

Atri
*l'apprenant*