Dear friends,
I am migrating from Debian GNU/Linux to FreeBSD. I could be using KfreeBSD, but I prefer blending-edge and also become part of a community, so I will not stick to Debian, although I appreciate the spirit.
The server board has 32 CPUs, 32GB of RAM, 1 x SSD and 5 x SATA disc drives. Recent hardware (2013 by the time of writing 2017). The server has a small RAID card, but it is disabled for obvious reasons.
What would be the best strategy when using PostgreSQL:
PostgreSQL is not the only application installed, and this is a low-traffice server, used for home experiments and self-teaching. PostgreSQL is installed with at least a 1/4 of shared memory and fsync on (I am not bold enough to remove sync). A power backup is installed. Frequent backups are done in PostgreSQL.
From a scrict point of view of disc transfer, what would give the better performance for a PostgreSQL database (larger than RAM and shared memory) with frequent disc access.
I am worried that ZFS might duplicate values when PostgreSQL has its own point-in-time recovery system. And this might leed to a traditional buffer-bloat issue.
So I am looking for the best solution which would go directly to disc. I like to remove all cache from my applications, including web, optimizing in code. But there are cases, i.e. database "INSERTS" when a large buffer might be preferable to let the OS migrate data inside the cluster.
So what would be your general opinion as regards ZFS, UFS2 and PostgreSQL? A solution would be to create a starting zpool of 4 drives and benchmark other solutions from that point. Still, I'd like to hear any feedback from users about this disc issue.
Kind regards,
French Fries
I am migrating from Debian GNU/Linux to FreeBSD. I could be using KfreeBSD, but I prefer blending-edge and also become part of a community, so I will not stick to Debian, although I appreciate the spirit.
The server board has 32 CPUs, 32GB of RAM, 1 x SSD and 5 x SATA disc drives. Recent hardware (2013 by the time of writing 2017). The server has a small RAID card, but it is disabled for obvious reasons.
What would be the best strategy when using PostgreSQL:
- All SATA drives in a single zpool, SSD as write/read cache in zpool.
- All SATA drives in a zpool0. 1 SSD in zpool1 with some tweaking to avoid duplication of data. PostgreSQL main cluster on zpool1, a secondary cluster on spool0.
- 4 x SATA drives on zpool0. 1 SSD on zpool1, no mirroring. 1 SATA on zpool2, no mirroring. PostgreSQL main cluster on zpool1, a secondary cluster on spool2.
- 4 x SATA drivers on zpool0. 1 SSD slice0 and 1 SATA slice1 on UFS2. PostgreSQL main cluster on SSD slice0 , a secondary cluster on slice1.
PostgreSQL is not the only application installed, and this is a low-traffice server, used for home experiments and self-teaching. PostgreSQL is installed with at least a 1/4 of shared memory and fsync on (I am not bold enough to remove sync). A power backup is installed. Frequent backups are done in PostgreSQL.
From a scrict point of view of disc transfer, what would give the better performance for a PostgreSQL database (larger than RAM and shared memory) with frequent disc access.
I am worried that ZFS might duplicate values when PostgreSQL has its own point-in-time recovery system. And this might leed to a traditional buffer-bloat issue.
So I am looking for the best solution which would go directly to disc. I like to remove all cache from my applications, including web, optimizing in code. But there are cases, i.e. database "INSERTS" when a large buffer might be preferable to let the OS migrate data inside the cluster.
So what would be your general opinion as regards ZFS, UFS2 and PostgreSQL? A solution would be to create a starting zpool of 4 drives and benchmark other solutions from that point. Still, I'd like to hear any feedback from users about this disc issue.
Kind regards,
French Fries