35 ms·
I'm not sure why the performance benefits hit a limit with 10 workers on a 64-core machine. It doesn't look like there should be too much communication complexi
by nymonym 7y ago
I'm not sure why the performance benefits hit a limit with 10 workers on a 64-core machine. It doesn't look like there should be too much communication complexity given largely disjoint workloads.
Are we hitting memory/IO bottlenecks?
- magicalhippo 7y agoAs you subdivide the workload you get to a point where the time it takes to issue work to a thread and collect/coordinate the results is greater than the time it takes for the thread to perform the work itself. Back in the AMD Q6600 days, in my early experimentation in multi-threading I found that my MT quicksort routine hit this limit at about 50k elements. At that point further subdivision just made it go slower. The exact limit depends on how expensive the comparison operator is and the synchronization overhead for the thread pool/work queue, but there will be a limit. Not sure if this is what's going on here, but is one aspect at least.
- michelpp 7y agohttps://en.wikipedia.org/wiki/Amdahl%27s_law https://en.wikipedia.org/wiki/Amdahl%27s_law
- wruza 7y agoI think it is a combination of factors, but notably it is 1) 426GB table on 7200rpm hdd, 2) SeqScan of 8 rows which is not computationally expensive. It basically goes through a very big file on hdd, probably for the purpose of presentation, not to show some impressive per-core efficiency.
- tempguy9999 7y agoAgreed: Table: 426GB, RAM: 256GB, and then he says "and it is clear that having more than 10 workers will not give any significant performance boost". Quite expected if it's IO bottlnecked. It doesn't sound like it's about presentation. Maybe I'm missing something.
- londons_explore 7y agoIO bottlenecking shouldn't prevent parallelism helping. One theoretical solution is to have millions of parallel queries, and rather than doing random reads to satisfy them, just read the whole table sequentially. Query latency is high, but throughout is then unconstrained.
- tempguy9999 7y ago> and rather than doing random reads to satisfy them, just read the whole table sequentially OK, good point, I believe this is called a carousel read (spent 5 mins looking for a link, sorry no luck. I know MSSQL does a slightly fancier version of it). I think the original article was unclear in what it was trying for, actual performance or exposition. Edit: could have utterly sworn it was a carousel read, but no cigar. Here it is https://www.mssqltips.com/sqlservertip/4472/sql-server-enterprise-advanced-scan-aka-merrygoround-scan/ https://www.mssqltips.com/sqlservertip/4472/sql-server-enter...
- nunwuo 7y agoPostgres does this, too; at least to some degree. They call it "synchronized sequential scans".
- slaymaker1907 7y agoIt’s not really because of Amdahl’s law but because the concurrency control used by Postgres is not infinitely scalable. IIRC, Postgres uses MVCC with some modifications to give full serializability if desired and that some of this is basically run optimistically such that it may have to abort a transaction if it cannot be committed in a serializable way. A consequence of this is that the number of aborts will grow very rapidly as you increase concurrency. A particularly nasty behavior occurs when you get into a situation where given a set of concurrent transactions, all of them must be aborted. While 2PL is not very popular these days, it does have the advantage that you can often entirely eliminate deadlock via carefully written queries. Then you just have to deal with Amdahl’s law and you won’t run into quite as catastrophic performance given high concurrency. Andy Pavlo wrote a paper looking at this comparing different concurrency control strategies under very high levels of concurrency.
- ellisv 7y ago> Andy Pavlo wrote a paper looking at this comparing different concurrency control strategies under very high levels of concurrency. I think you mean his paper "This is the Best Paper Ever on In-Memory Multi-Version Concurrency Control" which is available here: http://www.cs.cmu.edu/~pavlo/papers/p781-wu.pdf http://www.cs.cmu.edu/~pavlo/papers/p781-wu.pdf By the way, his courses are really excellent. I highly recommend subscribing to the CMU Database Group channel on YouTube.
- hinkley 7y agoThe limitations of your concurrency primitives are Amhdahl's law. We call them "concurrency primitives" but they're really about shared state. They are the sequential parts of the operation. The commit phase of optimistic locking is cheap except when it's a do-over, and by necessity it causes the second action to use the resource twice, the second time sequentially to the first action.
- londons_explore 7y agoAre you sure about this? I'm not aware of any parts of postgres which use optimistic locking?
- trollied 7y agoIt’ll be massively I/O bottlenecked on a single spindle, using a table that’s double the size of the available RAM. I don’t know what the blog post was trying to prove, to be honest. If I wanted to display the benefits of parallelism, i’d have at least tried to make the test heavily CPU constrained, given that that’s the entire point of being able to go parallel. I’d also have shown I/O performance and waits, along with CPU utilisation.