Tuning Postgres 9.1 on Windows

["Walker, James Les" <JAWalker@xxxxxxxxxx>]

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

 

CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald.
    E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission.  If verification is required please request a hard-copy version.
    Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s).

For further important information, please see  http://www.cantor.com/legal/statement