Thread: Pgsql vs Interbase: Transaction benchmark
Hi to all!
I'm doing some benchmarks to test Interbase vs PostgreSQL. The
test are done with the respective PHP client. The two servers are installed
on the same machine with RedHat 7.0.
Pgsql has been started with these configuration options:
- sort_mem = 512
- fsync = false
- shared_buffers = 1024
- commit_delay = 0
- commit_siblings = 1
Interbase is installed with the default options.
The test consists of these operations:
- START TRANSACTION
- for i = 1 to 100
- SELECT
- UPDATE (on the same row of the select)
- end for
- END TRANSACTION
Each transaction is then repeated 100 times by 10 different clients. The
transaction type is READ_COMMITTED in both servers.
All the operations are perfomed in the same table with 1.000.000 of records
and the searches (those specified by the WHERE clause of SELECT and UPDATE)
are done only on the primary key of the table itself.
I calculated the mean duration of a transaction and the machine load
(obtained using the w command). The results are:
INTERBASE (6.0.1)
Average Machine Load: 5.00
Duration of a transaction: 23 s
POSTGRESQL (7.1.1)
Average Machine Load: 10.00
Duration of a transaction: 40 s
I've also done a test without client concurrent select/updates (with only
one client...) and the results are:
INTERBASE
Average Machine Load: 0.40
Duration of a transaction: 7.5 s
POSTGRESQL
Average Machine Load: 1.10
Duration of a transaction: 6.4 s
Is there anything I can do to speed-up PostgreSQL? Is there anything wrong
in my configuration parameters?
The machine has 128Mb of memory and the processor is a PII 350Mhz.
Thank you in advance for your replyies and comments,
Denis
-------------------
denis@edistar.com
Denis Gasparin <denis@edinet.it> writes:
> All the operations are perfomed in the same table with 1.000.000 of records
> and the searches (those specified by the WHERE clause of SELECT and UPDATE)
> are done only on the primary key of the table itself.
Have you checked (with EXPLAIN) that you're actually getting indexscan
plans?
regards, tom lane
The table definition is:
CREATE TABLE ADDRESS (
GROUPID INTEGER NOT NULL,
ADDRESSID INTEGER NOT NULL,
NAME VARCHAR(256) NOT NULL,
SURNAME VARCHAR(256) NOT NULL,
ADDRESS VARCHAR(256),
PHONE VARCHAR(256),
EMAIL VARCHAR(256),
FAX VARCHAR(256),
PRIMARY KEY(GROUPID,ADDRESSID)
);
The explain command gives me these results:
explain SELECT * FROM ADDRESS1 WHERE GROUPID = 5 AND ADDRESSID = 1000;
NOTICE: QUERY PLAN:
Index Scan using address1_pkey on address1 (cost=0.00..2.02 rows=1 width=92)
(PS: There are 100 groups of 10000 recors each => 1.000.000 records)
Denis
At 16.14 01/06/01, Tom Lane wrote:
>Denis Gasparin <denis@edinet.it> writes:
> > All the operations are perfomed in the same table with 1.000.000 of
> records
> > and the searches (those specified by the WHERE clause of SELECT and
> UPDATE)
> > are done only on the primary key of the table itself.
>
>Have you checked (with EXPLAIN) that you're actually getting indexscan
>plans?
>
> regards, tom lane
I think it's expected result. In interbase you are using prepared statements. PostgreSQL have got to parse, planing and etc. every time you pass a statement. "Denis Gasparin" <denis@edinet.it> �������/�������� � �������� ���������: news:5.1.0.14.0.20010601161822.00a90010@10.1.1.2... > The table definition is: > CREATE TABLE ADDRESS ( > GROUPID INTEGER NOT NULL, > ADDRESSID INTEGER NOT NULL, > NAME VARCHAR(256) NOT NULL, > SURNAME VARCHAR(256) NOT NULL, > ADDRESS VARCHAR(256), > PHONE VARCHAR(256), > EMAIL VARCHAR(256), > FAX VARCHAR(256), > PRIMARY KEY(GROUPID,ADDRESSID) > ); > > The explain command gives me these results: > explain SELECT * FROM ADDRESS1 WHERE GROUPID = 5 AND ADDRESSID = 1000; > NOTICE: QUERY PLAN: > > Index Scan using address1_pkey on address1 (cost=0.00..2.02 rows=1 width=92) > > (PS: There are 100 groups of 10000 recors each => 1.000.000 records) > > > Denis > > At 16.14 01/06/01, Tom Lane wrote: > >Denis Gasparin <denis@edinet.it> writes: > > > All the operations are perfomed in the same table with 1.000.000 of > > records > > > and the searches (those specified by the WHERE clause of SELECT and > > UPDATE) > > > are done only on the primary key of the table itself. > > > >Have you checked (with EXPLAIN) that you're actually getting indexscan > >plans? > > > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
If this is the problem, is there a way to prepare the queryes in PGSQL? I've also noticed that there is a little speed improvement when increasing the number of WAL files... Let me kwow... Thank you Denis PS: i'm sorry for the late in replying, i have received the mail just now.... At 17.37 01/06/01, Sergey E. Volkov wrote: >I think it's expected result. > >In interbase you are using prepared statements. >PostgreSQL have got to parse, planing and etc. every time you pass a >statement. > >"Denis Gasparin" <denis@edinet.it> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: >news:5.1.0.14.0.20010601161822.00a90010@10.1.1.2... > > The table definition is: > > CREATE TABLE ADDRESS ( > > GROUPID INTEGER NOT NULL, > > ADDRESSID INTEGER NOT NULL, > > NAME VARCHAR(256) NOT NULL, > > SURNAME VARCHAR(256) NOT NULL, > > ADDRESS VARCHAR(256), > > PHONE VARCHAR(256), > > EMAIL VARCHAR(256), > > FAX VARCHAR(256), > > PRIMARY KEY(GROUPID,ADDRESSID) > > ); > > > > The explain command gives me these results: > > explain SELECT * FROM ADDRESS1 WHERE GROUPID = 5 AND ADDRESSID = 1000; > > NOTICE: QUERY PLAN: > > > > Index Scan using address1_pkey on address1 (cost=0.00..2.02 rows=1 >width=92) > > > > (PS: There are 100 groups of 10000 recors each => 1.000.000 records) > > > > > > Denis > > > > At 16.14 01/06/01, Tom Lane wrote: > > >Denis Gasparin <denis@edinet.it> writes: > > > > All the operations are perfomed in the same table with 1.000.000 of > > > records > > > > and the searches (those specified by the WHERE clause of SELECT and > > > UPDATE) > > > > are done only on the primary key of the table itself. > > > > > >Have you checked (with EXPLAIN) that you're actually getting indexscan > > >plans? > > > > > > regards, tom lane > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)