Message-ID: <4F9EBD27.9060501@squeakycode.net>
Date: Mon, 30 Apr 2012 11:26:15 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:12.0) Gecko/20120420 Thunderbird/12.0
MIME-Version: 1.0
To: "Walker, James Les" <JAWalker@cantor.com>
CC: "'pgsql-performance@postgresql.org'" <pgsql-performance@postgresql.org>
Subject: Re: Tuning Postgres 9.1 on Windows
References: <21BFB59709EBB84DB412ED7F739FFD3B19E5E3@TBPINFN0203.cad.local>
In-Reply-To: <21BFB59709EBB84DB412ED7F739FFD3B19E5E3@TBPINFN0203.cad.local>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable

On 4/30/2012 8:49 AM, Walker, James Les wrote:
> I=92m trying to benchmark Postgres vs. several other databases on my
> workstation. My workstation is running 64 bit Windows 7. It has 12 gb o=
f
> RAM and a W3550 @ 3 Ghz. I installed Postgres 9.1 using the windows
> installer. The data directory is on a 6Gb/s SATA SSD.
>
> My application is multithreaded and uses pooled connections via JDBC.
> It=92s got around 20 threads doing asynchronous transactions against th=
e
> database. It=92s about 70% read/30% write. Transactions are very small.
> There are no long-running transactions. I start with an empty database
> and I only run about 5,000 business transactions in my benchmark. That
> results in 10,000 =96 15,000 commits.
>
> When I first installed Postgres I did no tuning at all and was able to
> get around 40 commits per-second which is quite slow. I wanted to
> establish a top-end so I turned off synchronous commit and ran the same
> test and got the same performance of 40 commits per second. I turned on
> the =93large system cache=94 option on Windows 7 and got the same resul=
ts.
> There seems to be some resource issues that=92s limiting me to 40 commi=
ts
> per second but I can=92t imagine what it could be or how to detect it.
>
> I=92m not necessarily looking for advice on how to increase performance=
,
> but I at least need to know how to find the bottleneck.
>
> -- Les Walker
>

One thing I'd look at is your hardware and determine if you are CPU=20
bound or IO bound.  I use Linux so don't know how you'd do that on window=
s.

Have you checked your sql statements with "explain analyze"?

I don't know anything about config file settings on windows, but on=20
Linux its really important.  google could probably help you there.

Knowing if you are CPU bound or IO bound, and if you have any bad plans,=20
will tell you what config file changes to make.

-Andy