5 ms·
Naive question here, but why wouldn't parallel scans actually be worse when reading from a spinning disk? And even for cached data I'd expect the bottleneck to
by losvedir 11y ago
Naive question here, but why wouldn't parallel scans actually be worse when reading from a spinning disk? And even for cached data I'd expect the bottleneck to be I/O still. Or is this just important because it's laying the groundwork for parallel queries, and we shouldn't expect any improvements from this feature alone?
- gleb 11y agoPresumably the data is in cache already - i.e. it has already been read from disk and is sitting in RAM.
- fiatmoney 11y agoIt's a pretty rare database server that has its data on a single disk.
- jeltz 11y agoAnd the databases which fit on a single disk usually also fit in RAM, which removes the IO bottleneck.
- andy_ppp 11y agoWhat do SSDs look like when reading in parallel?
- LoSboccacc 11y agoGrey
- meshko 11y agoSSD drives contain multiple NANDs. So a well designed SSD controller will take advantage of that (including data striping, essentially turning your drive into a tiny RAID). Of course it is not clear how good your consumer SSD drives are at that, but any server-grade drive you are using in your db server should support parallel reads. It gets tricky though because it reads data so quickly it might saturate the io bandwidth even with a single request, I've never done that math. All I know is that SSDs make my queries magical :)
- Andys 11y agoIf you have SSDs, its faster. If you have more than 1 spinning disk in a RAID, its faster too.
- meshko 11y agobecause while one worker is blocked by IO the other one is processing the results of the previous IO.
- quizotic 11y agoThe post shows a 3.5x performance improvement for requested 4-way parallelism, so that's a significant improvement right now! You might be surprised how many queries are bound by memory bus bandwidth and CPU, rather than I/O ... but even I/O bound queries may benefit: Remember that postgres is a "demand-pull" system. A sequential scan node returns a single tuple at a time, whenever its parent node asks for a tuple. So there can be considerable time between requests for an actual disk block. Parallelizing the scan should result in more frequent requests to the disk controller, which could then have better opportunity to optimize reads - even for a single disk. Since most of the latency for spinning disks is seek time, having more outstanding block-read requests should help performance.
- gaius 11y agoThere are no stupid questions, only stupid answers... like this one :-) No but seriously, if your "spinning disk" is even a semi-modern storage controller, it will queue and reorder your parallel requests for the most efficient access to the several, or dozens, or hundreds of physical disks it manages. So why not slam it with all the requests in a single sequence? Because a) if you are set up to handle parallel returns in your code, you don't care about the ordering in which blocks are returned so much and b) the way this is physically implemented is n queues of d depth and you want to fill them all with work.