pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 9.0 x64 bit pgbench TPC very low question?
21+ messages / 8 participants
[nested] [flat]
* PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 18:15 tuanhoanganh <hatuan05@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-18 18:15 UTC (permalink / raw)
To: pgsql-performance
I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit, 8G
RAM, RAID 10 - 4 disks, dedicated server
Here is config of postgresql.conf after running pgtune
default_statistics_target = 100 # pgtune wizard 2010-12-15
maintenance_work_mem = 480MB # pgtune wizard 2010-12-15
constraint_exclusion = on # pgtune wizard 2010-12-15
checkpoint_completion_target = 0.9 # pgtune wizard 2010-12-15
effective_cache_size = 2816MB # pgtune wizard 2010-12-15
work_mem = 8MB # pgtune wizard 2010-12-15
wal_buffers = 32MB # pgtune wizard 2010-12-15
checkpoint_segments = 64 # pgtune wizard 2010-12-15
shared_buffers = 960MB # pgtune wizard 2010-12-15
max_connections = 254 # pgtune wizard 2010-12-15
After running pgbench
pgbench -i -h 127.0.0.1 -p 5433 -U postgres -s 10 pgbench
pgbench -h 127.0.0.1 -p 5433 -U postgres -c 100 -t 10 -C -s 10 pgbench
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 20.143494 (including connections establishing)
tps = 256.630260 (excluding connections establishing)
Why pgbench on my server is very low or is it common value with my server ?
Please help me. Thanks in advance.
Tuan Hoang ANh.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 18:35 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 21+ messages in thread
From: Kevin Grittner @ 2010-12-18 18:35 UTC (permalink / raw)
To: hatuan05@gmail.com; pgsql-performance
> tuanhoanganh wrote:
> tps = 20.143494 (including connections establishing)
> tps = 256.630260 (excluding connections establishing)
>
> Why pgbench on my server is very low or is it common value with my
> server ?
Those numbers look pretty low to me. I would start with looking at
why it is taking so long to establish a TCP connection. Are you
using SSL? How are you authenticating?
-Kevin
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 18:51 Jeff Janes <jeff.janes@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Jeff Janes @ 2010-12-18 18:51 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: pgsql-performance
On Sat, Dec 18, 2010 at 10:15 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit, 8G
...
> pgbench -h 127.0.0.1 -p 5433 -U postgres -c 100 -t 10 -C -s 10 pgbench
Why the -C option? You are essentially benchmarking how fast you can
make new connections to the database. Is that what you want to be
benchmarking?
If the code you anticipate using is really going to make and break
connections between every query, you should use a connection pooler.
Which means you should be benchmarking through the connection pooler,
or just leave off the -C.
Also, -t 10 is probably too small to get meaningful results.
> tps = 20.143494 (including connections establishing)
> tps = 256.630260 (excluding connections establishing)
>
> Why pgbench on my server is very low or is it common value with my server ?
Starting a new connection in PG is relatively slow, especially so on
Windows, because it involves starting and setting up a new process for
each one.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 18:57 tuanhoanganh <hatuan05@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 21+ messages in thread
From: tuanhoanganh @ 2010-12-18 18:57 UTC (permalink / raw)
To: pgsql-performance
No, I don't use SSL. Here is my pg_hba.conf
# IPv4 local connections:
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 trust
On Sun, Dec 19, 2010 at 1:35 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> > tuanhoanganh wrote:
>
> > tps = 20.143494 (including connections establishing)
> > tps = 256.630260 (excluding connections establishing)
> >
> > Why pgbench on my server is very low or is it common value with my
> > server ?
>
> Those numbers look pretty low to me. I would start with looking at
> why it is taking so long to establish a TCP connection. Are you
> using SSL? How are you authenticating?
>
> -Kevin
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 19:13 tuanhoanganh <hatuan05@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-18 19:13 UTC (permalink / raw)
To: pgsql-performance
My app has ~ 20 exe file, each of exe create new connect to postgesql and
there are 10-30 user use my application so I need -C to check PostgreSQL
performance.
I will test without -C option. But is there any way to decrease connect time
when there are 200 process, each of process will create new connect to
postgresql.
On Sun, Dec 19, 2010 at 1:51 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Sat, Dec 18, 2010 at 10:15 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> > I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit,
> 8G
> ...
> > pgbench -h 127.0.0.1 -p 5433 -U postgres -c 100 -t 10 -C -s 10 pgbench
>
> Why the -C option? You are essentially benchmarking how fast you can
> make new connections to the database. Is that what you want to be
> benchmarking?
>
> If the code you anticipate using is really going to make and break
> connections between every query, you should use a connection pooler.
> Which means you should be benchmarking through the connection pooler,
> or just leave off the -C.
>
> Also, -t 10 is probably too small to get meaningful results.
>
> > tps = 20.143494 (including connections establishing)
> > tps = 256.630260 (excluding connections establishing)
> >
> > Why pgbench on my server is very low or is it common value with my server
> ?
>
> Starting a new connection in PG is relatively slow, especially so on
> Windows, because it involves starting and setting up a new process for
> each one.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 19:42 tuanhoanganh <hatuan05@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-18 19:42 UTC (permalink / raw)
To: pgsql-performance
Here is my result without -C
pgbench -h 127.0.0.1 -p 9999 -U postgres -c 100 -t 10 -s 10 pgbench
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 98.353544 (including connections establishing)
tps = 196.318788 (excluding connections establishing)
On Sun, Dec 19, 2010 at 1:51 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Sat, Dec 18, 2010 at 10:15 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> > I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit,
> 8G
> ...
> > pgbench -h 127.0.0.1 -p 5433 -U postgres -c 100 -t 10 -C -s 10 pgbench
>
> Why the -C option? You are essentially benchmarking how fast you can
> make new connections to the database. Is that what you want to be
> benchmarking?
>
> If the code you anticipate using is really going to make and break
> connections between every query, you should use a connection pooler.
> Which means you should be benchmarking through the connection pooler,
> or just leave off the -C.
>
> Also, -t 10 is probably too small to get meaningful results.
>
> > tps = 20.143494 (including connections establishing)
> > tps = 256.630260 (excluding connections establishing)
> >
> > Why pgbench on my server is very low or is it common value with my server
> ?
>
> Starting a new connection in PG is relatively slow, especially so on
> Windows, because it involves starting and setting up a new process for
> each one.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 19:48 Jeff Janes <jeff.janes@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Jeff Janes @ 2010-12-18 19:48 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: pgsql-performance
On Sat, Dec 18, 2010 at 11:13 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> My app has ~ 20 exe file, each of exe create new connect to postgesql
But how often do they do that? Does each exe make a new connection,
do one transaction, and then exit? Or does each exe make one
connection, do one transaction, then close the connection and make a
new one? Or does each exe make one connection, then stick around for
a while using that connection over and over again?
In the first two cases, indeed -C is the correct way to benchmark it,
but in the third case it is not.
> and
> there are 10-30 user use my application so I need -C to check PostgreSQL
> performance.
>
> I will test without -C option. But is there any way to decrease connect time
> when there are 200 process, each of process will create new connect to
> postgresql.
I think the easiest way to decrease the connect time by a lot would be
use a connection pooler.
The critical question is how often does each process create a new
connection. 200 processes which make one connection each and keep
them open for 10 minutes is quite different from 200 processes which
make and break connections as fast as they can.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-18 22:27 Ivan Voras <ivoras@freebsd.org>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Ivan Voras @ 2010-12-18 22:27 UTC (permalink / raw)
To: pgsql-performance
On 12/18/10 20:42, tuanhoanganh wrote:
> Here is my result without -C
> pgbench -h 127.0.0.1 -p 9999 -U postgres -c 100 -t 10 -s 10 pgbench
You really should replace "-t 10" with something like "-T 60" or more.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-20 14:10 tuanhoanganh <hatuan05@gmail.com>
parent: Ivan Voras <ivoras@freebsd.org>
0 siblings, 2 replies; 21+ messages in thread
From: tuanhoanganh @ 2010-12-20 14:10 UTC (permalink / raw)
To: pgsql-performance
Here is my new pgbench's point
pgbench -h 127.0.0.1 -p 9999 -U postgres -c 200 -t 100 -s 10 pgbench
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 200
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 20000/20000
tps = 202.556936 (including connections establishing)
tps = 225.498811 (excluding connections establishing)
PostgreSQL config with pgtune
default_statistics_target = 100 # pgtune wizard 2010-12-15
maintenance_work_mem = 480MB # pgtune wizard 2010-12-15
constraint_exclusion = on # pgtune wizard 2010-12-15
checkpoint_completion_target = 0.9 # pgtune wizard 2010-12-15
effective_cache_size = 2816MB # pgtune wizard 2010-12-15
work_mem = 8MB # pgtune wizard 2010-12-15
wal_buffers = 32MB # pgtune wizard 2010-12-15
checkpoint_segments = 64 # pgtune wizard 2010-12-15
shared_buffers = 960MB # pgtune wizard 2010-12-15
max_connections = 254 # pgtune wizard 2010-12-15
I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit, 8G
RAM, RAID 10 - 4 disks
Is it common pgbench 's point with my server ?
Thanks you very much.
Tuan Hoang Anh
On Sun, Dec 19, 2010 at 5:27 AM, Ivan Voras <ivoras@freebsd.org> wrote:
> On 12/18/10 20:42, tuanhoanganh wrote:
>
>> Here is my result without -C
>> pgbench -h 127.0.0.1 -p 9999 -U postgres -c 100 -t 10 -s 10 pgbench
>>
>
> You really should replace "-t 10" with something like "-T 60" or more.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-20 14:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Scott Marlowe @ 2010-12-20 14:21 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: pgsql-performance
On Mon, Dec 20, 2010 at 7:10 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> Here is my new pgbench's point
>
> pgbench -h 127.0.0.1 -p 9999 -U postgres -c 200 -t 100 -s 10 pgbench
Your -c should always be the same or lower than -s. Anything higher
and you're just thrashing your IO system waiting for locks. Note that
-s is ignored on runs if you're not doing -i. Also -t 100 is too
small to get a good test, try at least 1000 or 10000 and let it run a
minute.
> Scale option ignored, using pgbench_branches table count = 10
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 10
> query mode: simple
> number of clients: 200
> number of threads: 1
> number of transactions per client: 100
> number of transactions actually processed: 20000/20000
> tps = 202.556936 (including connections establishing)
> tps = 225.498811 (excluding connections establishing)
> I have server computer install Windows 2008R2, PostgreSQL 9.0.1 64 bit, 8G
> RAM, RAID 10 - 4 disks
>
> Is it common pgbench 's point with my server ?
That's a pretty reasonable number for that class machine. I assume
you do NOT have a battery backed caching RAID controller or it would
be WAY higher.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-20 14:24 Scott Marlowe <scott.marlowe@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Scott Marlowe @ 2010-12-20 14:24 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: pgsql-performance
P.s. here's one of my two slower slave machines. It has dual quad
core opterons (2352 2.1GHz) and 32 Gig ram. Controller is an Areca
1680 with 512M battery backed cache and 2 disks for pg_xlog and 12 for
the data/base directory. Running Centos 5.4 or so.
pgbench -c 10 -t 10000 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 5289.941145 (including connections establishing)
tps = 5302.815418 (excluding connections establishing)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-21 03:31 tuanhoanganh <hatuan05@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-21 03:31 UTC (permalink / raw)
To: pgsql-performance; +Cc: Scott Marlowe <scott.marlowe@gmail.com>
Is there any tool work on windows can open 200 connect to postgresql and
application connect to this tool to decrease time connect to PostgreSQL
(because PostgreSQL start new process when have a new connect, I want this
tool open and keep 200 connect to postgreSQL, my application connect to this
tool instead of postgreSQL).
My server is running Windows 2008R2 and only has RAID 10 - 4 Disk
On Mon, Dec 20, 2010 at 9:24 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> P.s. here's one of my two slower slave machines. It has dual quad
> core opterons (2352 2.1GHz) and 32 Gig ram. Controller is an Areca
> 1680 with 512M battery backed cache and 2 disks for pg_xlog and 12 for
> the data/base directory. Running Centos 5.4 or so.
>
> pgbench -c 10 -t 10000 test
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> number of clients: 10
> number of transactions per client: 10000
> number of transactions actually processed: 100000/100000
> tps = 5289.941145 (including connections establishing)
> tps = 5302.815418 (excluding connections establishing)
>
Thanks in advance
Tuan Hoang Anh
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-21 03:35 Scott Marlowe <scott.marlowe@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Scott Marlowe @ 2010-12-21 03:35 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: pgsql-performance
On Mon, Dec 20, 2010 at 8:31 PM, tuanhoanganh <hatuan05@gmail.com> wrote:
> Is there any tool work on windows can open 200 connect to postgresql and
> application connect to this tool to decrease time connect to PostgreSQL
> (because PostgreSQL start new process when have a new connect, I want this
> tool open and keep 200 connect to postgreSQL, my application connect to this
> tool instead of postgreSQL).
Sure, that's what any good pooler can do. Have it open and hold open
200 connections, then have your app connect to the pooler. The pooler
keeps the connects open all the time. The app connects to a much
faster mechanism, the pooler each time. You need to make sure your
connections are "clean" when you disconnect, i.e. no idle transactions
left over, or you'll get weird errors about failed transactions til
rollback etc.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-21 08:43 Magnus Hagander <magnus@hagander.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Magnus Hagander @ 2010-12-21 08:43 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: tuanhoanganh <hatuan05@gmail.com>; pgsql-performance
On Tue, Dec 21, 2010 at 04:35, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Dec 20, 2010 at 8:31 PM, tuanhoanganh <hatuan05@gmail.com> wrote:
>> Is there any tool work on windows can open 200 connect to postgresql and
>> application connect to this tool to decrease time connect to PostgreSQL
>> (because PostgreSQL start new process when have a new connect, I want this
>> tool open and keep 200 connect to postgreSQL, my application connect to this
>> tool instead of postgreSQL).
>
> Sure, that's what any good pooler can do. Have it open and hold open
> 200 connections, then have your app connect to the pooler. The pooler
> keeps the connects open all the time. The app connects to a much
> faster mechanism, the pooler each time. You need to make sure your
> connections are "clean" when you disconnect, i.e. no idle transactions
> left over, or you'll get weird errors about failed transactions til
> rollback etc.
Yeah, AFAIK pgbouncer works fine on Windows, and is a very good pooler
for PostgreSQL. I haven't run it on Windows myself, but it should
support it fine...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-22 11:28 tuanhoanganh <hatuan05@gmail.com>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-22 11:28 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: pgsql-performance
As far as i know, Pgbouncer can help to minimum connect to postgresql, I
want tool can open and keep 200 connect to postgresql (be cause start new
connect to postgresql in windows very slow, i want it open 200 connect in
first time and my application connect to this tool)
Is there any tool like that in windows.
Thanks for you help.
Tuan Hoang ANh.
On Tue, Dec 21, 2010 at 3:43 PM, Magnus Hagander <magnus@hagander.net>wrote:
> On Tue, Dec 21, 2010 at 04:35, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
> > On Mon, Dec 20, 2010 at 8:31 PM, tuanhoanganh <hatuan05@gmail.com>
> wrote:
> >> Is there any tool work on windows can open 200 connect to postgresql
> and
> >> application connect to this tool to decrease time connect to PostgreSQL
> >> (because PostgreSQL start new process when have a new connect, I want
> this
> >> tool open and keep 200 connect to postgreSQL, my application connect to
> this
> >> tool instead of postgreSQL).
> >
> > Sure, that's what any good pooler can do. Have it open and hold open
> > 200 connections, then have your app connect to the pooler. The pooler
> > keeps the connects open all the time. The app connects to a much
> > faster mechanism, the pooler each time. You need to make sure your
> > connections are "clean" when you disconnect, i.e. no idle transactions
> > left over, or you'll get weird errors about failed transactions til
> > rollback etc.
>
> Yeah, AFAIK pgbouncer works fine on Windows, and is a very good pooler
> for PostgreSQL. I haven't run it on Windows myself, but it should
> support it fine...
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-22 12:13 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Gurjeet Singh @ 2010-12-22 12:13 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: Magnus Hagander <magnus@hagander.net>; pgsql-performance
On Wed, Dec 22, 2010 at 6:28 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
> As far as i know, Pgbouncer can help to minimum connect to postgresql, I
> want tool can open and keep 200 connect to postgresql (be cause start new
> connect to postgresql in windows very slow, i want it open 200 connect in
> first time and my application connect to this tool)
>
> Is there any tool like that in windows.
>
> Thanks for you help.
>
>
As Magnus said, pgBouncer does what you are asking for.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-23 14:20 tuanhoanganh <hatuan05@gmail.com>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: tuanhoanganh @ 2010-12-23 14:20 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: Magnus Hagander <magnus@hagander.net>; pgsql-performance
Could you show me what parameter of pgbouncer.ini can do that. I read
pgbouncer and can not make pgbouncer open and keep 200 connect to postgres
(Sorry for my English)
Thanks you very much.
Tuan Hoang ANh
On Wed, Dec 22, 2010 at 7:13 PM, Gurjeet Singh <singh.gurjeet@gmail.com>wrote:
> On Wed, Dec 22, 2010 at 6:28 AM, tuanhoanganh <hatuan05@gmail.com> wrote:
>
>> As far as i know, Pgbouncer can help to minimum connect to postgresql, I
>> want tool can open and keep 200 connect to postgresql (be cause start new
>> connect to postgresql in windows very slow, i want it open 200 connect in
>> first time and my application connect to this tool)
>>
>> Is there any tool like that in windows.
>>
>> Thanks for you help.
>>
>>
> As Magnus said, pgBouncer does what you are asking for.
>
> Regards,
> --
> gurjeet.singh
> @ EnterpriseDB - The Enterprise Postgres Company
> http://www.EnterpriseDB.com
>
> singh.gurjeet@{ gmail | yahoo }.com
> Twitter/Skype: singh_gurjeet
>
> Mail sent from my BlackLaptop device
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-23 14:37 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: tuanhoanganh <hatuan05@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Kevin Grittner @ 2010-12-23 14:37 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: Gurjeet Singh <singh.gurjeet@gmail.com>; Magnus Hagander <magnus@hagander.net>; pgsql-performance
tuanhoanganh <hatuan05@gmail.com> wrote:
> Could you show me what parameter of pgbouncer.ini can do that. I
> read pgbouncer and can not make pgbouncer open and keep 200
> connect to postgres
What makes you think that 200 connections to PostgreSQL will be a
good idea? Perhaps you want a smaller number of connections from
pgbouncer to PostgreSQL and a larger number from your application to
pgbouncer?
If you search the archives you can probably find at least 100 posts
about how both throughput and response time degrade when you have
more connections active then there are resources to use.
(Saturation is often around twice the CPU core count plus the
effective number of spindles, with caching reducing the latter.) It
is quite often the case that a transaction will complete sooner if
it is queued for later execution than if it is thrown into a mix
where resources are saturated.
-Kevin
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-23 14:46 Kenneth Marshall <ktm@rice.edu>
parent: tuanhoanganh <hatuan05@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Kenneth Marshall @ 2010-12-23 14:46 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: Gurjeet Singh <singh.gurjeet@gmail.com>; Magnus Hagander <magnus@hagander.net>; pgsql-performance
On Thu, Dec 23, 2010 at 09:20:59PM +0700, tuanhoanganh wrote:
> Could you show me what parameter of pgbouncer.ini can do that. I read
> pgbouncer and can not make pgbouncer open and keep 200 connect to postgres
> (Sorry for my English)
>
> Thanks you very much.
>
> Tuan Hoang ANh
>
You need to use session pooling for that to work. From the man page:
In order not to compromise transaction semantics for connection
pooling, pgbouncer supports several types of pooling when
rotating connections:
Session pooling
Most polite method. When client connects, a server connection
will be assigned to it for the whole duration the client
stays connected. When the client disconnects, the server
connection will be put back into the pool. This is the
default method.
Transaction pooling
A server connection is assigned to client only during a
transaction. When PgBouncer notices that transaction is over,
the server connection will be put back into the pool.
Statement pooling
Most aggressive method. The server connection will be put back
into pool immediately after a query completes. Multi-statement
transactions are disallowed in this mode as they would break.
The fact that pgbouncer will not keep 200 connections open to
the database means that you do not have enough work to actually
keep 200 permanent connections busy. It is much more efficient
to use transaction pooling. You typically want the number of
persistent database connections to be a small multiple of the
number of CPUs (cores) on your system. Then set pgbouncer to
allow as many client connections as you need. This will give
you the best throughput and pgbouncer can setup and tear down
the connections to your clients much, much faster than making
a full connection to the PostgreSQL database.
Regards,
Ken
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-23 15:20 tuanhoanganh <hatuan05@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 21+ messages in thread
From: tuanhoanganh @ 2010-12-23 15:20 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Gurjeet Singh <singh.gurjeet@gmail.com>; Magnus Hagander <magnus@hagander.net>; pgsql-performance
On Thu, Dec 23, 2010 at 9:37 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> tuanhoanganh <hatuan05@gmail.com> wrote:
>
> > Could you show me what parameter of pgbouncer.ini can do that. I
> > read pgbouncer and can not make pgbouncer open and keep 200
> > connect to postgres
>
> What makes you think that 200 connections to PostgreSQL will be a
> good idea? Perhaps you want a smaller number of connections from
> pgbouncer to PostgreSQL and a larger number from your application to
> pgbouncer?
>
If you read this thread, My app has ~ 20 exe file, each of exe create new
connect to postgesql and there are 10-30 user use my application.
My server running Windows 2008 R2. In this thread, postgresql on windows
create new connect very slow
"Starting a new connection in PG is relatively slow, especially so on
Windows, because it involves starting and setting up a new process for
each one.
Jeff Janes "
So I need a tool to open and keep 200 connect to postgres, my application
connect to this tool. And decrease time to connect to postgres ( because no
need to start new postgres process on windows)
>
> If you search the archives you can probably find at least 100 posts
> about how both throughput and response time degrade when you have
> more connections active then there are resources to use.
> (Saturation is often around twice the CPU core count plus the
> effective number of spindles, with caching reducing the latter.) It
> is quite often the case that a transaction will complete sooner if
> it is queued for later execution than if it is thrown into a mix
> where resources are saturated.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
@ 2010-12-23 16:37 Jeff Janes <jeff.janes@gmail.com>
parent: tuanhoanganh <hatuan05@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Jeff Janes @ 2010-12-23 16:37 UTC (permalink / raw)
To: tuanhoanganh <hatuan05@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Gurjeet Singh <singh.gurjeet@gmail.com>; Magnus Hagander <magnus@hagander.net>; pgsql-performance
What you still haven't clarified is how long each exe/user combo keeps
the connection open for.
If for a day, then who cares that it takes 4 seconds each morning to
open them all?
If for a fraction of a second, then you do not need 200 simultaneous
open connections, they can probably share a much smaller number. That
is the whole point of pooling.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2010-12-23 16:37 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-18 18:15 PostgreSQL 9.0 x64 bit pgbench TPC very low question? tuanhoanganh <hatuan05@gmail.com>
2010-12-18 18:51 ` Jeff Janes <jeff.janes@gmail.com>
2010-12-18 19:13 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-18 19:48 ` Jeff Janes <jeff.janes@gmail.com>
2010-12-18 19:42 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-18 22:27 ` Ivan Voras <ivoras@freebsd.org>
2010-12-20 14:10 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-20 14:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 14:24 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-21 03:31 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-21 03:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-21 08:43 ` Magnus Hagander <magnus@hagander.net>
2010-12-22 11:28 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-22 12:13 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-12-23 14:20 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-23 14:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-23 15:20 ` tuanhoanganh <hatuan05@gmail.com>
2010-12-23 16:37 ` Jeff Janes <jeff.janes@gmail.com>
2010-12-23 14:46 ` Kenneth Marshall <ktm@rice.edu>
2010-12-18 18:35 Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-18 18:57 ` tuanhoanganh <hatuan05@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox