ZFS-FreeBSD + postgresql performance

[Daniel Kalchev]

On 24.10.13 15:25, krad wrote:
> It is also generally better to allocate the ram to the db rather than zfs.
> This reduces context switches, and the db can cache more intelligently that
> the file system. However your mileage with this may vary depending on the
> db in question. On a system with such a large memory pool limiting zrc to 8
> gb, maybe a good thing to do, especially if you can hook up a large l2arc.
If you have large L2ARC, that will also require large ARC.

Indeed, the theory is that most memory should be allocated to the 
PostgreSQL shared memory pool. However, my practical experience 
demonstrates exactly the opposite, especially when the server runs mixed 
load. Limiting ARC has always resulted in worse performance. In short: 
you need to run extensive tests.
It used to be much worse, but current FreeBSD is behaving quite well 
without artificially limiting the ARC.

> If your dbs are doing lots of writes, consider implementing raid 10 on the
> pools level as its much faster for writes.

By all means, if you will run PostgreSQL on the zpool avoid any other 
redundancy than MIRROR. You will be better having one separate MIRROR 
(or multiple mirror vdevs) pool for the DB and another for other data, 
any time.

> As you have a lots of dbs, and potentially a lot of spindles, you could mix
> and match the above ideas depending on the dbs profile. Have say a raidz6
> pool for you lower traffic mostly read dbs, a raid 10 pool for your heavy
> write dbs, and a dedicated pool for you low latency db

I have actually experimented with this, in using 15k drives for the 
"fast" databases and 10k drives for the "slow" databases. At the end, I 
discovered it was better idea to combine all the drives in a single 
zpool with multiple vdevs, than to do such separation -- as multiple 
vdevs are always going to provide you with more performance.

With PostgreSQL, you want to have dedicated ZIL device. Any current SSD 
will do, but consider your workload -- you might be interested in an 
enterprise grade (more write endurance) SSD, or -- you might do what I 
do -- consider SSDs used as ZIL and L1ARC as consumables -- when they 
wear out, just put a new one. Their only role is to increase 
performance, no data is dependent on their long life...

I also experimented with SSD-only zpools for PostrgeSQL. What to say.. 
for reasonably sized databases, this setup beats anything else hands 
down. Just make sure you have enough redundancy and backups, as any SSD 
can fail at any time for no obvious reason (disks too).

Daniel