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: [postg...@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) [postg...@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 [email protected] 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 > [email protected] > > _______________________________________________ > Pgpool-general mailing list > [email protected] > http://pgfoundry.org/mailman/listinfo/pgpool-general _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
