9 ms·
This is legitimately fast (7s for 100M), but I'm more interested in the impact of the disk spills when swapping the hardware underneath, because the pointer swi
by gopalv 5y ago
This is legitimately fast (7s for 100M), but I'm more interested in the impact of the disk spills when swapping the hardware underneath, because the pointer swizzling is a very page-dirty way of loading data into memory with a memory map.
> When a heap block is offloaded to disk, the pointers pointing into it are invalidated. When we load the block back into memory, the pointers will have changed.
> The machine has an Intel(R) Xeon(R) W-2145 CPU @ 3.70GHz, which has 8 cores (up to 16 virtual threads), and 128 GB of RAM, so this time the data fits fully in memory.
However, the M1 + SSD is basically cheating on that trend, because that beats most of my server hardware on both memory latency & ssd. Though that fits with how people will use duckdb for local analytics.
But otherwise this is page-fault hell.
> catalog_sales table is selecting 1 column ... always ordering by cs_quantity and cs_item_sk
The choice of sort keys is a bit odd for a comparison like this, because that tuple has a lot of duplicates. So one of the tricks my code in Tez uses to sort faster is the gallop borrowed from Tim Sort, which skips over the identical key sections when doing the merge-sort to do fewer comparisons over all.
If you sorted on the primary key for catalog_sales, which is (cs_item_sk, cs_order_number), then that is actually used to store data in-order for sort-merge-joins out of disk (against catalog_returns).
And if you get into storage ordering optimizations, you might see a massive difference between ordering them by swapping the order of those columns - if you pull the radix out, then putting the most variable keys in the beginning to skew the bits changing to the first word of the key.
- lnkuiper 5y agoI'm not sure if I understand, but we did not use a memory map (mmap), but rather blocks of memory that are explicitly (un)loaded by the buffer manager. The M1 + SSD performs really well here. We tried to an external sort experiment on the x86 machine, but the SSD is old and only has a write speed of 150MB/s (compared to the MacBook's 3GB/s) and it was incredibly slow. So you definitely need a fast SSD for this. The columns we chose to sort by are rather arbitrary, but we shuffled the table before running the experiments to make sure there is no ordering left from the generation in there. I like the merge sort trick you described!
- nextaccountic 5y ago> I'm not sure if I understand, but we did not use a memory map (mmap), but rather blocks of memory that are explicitly (un)loaded by the buffer manager. But then, do you have your own cache competing with the OS cache? Or you use O_DIRECT to disable the filesystem cache (at least on Linux)?
- deleted 5y ago[deleted]
- tomnipotent 5y agoMost databases rely on OS filesystem cache, which is very compatible with the way buffer caches manage data with fixed-size pages/blocks.
- jandrewrogers 5y agoSome databases rely on the OS file system cache, this is true. However, it is not controversial that doing so reduces performance by integer factors. Bypassing the OS file system cache is idiomatic in database kernel designs where absolute performance is a key objective.
- deleted 5y ago[deleted]
- tomnipotent 5y agoUsing O_DIRECT and other features still makes you reliant on the OS cache, even if all you're doing is keeping it empty and out of your business. I've only run into a few that manually manage block devices directly.