From: Bill Moran <wmoran@collaborativefusion.com>
To: alan bryan <alan.bryan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance tuning on FreeBSD
Date: Mon, 3 Mar 2008 19:26:06 -0500
Message-ID: <20080303192606.d92b682c.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <892916310803031539scffd5bchcce5a848b3a57a32@mail.gmail.com>
References: <892916310803031539scffd5bchcce5a848b3a57a32@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, alan.bryan@gmail.com
Subject: Re: Performance tuning on FreeBSD
In-Reply-To: <20080303192606.d92b682c.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox