FreeBSD 10 and PostgreSQL 9.3 scalability issues

[Matthew Seaman]

On 21/05/2014 18:17, Sean Chittenden wrote:
>> I did some tests with zfs, and results where appallingly bad, but that was with db size > ram. 
>> > 
>> > I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved. 
> I have some hardware that I ran with last week wherein I was *not* able to reproduce any performance difference between ZFS and UFS2. On both UFS2 and ZFS I was seeing the same performance when using a a RAID10 / set of mirrors. I talked with the Dragonfly folk who originally performed these tests and they also saw the same thing: no real performance difference between ZFS and UFS. I ran my tests on a host with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling image and ran the 20 concurrent user test under kgprof(1), dtrace, and pmcstat and have the results available:
> 
> http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/
> 
> There are some investigations that are ongoing as a result of these findings. The dfly methodology was observed when generating these results. Stay tuned. -sc
> 

I'm not sure that the ZFS vs UFS2 question is at the core of the
performance problem.  We're definitely seeing marked slowdowns between
Pg 9.2 and 9.3 on UFS2 (RAID10 + Dell H710p (mfi) raid controller with
1GB NVRAM)

In our case the DB size is significantly bigger than RAM, and we also
run with a large (3GB) work mem, which seems to exacerbate the slow-down
effect.

	Cheers,

	Matthew

-- 
Dr Matthew J Seaman MA, D.Phil.
PGP: http://www.infracaninophile.co.uk/pgpkey


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 1036 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20140521/0a1c2de6/attachment.sig>