From: | Hari Babu <haribabu(dot)kommi(at)huawei(dot)com> |
---|---|
To: | "'Merlin Moncure'" <mmoncure(at)gmail(dot)com> |
Cc: | "'Greg Smith'" <greg(at)2ndquadrant(dot)com>, "'Atri Sharma'" <atri(dot)jiit(at)gmail(dot)com>, "'PostgreSQL-development'" <pgsql-hackers(at)postgresql(dot)org>, "'Amit Kapila'" <amit(dot)kapila(at)huawei(dot)com> |
Subject: | Re: WIP patch for hint bit i/o mitigation |
Date: | 2012-12-13 13:06:58 |
Message-ID: | 000301cdd932$bd48f600$37dae200$@kommi@huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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.
Attachment | Content-Type | Size |
---|---|---|
results.tar.gz | application/octet-stream | 1.6 KB |
scripts.tar.gz | application/octet-stream | 8.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Atri Sharma | 2012-12-13 13:21:45 | Re: WIP patch for hint bit i/o mitigation |
Previous Message | Karl O. Pinc | 2012-12-13 13:05:57 | Re: Multiple --table options for other commands |