Database Hardware Selection Guidelines

on YouTube ↗53m

" summary

Benchmarking with pg_test_fsync shows real differences: an SSD with proper power protection delivers about 6000 fsync/s vs 80 fsync/s for a magnetic drive with cache disabled. Practical rules: use battery-backed HBA or enterprise SSDs, avoid RAID5/6 (prefer RAID10), run ECC RAM, size memory to hold ~five minutes of working set, run smartd/iostat/free to spot I/O or memory pressure, and on cloud use provisioned IOPS. Tuning: move XLOG, prefer XFS, and lower random_page_cost for true SSDs.

AI-written summary · about

" chapters (13)

1. 0:00Introduction and philosophy
2. 4:18Rethinking server architecture
3. 7:07Why databases are unique
4. 7:58Hardware bottlenecks
5. 13:29The role of the IO stack
6. 17:49Testing storage performance
7. 21:22Ensuring data durability
8. 27:26Caching and RAID strategies
9. 28:11Storage monitoring and health
10. 31:30Optimizing SSD usage
11. 35:29Shared storage and cloud
12. 43:46Memory and CPU considerations
13. 49:20Conclusion and Q&A

" related talks

• 50mPostgres vs. Linux filesystemsTomáš Vondra · PostgreSQL Conference Europe 2023
• 45mBig Data and SSDsBrian Bulkowski · The Fifth Elephant 2013
• 25mPerformance Analysis of NVMe SSDs and their Implication on Real World DatabasesQiumin Xu · Percona Live Open Source Database Conference 2017
• 29mMySQL Performance for DevOpsSveta Smirnova · Percona University Istanbul 2022
• 36mBest Practices in PostgreSQL Tuning: Navigating Key Performance Bottlenecks in the CloudAlicja Kucharczyk, Dilip Ramachandran · SQLBits 2024
• 43mPostgreSQL in the cloud: Theory and PracticeJohn Melesky · Postgres Open 2012
• 25mVindicating ZFS with PostgreSQL: Unleashing the Power of ScalabilityFederico Campoli · POSETTE: An Event for Postgres 2024
• 35mQuestioning Database Claims: Design Patterns of Storage EngineSarthak Makhija · GopherCon India 2024