pg_shard and pgbouncer

[armand pirvu]

Hi

one master

two workers

CREATE TABLE person

(

  person_id bigint NOT NULL,

  first_name character varying(64),

  middle_name character varying(64),

  last_name character varying(64),

  title character varying(64),

  prefix character varying(64),

  suffix character varying(64),

  CONSTRAINT person_pkey PRIMARY KEY (person_id)

)

;

2 shards per worker

pgbouncer installed on all three of them

[databases]

postgres = host=localhost dbname=postgres

psharddb = host=localhost dbname=psharddb

listen_addr = *

listen_port = 6432

-bash-4.1$ cat pg_worker_list.conf

192.168.5.222  6432 

192.168.5.223  6432 

50KinsertsV2.sql just statements like

insert into person values ...

-bash-4.1$ time psql -p 6432 -dpsharddb -q -f 50KinsertsV2.sql > /dev/null

real 0m26.680s

user 0m0.683s

sys 0m0.595s

Whether I use pgbouncer or not same timing

Not sure what I am doing wrong.

Any hints / ideas ?

And if this is not the place, but rather the pgbounce list my apologies

Thank  you

-- Armand

[Onder Kalaci]

Hey Armand,

First thing to mention is that, pg_shard's INSERT rate increases if you create multiple connections to the master node and execute INSERTs in parallel. However, the psql command you shared is supposed to create a single connection to the master node and execute the INSERTs in the 50KinsertsV2.sql file sequentially. 

Also, pgbouncer is mostly useful when a large number of clients connect to the server. So, with a single connection, I'd not expect that pgbouncer helps us to get better performance. 

My suggestion would be to first parallelize the INSERTs that are executed, then try to measure the difference with pgbouncer.

Hope this helps,

Onder

[Armand Pirvu (home)]

Roger that Onder

For some reason I thought I did not understand the basics and whilst the question was silly , thank you for a concise clarification and confirmation so to speak

Cheers

Armand

-- 
You received this message because you are subscribed to the Google Groups "pg_shard users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pg_shard-user...@googlegroups.com.
To post to this group, send email to pg_shar...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pg_shard-users/8e67f730-ac07-492e-ba6f-07407e8afff5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.