I have a client that was sold a server long ago by their software company. They did a 3 drive raid 5 and a single partition windows install to run their software that uses an MSSQL database. I now have the opportunity to design a new server to hopefully increase performance because they’re growing so fast and expect to continue to do so. I was thinking of using proxmox on SSDs but since ZFS kills them and the prices are so high, perhaps it’s better to stick with mechanical for now. I understand that the OS should be on one partition and the database on another but I also fail to understand how that makes a difference. Perhaps that information is wrong but that’s why I’m here asking. Any advice would be greatly appreciated. Thanks!
First, that is not true. ZFS on SSD does not kill them. ZFS runs fine on SSD and NVME. I would go not just with SSD but an enterprise model as it will have a higher queue depth. It’s always good practice to separate data and operating system. This gives you more control over tuning parameters on a per volume basis.
If you want to deep dive into database tuning me and Alan Jude did a talk on this topic here:
3 Likes
I think he may be talking about the write amplification inherent with ZFS. However that was clearly documented in many places, and the tuning one could do to somewhat alleviate it.
What I do not understand is if your client will continue to be Windows based, because ZFS is a challenging experiment and certainly not standard practice native on Windows.
Were you thinking to perhaps move all their data to a dedicated NAS, maybe with ZFS on TrueNAS, and simply expose a volume to their Windows server?
1 Like
Thanks for your reply. I now figured out that they’re outgrowing their storage at a rapid pace and it doesn’t make sense to buy SSDs anyway, this was news to me that they need so much more storage. I did mention that I intend to use Proxmox, which is why I typically do ZFS on my hypervisor. I’m going to review Tom’s video above and see if that helps me figure it out but I need to pick a performance mechanical drive setup.
You may still want to consider adding some SSD’s to accelerate the spinning rust. Such as for an SLOG, which will help absorb any synchronous writes. Especially random writes. They’re good for metadata as well if there are lots of files.
Edit: Corrected ‘asynchronous’ to ‘synchronous’ (my brain loves to mix them up)
1 Like
I appreciate the idea! I’ve never done a mixed SSD/mechanical setup yet but perhaps this should be the first.
1 Like
In TrueNAS I use an Optane P4801X for a SLOG. The latency of the Optane DC parts with PLP and high endurance can’t be beaten for a SLOG. I also use Micron 7300 Pro NVMEs as a special metadata VDEV and small block storage. If you get enough NVME small block storage you can put your most sensitive DBs onto the NVME by setting blocksize and small block size equal. I believe MSSQL would use 16kb blocks and therefore 16kb small block size. But you better double check me on that.
1 Like