[ADMIN] ZFS-FreeBSD + postgresql performance

[Patrick Dung]

> I would also recommend to use 4K sector size using
gnop and zpool export/import.

Agree, in my small setup, 4K sectors and aligned partition can be seven or eight times faster than 512kb sectors when the harddisk is in advance format (4kb per sectors)

Also suggest to have two separte fast SSD (in mirror mode) for the ZIL.
This is to avoid ZFS file system fragmentation when the ZIL is stored intent with the ZFS.

Patrick




On Thursday, October 24, 2013 10:18 PM, "Ivailo.TANUSHEFF at raiffeisen.bg" <Ivailo.TANUSHEFF at raiffeisen.bg> wrote:
 
owner-freebsd-fs at freebsd.org wrote on 23.10.2013 23:57:57:

> From:
> 
> Michael Schmiedgen <schmiedgen at gmx.net>
> 
> To:
> 
> Mark Felder <feld at FreeBSD.org>, Albert Shih <Albert.Shih at obspm.fr>, 
> pgsql-admin at postgresql.org, freebsd-fs at freebsd.org, freebsd-jail at freebsd.org, 
> 
> Date:
> 
> 23.10.2013 23:58
> 
> Subject:
> 
> Re: ZFS-FreeBSD + postgresql performance
> 
> Sent by:
> 
> owner-freebsd-fs at freebsd.org
> 
> On 10/23/13 19:14, Mark Felder wrote:
> > On Wed, Oct 23, 2013, at 11:25, Albert Shih wrote:
> >> My question is about the performance, I known ZFS eat all
memory he can
> >> have (or almost), so what append when we run database like
postgresql and
> >> jail ? (it's also the reason of 96 Go ram).
> >>
> >> Sorry for cross-posting but it's about 3 differents things....
> >>
> >
> > To my understanding the solution is to change the primarycache
to
> > "metadata" for any ZFS filesystem that you do not want
ZFS to heavily
> > cache in memory.
> >
> > Example:
> >
> > # zfs set primarycache=metadata tank/usr/local/pgsql
> 
> In addition to this it is recommended to set the recordsize
> to the fixed value of 8k on dataset creation.
> 
> Michael
> 

I would also recommend to use 4K sector size using
gnop and zpool export/import.
I agree that using fixed recordsize is a must when
you have databases and you have DB on each jail.
Also there are a bunch of sysctl parameters you may
want to play with, mostly about tuning the memory usage.

Also it might be an idea to use partitions on disks
and:
1. Use first 1-2G on each disk for swap (just in case).
2. Use the rest of the disk for 4K aligned and 4K
sector size partition, labelled in some way (I use disk ID as label, for
easy identification).

Best regards,
Ivailo Tanusheff



DISCLAIMER: The contents of this e-mail are intended for the named addressee only. It contains information that may be privileged and/or confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. Messages transported over the public Internet are susceptible to alteration. Raiffeisenbank (Bulgaria) EAD, Raiffeisen Asset Management (Bulgaria) EAD, Raiffeisen Insurance Broker EOOD, Raiffeisen Services EAD, Raiffeisen Leasing Bulgaria OOD, Raiffeisen Auto Leasing Bulgaria EOOD and Raiffeisen Real Estate EOOD shall not be liable for the message if altered, changed or falsified. 

ОГРАНИЧЕНИЕ НА ОТГОВОРНОСТТА: Съдържанието на това електронно съобщение е предназначено единствено за посочения получател. Съобщението съдържа информация, която може да бъде предназначена за ограничено ползване и/или поверителна. В случай че не сте указания получател на това съобщение или негов оторизиран представител, нямате право да го копирате или използвате, както и да разкривате съдържанието му пред трети лица. Ако сте го получили по грешка, моля да уведомите подателя незабавно и да изтриете това съобщение от
 системата. Електронните съобщения, разпространявани по Интернет могат да бъдат променяни. Райфайзенбанк (България) ЕАД, Райфайзен Асет Мениджмънт (България) ЕАД, Райфайзен Застрахователен Брокер ЕООД, Райфайзен Сървисиз ЕАД, Райфайзен Лизинг България ООД, Райфайзен Ауто Лизинг България ЕООД и Райфайзен Имоти ЕООД не носят отговорност за модифицирани, променени или фалшифицирани съобщения.