Thread: Performance tuning on FreeBSD
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 kern.ipc.semmap=256 kern.ipc.shm_use_phys=1 postgresql.conf settings (changed from Default): max_connections = 180 shared_buffers = 1024MB 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.
"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
On Mon, Mar 3, 2008 at 4:26 PM, Bill Moran <wmoran@collaborativefusion.com> wrote: > > > 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. I'll try that and report back. > > 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? Ditto - I'll report back. > 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. Yes, FreeBSD 7.0-Release. Tried both the 4BSD and ULE schedulers and didn't see much difference with this test. I do have the Battery for the 3ware and it is enabled. I'll do some bonnie++ benchmarks and make sure disk is near where it should be. Should turning off fsync make things roughly 8x-10x faster? Or is that indicative of something not being correct or tuned quite right in the rest of the system? I'll have to run in production with fsync on but was just testing to see how much of an effect it had. Thanks, Alan
On Mon, 3 Mar 2008, alan bryan wrote: >> pgbench -c 100 -t 1000 testdb > tps = 558.013714 (excluding connections establishing) > > Just for testing, I tried turning off fsync and got: > tps = 4061.662041 (excluding connections establishing) This is odd. ~500 is what I expect from this test when there is no write cache to accelerate fsync, while ~4000 is normal for your class of hardware when you have such a cache. Since you say your 3Ware card is setup with a cache and a BBU, that's suspicious--you should be able to get around 4000 with fsync on. Any chance you have the card set to write-through instead of write-back? That's the only thing that comes to mind that would cause this. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, Mar 3, 2008 at 5:11 PM, Greg Smith <gsmith@gregsmith.com> wrote: > On Mon, 3 Mar 2008, alan bryan wrote: > > >> pgbench -c 100 -t 1000 testdb > > > tps = 558.013714 (excluding connections establishing) > > > > Just for testing, I tried turning off fsync and got: > > > tps = 4061.662041 (excluding connections establishing) > > This is odd. ~500 is what I expect from this test when there is no write > cache to accelerate fsync, while ~4000 is normal for your class of > hardware when you have such a cache. Since you say your 3Ware card is > setup with a cache and a BBU, that's suspicious--you should be able to get > around 4000 with fsync on. Any chance you have the card set to > write-through instead of write-back? That's the only thing that comes to > mind that would cause this. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > According to 3dm2 the cache is on. I even tried setting The StorSave preference to "Performance" with no real benefit. There seems to be something really wrong with disk performance. Here's the results from bonnie: File './Bonnie.2551', size: 104857600 Writing with putc()...done Rewriting...done Writing intelligently...done Reading with getc()...done Reading intelligently...done Seeker 1...Seeker 2...Seeker 3...start 'em...done...done...done... -------Sequential Output-------- ---Sequential Input-- --Random-- -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks--- Machine MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU 100 9989 4.8 6739 1.0 18900 7.8 225973 98.5 1914662 99.9 177210.7 259.7 This is on FreeBSD 7.0-Release. I tried ULE and 4BSD schedulers with no difference. Maybe I'll try FreeBSD 6.3 to see what that does?
On Tue, 4 Mar 2008, alan bryan wrote: > There seems to be something really wrong with disk performance. Here's > the results from bonnie So input speed is reasonable but write throughput is miserable--<10MB/s. I'd suggest taking this to one of the FreeBSD lists; this doesn't look like a PostgreSQL problem. > This is on FreeBSD 7.0-Release. I tried ULE and 4BSD schedulers with > no difference. Maybe I'll try FreeBSD 6.3 to see what that does? The other thing you might consider is booting with a Linux live CD/DVD (something like Ubuntu would work) and running bonnie++ from there to see what you get. Help to sort out whether this ia a server problem or an OS one. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
alan bryan wrote: > File './Bonnie.2551', size: 104857600 > Writing with putc()...done > Rewriting...done > Writing intelligently...done > Reading with getc()...done > Reading intelligently...done > Seeker 1...Seeker 2...Seeker 3...start 'em...done...done...done... > -------Sequential Output-------- ---Sequential Input-- --Random-- > -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks--- > Machine MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU > 100 9989 4.8 6739 1.0 18900 7.8 225973 98.5 1914662 > 99.9 177210.7 259.7 > > This is on FreeBSD 7.0-Release. I tried ULE and 4BSD schedulers with > no difference. Maybe I'll try FreeBSD 6.3 to see what that does? Generally, you should set the "size" parameter to be twice the RAM you've got (or use bonnie++ which will auto-size it), but anyway, something is definitely wrong with your drives, controller or the driver. Switching schedulers won't help you, and trying different releases will only help you if the problem is in the driver. Try asking on the freebsd-performance @ freebsd.org list.
Greg Smith wrote: > On Mon, 3 Mar 2008, alan bryan wrote: > >>> pgbench -c 100 -t 1000 testdb >> tps = 558.013714 (excluding connections establishing) >> >> Just for testing, I tried turning off fsync and got: >> tps = 4061.662041 (excluding connections establishing) > > This is odd. ~500 is what I expect from this test when there is no > write cache to accelerate fsync, while ~4000 is normal for your class of > hardware when you have such a cache. I'm curious about the math behind this - is ~4000 burst or sustained rate? For common BBU cache sizes (256M, 512M), filling that amount with data is pretty trivial. When the cache is full, new data can enter the cache only at a rate at which old data is evacuated from the cache (to the drive), which is at "normal", uncached disk drive speeds.
On Tue, 4 Mar 2008, Ivan Voras wrote: > I'm curious about the math behind this - is ~4000 burst or sustained > rate? For common BBU cache sizes (256M, 512M), filling that amount with > data is pretty trivial. When the cache is full, new data can enter the > cache only at a rate at which old data is evacuated from the cache (to > the drive), which is at "normal", uncached disk drive speeds. Should be sustained rate. The reason is if you have no BBU cache, then each transaction needs to wait for the disc to rotate around to the bit where you want to write, even though each transaction is going to be writing in approximately the same place each time. However, with a BBU cache, the system no longer needs to wait for the disc to rotate, and the writes can be made from the cache to the disc in large groups of sequential writes, which is much faster. Several transactions worth can be written on each rotation instead of just one. Matthew -- People who love sausages, respect the law, and work with IT standards shouldn't watch any of them being made. -- Peter Gutmann
On Tue, 4 Mar 2008, Ivan Voras wrote: > I'm curious about the math behind this - is ~4000 burst or sustained > rate? Average, which is not quite burst or sustained. No math behind it, just looking at a few samples of pgbench data on similar hardware. A system like this one is profiled at http://www.kaltenbrunner.cc/blog/index.php?/archives/21-8.3-vs.-8.2-a-simple-benchmark.html for example. > For common BBU cache sizes (256M, 512M), filling that amount with data > is pretty trivial. I don't have any good numbers handy but I think the burst is >6000, you only get that for a few seconds before all the caches fill and the rate drops considerably. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD