Обсуждение: PostgreSQL 9.0 x64 bit pgbench TPC very low question?

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.

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

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.

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 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

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.

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 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.

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)

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 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.

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/

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.

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

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

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

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