> -----Original Message----- > From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 11, 2003 8:54 PM > To: Hackers; Advocacy > Subject: [HACKERS] PostgreSQL Tuning Results > > > Hi Everyone, > > I have just completed a basic set of benchmarking on our new > database server. I wanted to figure out a good value for > shared_buffers before we go live. > > We are a busy ecommerce-style website and so we probably get > 10 or 20 to 1 read transactions vs. write transactions. We > also don't have particularly large tables. > > Attached are the charts for select only and tpc-b runs. Also > attached is an OpenOffice.org spreadsheet with all the > results, averages and charts. I place all these attachments > in the public domain, so you guys can use them how you wish. > > I installed pgbench, and set up a pgbench database with scale > factor 1. > > I then set shared_buffers to all the values between 2000 and > 11000 and tested select and tcp-b with each. I ran each test > 3 times and averaged the values. TPC-B was run after select > so had advantages due to the buffers already being filled, > but I was consistent with this. > > Machine: > 256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz > > TPC-B config: > pgbench -c 64 -t 100 pgbench (Note: only 64 users here) > > SELECT config: > pgbench -c 128 -t 100 -S pgbench (Note: full 128 users here) > > I'm not sure why 8000 and 9000 are low on tpc-b, it's odd. > > Anyway, from the attached results you can see that 4000 > buffers gave the best SELECT only performance, whereas the > TPC-B stuff seemed to max out way up at 10000 or so. Since > there is a 20% gain in performance on TPC-B going from 4000 > buffers to 5000 buffers and only a 2% loss in performance for > SELECTs, I have configured my server to use 5000 shared > buffers, eg. 45MB RAM. > > I am now going to leave it on 5000 and play with wal_buffers. > Is there anything else people are interested in me trying?
Keenly interested. Who wouldn't want to know how to optimize it? That's the hardest guideline to find. > Later on, I'll run pg_autotune to see how its recommendation > matches my findings. I would like to hear about that also. Please report on it. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html