In this talk, we will present how we replaced a Windows-based Oracle server that has been used in the CS department of the University of Applied Sciences, Darmstadt in the database education of our undergraduate students with FreeBSD 12.0, OpenZFS, and PostgreSQL 10.
We will briefly explain the use of the server in our database labs and what the pain points were (costs, usability, missing features, etc) in the past. We will then outline how we built our new solution, addressing these pain points. FreeBSD plays a central role in this solution not only as the base operating system, but also as the system providing students with a self service to register themselves with the database and create accounts. This avoids creation of a massive number of dummy accounts at the beginning of the semester, which was done with the Oracle server. With the new solution, students can use their own university account provided by LDAP. Everything is nicely hooked together with a couple of scripts and open source software. If we would have built this solution with Oracle, we would have to get additional licenses simply to connect to our LDAP server. With OpenZFS, we have a quick and easy way to reset a working lab configuration after the semester to a clean state. Additionally, we benefit from dataset compression and some specific tuning of the PostgreSQL database for ZFS. This will be outlined in the talk by providing the relevant configuration settings and datasets, as well as some ARC stats. We achieved good compression rates on the database (1/3rd the original size) and logs (nearly 12 times), something we would not have gotten for free from any other filesystem. FreeBSD provided us with the necessary tooling to set up the system fairly easy and cost-effective, which is an important factor in academia in times of short budgets. The open source nature of the operating system and database allows new approaches in our academic education in the field of databases which where not available to us before. For example, DTrace probes can now be used to show the path of a transaction through the database to the disk. The solution we’ve built has been used for two semesters (one year) now with great success. We will present some future work and learnings from the two semesters at the end of our talk. Sysadmins will get an insight from our talk into how systems in academia are managed. The solution we’ve built can be adopted in other academic institutions and companies dealing with similar issues.