Date: Mon, 3 Mar 2008 19:26:06 -0500
From: Bill Moran <wmoran@collaborativefusion.com>
To: "alan bryan" <alan.bryan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance tuning on FreeBSD
Message-Id: <20080303192606.d92b682c.wmoran@collaborativefusion.com>
In-Reply-To: <892916310803031539scffd5bchcce5a848b3a57a32@mail.gmail.com>
References: <892916310803031539scffd5bchcce5a848b3a57a32@mail.gmail.com>
Organization: Collaborative Fusion Inc.
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

"alan bryan" <alan.bryan@gmail.com> wrote:
>
> I've got a new server and am myself new to tuning postgres.
> 
> Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU.
> 
> It's serving as the DB for a fairly write intensive (maybe 25-30%) Web
> application in PHP.  We are not using persistent connections, thus the
> high max connections.
> 
> I've done the following so far:
> 
> > cat /boot/loader.conf
> kern.ipc.semmni=256
> kern.ipc.semmns=512
> kern.ipc.semmnu=256
> 
> > cat /etc/sysctl.conf
> kern.ipc.shmall=393216
> kern.ipc.shmmax=1610612736

I would just set this to 2G (which is the max).  It doesn't really hurt
anything if you don't use it all.

> kern.ipc.semmap=256
> kern.ipc.shm_use_phys=1
> 
> postgresql.conf settings (changed from Default):
> max_connections = 180
> shared_buffers = 1024MB

Why not 2G, which would be 25% of total memory?

> maintenance_work_mem = 128MB
> wal_buffers = 1024kB
> 
> I then set up a test database for running pgbench with scaling factor
> 100. I then ran:
> > pgbench -c 100 -t 1000 testdb
> and got:
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> number of clients: 100
> number of transactions per client: 1000
> number of transactions actually processed: 100000/100000
> tps = 557.095867 (including connections establishing)
> tps = 558.013714 (excluding connections establishing)
> 
> Just for testing, I tried turning off fsync and got:
> > pgbench -c 100 -t 1000 testdb
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> number of clients: 100
> number of transactions per client: 1000
> number of transactions actually processed: 100000/100000
> tps = 4014.075114 (including connections establishing)
> tps = 4061.662041 (excluding connections establishing)
> 
> Do these numbers sound inline with what I should be seeing?  What else
> can I do to try to get better performance in the more general sense
> (knowing that specifics are tied to real world data and testing).  Any
> hints for FreeBSD specific tuning would be helpful.

Are you running FreeBSD 7?  If performance is of the utmost importance,
then you need to be running the 7.X branch.

Based on your pgbench results, I'm guessing you didn't get battery-backed
cache on your systems?  That makes a big difference no matter what OS
you're using.

Besides that, I can't think of any FreeBSD-specific things to do.  Basically,
general tuning advice applies to FreeBSD as well as to most other OS.

-- 
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023