On Thu, Mar 6, 2014 at 10:15 PM, Kohei KaiGai <[email protected]> wrote:
> 2014-03-06 18:17 GMT+09:00 Haribabu Kommi <[email protected]>:
>> I will update you later regarding the performance test results.
>>
I ran the performance test on the cache scan patch and below are the readings.
Configuration:
Shared_buffers - 512MB
cache_scan.num_blocks - 600
checkpoint_segments - 255
Machine:
OS - centos - 6.4
CPU - 4 core 2.5 GHZ
Memory - 4GB
Head patched Diff
Select - 500K 772ms 2659ms -200%
Insert - 400K 3429ms 1948ms 43% (I am
not sure how it improved in this case)
delete - 200K 2066ms 3978ms -92%
update - 200K 3915ms 5899ms -50%
This patch shown how the custom scan can be used very well but coming
to patch as It is having
some performance problem which needs to be investigated.
I attached the test script file used for the performance test.
Regards,
Hari Babu
Fujitsu Australia
shared_buffers = 512MB
shared_preload_libraries = 'cache_scan'
cache_scan.num_blocks = 600
checkpoint_segments - 255
\timing
--cache scan select 5 million
create table test(f1 int, f2 char(70), f3 float, f4 char(100));
truncate table test;
insert into test values (generate_series(1,5000000), 'fujitsu', 1.1, 'Australia
software tech pvt ltd');
checkpoint;
CREATE TRIGGER test_cache_row_sync AFTER INSERT OR UPDATE OR DELETE ON test FOR
ROW EXECUTE PROCEDURE cache_scan_synchronizer();
CREATE TRIGGER test_cache_stmt_sync AFTER TRUNCATE ON test FOR STATEMENT
EXECUTE PROCEDURE cache_scan_synchronizer();
vacuum analyze test;
explain select count(*) from test where f1 > 0;
select count(*) from test where f1 > 0;
select count(*) from test where f1 > 0;
delete from test where f1 > 1000000 and f1 <= 1200000;
update test set f1 = f1 + 3000000 where f1 > 1200000 and f1 <= 1400000;
insert into test values (generate_series(1000001, 1400000), 'fujitsu', 1.1,
'Australia software tech pvt ltd');
drop table test cascade;
--sequence scan select 5 million
create table test(f1 int, f2 char(70), f3 float, f4 char(100));
truncate table test;
insert into test values (generate_series(1,5000000), 'fujitsu', 1.1, 'Australia
software tech pvt ltd');
checkpoint;
vacuum analyze test;
set cache_scan.enabled = off;
explain select count(*) from test where f1 > 0;
select count(*) from test where f1 > 0;
delete from test where f1 > 1000000 and f1 <= 1200000;
update test set f1 = f1 + 3000000 where f1 > 1200000 and f1 <= 1400000;
insert into test values (generate_series(1000001, 1400000), 'fujitsu', 1.1,
'Australia software tech pvt ltd');
drop table test cascade;
--cache scan select 500K
create table test(f1 int, f2 char(70), f3 float, f4 char(100));
truncate table test;
insert into test values (generate_series(1,5000000), 'fujitsu', 1.1, 'Australia
software tech pvt ltd');
checkpoint;
CREATE TRIGGER test_cache_row_sync AFTER INSERT OR UPDATE OR DELETE ON test FOR
ROW EXECUTE PROCEDURE cache_scan_synchronizer();
CREATE TRIGGER test_cache_stmt_sync AFTER TRUNCATE ON test FOR STATEMENT
EXECUTE PROCEDURE cache_scan_synchronizer();
vacuum analyze test;
explain select count(*) from test where f1 % 10 = 5;
select count(*) from test where f1 % 10 = 5;
select count(*) from test where f1 % 10 = 5;
delete from test where f1 > 1000000 and f1 <= 1200000;
update test set f1 = f1 + 3000000 where f1 > 1200000 and f1 <= 1400000;
insert into test values (generate_series(1000001, 1400000), 'fujitsu', 1.1,
'Australia software tech pvt ltd');
drop table test cascade;
--sequence scan select 500K
create table test(f1 int, f2 char(70), f3 float, f4 char(100));
truncate table test;
insert into test values (generate_series(1,5000000), 'fujitsu', 1.1, 'Australia
software tech pvt ltd');
checkpoint;
vacuum analyze test;
set cache_scan.enabled = off;
explain select count(*) from test where f1 % 10 = 5;
select count(*) from test where f1 % 10 = 5;
delete from test where f1 > 1000000 and f1 <= 1200000;
update test set f1 = f1 + 3000000 where f1 > 1200000 and f1 <= 1400000;
insert into test values (generate_series(1000001, 1400000), 'fujitsu', 1.1,
'Australia software tech pvt ltd');
drop table test cascade;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
