28 ms·
PostgreSQL Scalability: Towards Millions TPS
- snhkicker 10y agoI don't know much but to me it seems PostgreSQL is probably one of the most open and supporting communities maybe this is reason alot of new faces are looking at it including me.
- morgante 10y agoIt's also a great example of a technology which is mature and well-tested yet actively growing and improving. Most open source projects have a lot to learn from Postgres.
- deleted 10y ago[deleted]
- movedx 10y agoWhat would you say those learning points are? What key factors would you introduce into another OSS project that PostgreSQL currently employs?
- stubish 10y agoOne of the weirdest is that there is no bug tracker. When things are found to be broken, they tend to get fixed immediately and there is nothing to track. Feature and incremental improvement work seems an arduous process, where you need to own your work and deal with extensive reviewing (no tossing it over the wall for others to maintain).
- tacos 10y agoThey were my introduction to open source -- and, well, let's just say I wish every project were so well-run. One of the best for sure.
- problem_tudo 10y agoEu quero hackear essa pessoa tem bastante seguidores e eu quero ela
- problem_tudo 10y agoEu quero hackear essa pessoa tem bastante seguidores e eu quero ela
- problem_tudo 10y agoEu quero aquela conta agora
- problem_tudo 10y agoEu quero aquela conta pq tem bastante segs
- gnarbarian 10y agoThe more efficient we can be at completing TPS reports the better. I must spend upwards of 40% of my time on them.
- gaius 10y agoDid you get the memo?
- gnarbarian 10y agoNo fun allowed here.
- juhq 10y agoAh! Yeah. It's just we're putting new coversheets on all the TPS reports before they go out now. So if you could go ahead and try to remember to do that from now on, that'd be great. All right!
- jasonmp85 10y agoAndres is a great coworker to have at Citus Data, though I first ran into him on the mailing lists shortly after starting at Citus myself. I was tasked with figuring out "why do certain read-only workloads fail miserably under high concurrency?" I had never touched PostgreSQL before, nor any Linux performance tools, but I noticed that replacing certain buffer eviction locks with atomic implementations could drastically help this particular case. I emailed the list about it and Andres was someone who chimed in with helpful advice. I wrote up what I'd discovered in my deep dive here: http://tiny.cc/postgres-concurrency http://tiny.cc/postgres-concurrency Turns out Andres was already working on a "better atomics" patch to provide easier methods of using atomic operations within PostgreSQL's code base (my patch was a quick hack probably only valid on x86, if that). It's been useful in removing several performance bottlenecks and—two years in—it looks like it's still paying off.
- AbacusAvenger 10y agoI wonder, does Intel's TSX/HLE help with these workloads? If it's read-only then I'd expect that it'd be able to elide a lot of the locking (assuming the Intel-designed heuristics do the job).
- anarazel 10y agoI'd bought one of the first haswell notebooks to play around with tsx. Before I'd time to do so Intel found the tsx bug... I hope to have time to play around with it one I have new hardware (I refuse to do performance development on virtual). But honestly, most remaining performance/scalability problems in pg are more algorithmically caused. So micro optimization, and that's what I'd call tax/hle, aren't likely to biggest bottleneck.
- ziedaniel1 10y agoWasn't TSX only enabled on Xeons to begin with?
- anarazel 10y ago
- jrcii 10y agoSlightly tangential but I'm genuinely curious, does any have a theory as to why nearly every RDBMS post on Hacker News is about Postgres and almost never MySQL or MariaDB? Considering the relative obscurity of the former it seems somewhat inexplicable.
- fbernier 10y agoI guess it depends on your own personal background, but PostgreSQL is far from 'obscure' and much more feature complete than MySQL nowadays.
- zAy0LfpBZLC8mAC 10y agoWhy "nowadays"? I can't really remember a time when it wasn't. If anything, MySQL is far less incomplete than it once was (like, it even supports transactions now, which once was one of the most important reasons for using Postgres over MySQL).
- Alex3917 10y agoIt's because Python tends to be the most common programming language for startups due to being easy to hire for, having good library support for a wide range of use cases, being good for web development, etc. And MySQL is focused on catering to enterprise customers who don't use Python. The last time I heard, they had something like one person working part time on Python support, so the drivers weren't nearly as reliable as the Postgres tooling.
- devy 10y agoIn partnership with IBM we researched PostgreSQL scalability on modern Power8 servers. That statement and the linked Russian blog white paper[1] makes it seem like a Power8 specific and Power8 is a "a massively multithreaded chip"[2]. I wonder how far off it would be to x86-64? [1] https://habrahabr.ru/company/postgrespro/blog/270827/ https://habrahabr.ru/company/postgrespro/blog/270827/ [2] https://en.wikipedia.org/wiki/POWER8 https://en.wikipedia.org/wiki/POWER8
- dimfeld 10y agoThe article says a few lines down, "The optimization #1 appears to give huge benefit on big Intel servers as well, while optimization #2 is Power-specific. After long rounds of optimization, cleaning and testing #1 was finally committed by Andres Freund." Also, the chart indicates that the benchmarks shown were run on Xeon chips.
- tormeh 10y agoHow does PostgreSQL compare to VoltDB? I'm trying to get a handle on the different databases, and VoltDB sounds exciting, but everyone's talking about PostgreSQL. Then there's Mnesia which I hear is, as all things Erlang, excellent, though it's kinda tied to Erlang. I know it's hard to say what's best, but what would you say is the best DB for a completely new multilingual project that needs throughput but prioritizes low latency, for example? Also, VoltDB is licensed under AGPL. Does this mean that it can't be used in commercial projects? Or is it OK as long as the other components are on different servers or similar?
- Alex3917 10y ago> How does PostgreSQL compare to VoltDB? If you don't know the difference, you probably want Postgres. VoltDB is a specialty database for things like high frequency trading. It wouldn't make sense to use for, say, a consumer app or web startup.
- davidw 10y agoYeah, if I start a new project, I default to Postgres: https://journal.dedasys.com/2015/02/21/i-default-to-postgres/ https://journal.dedasys.com/2015/02/21/i-default-to-postgres... I'd consider something else if I'm really, really sure that it's better suited to whatever niche problem than Postgres, but it'd take a lot of thinking and convincing myself.
- SEJeff 10y agoIn specific, it is a column store, which is advantageous to do things like real time analytics over millions of data points via streaming market data. This has uses for HFT, but also for anyone who wants to do their own day trading.
- Jweb_Guru 10y agoVoltDB isn't a column store. It's designed for serializable OLTP workloads with really fast index updates, neither of which characterize column stores. You may be thinking of one of Stonebraker's other projects, Vertica.
- Ono-Sendai 10y agoSounds like the padding stuff is a false sharing issue. They might want to look into putting 128 bytes of padding between the data structures as well: http://www.forwardscattering.org/post/29 http://www.forwardscattering.org/post/29
- anarazel 10y agoThat's what the discussed padding patch does (except to only padding to 64bytes on most platforms). There's a downside though - on low concurrency the padding reduces the cache hit ratio sufficiently enough to cause a slowdown. Given we're in code freeze anyway, I've not spent a lot of though on that yet; but I suspect that rearchitecting things so the lines are dirtied fewer times, is the better fix; with less potential for regressions at lower client counts.
- Jweb_Guru 10y agoWould it be possible to dynamically choose the padding at server start time? Given that cache line sizes vary that seems like it might be a prudent choice.
- anarazel 10y ago> Would it be possible to dynamically choose the padding at server start time? I doubt it. Allowing the compiler to generate accesses with lea et al. is quite beneficial; and that'd likely be gone by making this not be a compile time constant. It'd also end up being a tuning knob very very few knew how to tune... > Given that cache line sizes vary that seems like it might be a prudent choice. They usually only vary between architectures. Netburst IIRC was the last time x86 cache line sizes varied. If there's any doubt it's usually ok to just use the higher (128 byte) line size, the "unused" padding cache-line will never be accessed and thus not occupy cache space.
- Ono-Sendai 10y agocache line size is 64 B on recent intel CPUs.
- bnchrch 10y agoPostgreSQL has continued to be one the best open source en-devours so far. An amazingly smart and welcoming community that turns out arguabley the best in class relational database. Kudos and keep innovating team PG.
- jlgaddis 10y agoMan, how I wish WordPress had originally chosen to use PostgreSQL instead of MySQL back in the day.
- chc 10y agoAFAIK WordPress was based on a stack that worked well on shared hosts of its day. They didn't really choose a lot of that stuff so much as have it chosen for them by the hosting community.
- the-dude 10y agohttp://www.hawkix.net/pgsql-for-wordpress/ http://www.hawkix.net/pgsql-for-wordpress/ https://wordpress.org/plugins/postgresql-for-wordpress/ https://wordpress.org/plugins/postgresql-for-wordpress/
- nisa 10y agoCompatible up to: 3.4.2 Last Updated: 2 years ago Active Installs: 400+ WordPress 4.5 is current. Anyone doing WordPress will just skip this if they don't have developers that can fix the issues. Also while the base may work a lot of popular plugins (used to?) don't utilize WP_Query or whatever else WordPress offers. It's also probably easier to add indexes to the code or rewrite the logic. Mostly it's bad database code that sucks on MySQL and would likely suck the same way on PostgreSQL. Besides that if you use an object cache like memcache or redis you can avoid a lot of database accesses and InnoDB seems to be able to deal with concurrent tables like wp_comment. Would be cool to see in WordPress itself but I doubt it.
- jcoffland 10y agoWhy? WordPress would not be any better or easier to use unless you already had PostgreSQL installed. Frankly for a use case as simple as WordPress you don't need to over optimize your database.
- jrapdx3 10y ago> WordPress would not be any better or easier to use unless you already had PostgreSQL installed. Sounds like a situation I'm facing: assisting a non-profit org that wants a new website. Some members push for using Wordpress as the CMS. However the org has an established pgsql db containing lots of data that optimally could be employed on the site, e.g., lists of members/events, searchable documents, etc. Also, there's a separate web app for accessing the database using SQL features mysql doesn't support. Lack of Wordpress/pgsql compatibility makes integrating the existing db and folding in the webapp functionality just about impossible, at least I'm not seeing how that could be done. Since Wordpress isn't going to embrace pgsql, I've recommended considering alternative CMSs. Decisions are still pending.
- graffitici 10y agoAre there any best practices for using PostgreSQL for storing time series data? Would it be comparable in performance to some of the NoSQL solutions (like Cassandra) for reasonable loads?
- lfittl 10y agoDo you mean timeseries data as in IoT/sensor data/etc, DevOps monitoring metrics (i.e. server load, app performance, etc), or something else? Curious since I'm currently researching how PostgreSQL could do better in this space :)
- takeda 10y agoNot the TP, but I personally am interested in the later one (metrics). There doesn't seem to be any silver bullet yet. And it is also hard to even see how relational database compares to the existing solutions, since most people dismiss it immediately.
- pzaich 10y agoI'm also in a similar position where I'd like to store approximately 560k records / user / year. My understanding is that Cassandra doesn't support some useful queries that would be useful when business logic is less clear (like group by)[1]. I'm leaning towards using PostgreSQL with a dedicated write DB until performance becomes an issue. [1] http://stackoverflow.com/questions/17342176/max-distinct-and-group-by-in-cassandra http://stackoverflow.com/questions/17342176/max-distinct-and...
- thom_nic 10y agoI decided to tee my timeseries data into InfluxDB. Purpose built for the task and has builtin support for rollups/ aggregation/ retention policy/ gap filling. Admittedly I have not put Influx under much stress or scalability testing since my use case is more based on utility than performance. Unless PG has some timeseries-specific extensions I have assumed it would be appropriate for a TS-specific database. Also curious to try Riak TS.
- 10y ago
- hinkley 10y agoAlways nice to hear about throughput improvements in Postgres. How do these changes affect more heterogeneous workflows, of mixed reads and writes? A little better? A lot better? A little worse?
- anarazel 10y agoVery dependent on the workload. The optimization isn't specific to reads or writes, but in many cases your bottleneck when writing will be elsewhere.
- gjolund 10y agoPostgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a very specific problem (2 there is already a legacy db in place I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown. The only time I will advocate for anything other than Postgres is when Wordpress is involved. If the data model is simple enough then MySQL is more than up for the task, and it avoids an additional database dependency. Thankfully all the ORM's that are worth using support MySQL and Postgres, so using both is very doable. ### Useful Postgres (or SQL in general) tools/libraries : Bookshelf ORM http://bookshelfjs.org/ http://bookshelfjs.org/ PostgREST automated REST API https://github.com/begriffs/postgrest https://github.com/begriffs/postgrest Sqitch git style migration management http://sqitch.org/ http://sqitch.org/
- __float 10y agoWhat do you mean by MySQL avoiding an additional database dependency?
- deleted 10y ago[deleted]
- gleenn 10y agoIIRC PHP has built-in support for MySQL out of the box.
- charliedevolve 10y agoI think he meant that MySQL is the only supported DB for Wordpress, and while you might be able to get it running on another DB after jumping through some hoops, it's probably not worth the effort.
- ionheart 10y agoBig thanks for the community for the hardwork! I wonder if we set the "sync=off" in the test, will it be way higher than the OP results?
- jeltz 10y agoSince this is a read-only benchmark it won be affected by either synchronous_commit=off or fsync=off (do not turn off fsync for any data you care about, it can be silently corrupted on a crash!).
- malloryerik 10y agoAny opinions about AWS' SQL database, Aurora?
- pritambarhate 10y agoThese 2 articles on Aurora by Vadim Tkachenko, the CTO and co-founder of Percona are very informative - https://www.percona.com/blog/2015/11/16/amazon-aurora-looking-deeper/ https://www.percona.com/blog/2015/11/16/amazon-aurora-lookin... and https://www.percona.com/blog/2015/12/03/amazon-aurora-sysbench-bencmarks/ https://www.percona.com/blog/2015/12/03/amazon-aurora-sysben...
- znpy 10y agoDumb question: in this context, "TPS" means... ? T<what?> Per Second?
- hbrid 10y agoPostgres is still going through the motions of a transaction for every query you issue it even if nothing else but that transaction is happening on the server. So obviously if you add extra load in the form of writes, you may slow your reads down, but this was not a full benchmark, but instead a comparison of the same workload running against multiple versions of Postgres.
- Tostino 10y agoHuh, that's funny to see a comment of mine copied word for word from Reddit: https://www.reddit.com/r/programming/comments/4in70l/postgresql_scalability_towards_millions_tps/d2zo1kl?context=3 https://www.reddit.com/r/programming/comments/4in70l/postgre...