Slashdot Mirror

The problem with relational databases is that they can't scale beyond a single host.

Uh, what? I'm pretty sure Oracle solved this problem decades ago via their RAC technology, not to mention PostgreSQL is almost there with the work being done on BDR.

https://2ndquadrant.com/en/res...

You're welcome.

The write cache on the Crucial M500 survived my power plug testing with similar properties to this article, but it doesn't have SMART data for drive wear. Can't take that one seriously for business use. I've been very happy with the figures I've seen from Intel drives on their internal lifespan tracking; see my Intel SSD lifespan for example. I'm not the only one who noticed this flaw in the M500, the Tech Report review has another complaint.

None of the cheap and easy to buy Samsung drives (840 and 840 Pro) claim any power loss protection, and they all fail this sort of test. They have enterprise models that might work, but those wouldn't fit within the budget parameters here. I find it hard to take those seriously when the 840 drive they share design features with are so terrible handling lifespan failures.

I'm not aware of any Sandisk models with that feature, but I haven't gone looking for them either.

The only drive really missing here that might have passed are the Seagate 600 Pro models. Those haven't been shipping long enough for me to recommend them yet.

Intel offered an enterprise version of the 320 series drive in the 710. The only difference was in expected lifetime, which ripples out to impact write performance guarantees too. See my 320 vs. 710 lifespan article for details.

I've done a lot more of this type of testing than this article, and the only other drive on the market to consider is Seagate's 600 Pro. That hasn't been out long enough for me to have a strong opinion, it seems fine. This article is correct--if you look at drives where there is more field data available, Intel's 320/710 and DCS3500/DCS3700 are the only models that do power protection and have solid lifespan monitoring data too. Crucial's M500 seems to get power protection write, but there's no SMART data on wearout.

The power loss protection on the Crucial M500 worked fine in my initial tests. It can't be taken seriously as a reliable drive because it doesn't have any SMART data on longevity. There's no way to know when the drive is wearing out, so it's pretty much useless for serious work. The one I bought for testing is in my laptop, it's a fine drive there. See Tech Report for a review complaining about the missing SMART data, I'm not the only one who noticed.

Intel's data on wear is very good, see my look at the 320 vs. 710 lifetime for example. The replacement models, DCS3500 and DCS3700, are even better drives in every way.

There are a some ways to force a query plan onto Postgres that works effectively as hints. See my Hinting At PostgreSQL. It's also possible to overide how Postgres runs selectivity functions to get different results. That mechanism is powerful enough that you can do almost everything possible with hints and then some. The problem is that it's too difficult for most to develop their own statistics model just to fix a broken query. When the alternative is sucking on everything Oracle makes hard, I can't understand why people aren't willing to do this the right way sometimes.

The performance analysis tools for PostgreSQL are still rough, but they're coming out stronger now than ever before. The old slow query profiling approach is based on database log files, and the pgbadger tool has gotten a lot of improvements in the last year to take the lead in that area. Some web app providers have added PostgreSQL data collection and visualization to the products recently, Datadog is a good example, they even run Postgres internally.

Last year's PostgreSQL 9.2 added a built-in query profiling feature via an improved pg_stat_statements module. That makes it relatively easy to see what queries are taking up time on the server, in a way that matches similar statements based on underlying their query plan. I wrote a sort of call to arms to suggest how the next generation of analysis tools can leverage that in Beyond Query Logging.

You are correct that no one has really grabbed ahold of this area and put together a really easy to use tool set around it. All of the hard to construct pieces needed are in place now, and my list of goals for this year's 9.3 development includes pushing the tuning methodology outlined in my High Performance PostgreSQL 9.0 book into some reference tool implementations. The idea that this is a "black art" is coming from consultants who want you to be intimidated. People who want to understand how things work can read my book, and then wander out to confidently build terabyte size databases. I talk with new people who have done just that every week now.

This isn't unprecedented. When I looked into the 710 series models it was the same trade-off: those drives were also only specified to save their data for 3 months between refreshes.

The small amount of RAM on Intel's SSDs are not used to cache writes in a significant quantity. The idea that you'll only have to write the most popular cells once per shutdown is a dream. The main benefit of having a bit of reliable capacitor backup is that the drive can be less aggressive about forcing an erase of a large cell just to write a fraction of it out, therefore improving the write amplification situation on the drive. You can even see limiting small writes as a factor in the claimed longevity of the drives if you dig into their spec sheets enough. I did an article comparing the 320 vs 710 series lifetimes, approaching from the perspective of one of those specialist things you allude to--database server operation. One of the things that I noticed there is that the longer lifetime of the 710 came with the restriction that you couldn't do nearly as many small random writes per second (write IOPS) and still hit the claimed lifespan target. If the cache was larger and really effective at postponing writes, that trade-off wouldn't exist.

Serious users should insist on SSD with a battery or super capacitor. If not, then you might lose data in internal caches in an unclean shutdown.

Unlike the Intel 320 series, I can't find anywhere whether the 335 series has backup power, so I strongly assume that it doesn't.

It's not trivial to figure out, but we've been deploying PostgreSQL 9.0 without the problem you describe (must do a fresh dump from the master) for a while now. The repmgr software we've released takes care of all the promotion trivia. Worst-case, unusual situations can require you use a tool like rsync to make an out of date standby node into a copy of the new master. That's not the expected case though.

Lots of companies are converting from Oracle Spatial to PostgreSQL plus PostGIS because it's faster and has better compliance to GIS standards. The text of the talk isn't available, but the FAA Airports GIS and PostgreSQL presentation was a typical report I was in the audience for. The FAA's first conversion happened very quickly: just export their data in a standard format, import into PostgreSQL, and tweak some queries. The result worked so much better that they've standardized on PostgreSQL for spatial applications at the FAA now. Internal projects needing a spatial database have to justify why they want the budget for Oracle Spatial, and it's default deny unless you have a really good reason.

The addition of synchronous replication to 9.1 has made it a pretty even match for Oracle's Data Guard now. The main bonus is that you can control the integrity level you want at the transaction level. So you can have a database with a mix of important data (only considered safe when on two nodes) and fast, best attempt eventual consistency data, all in one place. Nothing else can replace Oracle at the top end while still having a lean enough mode to be competitive with NoSQL database when integrity isn't the top priority.

We convert Oracle installs to PostgreSQL all the time at my day job. The main obstacles I keep seeing that don't have simple solutions are 1) using a lot of PL/SQL, 2) differences in query handling, such as OUTER JOIN behavior or reliance on optimizer hints, and 3) can't limit the resources used by individual users easily in PostgreSQL yet. I actually have a design outline for how to solve (3)...would only cost a fraction of a typical Oracle license to sponsor that feature. EnterpriseDB's version of Oracle comes with PL/SQL compatibility, but only in a commercial product that lags behind the open-source releases--and buying from them just switches which vendor you're locked into.

I wrote a blog entry on The rise and fall of CentOS that talks at some length (including long into the discussions) about why CentOS has failed to prosper specifically because they are not an open, growing community, and about how they might emulate more successful open-source projects instead.

Not just currently. Today's organizational turmoil within CentOS is nothing compared to when they lost access to much of the infrastructure a few years ago. I just wrote a blog entry on the rise of and fall of CentOS; the theme is why it's important to build an open community, not a tight clique, if you want an open-source project to scale.

There's also Hinting at PostgreSQL, by the author of the book being reviewed here (me), covering what hinting mechanism are available. And finally Why the F&#% Doesn't Postgres Have Hints?!?! suggesting why some feel that still isn't enough.

You've got the performance part backwards for PostgreSQL; it goes up with every release, sometimes a little, sometimes in a big way. See PostgreSQL history for a comparison covering versions 8.0 to 8.4. The mild regression in 8.4 shown there is actually reversible; it's mainly because a query related parameter for how many statistics to collect and use for query planning was increased by default. That results in better plans for most real-world queries, but it detuned this trivial benchmark a little bit. You can get performance back to 8.3 levels just by turning the parameter back to the "optimized for trivial queries" default of the older versions if you care about that. Most people prefer the new default. In the real world, 8.4 is actually faster due to improved handling of background VACUUM tasks too, which don't show up in simple benchmarks either.

I'm the current lead architect on building a PostgreSQL Performance Farm to prevent regressions from popping into future versions of the code too. There is a recently completed beta client for that purpose. We're in the process of working out how to integrate into future development, starting with 9.1, so that potential regressions are spotted on a commit by commit basis. I haven't seen any performance regressions between 8.4 and 9.0, only moderate improvements overall and large ones in specific areas that were accelerated.

Now, if you use some of the new replication features aggressively, that can add some overhead to slow down the master. But that's true of most solution; the data coming off the master has to take up some time to generate. The way PostgreSQL 9.0 does it is is pretty low overhead, it just ships the changed blocks around. Theoretically some statement based solutions might have lower overhead, but they usually come with concerns about non-determinism on the slaves when replayed (random numbers, timestamps, and sequence numbers are common examples).

Given the non-disclosure terms of most of the closed source databases, nobody can publish benchmarks that include them without going through something like the TPC or SPEC process. The last time that was done in 2007, PostgreSQL 8.2 was about 15% slower than Oracle running the same database-heavy workload. And note that it was PostgreSQL 8.3 that had one of the larger performance increases, so that was from just before a large leap forward in PostgreSQL performance.

At this point, Oracle and most other commercial databases still have a large lead on some of the queries run in the heavier TPC-H benchmarks. Links to more details as to why are on the PostgreSQL wiki. It just hasn't been a priority for development to accelerate all of the types of queries required to do well in that benchmark, and nobody so far has been willing to fund that or the subsequent certification via the TPC yet. Sun was the only one throwing money in that direction, and obviously the parts of that left within Oracle will no longer do so.