Re: Running PostgreSQL with ZFS ZIL

On Thu, Jul 2, 2015 at 9:35 AM, Vick Khera <vivek(at)khera(dot)org> wrote:

>
> On Wed, Jul 1, 2015 at 5:07 PM, Joseph Kregloh <jkregloh(at)sproutloud(dot)com>
> wrote:
>
>> We recently built a new server for our Production database. The machine
>> is top of the line with 128GB of RAM, dual E5-2650. We also included NVME
>> drives for ZIL and L2ARC. Currently we have 3 zpools. First one holds the
>> FreeBSD install. Second holds the jails, and third holds all of the
>> database data. Needless to say it's fast.
>>
>
> FWIW I did not find having a ZIL beneficial for my workload on a similarly
> huge servers, also running FreeBSD 10. I do have the L2ARC on the SSD, but
> the size of my data set usually leaves the ARC as sufficient to handle
> almost all requests. That is, the L2ARC is mostly empty most of the time
> (or at least never gets re-fetched from).
>

With my dataset I have been able to take advantage of the L2ARC. Currently
using about 80GB on ARC and 260GB on L2ARC. With the ARC currently having
the greater Hit ratio.

ARC Size: 66.77% 82.41 GiB
Target Size: (Adaptive) 66.79% 82.44 GiB
Min Size (Hard Limit): 12.50% 15.43 GiB
Max Size (High Water): 8:1 123.44 GiB

ARC Efficiency: 424.85m
Cache Hit Ratio: 97.39% 413.76m
Cache Miss Ratio: 2.61% 11.09m
Actual Hit Ratio: 93.08% 395.43m

L2 ARC Size: (Adaptive) 264.37 GiB
Header Size: 0.18% 485.87 MiB

L2 ARC Breakdown: 11.09m
Hit Ratio: 7.13% 790.96k
Miss Ratio: 92.87% 10.30m
Feeds: 122.76k

> I'd start by testing the speed of the driver running the NVME drive. Does
> it show up as a normal drive in FreeBSD? I've only ever used regular Intel
> SSDs. I don't know if NVME devices connect differently. I hear if you ask
> very nicely to the right people you can get special drivers for some fancy
> PCI-e based "drives" which make them really fast.
>

Both SSDs are the same part for the ZIL and L2ARC. I am currently testing
some of our most heavy procedures with ZIL enabled and with ZIL disabled.

Thanks,
-Joseph Kregloh