Re: WIP patch for hint bit i/o mitigation - Mailing list pgsql-hackers

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@gmail.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.