Re: Tuning Postgres 9.1 on Windows

On Mon, Apr 30, 2012 at 8:49 AM, Walker, James Les <JAWalker(at)cantor(dot)com> 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.

It's almost certainly coming from postgres being anal about making
sure the data is syncing all the way back to the ssd through all the
buffers. Although ssd are quite fast, if you run them this way they
are no better than hard drives. Trying turning off fsync in
postgrsql.conf to be sure. If you're still seeing poor performance,
try posting and explain analyze of the queries you think might be
slowing you down.

Also, which ssd?

merlin