20 ms·

Postgres 9.2 will feature linear read scalability up to 64 cores

262 points by chanks 15y ago

gtaylor 15y ago
Yikes, that's a lot of cores. Glad to see the Postgres team keep pushing the scalability envelope.
- TylerE 15y ago
  Is it? I mean, bargain basement budget desktops have more cores than a typical server of 10 years ago. A 24 or 32 core server can't really be considered that exotic these days, can it?
  - wmf 15y ago
    On HN anything that EC2 doesn't provide is considered exotic. And cheap servers still only have 16 cores/32 threads.
    - fdr 15y ago
      To some extent true, but when core counts increase, they tend to double. Given the very new version of PG and Linux required to get these scalability benefits, it is nice to seem them pipelined ahead of the EC2 curve...it takes a long time and a lot of work to correctly alleviate lock contention and get new versions of Linux disseminated (the new LTS from Canonical will do a lot...I believe it features 3.2)
  - __alexs 15y ago
    The number of places that run e.g. 4U $10,000 HP servers with 4 socket, 16 core Opteron servers is reasonably low I think.
    - justauser 15y ago
      Change the branding to Supermicro and cut the cost to $5,000 and suddenly, many dedicated host providers out there have these servers. We're probably only 2-3 years away from this core count being a $2,000-$3,000 box. Throw ARM and Tilera architecture and whatever AMD is doing with the acquisition of SeaMicro in there and it might even accelerate that pace.
    - mrb 15y ago
      Not everybody buys HP... A 1U Supermicro 4-socket 64-core Opteron server with 32GB RAM costs $3850. http://www.newegg.com/Product/Product.aspx?Item=N82E16816101317 http://www.newegg.com/Product/Product.aspx?Item=N82E16816101... http://www.newegg.com/Product/Product.aspx?Item=N82E16819113036 http://www.newegg.com/Product/Product.aspx?Item=N82E16819113... http://www.newegg.com/Product/Product.aspx?Item=N82E16820239015 http://www.newegg.com/Product/Product.aspx?Item=N82E16820239...
    - wisty 15y ago
      Since databases are the bottleneck in a lot of apps, it makes a lot of sense to spend a ton of money on them. $10k will cover roughly the monthly wage of a good DBA. It won't cover anywhere near the costs of getting a software team to optimize everything for "web scale performance". The only reason you wouldn't want a 64 core machine is that Oracle will try to charge per core, so a machine with less cores and better per-core performance might be more cost effective.
vladev 15y ago
To me, Postgres is the most underestimated database. Not sure if this is a bad thing...
- leftnode 15y ago
  I just switched from MySQL to Postgres for everything (as a result of seeing how powerful and stable it is at my full time job) and it is simply amazing. Easily one of the most impressive pieces of software built.
- ryandvm 15y ago
  Agreed. I get that MySQL is incredibly simple to set up, so I can sort of understand why people use it for pet projects or whatever. But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)?
  - rdl 15y ago
    Heroku?
  - randomdata 15y ago
    > But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)? An interesting selection of companies given that all three are known for their use of home-grown databases (BigTable, Dynamo, Cassandra) for their primary offerings that are not of the SQL variety at all. Though I think it is still a good question. It may have something to do with the ease of setting up MySQL when you are a young startup trying to get something working as quickly as possible, leaving it often hard to justify a change after you've hit the big leagues.
    - justinsb 15y ago
      They may be known for their home-grown databases, but they shouldn't be known for their use of them: Amazon's primary database is Oracle. Dynamo is used for their shopping carts i.e. for storing sessions; Memcache would probably work just as well. Facebook's database is MySQL, with sharding and Memcache. I was under the impression they stopped using Cassandra entirely? Google's business (advertising) is built on MySQL, as are many of their sites e.g. YouTube. I think the newer Google-developed sites (e.g. GMail, Reader etc) are indeed built on BigTable.
rosser 15y ago
As much as I love PostgreSQL (and I do; it's put food on my table for the last decade), I have to stress that this linear-ish scalability needs both pg >= 9.2 and a Linux kernel >= 3.2. It seems to be a combination of the lseek(2) changes in the kernel, and the lock contention/handling changes in the db. That is: if you're running on an older kernel, you probably won't see quite as much gain.
- shanemhansen 15y ago
  At my last company one of the biggest complaints against postgreSQL (when being compared to oracle) was that it "could only scale to 20 cores", which made it unsuitable for "enterprise use". Nice to see that non-issue removed. Hey Rosser! Didn't know you hung out here.
  - rosser 15y ago
    Shane, I think most of the complaints about postgres at said company were made out of a pre-existing bias towards the expensive commercial solution, rather than against the (at the time, admittedly) somewhat less capable FOSS project. When I was asked about vertical scalability, I told them "16-20 cores right now, but at the rate they're improving things, it'll be 32, and then 64 in the next 3-5 years." Nice to see things tracking so closely with my predictions.
- jeltz 15y ago
  The lseek scalability issue was not very noticeable on PostgreSQL 9.1, but first became obvious after work had started on fixing the scalability problems in PostgreSQL itself. http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability.html http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability...
- ssmoot 15y ago
  Does that mean that FreeBSD will become a bit of a second-class-citizen with the release of 9.2? Or are similar optimizations available and planned there?
  - 15y ago
ww520 15y ago
That's awesome. I have to admit I have always know Postgres is great and toyed with it but never used it in a real project, due to the availability of MySQL or client preference. I'll try to put it into the current project. Client wants Oracle since they already have Oracle license, but I will change the requirement to support Postgres as well.
- j-kidd 15y ago
  If possible, try not to go down the "support Postgres as well" route. I was in your situation, where client wanted SQL Server since they already have the license. During development, I use PostgreSQL instead, to "support Postgres as well". At the end, roughly one-third [1] of the total development effort was spent on overcoming SQL Server's limitations, things that you would never have to think about in PostgreSQL. So, try telling the client that they already have PostgreSQL license as well, with unlimited future upgrade. [1] This figure was pulled from ass. The actual productivity loss could be more due to similar reasons outlined in http://news.ycombinator.com/item?id=3784750 http://news.ycombinator.com/item?id=3784750
  - xradionut 15y ago
    Maybe the requirement is Oracle or SQL Server because the client has the resources (DBA, support contracts, etc) to support those platforms in house?
    - j-kidd 15y ago
      Not in my case, the client doesn't have the resource, and we don't have the expertise. At the end, I became somewhat of an expert in SQL Server (i.e. I know how to start SQL Profiler and read query plan), at the cost of important functionality not delivered to the client on time.
ww520 15y ago
Database vendors typically charge by the number of cores. 64-core can get really expensive with database licenses. The hardware cost has decreased drastically over time but database licensing are still in the dark age. Postgres 9.2 has real competitive advantage here. Hopefully it would force the other vendors' licensing cost down.
- jonknee 15y ago
  Evidence: http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf http://www.oracle.com/us/corporate/pricing/technology-price-... "The number of required licenses shall be determined by multiplying the total number of cores of the processor by a core processor licensing factor specified on the Oracle Processor Core Factor Table" http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf http://www.oracle.com/us/corporate/contracts/processor-core-... They give you a big discount for buying Sun servers (.25 factor). Either way, it's a huge amount of money, the standard edition costs a cool $17,500 per processor so with 64 cores and the best .25 multiplier you're still looking at 16 x $17,500 or $280,000 for the DB processor license (that doesn't cover support or anything else). The Enterprise edition runs an astounding $47,500 per CPU, so you can easily run north of a million dollars per server if you're running a lot of cores.
  - etrain 15y ago
    Oracle tends to be pretty opaque in their pricing, and part of that is because with any sale this big, there's always going to be a lot of negotiation. Sure, it's going to be expensive, but only schmucks pay full price for a 64-core license. Still, it's good to see the best open source database out there delivering cutting edge performance. Great work!
    - mapgrep 15y ago
      Yes you can get a big discount. Then when your business booms, and you need to expand, upgrade or (for some licenses) renew, Oracle gets decide how much of your profit to take off the table in licensing fees, up to the full list. But of course I'm speaking out of school here. I have no proof Larry Ellison or his minions will pursue such ruthless business tactics. Just a feeling :)
verminoth 15y ago
I'm new to all of this, so does this mean that other databases don't have this kind of performance?
- gauravk92 15y ago
  Performance is subjective, but let's dig into the performance optimization this patch includes. The update addresses an issue where to do a llseek (read) of the database, the linux kernel would lock the read, causing it to have only one output at a time. The patch removes the lock because it was unnecessary and thus the performance scales concurrently without a lock creating contention. This kind of performance optimization isn't new, concurrency is the name of the game. Erlang is a language built around concurrency and it has some databases written in it (couchdb) that scale with more cores due to erlangs inherent capabilities. So has this kind of performance increase been seen before, yes.
  - jeltz 15y ago
    The bulk of the fixes were in the locking in PostgreSQL though, and before those fixes were made the llseek problem did not appear to many since in almost all cases people hit a bottleneck in the PostgreSQL code before hitting the Linux Kernel one.
    - gauravk92 15y ago
      Thank you! Do you have any experience with CouchDB? I'm sure you've at least of heard of erlang and all the benefits it promises, I'm considering whether to use it for a future project, I might because the JSON REST api seems convenient to use without having to worry about scale. But I haven't done testing and could use advice.
bsg75 15y ago
With this improvement, how much is Postgres hampered by a lack of a parallel query processor? For OLAP work, it seems to be the primary bottleneck.
- petrohi 15y ago
  +1 on that.
- einhverfr 15y ago
  A good project to watch in this regard is Postgres-XC. Maybe not quite ready for production but it's close. (http://sourceforge.net/projects/postgres-xc/ http://sourceforge.net/projects/postgres-xc/)
robomartin 15y ago
Is there such a thing as a suite of standardized performance tests for large-scale, multi-core databases? How are people comparing Cassandra, PostgreSQL, mySQL and other options against each other for raw performance?
- zorked 15y ago
  There is no such thing as "raw performance". It's all very, very application-specific.
- jacques_chester 15y ago
  > Is there such a thing as a suite of standardized performance tests for large-scale, multi-core databases? There is: the TPC family[1] and their opensource dopplegangers, the OSDL-DBT family[1]. I don't think they've been applied to non-relational databases as yet. [1] http://www.tpc.org/information/benchmarks.asp http://www.tpc.org/information/benchmarks.asp [2] http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page http://sourceforge.net/apps/mediawiki/osdldbt/index.php?titl...

262 points by chanks 15y ago

gtaylor 15y ago

Yikes, that's a lot of cores. Glad to see the Postgres team keep pushing the scalability envelope.

TylerE 15y ago
Is it? I mean, bargain basement budget desktops have more cores than a typical server of 10 years ago. A 24 or 32 core server can't really be considered that exotic these days, can it?
- wmf 15y ago
  On HN anything that EC2 doesn't provide is considered exotic. And cheap servers still only have 16 cores/32 threads.
  - fdr 15y ago
    To some extent true, but when core counts increase, they tend to double. Given the very new version of PG and Linux required to get these scalability benefits, it is nice to seem them pipelined ahead of the EC2 curve...it takes a long time and a lot of work to correctly alleviate lock contention and get new versions of Linux disseminated (the new LTS from Canonical will do a lot...I believe it features 3.2)
- __alexs 15y ago
  The number of places that run e.g. 4U $10,000 HP servers with 4 socket, 16 core Opteron servers is reasonably low I think.
  - justauser 15y ago
    Change the branding to Supermicro and cut the cost to $5,000 and suddenly, many dedicated host providers out there have these servers. We're probably only 2-3 years away from this core count being a $2,000-$3,000 box. Throw ARM and Tilera architecture and whatever AMD is doing with the acquisition of SeaMicro in there and it might even accelerate that pace.
  - mrb 15y ago
    Not everybody buys HP... A 1U Supermicro 4-socket 64-core Opteron server with 32GB RAM costs $3850. http://www.newegg.com/Product/Product.aspx?Item=N82E16816101317 http://www.newegg.com/Product/Product.aspx?Item=N82E16816101... http://www.newegg.com/Product/Product.aspx?Item=N82E16819113036 http://www.newegg.com/Product/Product.aspx?Item=N82E16819113... http://www.newegg.com/Product/Product.aspx?Item=N82E16820239015 http://www.newegg.com/Product/Product.aspx?Item=N82E16820239...
  - wisty 15y ago
    Since databases are the bottleneck in a lot of apps, it makes a lot of sense to spend a ton of money on them. $10k will cover roughly the monthly wage of a good DBA. It won't cover anywhere near the costs of getting a software team to optimize everything for "web scale performance". The only reason you wouldn't want a 64 core machine is that Oracle will try to charge per core, so a machine with less cores and better per-core performance might be more cost effective.

vladev 15y ago

To me, Postgres is the most underestimated database. Not sure if this is a bad thing...

leftnode 15y ago
I just switched from MySQL to Postgres for everything (as a result of seeing how powerful and stable it is at my full time job) and it is simply amazing. Easily one of the most impressive pieces of software built.
ryandvm 15y ago
Agreed. I get that MySQL is incredibly simple to set up, so I can sort of understand why people use it for pet projects or whatever. But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)?
- rdl 15y ago
  Heroku?
- randomdata 15y ago
  > But what I never understood was why doesn't PostgreSQL see better adoption from the big player (Google, Amazon, Facebook, etc.)? An interesting selection of companies given that all three are known for their use of home-grown databases (BigTable, Dynamo, Cassandra) for their primary offerings that are not of the SQL variety at all. Though I think it is still a good question. It may have something to do with the ease of setting up MySQL when you are a young startup trying to get something working as quickly as possible, leaving it often hard to justify a change after you've hit the big leagues.
  - justinsb 15y ago
    They may be known for their home-grown databases, but they shouldn't be known for their use of them: Amazon's primary database is Oracle. Dynamo is used for their shopping carts i.e. for storing sessions; Memcache would probably work just as well. Facebook's database is MySQL, with sharding and Memcache. I was under the impression they stopped using Cassandra entirely? Google's business (advertising) is built on MySQL, as are many of their sites e.g. YouTube. I think the newer Google-developed sites (e.g. GMail, Reader etc) are indeed built on BigTable.

rosser 15y ago

As much as I love PostgreSQL (and I do; it's put food on my table for the last decade), I have to stress that this linear-ish scalability needs both pg >= 9.2 and a Linux kernel >= 3.2. It seems to be a combination of the lseek(2) changes in the kernel, and the lock contention/handling changes in the db. That is: if you're running on an older kernel, you probably won't see quite as much gain.

shanemhansen 15y ago
At my last company one of the biggest complaints against postgreSQL (when being compared to oracle) was that it "could only scale to 20 cores", which made it unsuitable for "enterprise use". Nice to see that non-issue removed. Hey Rosser! Didn't know you hung out here.
- rosser 15y ago
  Shane, I think most of the complaints about postgres at said company were made out of a pre-existing bias towards the expensive commercial solution, rather than against the (at the time, admittedly) somewhat less capable FOSS project. When I was asked about vertical scalability, I told them "16-20 cores right now, but at the rate they're improving things, it'll be 32, and then 64 in the next 3-5 years." Nice to see things tracking so closely with my predictions.
jeltz 15y ago
The lseek scalability issue was not very noticeable on PostgreSQL 9.1, but first became obvious after work had started on fixing the scalability problems in PostgreSQL itself. http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability.html http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability...
ssmoot 15y ago
Does that mean that FreeBSD will become a bit of a second-class-citizen with the release of 9.2? Or are similar optimizations available and planned there?
- 15y ago

ww520 15y ago

That's awesome. I have to admit I have always know Postgres is great and toyed with it but never used it in a real project, due to the availability of MySQL or client preference. I'll try to put it into the current project. Client wants Oracle since they already have Oracle license, but I will change the requirement to support Postgres as well.

j-kidd 15y ago
If possible, try not to go down the "support Postgres as well" route. I was in your situation, where client wanted SQL Server since they already have the license. During development, I use PostgreSQL instead, to "support Postgres as well". At the end, roughly one-third [1] of the total development effort was spent on overcoming SQL Server's limitations, things that you would never have to think about in PostgreSQL. So, try telling the client that they already have PostgreSQL license as well, with unlimited future upgrade. [1] This figure was pulled from ass. The actual productivity loss could be more due to similar reasons outlined in http://news.ycombinator.com/item?id=3784750 http://news.ycombinator.com/item?id=3784750
- xradionut 15y ago
  Maybe the requirement is Oracle or SQL Server because the client has the resources (DBA, support contracts, etc) to support those platforms in house?
  - j-kidd 15y ago
    Not in my case, the client doesn't have the resource, and we don't have the expertise. At the end, I became somewhat of an expert in SQL Server (i.e. I know how to start SQL Profiler and read query plan), at the cost of important functionality not delivered to the client on time.

ww520 15y ago

Database vendors typically charge by the number of cores. 64-core can get really expensive with database licenses. The hardware cost has decreased drastically over time but database licensing are still in the dark age. Postgres 9.2 has real competitive advantage here. Hopefully it would force the other vendors' licensing cost down.

jonknee 15y ago
Evidence: http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf http://www.oracle.com/us/corporate/pricing/technology-price-... "The number of required licenses shall be determined by multiplying the total number of cores of the processor by a core processor licensing factor specified on the Oracle Processor Core Factor Table" http://www.oracle.com/us/corporate/contracts/processor-core-factor-table-070634.pdf http://www.oracle.com/us/corporate/contracts/processor-core-... They give you a big discount for buying Sun servers (.25 factor). Either way, it's a huge amount of money, the standard edition costs a cool $17,500 per processor so with 64 cores and the best .25 multiplier you're still looking at 16 x $17,500 or $280,000 for the DB processor license (that doesn't cover support or anything else). The Enterprise edition runs an astounding $47,500 per CPU, so you can easily run north of a million dollars per server if you're running a lot of cores.
- etrain 15y ago
  Oracle tends to be pretty opaque in their pricing, and part of that is because with any sale this big, there's always going to be a lot of negotiation. Sure, it's going to be expensive, but only schmucks pay full price for a 64-core license. Still, it's good to see the best open source database out there delivering cutting edge performance. Great work!
  - mapgrep 15y ago
    Yes you can get a big discount. Then when your business booms, and you need to expand, upgrade or (for some licenses) renew, Oracle gets decide how much of your profit to take off the table in licensing fees, up to the full list. But of course I'm speaking out of school here. I have no proof Larry Ellison or his minions will pursue such ruthless business tactics. Just a feeling :)

verminoth 15y ago

I'm new to all of this, so does this mean that other databases don't have this kind of performance?

gauravk92 15y ago
Performance is subjective, but let's dig into the performance optimization this patch includes. The update addresses an issue where to do a llseek (read) of the database, the linux kernel would lock the read, causing it to have only one output at a time. The patch removes the lock because it was unnecessary and thus the performance scales concurrently without a lock creating contention. This kind of performance optimization isn't new, concurrency is the name of the game. Erlang is a language built around concurrency and it has some databases written in it (couchdb) that scale with more cores due to erlangs inherent capabilities. So has this kind of performance increase been seen before, yes.
- jeltz 15y ago
  The bulk of the fixes were in the locking in PostgreSQL though, and before those fixes were made the llseek problem did not appear to many since in almost all cases people hit a bottleneck in the PostgreSQL code before hitting the Linux Kernel one.
  - gauravk92 15y ago
    Thank you! Do you have any experience with CouchDB? I'm sure you've at least of heard of erlang and all the benefits it promises, I'm considering whether to use it for a future project, I might because the JSON REST api seems convenient to use without having to worry about scale. But I haven't done testing and could use advice.

bsg75 15y ago

With this improvement, how much is Postgres hampered by a lack of a parallel query processor? For OLAP work, it seems to be the primary bottleneck.

petrohi 15y ago
+1 on that.
einhverfr 15y ago
A good project to watch in this regard is Postgres-XC. Maybe not quite ready for production but it's close. (http://sourceforge.net/projects/postgres-xc/ http://sourceforge.net/projects/postgres-xc/)

robomartin 15y ago

Is there such a thing as a suite of standardized performance tests for large-scale, multi-core databases? How are people comparing Cassandra, PostgreSQL, mySQL and other options against each other for raw performance?

zorked 15y ago
There is no such thing as "raw performance". It's all very, very application-specific.
jacques_chester 15y ago
> Is there such a thing as a suite of standardized performance tests for large-scale, multi-core databases? There is: the TPC family[1] and their opensource dopplegangers, the OSDL-DBT family[1]. I don't think they've been applied to non-relational databases as yet. [1] http://www.tpc.org/information/benchmarks.asp http://www.tpc.org/information/benchmarks.asp [2] http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page http://sourceforge.net/apps/mediawiki/osdldbt/index.php?titl...