[Pgpool-general] Pgpool optmization

[Fernando Morgenstern]

Hello,

Not sure if this will help. But i have created a database only for testing. It contains  simple structure but my results are still bad.

The table structure:

CREATE TABLE groups ( id INT NOT NULL ,  name VARCHAR( 50 ), PRIMARY KEY ( id ) );
CREATE TABLE users ( id INT NOT NULL, name VARCHAR(50), groupid INT REFERENCES groups(id), PRIMARY KEY( id ) );

My queries:

SELECT * FROM users WHERE id  = 10000;
SELECT * FROM users WHERE groupid IN ( SELECT id FROM groups WHERE name = 'name3' );
SELECT * FROM groups;
SELECT * FROM users WHERE id > 1000 AND id < 5000;
SELECT * FROM users WHERE id > 5000 AND id < 10000 ORDER BY name ASC;

Current data is 10 groups and 50.000 users.

Pgbench, first one is pgpool:

[postgres at im-pp1 ~]$ pgbench -U postgres  -c 10   -f pgpoolQueryTest.sql performance    -h 192.168.100.51 
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 45.963571 (including connections establishing)
tps = 47.000543 (excluding connections establishing)
[postgres at im-pp1 ~]$ pgbench -U postgres  -c 10   -f pgpoolQueryTest.sql performance    -h 192.168.100.51  -p 4000
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 79.332465 (including connections establishing)
tps = 81.526635 (excluding connections establishing)

For some reason, standalone server is twice faster than pgpool with 4 nodes. This is what i don't understand, why 4 nodes are slower than 1 node?

Any suggestions?

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando at consultorpc.com


Em 28/12/2009, às 15:32, Fernando Morgenstern escreveu:

> Hello,
> 
> Is there a guide for pgpool optimization?
> 
> I am having some bad results with my app queries, but good ones with pgbench. Let me give an example:
> 
> 1) With pgbench and SELECT only queries i get:
> 
> For pgpool: tps = 644.504312 (including connections establishing)
> For stand alone server: tps = 174.842415 (including connections establishing)
> 
> 2) With my app most-used SELECT queries i get:
> 
> For pgpool: tps = 42.908523 (including connections establishing
> For stand alone server: tps = 37.476283 (including connections establishing)
> 
> On 2) there is a small difference. I was wondering if, as pgpool is running only selects, shouldn't it split queries between 4 servers and have a tps 4x higher than standalone server? ( i have 4 nodes, that's why at least 4x higher )
> 
> Another question, enabling parallel query would help in this situation?
> 
> Best Regards,
> ---
> 
> Fernando Marcelo
> www.consultorpc.com
> fernando at consultorpc.com
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general