databases

Databases

Relational

sqlite
postgres
MySQL
- innodb storage engine prefetches and caches itself with default 16k record size
  - dzfs_prefetch_disable=1, logbias=throughput, primarycache=metadata, atime=off, xattr=sa, recordsize=16k
  - innodb_log_write_ahead_size=16384, innodb_doublewrite=0, innodb_checksum_algorithm=none, innodb_flush_neighbors=0, innodb_use_native_aio=0, innodb_use_atomic_writes=0
  - increase writes by disabling disk flushing with innodb_flush_log_at_trx_commit=0 and sync_binlog=0. ZFS disable all explicit disk flushing to any one file system by setting sync=disabled so it flushes every ~5sec
- https://enhancedformysql.github.io/The-Art-of-Problem-Solving-in-Software-Engineering_How-to-Make-MySQL-Better/
- TiDB
  - somewhat compatible with MySQL
  - TiKV for key value storage
  - distributed db

olap

duckdb
- column store for faster analytics
- sqlite/postgres scanner support
- no concurrent readers/writers yet
Clickhouse
https://transactional.blog/notes-on/disaggregated-oltp

notes

BTree based DB's are more cache friendly as nodes can have more than 2 children (abseil uses 62 children) unlike Binary Trees
- binary tree's (red-black like C++ STL) are better with larger sized nodes (moving pointers) than smaller (moving values)