On Thu, Dec 13, 2012 at 6:36 PM, Hari Babu <haribabu.ko...@huawei.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 <mmonc...@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$@kap...@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*
