-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Storage Execution
-
120
We should ensure that queries that spill to the storage engine help pay the cost of the spilling and ensure their data is actually on disk. My proposal is that we ensure queries that spill to disk periodically checkpoint the data for their temporary table, which is supported by the WiredTiger checkpoint() API.
More detail:
We hit a bug in WT (WT-10576) when we try to force drop a TemporaryRecordStore (TRS) that has uncommitted data. This can happen in query stages (e.g. hash agg) that spill using the storage engine inside multi-document transactions and the lifetime of the storage transaction exceeds the lifetime of the table.
We tried to fix this by not using "force" to drop these temporary tables, but because these temporary record stores are not included in any checkpoints yet, we will fail to drop the table for up to 1 minute until the next checkpoint completes, persisting the data to disk.
This raised a different question: if these TRS are not actually spilled to disk, what is the value of using them? We are essentially polluting the storage engine cache and creating more work for the next checkpoint, which could have performance impacts on the system. If we make these queries pay the cost of spilling, we would probably have fewer performance issues for the rest of the system, and also ensure that the tables get dropped faster.
- is related to
-
SERVER-74133 Spilling to TemporaryRecordStores in multi-doc transactions does not work as expected
- Backlog
-
SERVER-75367 Truncate TemporaryRecordStores for query spilling before dropping
- Closed
- related to
-
SERVER-73928 Defer lifetime drop of DeferredDropRecordStore
- Closed
-
SERVER-75714 Investigate refactor of TemporaryRecordStore
- Backlog
-
SERVER-74033 Remove ident force drop in favour of handling ENOENT
- Closed
-
WT-10576 Return EBUSY on forced drop if there is an active transaction
- Closed