Ben Dicken
@BenjDicken • 35,240 subscribers
databases → @planetscale videos → https://t.co/J5soMyfENa writing → https://t.co/YpfkF0JIKG
Shorts
Videos
Important to know that Postgres/MySQL on local SSD is *the* best way to get great performance. The reason it could get *even faster* boils down to abstraction boundaries. SSDs are built to be workload agnostic. They have their own controllers and garbage collectors meant to balance performance of many different I/O patterns. DBMS are written to support arbitrary backing I/O devices: SSD, HDD, or even a remote storage system. It's good that they're written to be broadly performant! The point of the paper was that, if you're willing to build something that specifically targets local SSDs (and even specific models of local SSDs) you can optimize the full DBMS -> OS -> hardware stack for the exact characteristics of a workload and hardware specifications. This is a classic tradeoff. You can usually get better performance as you narrow scope for specific hardware, architectures, etc. Maintainers of OSS must walk a careful balance of optimizing for performance while also maintaining wide compatibility. For the curious-minded, I wrote an interactive article on the history of I/O devices, and how that interplays with database performance:
Ben Dicken79,264 次观看 • 17 天前
Database table size impacts performance in more ways than one: a) B-tree depth. Using 8k pages and a 16b uuid: 1 level = ~370 rows 2 levels = ~138k rows 3 levels = ~50m rows 4 levels = ~20b rows The lookup cost on a table with 100k rows is not the same as one with 1b rows. This can apply both to the table itself (MySQL cluster index) as well as the indexes. Sometimes a single query requires many of them. b) Small table → fits in RAM → fast reads. The larger the table, the more likely to read from disk plus churn the cache. c) # of indexes. Each adds maintenance overhead for insertions, and for Postgres vacuum overhead as well. Keep an eye on this! It's useful to take regular stock of your tables + indexes. Clean bloat. Remove unused indexes. Partition if needed.
Ben Dicken211,360 次观看 • 1 个月前
