Re: WIP patch for hint bit i/o mitigation

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$@k
apila(at)huawei(dot)com>
http://archives.postgresql.org/message-id/00d301cdd398$6e3fff30$4abffd90$@ka
pila(at)huawei(dot)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.