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

<div class="WordSection1"><p class="MsoPlainTextCxSpFirst"><span style="font-size:8.0pt;font-family:"Courier New"">On
Thu,Dec 7, 2012 at 7:56 PM, Hari babu <haribabu(dot)kommi(at)Huawei(dot)com> wrote:</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>>On Thu, Dec 6, 2012 at
8:52PM, Merlin Moncure <<a href="mailto:mmoncure@gmail.com">mmoncure@gmail.com</a>> wrote:</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>>Thanks for that -- that's
fairlycomprehensive I'd say.  I'm quite </span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew""> </span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>>interested in that benchmarking framework as well.  Do you
needhelp </span><p class="MsoPlainTextCxSpMiddle"><span style="font-size:8.0pt;font-family:"Courier
New"">>>settingup the scripts?</span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew""> </span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>Presently I am testing with pgbench custom query option &
takingIO & VM statistics in parallel. </span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>Following way I had written the test script for case -1.</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New""> </span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>./psql -f
bench_test_1_init.sqlpostgres 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</span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew""> </span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>Where the sql files are as follows:</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>-- bench_test_1.sql</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>select count(*) from  bench
wheref1 is not null;</span><p class="MsoPlainTextCxSpMiddle"><span style="font-size:8.0pt;font-family:"Courier
New""> </span><pclass="MsoPlainTextCxSpMiddle"><span style="font-size:8.0pt;font-family:"Courier
New"">>--bench_test_1_init.sql</span><pclass="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>drop table if exists bench;</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>create table bench(f0 int
primarykey, f1 char(50)); insert into bench values (generate_series(1, 100000), 'a'); insert >into bench values
(generate_series(100001,200000), 'a'); ...</span><p class="MsoPlainTextCxSpMiddle"><span
style="font-size:8.0pt;font-family:"CourierNew"">>insert into bench values (generate_series(9800001, 9900000), 'a');
insertinto bench values (generate_series(9900001, >10000000), 'a'); checkpoint;</span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New""> </span><p
class="MsoPlainTextCxSpMiddle"><spanstyle="font-size:8.0pt;font-family:"Courier New"">>I will provide the test
resultslater.</span><p class="MsoPlainTextCxSpLast"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""> </span><pclass="MsoNormalCxSpFirst"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Pleasefind the review of the patch.</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:9.0pt;font-family:"Arial","sans-serif""><br/></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Basicstuff:</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">------------</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">-Patch applies with offsets. </span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">-Compiles cleanly with no warnings</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">-Regression Test pass.</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">CodeReview:</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">-------------</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       1. Better to set the hint bits for the tuples in a
page,if the page is already dirty. </span><span style="font-size:8.0pt;font-family:"Arial","sans-serif""><br /><br
/></span><b><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif"">Test cases:</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">----------</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       </span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Testcases are already described in the following
link.</span><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       </span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><a
href="http://archives.postgresql.org/message-id/00d301cdd398$6e3fff30$4abffd90$@kapila@huawei.com"><span
style="color:blue">http://archives.postgresql.org/message-id/00d301cdd398$6e3fff30$4abffd90$@kapila@huawei.com</span></a><br
/><br/></span><b><span style="font-size:8.0pt;font-family:"Arial","sans-serif"">Test Results:</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">------------</span></b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Machinedetails:</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       CPU cores : 4</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       RAM       : 24GB</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       OS        : Suse Linux 10 SP2</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""></span><pclass="MsoNormalCxSpMiddle"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Configuration:</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">   shared_buffers = 500MB</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">   autovacuum = off</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">   checkpoint_segments = 256</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">   checkpoint_timeout = 10min</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Followingresult are average of 3 runs each run is of 5min
throughpgbench custom query.</span><span style="font-size:8.0pt;font-family:"Arial","sans-serif""><br /><br
/></span><u><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif"">Test case - 1</span></u><u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">:</span></u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       Init:</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               None</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       Run:</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               create temp table atri1 as select v from
generate_series(1,1000000)v;</span><span style="font-size:8.0pt;font-family:"Arial","sans-serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               select * from atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               VACUUM atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               DROP TABLE atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Testcase - 2</span></u><u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">:</span></u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">      Init: </span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               create table atri1 as select v from
generate_series(1,1000000)v;  </span><span style="font-size:8.0pt;font-family:"Arial","sans-serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       Run:</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               select * from atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Testcase - 3</span></u><u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">:(without pgbench)</span></u><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">       connect two sessions s1, s2</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               s1 : start transaction;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">               s2 : </span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">                       </span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">createtable atri1 as select v from generate_series(1,1000000)
v;</span><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">                       \timing</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">                       select * from atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">                       VACUUM atri1;</span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif""><br/><br /></span><b><u><span
style="font-size:8.0pt;font-family:"CourierNew"">Results:</span></u></b><span style="font-size:8.0pt;font-family:"Times
NewRoman","serif""><br /></span><span style="font-size:8.0pt;font-family:"Courier New"">                  9.3devel(Tps)
  HintbitIO(Tps)         </span><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">---------------------------------------------------</span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Test case - 1:     1.762946        1.922219         </span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Test case - 2:     4.038673        4.044356         </span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /><br /></span><u><span
style="font-size:8.0pt;font-family:"CourierNew"">Pgbench without vacuum scale factor 75, 8 threads & 8 client.
(referreports attached)</span></u><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br
/></span><spanstyle="font-size:8.0pt;font-family:"Courier New"">Default tables select :      64980.736149  
 64550.118693    </span><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Unlogged tables select:      64874.974334    64550.118693  
 </span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /><br /></span><u><span
style="font-size:8.0pt;font-family:"CourierNew"">Multiple transaction bulk inserts: Select performance (refer script -1
&2 which attached)</span></u><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">sequential scan:      6.492680        6.382014     </span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Index scan:           1.386851        1.36234              
</span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /><br /></span><u><span
style="font-size:8.0pt;font-family:"CourierNew"">Single transaction bulk inserts: Select performance  (refer script - 3
&4 which attached)</span></u><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">sequential scan:      6.49319         6.3800147              
 </span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Index scan:           1.384121        1.3615277                      
 </span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /><br /></span><u><span
style="font-size:8.0pt;font-family:"CourierNew"">Long transaction open then Vacuum & select performance in milli
seconds.(refer reports output)</span></u><span style="font-size:8.0pt;font-family:"Times New Roman","serif""><br
/></span><spanstyle="font-size:8.0pt;font-family:"Courier New"">Testcase - 3:</span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Single Vacuum Perf   : 128.302 ms      181.292 ms          
</span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Single select perf   : 214.107 ms      177.057 ms  </span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"CourierNew"">Total                : 342.409 ms      358.349 ms</span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Iwas not able to find the reason why in some of cases results
arelow so please use the attached scripts to validate the same.</span><span style="font-size:8.0pt;font-family:"Times
NewRoman","serif""><br /><br /></span><span style="font-size:8.0pt;font-family:"Arial","sans-serif"">I was not able to
providethe IO statistics as IOSTAT & VMSTAT was giving the current snapshot</span><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">butnot the cumulative from start to end of test
execution.</span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /><br /></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Documentation:</span></b><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><b><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">-------------</span></b><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif""><br /></span><span
style="font-size:8.0pt;font-family:"Arial","sans-serif"">Nouser visible changes, so no documentation is need to
update.</span><spanstyle="font-size:8.0pt;font-family:"Times New Roman","serif""><br /><br /><br /></span><span
style="font-size:8.0pt"></span><pclass="MsoNormalCxSpLast"><span style="font-size:8.0pt">Regards,</span><p
class="MsoPlainText"><spanstyle="font-size:8.0pt">Hari babu.</span></div>