5 ms·
How common is an automatically expanding WAL across other DBs? I'm most familiar with MySQL which uses a fixed size WAL (unless something changed recently). Tha
by grogers 3y ago
How common is an automatically expanding WAL across other DBs? I'm most familiar with MySQL which uses a fixed size WAL (unless something changed recently). That of course comes with other tradeoffs like potentially blocking writes if checkpointing falls behind. But I'm curious if SQLite is an oddity in this respect compared to other DBs. Since it is used in embedded contexts it might prefer to save less data on disk in the common case, but with the edge case that wal2 mode now fixes.
- mike_hearn 3y agoFor example, RocksDB/LevelDB have done this from the very beginning. If a WAL file gets too long then a new one is created and the old one is sorted and written to an sstable asynchronously.
- ddorian43 3y ago> and the old one is sorted and written to an sstable asynchronously This doesn't happen.
- mike_hearn 3y agoNo? Am I mis-remembering? Their wiki says: The logfile is a sequentially-written file on storage. When the memtable fills up, it is flushed to a sstfile on storage and the corresponding logfile can be safely deleted. [...] Background compaction threads are also used to flush memtable contents to a file on storage. If all background compaction threads are busy doing long-running compactions, then a sudden burst of writes can fill up the memtable(s) quickly, thus stalling new writes. This situation can be avoided by configuring RocksDB to keep a small set of threads explicitly reserved for the sole purpose of flushing memtable to storage.
- ddorian43 3y agoThe in-memory memtable gets converted to sstable. The WAL is ONLY read after crashing, to fill a new memtable. Your comment looked like "WAL is sorted and converted to sstable": > If a WAL file gets too long then a new one is created and the old one is sorted and written to an sstable asynchronously.
- mike_hearn 3y agoSeems a bit pedantic. The memtable is (when fully flushing writes) a derivative of the WAL. Or vice-versa if you like. They hold equivalent data, organized differently (yes yes modulo tombstones). Anyway you're right, I was being lazy in not writing out memtable explicitly.
- ddorian43 3y agoWAL splitting isn't connected to memtable flushing, they are separate processes.
- dist-epoch 3y agoPostgres creates multiple WAL files of fixed size. Old ones are deleted according to nebulous rules.
- anarazel 3y agoI'm a postgres dev, so I'm surely too close to actually see what's nebulous. That said, I don't think it's that nebulous: - data in WAL segments has to be checkpointed - no replication slot, physical or logical, may require the WAL file (see the pg_replication_slots view) - archiving, if configured, has to have archived the file (see pg_stat_archiver) It used to be more complicated, for historical reasons we used to keep two checkpoints worth of WAL around, but I don't think any supported versions of postgres still have that behavior. Edit: What's more mysterious is whether WAL files are removed when not necessary, or whether they're recycled (renamed to be reused). That's indeed a bit hard to get insight to.