Re: Tuning Postgres 9.1 on Windows

On 4/30/2012 8:49 AM, Walker, James Les wrote:
> I’m trying to benchmark Postgres vs. several other databases on my
> workstation. My workstation is running 64 bit Windows 7. It has 12 gb of
> 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’s got around 20 threads doing asynchronous transactions against the
> database. It’s 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 – 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 “large system cache” option on Windows 7 and got the same results.
> There seems to be some resource issues that’s limiting me to 40 commits
> per second but I can’t imagine what it could be or how to detect it.
>
> I’m 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
bound or IO bound. I use Linux so don't know how you'd do that on windows.

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

I don't know anything about config file settings on windows, but on
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,
will tell you what config file changes to make.

-Andy