3 ms·
There's nothing to complain about with this. But aren't most large database queries ultimately IO capped, rather than CPU capped? Wouldn't this just put more lo
by Someone1234 11y ago
There's nothing to complain about with this. But aren't most large database queries ultimately IO capped, rather than CPU capped? Wouldn't this just put more load on the IO and it would just bottleneck there?
- gshulegaard 11y agoYes and no. Take the following with grain of salt as it is just my personal opinion/understanding. Only 1 or 2 points are actually from challenges I have run into. Restricting every query to a single core places an artificial CPU cap on a system that would otherwise be IO capped. Disks/storage is also getting faster (http://www.geek.com/chips/new-intel-storage-is-1000-times-faster-than-your-ssd-1629656/ http://www.geek.com/chips/new-intel-storage-is-1000-times-fa...). As analytics keeps gaining popularity, the need/desire to move more and more CPU bound processing to the data storage layer is increasing. And finally, PostgreSQL's advanced query planning/partitioning support would immensely benefit from being able to split partition queries into multiple process buckets.
- TylerE 11y agoAnd also RAM keeps getting cheaper. 64GB is absolutely nothing these days and can fit a LOT of data.
- snuxoll 11y agoAll depends on the workload, aggregates are more often CPU capped than IO cap in workloads we use in-house. We have a PostgreSQL server with 16GB of memory (the database total is over 80GB, but the table that is hit the most is under 3GB, the rest is just BLOB's) - more than enough to keep the relevant data in memory. Most of the time running some aggregate queries that hit the database server pretty hard are spent in USER, not IOWAIT. It's pretty annoying, because I frequently see my CPU usage at 50% (one of two cores maxed) when I know these queries could be parallelized and take advantage of the second core (or more).
- stubish 11y agoNow queries can be parallelized, combining the results of several subqueries in different processes. This is a stepping stone to being able to run several subqueries on different machines. And if those different machines contain different shards of data, well, PostgreSQL has just evolved into a scale out 'Big Data' database without throwing out any relational features. Two phase commit, synchronous replication, logical replication - all the pieces are falling into place, and you can see the remaining pieces forming in extensions like BDR and Citrus Data and others.
- snaky 10y agoNot anymore. >The age-old assumption that I/O is slow and computation is fast is no longer true: this invalidates decades of design decisions that are deeply embedded in today’s systems https://blog.acolyer.org/2016/01/15/non-volatile-storage/ https://blog.acolyer.org/2016/01/15/non-volatile-storage/