5 ms·
Congratulations to the PostgreSQL Global Development Group on a much-anticipated release. Curious about this: > parallelism can speed up big data queries by a
by jbkkd 10y ago
Congratulations to the PostgreSQL Global Development Group on a much-anticipated release.
Curious about this:
> parallelism can speed up big data queries by as much as 32 times faster
Why would it be only 32 times faster? The sky's the limit if there aren't major bottlenecks on the way.
- greggyb 10y agoNo one has tested a query that got more than 32x faster, so they don't want to promise something they can't prove.
- Jweb_Guru 10y agoThere's also a limited amount of memory-level parallelism available... with 4-DIMM sockets you might need an 8-socket machine to get a 32x improvement on large (memory-bound) sequential scans, which I'd guess you can get on top-end Power machines. (You can probably get more memory level parallelism with random access, but your overall bandwidth will likely be lower... fully exploiting memory bandwidth is complicated and difficult to do for real applications).
- joshberkus 10y agoThat's pretty much the case, yes.
- noxin 10y agoThey likely benchmarked it on a 32 core system. Like a dual Opteron board. If the task was single-threaded before a 32-fold improvement is reasonable.
- pedrocr 10y agoIt's very difficult to get a 32x speedup from 32 cores as there are always parts that are inherently serial, so it's more likely they tested it on a 64 core machine or something like that.
- jelder 10y agoYes, this is thanks to Amdahl's Law. https://en.wikipedia.org/wiki/Amdahl%27s_law https://en.wikipedia.org/wiki/Amdahl%27s_law
- olavgg 10y agoI tested parallel queries on PostgreSQL 9.6 on a few TBs of data, 5 billion rows on an older dual Xeon E5620 server. I also striped 4 Intel S3500 800GB drivers with ZFS and enabled LZ4 compression which has a compressratio of 4x. For a sequential full table scan I could process about 2000MB/s of data(only 125MB/s was read from each SSD), I was limited by CPU power. Anyway, same query took about 25 minutes on PostgreSQL 9.5 and now it was down to 2minutes and 30 seconds. For comparison, SQL Server 2012 spent 7 minutes on the same dataset on the same hardware.
- greggyb 10y agoWould you be willing to re-run that with SQL Server 2016? A Dev license is free, and there's been a lot of relational engine optimization since 2012. I'd be curious to see what the latest release can do compared to Postgres' latest. I realize I'm asking a stranger on the internet to do something for free for me. If you don't have time or inclination to do this, no worries, but it seems like you've got a nice setup to be able to play with this. I'm sure I'm not the only one curious to see such a comparison.
- dhd415 10y agoI'm a fan of both PostgreSQL and SQL Server, but I think these numbers are very workload-specific. I've gotten 1GB/s throughput on SQL Server 2012 on spinning disks and CPUs older than the E5620, so I've no doubt that same workload would exceed 2GB/s on your hardware. The apples-to-apples comparison here is between the two versions of PG where the performance improvement is clear. It's harder to do an apples-to-apples comparison between PG and SQL Server because the optimal schema and queries for a particular workload are likely to differ for each of them.
- zejn 10y agoA blog post on 2ndQuadrant shows a bit more how parallelism in PostgreSQL scales across cores: http://blog.2ndquadrant.com/parallel-aggregate/ http://blog.2ndquadrant.com/parallel-aggregate/
- ris 10y agoNothing ever scales linearly without limit.