First "Real" Benchmark for PostgreSQL

anticlimate writes "A new benchmark published on SPEC shows PostgreSQL's performance approaching that of Oracle's and surpassing or on par with MySQL (however the test-hardwares of the other DB systems are somewhat different). The test was put together by PostgreSQL's core developers working at Sun. They certainly are not unbiased, but this is the first 'real' benchmark with PostgreSQL — according to Josh Berkus's blog. The main difference compared to earlier benchmarks (and anecdotes) seems to be the tuning of PostgreSQL."

Comments

I do not think it means what you think it means.

[Control Group]

however the test-hardwares of the other DB systems are somewhat different

Which makes the results pretty much useless. But, being the intrepid slashdotter I am, I went ahead and R'ed the FA anyway, in case I could glean some useful information from it.

Which revealed that the linked article doesn't actually contain any information whatsoever about Oracle* or MySQL, much less benchmarks on named hardware.

So...what am I supposed to get out of this, again? Or is this just supposed to be some kind of PostgreSQL love-in, so I should take my wet blanket elsewhere?

*Well, the second link contains someone claiming that Oracle is only 15% faster...but without providing any actual data.

Mod parent way up!

[khasim]

You cannot compare benchmarks without SOMETHING standard between them.

Okay, if they can't match the hardware (why not?) then focus on price points. I notice that they're looking at "$65,500 for the hardware". That's a LOT of hardware at today's prices.

I'm sure MySQL would (and will) come back with a "benchmark" on hardware costing $10,000.

There is nothing "real" about this "benchmark".

Re:Mod parent way up!

[Anonymous Coward]

I can't speak for complex queries, but here are some simple findings from my testing:

Inserting 20 million rows, all simple inserts, only one primary key (int) with autoincrement for mysql and a sequence for postgres:
Avg Mysql time per 1000 inserts: 3 seconds
Avg Postgres time per 1000 inserts: 15 seconds (and gets worse over time)

That was after increasing the caches and disabling fsync on postgres too.

I also did a delete then insert for both (to flush out already existing rows), with similar results.

Ended up just inserting into a mysql table with no keys/indexes at all for maximum speed (average of .4 seconds per 1000 inserts), but didn't test that with postgres. Decided I only needed a static table anyway, so mysql was more ideal for it's speed in my situation. Sure with I had used postgres on a few more complicated projects though...

Posting AC to avoid flames.

Re:Mod parent way up!

[Vellmont]

You cannot compare benchmarks without SOMETHING standard between them.

The thing that's standard is the benchmarking software.

If I were to buy a database server, do I really care which component of the solution is providing me with the great performance, or do I just want the performance? At the end of the day the only thing that really matters is the performance that comes out of the box.

It doesn't really matter if "Postgresql" is faster than "MySQL", because they always run on a certain physical computer. What matters is "I need to accomplish X,Y and Z. I have A dollars to spend. Which solutions accomplishes X, Y and Z the best within my budget? You can't separate the software from the hardware and get an answer that's very meaningful.

This benchmark isn't the last word on anything. Even a benchmark run on the exact same hardware means very little if you have a 2 core machine instead of 8.

Re:Mod parent way up!

[Doctor Memory]

Inserting 20 million rows, all simple inserts, only one primary key (int) with autoincrement for mysql and a sequence for postgres:
Avg Mysql time per 1000 inserts: 3 seconds
Avg Postgres time per 1000 inserts: 15 seconds (and gets worse over time) OK, now do a seven-table join, including a self-join with a correlated subquery (MySQL does those now, right?). I think everybody knows by now that MySQL is pretty much untouchable as long as all you're doing is simple single-table stuff. Kind of like comparing a pickup truck to a moving van: if all you're doing is moving a couple of boxes around, then the pickup kicks. But when you need to move serious loads, then it's the pickup that gets to sit by the curb...

Re:Mod parent way up!

[jaredmauch]

AC, sorry, but I have a postgres install working where I get 70k inserts/second or more with a single index on the table during the day. The first insert of course is faster as the index doesn't exist yet. I'm not sure what you're doing, but I can tell you that I have tuned postgres by increasing some simple parameters. If you're using some Linux package, you're likely not seeing the benefits that are possible by stuff like changing the block size parameter in the source. Yes, it's kinda lame you have to do this, but at the same time, it's not too unreasonable. I'd like it if I could set this larger.

This is on 'decent' hardware running Solaris 10 (amd64). Obviously you need to tweak stuff like wal size, checkpoints, etc.. But getting this type of performance is not hard to do. I can scan an hours worth of data in a short amount of time. Each one of these 'hourly' tables contains roughly 30-32M rows. this is nothing to sneeze at from what I can tell. I haven't had a reason to re-evaluate mysql to see if there are enough tweaks to make it perform similarly, but if you're getting the crappy insert rate that you're talking about, you clearly need to change something as you're doing it wrong if you truly care about performance. E-Mail me if you're interested in my postgresql config files. I'm happy to share to minimize the FUD out there.

Re:Mod parent way up!

[jedidiah]

If you do that to even a "fast and robust" RDBMS server you are bound to be bludgeoned by the DBA.

Re:Mod parent way up!

[ctr2sprt]

You can do way better than that with PostgreSQL, at least, and I suspect with MySQL as well. I wrote a benchmark similar to yours, but a good bit more complex. I had two tables, one of which was seeded and another which was populated by the benchmark. The benchmark table had six columns (int, timestamp, 4x bigint), a primary key (int + timestamp), four check constraints (on the bigints), a foreign key constraint (int, to the seeded table), and two indexes (one int, one timestamp). I would do a commit every 75k rows, with 24 such commits per iteration and 30 passes per benchmark run, so 54 million rows total. I also used a thread pool, and there are two reasons for that. First, some amount of parallelism improves DB performance. Second, it more accurately simulated our predicted usage patterns of the database. We ran my benchmark against PSQL and IBM DB2.

The results were interesting (at least, I thought they were). First, PSQL can only handle about 10 threads doing work at once. Past about 10 threads, the DB completely falls apart. DB2, however, could handle more busy threads than Linux could, with a very gradual (and linear) degradation in performance past about 25 threads. I stopped testing at 100 threads. Second, PSQL's inserts per second (IPS) rate cut in half by the end of the bechmark. DB2 followed a similar trend until about 5 million rows, at which point IPS went up to where it started and stayed there without moving. Third, DB2 was I/O-bound, whereas PSQL was CPU-bound. I suspect it's why DB2 was able to handle an order of magnitude greater concurrency: more threads just meant the CPUs had something to do while waiting on the disks. However, it does mean that PSQL might do better with faster CPUs, whereas DB2 would not (it'd just be able to handle more threads).

And the numbers: DB2 averaged 1100 IPS, PSQL 600. Note that for the first million rows or so PSQL was faster: it just eventually dropped down to ~400 IPS after ten million rows or so, killing the average. Of course, since this table would never have fewer than 54M rows - actually, it would typically have 160M - the IPS I got at the end was the one that mattered. Also, this was on a pretty weak server, at least for this kind of workload. With more (and faster) cores, more memory, and more spindles, I'm pretty sure you could increase those numbers by 50% or more. With tuning, perhaps that much again.

Re:Mod parent way up!

[megalomaniacs4u]

As the DBA for Postgres DB where we do that for a website that searches over several million rows (with daily updates of up to 25% of the DB) in under 10 seconds. I can say that a similar query is ugly as hell but it only took a couple days tweaking to get the average case to under 3 seconds - certain parameters max out at 20 seconds.

Certainly postgres plays nice with self joins and natural joins as the query turned out faster than trying an iterative stored proc so we just access a view from the proc.

Re:Mod parent way up!

[Gorshkov]

If you need one of Sun's key luminaries in order to get that level of performance it is also of limited value. I don't want to have to drag Burleson or Niemiec out to my shop just to get my database to run well. I need to be able to get the database to run well on my own. My company won't spring for Niemiec's pool boy, nevermind Niemiec himself.

You're missing the entire point of what he's saying.

When you see benchmarks run and comparisons made between different databases that are conducted by a single person or company, all things are seldom equal. The people involved have more expertiese with this db package and less with that ... things are not set up equally well.

For these published tests, Sun spent a lot of time optimizing postgress to make it fly .... just as the people at oracle spent a lot of time optimizing *their* database configuration for the tests that *they* publish.

So, all things ARE equal, in relation to the database tuning & setup - both were done by experts with the respective packages, and we don't have to wonder if some yahoo screwed things up, intentionally or otherwise, by missing a basic configuration option.

Re:Mod parent way up!

[jedidiah]

I've done OLAP that would make your database server hardware melt and make a hole in your datacenter floor.

Re:Mod parent way up!

[moderatorrater]

Great analogy. Just remember that there are far, far fewer moving vans in this world for a reason and that they sit next to the curb more than the pickups.

Re:Mod parent way up!

[timmarhy]

that would be because they are testing serious db applications, not your fucking toy shit. $65,000 on a server is no big deal at all.

Re:Mod parent way up!

[arivanov]

Who cares if MySQL does them or not. Show me the developers that can both develop applications and do SQL. That is a dieing breed. Most developers nowdays go for a really trivial schema and an abstraction layer. At that point the only thing that matters is row speed on simple table operations and there MySQL or in-memory OO database frameworks with a simple backing store wipe the floor. This is the reality of life. And it is not going to get better. If you look at the books on the market the only book that used to teach "proper" SQL (with joins and the lot) strictly from the context of application development was the old DB2 bible. It has not been reprinted since the late 90-es. All the rest that is out there is either heavily slanted toward the app side or towards the DB side (usually the latter). Add to that the fact that many universities try to teach "real life software engineering skills" instead of proper data structure and data manipulation classes and the picture is complete: http://www.joelonsoftware.com/articles/ThePerilsof JavaSchools.html. Add to that the fact that DBDs when you actually corner them to ask something meaningfull answer with SQL technobabble like in your post. To the average developer it sounds like fortran. And if it looks like fortran, walks like fortran and talks like fortran it gotta be fortran. From the point of view of a average software engineer SQL and especially stored procedures look like a blast from the past. He expects to see objects, constructors, destructors, private and public structures. And what does he see? He sees something that looks like written by his grandparents. As a result he turns around and starts doing delete/insert/last_insert_id instead of replace and sequential deletions in software instead of foreign keys. I have tried in the past to work with developers who write commercial apps on top of SQL to optimise their code. And I have wanted to scream all along. In 95% of the cases you deal with either one of the following:

• A nice schema designed once upon a time properly by a proper DBD that is vandalised in the application abstraction layer because the developers are are sorely pissed off by the endless wingeing of the SQL server and/or its abissmal performance. So they take the matters in their own hands and violate ACID by cashing and bypassing restrictions in the app. Sooner or later someone comes around and says - WTF, why don't we rewrite this all in software and sod off the expensive database. And surprise surprise it ends up being done in MySQL.
• An abissmal schema or no schema at all where all restrictions are done in the app. That is MySQL country all the way.

MySQL is a result of the way current software development is taught and done. Unless Jo the Average Developer starts understanding how to use SQL in his application (and he does not) and unless SQL data representation grows up to modern non-fortran-like OO semantics MySQL will proliferate. And if you think that MySQL is bad think twice. There are the object persistence frameworks and in-memory crap that follow in its wake.

Re:Mod parent way up!

[arivanov]

That is the problem - it does not in real life. Application works in developer hands, goes out in the field and breaks (seen that one time too many). Millions if not billions of dollars have been put to make sure that RDBMS transactions are atomic and preserve data integrity. No application level interface abstraction has ever afforded the expense and could ever afford the expense to do that. In every single instance I have looked at application developers replacing SQL ACID with "bake-their-own" system I have found cases of data integrity violations. In modern multithreaded (or web server based) apps the most common result from this is race conditions which are probably the hardest to debug problem in software.

The other common problem in using application level abstractions is performance. Once again - works in developer hands, goes in the field, gets real data loaded in it and all hell breaks lose. Similar reasons to ACID as the next biggest investment after data integrity in a database is in its ability to fine-grain lock data objects. If a developer tries to replace RDBMS locking in the application layer, he usually ends up with higher granularity lock that is more contended. In addition to that to avoid race conditions, developers usually deliberately create a bottleneck by muxing all RDBMs access to a single thread and a single access point to simplify locking. In fact probably one of the most beneficial uses of MySQL is its ability to support server-based fine-grained locks that are not tied to a specific data object. You can use these in global semaphors and global locking even in cases where POSIX locks do not work (f.e. across clusters).

Overall, yeah, MySQL and your app "already works". For Proof of Concept - maybe (in fact I use it myself). For real stuff - no, not really, unless you put a lot of work in the application layer. I have done that on quite a few occasions and the performance gains can be staggering compared to ACIDising your brain with a proper RDBMS, but the effort is hardly worth it in most real life scenarios. It also makes it considerably less maintainable.

Re:Mod parent way up!

[kpharmer]

> unless SQL data representation grows up to modern non-fortran-like OO semantics MySQL will proliferate

You do realize that in activities like reporting sql and its set-based operations are far, far, far faster and easier to work with than oo implementations, right?

You can set up a typical star-schema and have certain tools (like microstrategy) immediately recognize it and generate queries for you. These queries will typically perform just fine and allow very powerful and fast drill-downs, drill-across, etc.

And oo approach involving the marshalling of millions of objects related to entries in the database would take forever to build to this kind of flexibility and would run slow as molasses. Might make the oo purists happy, but the customers would never use the product. At least against non-trivial amounts of data.

Re:Mod parent way up!

[Doctor Memory]

Most developers nowdays go for a really trivial schema and an abstraction layer. At that point the only thing that matters is row speed on simple table operations and there MySQL or in-memory OO database frameworks with a simple backing store wipe the floor. Until, of course, they don't. All it takes is a couple of users who want to actually get information *out* of the database ("How many widgets do we typically sell in Poughkipsie in March? And when I say 'Poughkipsie', I mean the greater Poughkipsie metroplex.") and you're stuck building indexes and making joins in your code. Eventually your code either becomes unmaintainable, or collapses under its own bulk. Agile/XP developers like the DRY axiom: Don't Repeat Yourself. Why write code to do what the database already does?

DBDs when you actually corner them to ask something meaningfull answer with SQL technobabble like in your post. To the average developer it sounds like fortran. And if it looks like fortran, walks like fortran and talks like fortran it gotta be fortran. Um, are you sure you want to be bringing up FORTRAN as a counterexample when discussing performance? I'm not an HPC enthusiast, but I don't recall seeing Java or Python mentioned in the same sentence as Linpack or STREAMS. It's all FORTRAN (77, some 90) or "C/C++" (the C++ is silent). Just another case of "pick the right tool for the job".

From the point of view of a average software engineer SQL and especially stored procedures look like a blast from the past "Those who do not understand Unix are condemned to reinvent it, poorly" — Henry Spencer. Relational databases are based on set theory, and have proven their worth over the last thirty years. Neither the old CODASYL or the new object databases could compete. Anyone who claims to be a "software engineer" and can't understand SQL is a poser. If they can't learn old tech, how are they going to learn new tech? There is an "SQL for Dummies", you know...

unless SQL data representation grows up to modern non-fortran-like OO semantics MySQL will proliferate In that case, why even use MySQL? Fall back to MyISAM and do all the work yourself! Hell, you are anyway, why even stick an SQL abstraction layer in there? Some fast serialization logic and you're good to go. For toy apps (like those presented in programming books), you can get away with stuff like that. When you get into real projects (something that requires more than one developer more than a year to do), hand-rolled "abstraction layers" that can't guarantee consistency or even simple reliability (quick: what does your data layer do with uncomitted writes when the kernel panics?) just don't cut it.

The best way to truly compare

[CaptainPatent]

Because Sun systems will always be different from the x86 based cores that run MySQL and Oracle, I think the best way to compare such software would be by constructing servers of equal price and seeing how PostgreSQL fares. The true question on any business person's mind is "how much to implement?"

Re:The best way to truly compare

[Doctor Memory]

Sun systems will always be different from the x86 based cores that run MySQL and Oracle Umm, wrong both ways. Oracle runs really well on Sun SPARC hardware (and I suspect MySQL at least runs), and Sun also makes x86-based servers (built with AMD's Opteron chips). It shouldn't be any trouble to benchmark all three on the same hardware.

Well, no technical trouble, anyway — I doubt Oracle would like to have its performance compared to two free-as-in-beer competitors. Even if it comes out on top, people will still be tempted to think "Jeez, with the money I save on Oracle licenses, I can buy a faster server and make up the speed difference"...

Re:The best way to truly compare

[jhines]

If you read the details, while being Sun machines, they are Opteron based, so yeah they compare.

Re:I do not think it means what you think it means

[Ngarrang]

To paraphrase an old saying:

There are lies, damned lies and benchmarks.

Bad firehose!

[greg1104]

Why this emaciated post made it while mine didn't I'll never know...here's how I submitted this story:
 
The current version of PostgreSQL now has its first real benchmark, a SPECjAppServer2004 submission from Sun Microsystems. The results required substantial tuning of many performance-related PostgreSQL parameters, some of which are set to extremely low values in the default configuration — a known issue that contributes to why many untuned PostgreSQL installations appear sluggish compared to its rivals. The speed result is close but slightly faster than an earlier Sun submission using MySQL 5 (with enough hardware differences to make a direct comparison of those results unfair), and comes close to keeping up with Oracle on similarly priced hardware — but with a large software savings. Having a published result on the level playing field of an industry-standard benchmark like SPECjAppServer2004, with documentation on all the tuning required to reach that performance level, should make PostgreSQL an easier sell to corporate customers who are wary of adopting open-source applications for their critical databases.

Re:Bad firehose!

[MrNaz]

I like yours better. The Slashdot editors need to have their balls cut off if they think the post that beat your onto the front page is better. Feel free to mod me down any time for bitching about this, but seriously, this post is SO much better than the one that made it.

Re:on the playground...

[CaseCrash]

boys use mysql men use PostgreSQL, and _____ use MSSQL. and people who like to collect a paycheck use MSSQL.

What are the tuning parameters?

[Anonymous Coward]

For those of us who don't have dozens of hours to do the necessary research, can some postgresql gurus sum up some of the most significant tuning parameters so us mere mortals can see similar performance gains?

I realize that a large part of the answer is going be "it depends on application, your hardware, and you query types", but surely there must be some general tips that we can follow given various typical setups. MySQL, for example, ships with several different configuration files: One suitable for a small installation, one for a mid-sized installation, one for large installation, etc.

What tuning can someone do to tune postgresql's default (conservative) config file to make it perform better?

Re:What are the tuning parameters?

[Anonymous Coward]

Google is your friend...

http://www.varlena.com/GeneralBits/Tidbits/perf.ht ml

http://www.revsys.com/writings/postgresql-performa nce.html

There is also good material in chapter 4 (Performance) of "PostgreSQL" by Douglas and Douglas (Sams Publishing Developer's Library)

Re:on the playground...

[Kalriath]

DB2? It's only useable on large mainframes (big iron, so to speak) from what I understand. Generally speaking only the REALLY large shops would use it, so I wouldn't be surprised you'd never seen it - neither have I. We're a pretty big organisation where I work, and we have a mix of Oracle, MSSQL, and Sybase servers.

Re:I do not think it means what you think it means

[KillerCow]

I think that somebody sent the wrong link and (surprise!) the editors didn't even follow it to check.

Here's a more useful one: All SPEC jAppServer2004 Results Published by SPEC

The benchmarks aren't standardized enough for any useful comparison. The hardware and configurations vary in almost every one.

Re:I do not think it means what you think it means

[Ungrounded Lightning]

however the test-hardwares of the other DB systems are somewhat different

Which makes the results pretty much useless.

Not necessarily.

It's essentially useless for separating out how much of the performance difference is the result of the software's design, implementation, and tuning versus how much is due to the platform differences.

But such tests CAN be used to examine the performance of competing ENTIRE SYSTEMS, to inform choices between them.

They say: "Oracle on does THIS well, PostgreSQL on can be tuned so it does THAT well on the same benchmark."

This lets administrators (presuming they have access to the hardware info) get a bang-for-the-buck comparison.

For the rest of us, the interesting point is that PostgreSQL, running on its team's idea of realistic hardware, can produce performance in the same ballpark as Oracle running on Oracle's choice of hardware.

(Whether the necessary remaining data (what are hardwares x and y? how was PostgreSQL tunde) is published now, later, or never, is a separate issue. B-) )

Re:on the playground...

[Kalriath]

I dunno, I kinda like MSSQL. Hell, I use it alongside MySQL servers for my own projects (that, and having support for multiple platforms in your product is kinda a good idea). Sure, it's got horrific licensing (nowhere near as bad as Oracle's, though) but other than that, it's pretty good and reliable. I get the impression that the core of it wasn't written by MS way back when, though. And it sure wasn't built by the Windows team.

Good, but it can be improved.

[khasim]

Get people from each group, give them the requirements and 5 different dollar amounts.

Let each team setup their systems how ever they want at each price point. Some will go with clustered servers. Some will go a single monster server. They know their products best so they'll be the ones best suited to choosing the configuration.

Then run the benchmarks. And keep hammering on them until AFTER the next patch release.

Yeah, it might run fast, but still be a bitch to patch/upgrade.

At $5,000 you might find that a cluster of MySQL boxes beats everything.

At $10,000 maybe something else is best.

$25,000

$50,000

$100,000

etc.

And finally, break it. Break it bad. What happens when something goes wrong? Oracle might cost a lot, but if they can come through with your data they might just be worth it.

If nothing else, you'll get the "best practices" nicely demonstrated by each group. :)

Re:Good, but it can be improved.

[suv4x4]

Get people from each group, give them the requirements and 5 different dollar amounts.
Let each team setup their systems how ever they want at each price point. Some will go with clustered servers. Some will go a single monster server. They know their products best so they'll be the ones best suited to choosing the configuration.

We gave each team $10000 and told them to build the best hardware and db setup they can:

** PostgreSQL got a small IBM Blade quad machine redundant setup:
"We're relying on standard industry solutions and reliability."

** MySQL clustered 4 PlayStation3 machines and wasted the rest on booze and women:
"We're practical, plus we know what is money best spent on!".

** Oracle purchased a 1200 square foot datacenter and installed a megacluster of 8132 quad-Xeon 64GB RAM 4TB disk machines. With $10'000...?!
"We... uhmm... we hit a great bargain, guys! You wouldn't believe it, but it's true!"

Re:on the playground...

[morgan_greywolf]

DB2? It's only useable on large mainframes (big iron, so to speak) from what I understand.

Um, no. DB2 these days runs on most major UNIX variants (HP-UX, Solaris, AIX, IRIX, etc.), Linux and Windows. It's used quite often, in fact. Most Enovia/VPM installations use DB2 backends, for instance. Modern versions use XML along with regular relational database stores and are very, very up-to-date technology-wise. Very scalable.

We finally have PROOF (but not real proof)

[Pap22]

This publication shows that a properly tuned PostgreSQL is not only as fast or faster than MySQL, but almost as fast as Oracle (since the hardware platforms are different, it's hard to compare directly). This is something we've been saying for the last 2 years, and now we can prove it.

Postgresql 8.2 on UltraSPARC T1
MySQL 5 on AMD Opteron 285

The UltraSPARC has 8 cores on 1 chip and 16GB of memory.
The Opteron has 4 cores and 8GB of memory.

The UltraSPARC should smoke it every time.

Re:hardware

[morgan_greywolf]

nope. It's h4rdw4r3z!

Elephant

[suv4x4]

Won't you guys agree, "elephant" doesn't exactly communicate "fast and modern" very well.
"Dolphin" comes a bit closer.

Who's coming up with those logos?

Re:Elephant

[pavera]

"Dolphin" also conveys "fun play thing" to me...

I'd prefer the elephant that never forgets.

Re:Elephant

[turing_m]

If the final decision in choosing an RDBMS comes down to the logo, the choice of database will be the least of your problems.

Re:I do not think it means what you think it means

[Control Group]

Oh, I agree. A benchmark of whole systems can be just as (or more) useful as a benchmark of individual pieces of software, depending on what your goals are.

But what's been presented here isn't even that. Links #1 takes us to a SPEC benchmark of PostgreSQL. It doesn't provide any information about anything else; there isn't anything to compare the benchmark to. Link #2 provides an unreferenced statement about Oracle's marginally superior performance on much more expensive equipment.

So, perhaps, one can begin to draw conclusions about PostgreSQL vs Oracle in the contexts of full systems. But neither link #1 nor link #2 provide any information about MySQL (except the quote: "[t]his publication shows that a properly tuned PostgreSQL is not only as fast or faster than MySQL").

Really, my criticism isn't of the benchmark (the data are the data, after all) or of the blog (one expects a vested PostgreSQL interest to comment on such a benchmark), but of the blurb here that either a) draws totally unwarranted conclusions, or b) depends on information it doesn't bother sharing.

Re:on the playground...

[teknopurge]

Intelligent people use SQLite (or Berkeley DB) for apps that don't require a heavyweight DBMS.

Databases are even being used in many situations that would be better addressed using flat files. That kind of mainframe/JCL talk is not welcome here...

Re:I do not think it means what you think it means

[PhrostyMcByte]

If you want to setup a dedicated database server, you want to know what software with what hardware will run the fastest. So while the benchmarks may not be useful to people wanting to setup a small multi-purpose server, it can still be useful for some people.

Re:hardware

[Deadplant]

No! gad damn it!
"h4rdw4r3" is however an acceptable alternate spelling.

DUH!

[Slashdot Parent]

Why this emaciated post made it while mine didn't I'll never know. Yours wasn't posted because it didn't contain enough flamebait.

Re:performance isn't the issue

[pavera]

I've been using both MySQL and PostgreSQL for 7 years now... I've never felt a need for "developer support". Or if that phrase means "documentation + message boards" I've never felt a lack from postgresql. I also really don't get the PostgreSQL is hard to use argument. In every linux distro I've used in the last 7 years (redhat, centos, suse, fedora core, ubuntu) they are exactly the same... IE apt-get, yum, whatever, install postgresql-server/mysql-server. Then $startupscriptdir/postgresql start....

Do people really get that hung up on using postgres as the initial user instead of root?!? That is the ONLY difference that I can see.

Now I can agree that maybe postgresql doesn't really "target" an audience, but that is also because they are a true open source project. They don't have a commercial version. I really don't think Linus sits around saying "Ok, we need to add this feature so that more fortune 500's will adopt linux". Or "we need to add feature xyz so this will appeal to small businesses". MySQL has a "target" audience because they are selling something. If you aren't selling anything, by definition you don't have a target.

Re:performance isn't the issue

[LurkerXXX]

Actually I think PostgreSQL might have eaten a lot more of the MySQL market if they'd simply been faster to market with better admin tools and Windows support.

Lots of folks went with MySQL early because of those factors. They also then tended to write all their PHP, etc, applications to only talk to MySQL, thus making folks who might have preferred PostgreSQL use MySQL to run the app that they needed to run. Once that happens you are kind of in a Catch-22 place. Folks won't write the apps for PostgreSQL until it's used by a larger chunk of the market, but it won't take that large chunk because all the 'cool' apps were written MySQL only, so they have to run MySQL instead of PostgreSQL

Which MySQL?

[itsdapead]

MySQL is modular - pick'n'mix data storage engines sharing a SQL front end. I can't find the bit in TFA that says which one they compared.

I've always suspected that most MySQL vs Postgres flame wars are based on comparing Postgres with the speed of MySQL/MyISAM (No transactions or relational integrity checks - so, big surprise, dead fast for simple queries) and then waving MySQL/InnoDB around when the functionality issue is raised.

MySQL/MyISAM hits the speed/functionality sweet spot for LAMP data-driven websites, is supported by lots of free webapp software and offered by most decent web hosting services. Comparing it speedwise with Postgres has always been pointless, though. If Postgres has caught up, colour me impressed, but if they're pro-Postgres I bet they're comparing with MySQL/InnoDB (which is a bit closer to like-with-like).

Never quite seen the point of MySQL/InnoDB really - all the advantages of MySQL/MyISAM minus the speed, support by popular webapps, availbility on low-cost hosts... and still lacks the features of Postgres.

Re:Which MySQL?

[jadavis]

MySQL is modular [ from grandparent post ]
The point IS pick'n'mix as you put it. [ from parent ]

I think this is a huge misconception, and completely backwards. MySQL allows you to change storage engines, but the behavior at the semantic level changes. That's the antithesis of modularity: the semantic behavior should remain constant, while the performance changes. If you change both, that's not modularity, that's a new configuration that breaks client applications.

MySQL is configureware, like PHP. Everything you get is a trade. Want full text indexes (MyISAM)? You have to give up transactions (InnoDB). But the marketing material always just says "Yup, we have full text indexes (MyISAM), SQL compliance (strict mode=on), transactions (InnoDB), large number of apps (strict mode=off)". Of course many of the features are mutually exclusive. When postgresql says it has a feature, it's really there.

Just need some in-memory lookups? Memory table engine. Clustered data? NDB.

Seems like the memory engine and NDB are the same thing: http://dev.mysql.com/doc/refman/5.0/en/mysql-clust er-overview.html. That means that your "cluster" is not much of a database, and is totally unacceptable for many applications that require a database. Power off == no more data.

Now compare with PostgreSQL. PostgreSQL has one "storage-engine", but there are many access methods to that storage engine. There's Btree, GiST, and GIN. On top of the access methods, there are also a lot of plans. There's sequential scan, index scan (good for lookups or some sorting needs), bitmap index scan (good for AND/OR with other bitmap index scans, and always orders the I/O in file order), hash join, hash aggregate, merge join, nested loop, group aggregate, etc.

Look at all those algorithms for accessing the single storage mechanism. It's amazing. MySQL doesn't have all those, and even if it did have the algorithms, it couldn't use them. How would MySQL know when to use a hash join and when to use a merge join? PostgreSQL collects statistics, calculates expected costs, and chooses the best plan based on the given query (called a cost-based planner). MySQL uses a rule-based planner (does it have an index? ok, let's use it then). To PostgreSQL, these two queries are different:

  (1) SELECT * FROM parents WHERE number_of_children=2;
  (2) SELECT * FROM parents WHERE number_of_children=20;

The stats collector would know that #1 will match many more records than #2. It will probably choose a sequential scan for #1, since it needs to read every page anyway. It will probably choose an index scan for #2. Now, if that's in a subselect, postgresql will know that #1 will return a lot of records, and maybe choose a different join algorithm than if it were #2. Again, PostgreSQL chooses these plans for you based on cost -- no special configuration required.

If you want modular, it's being able to replace a full text GiST index with a full text GIN index and the application never knows the difference except performance. And by the way, the DDL in postgresql is transactional, so you don't even have to restart the application even if you do some major restructuring of the table (like replacing a table with an updatable view).

Re:performance isn't the issue

[kpharmer]

> MySQL, as others have pointed out, has better developer support and they know their target audience. They supply a fast,
> easy to use database for those who don't need a whole lot.
> Oracle supplies an enterprise level database that MySQL doesn't aspire to.
> PostgreSQL doesn't know where to fit in.

This is an oversimplification. Each vendor sees itself in all markets:
    - oracle/db2/sql server have free versions for tiny apps and very expensive versions for massive apps
    - mysql says it doesn't want to do what oracle does, but also says that this is less than 1% of the market - and knows that plenty of smallish databases are on oracle
    - postgresql like the others sees itself doing anything from very small databases to very large ones (often via Enterprise DB or other vendor extensions)

And using a single product for multiple sizes isn't illogical: if you have any very large databases (hundreds of gbytes or more) then you probably have a few dozen little ones as well. It's *far* easier to manage them all on oracle/db2/sql server - even with the small additional licensing costs - than to have a frankenstein collection of products to manage.

"Best tool for the job" is a good consideration when evaluating products (along with vendor viability, cost, etc, etc) - but once you've got a single tool in house to keep adding new products - each with their own licensing, support, patch, backup/recovery procedures, etc is a nightmare. Let alone actually federating your data - and having to test out how to virtualize or replicate data from oracle 10.x.x with mysql 5.y.y

> Performance is one aspect of the price tag, but it is certainly not the only factor.
Very true - and for that reason Postgresql has more going for it than many alternatives, like:
    - best licensing options - you don't need to pay a lawyer to go over your contract or license like you should if you use oracle or mysql commercially. And there's no fear that the vendor will change its license terms once you're locked in and start charging an arm and a leg.
    - very good foundation - postgesql isn't built from duct tape and bailing wire. The functionality within it is well tested and robust.
    - great support for standard database features - whether its subselects, stored procedures, triggers, etc - it's very simple to move from oracle to postgresql.
    - great ansi sql support - again, very standard sql - no unnecessarily propretary language elements.

So, yeah - just because Postgresql is performing well on some benchmarks that doesn't mean you should immediately throw out oracle in favor of postgresql. On the other hand, you also shouldn't discard it because it is a good general purpose database solution.

Re:on the playground...

[Fozzyuw]

Intelligent people use SQLite (or Berkeley DB) for apps that don't require a heavyweight DBMS.

Databases are even being used in many situations that would be better addressed using flat files.

Now there's a good point. Is there any good documentation out there on databasing on what is the best solution for what kind of problem? Particularly, I would like to see something between flat-files and DBMS and then between different DBMS.

Though, technically, I'm stuck between whatever our 'host' is giving us (currently PostgreSQL, but usually MySQL, and I dabbled with MSSQL when an intern for the Government), I'd like to know when it might be better to actually just store some stuff in a flat file or even use an XML DB (which one collage professor loved to talked about as a holy grail).

Outside of "Basic Database Theory and Generic SQL" books, a programming class on Databases (how to build tree structures and navigate them, etc), I've never really got any 411 on the benefits of using MSSQL, Oracal, MySQL, PostgreSQL, etc and I've never gotten the change to learn more about advanced database features such as stored procedures or functions or whatever they might be called and what they're good for.

Recommended books, anyone?

Cheers,
Fozzy

p.s. I work mostly with internet applications.

Re:performance isn't the issue

[RelliK]

1) Ease of use: MySQL has readline, and supports lazier SQL programming. It has loads of syntactic sugar that lets you write simple things in whatever way makes sense to you. Adding users, changing passwords, backing up... it is all _easy_. Of course, postgres can do everything MySQL can too, the question is which is easier.

PostgreSQL's command-line shell is far better than mysql. In addition to what mysql does, psql supports tab-completion and other nice things. It's been a while since I looked at mysql, but when I did the difference between the two respective command-line interfaces was like the difference between bash and sh.

2) Extensive developer community. We use python and the MySQL/python integration is great. We have a few UDFs that are home-grown but some of them were just downloaded off the net and installed. I'm sure you can find far more for MySQL than for Postgres.

Uh-huh. You are "sure". Several times I asked for support on postgresql mailing lists and the response has always been excellent. Usually I got answers within hours.

I think one of the reasons that mysql became ubiquitous is that it had proper windows support early on. So, just like windows, everyone uses mysql because everyone else does, and they are willing to jump through hoops to work around all the deficiencies the platform has, simply because they don't know any better.

I once did benchmarking

[Stinking Pig]

I worked for a company whose product ran on MS-SQL, PostgreSQL, and Oracle. Should I explain why we didn't support MySQL or not? It'll draw fanboys either way. I used the same server, reinstalled the OS (Red Hat Enterprise 3 or Windows 2000) and database between each test, and rebuilt the application server to be extra sure.

Since it was more difficult to write Oracle-compliant SQL and we didn't have a lot of Oracle customers, the developers didn't care to spend time on it, and our stuff ran about 20 percent slower there. That's after a lot of tuning time, it was 50% slower on a default install. Oracle 9 took two days to install and tune, plus another two days of preparation. I was particularly underwhelmed that I had to deal with stupid errors like tarballs that extracted onto themselves and assumptions about the shell being used. At the time, Oracle was a very Solaris-like experience; user-unfriendly to the extreme.

Postgresql 7 ran great; it was neck and neck with MS-SQL in all tests, after proper tuning, and 30 percent slower on a default install. Postgres took half a day to install and tune, but it took me a week and conversation with the postgres mailing lists to find out what needed tuning. Still, we were able to put together a document that took users from bare metal to RHEL+Postgres in four hours if they had all their media handy.

Microsoft SQL Server 2000 ran great with no tuning at all, and took fifteen minutes to install. It also cost as much as paying me to do the entire set of tests. OS installation/patching times and tested workloads were the same for all three tests.

YMMV.

I love this

[RelliK]

Back when mysql was faster than postgres for simple selects, mysql proponents were jumping up and down about performance. And, in fact, a very narrow definition of performance, with only simple reads. No writes. Single CPU. One client. You can't step too far away from those parameters because then mysql performance starts to suck.

Now that there is a respectable benchmark showing postgresql to be faster, performance is suddenly not the issue.

Yep. It's right up there with mysql developers claiming that transactions are useless back when mysql didn't have them.

Re:performance isn't the issue

[aled]

MySQL, as others have pointed out, has better developer support and they know their target audience. They supply a fast, easy to use database for those who don't need a whole lot.

What is exactly easier in MySql?

Version 8.1 :-)

[Ayanami Rei]

8.1 automatically does table maintenance (vacuum, stats, etc.)
http://www.postgresql.org/docs/8.2/static/routine- vacuuming.html#AUTOVACUUM

You can tune it as to when to do such operations, or what max percent of normal I/O can be used for those tasks.

Re:VACUUM FULL / VACUUM ANALYZE

[Per Wigren]

What am I missing? You are missing that recent versions don't need that anymore as it will auto-vacuum for you incrementally in the background. You should still run a "vacuum full analyze" after schema changes though but you usually don't change the schema live on heavily loaded databases anyway...

Re:SQLite versus Postgres

[nuzak]

> Can't say anything about PgSQL, because I couldn't figure out how to set that up.

apt-get install postgresql-server too hard for you? Or are you having trouble double-clicking on the .MSI installer?

Re:PostgreSQL's Core Developers @ Sun?

[greg1104]

Josh Berkus, the person whose blog was referenced here, is one of the PostgreSQL core developers, and he's currently employed by Sun to work on projects like this--which includes contributions back to the PostgreSQL development efforts. He's been doing a lot of work scaling performance upwards to larger capacity servers than the database has traditionally been deployed on.

Re:I do not think it means what you think it means

[swusr]

Real close, I agree.. Postgres had a 40GB cache, Oracle used 21GB

PostgreSQL setup: 8 cores, 16GB RAM.

Oracle setup: 64 cores, 512GB RAM.

Re:on the playground...

[Dan Ost]

The guidelines are pretty simple, but highly subjective.

Here are your options:
A. flat file
B. key-indexed (berkley db, dbm, gdbm)
C. feature/config light db (sqlite)
D. feature/config heavy db (postgrsql, oracle)

* Use A for configuration
* Use A if you have a small amount of data and can slurp it all into memory.
* Use B if you only access data via a known unique value and never compare records.
* Never use either A or B if the data is shared between processes unless file locking is okay.

For anything else, use C or D. If you've got a lot of familiarity with D, sometimes it's easier to use D than C, even if D seems like overkill. My recommendation is to get familiar with C and then worry about D once you understand the limitations of C. It'll help you appreciate what D gives you when you're ready for it.

Re:Bias

[pavera]

The great (sarcasm) thing to me is they have added all these features (views, stored procs, triggers) in the last year or so, unfortunately if you want to use any of those features there are huge caveats when using their main selling point of "easy" clustering. Triggers, Views and Stored Procs all break replication under pretty normal use cases. So, if you want to use them you can't use the built in clustering.

I don't know if the same holds true for PostgreSQL, but I would be extremely surprised since Postgre has had all of those features for years and anyone designing a replication scheme would take them into account. MySQL in contrast had replication first, and now the designers of the additional features have to take the replication scheme into account (which is much harder I'd imagine, as views, triggers and stored procs all have some semblance of a standard, and at the very least have a well defined feature set, and sometimes you can't bend a feature set to fit into an already existing replication scheme)

Also, even if you own the server, converting an existing MyISAM DB to InnoDB can be a huge chore. I've done this for at least 3 customers, and every time it is a much more painful process than you'd think. Mostly because existing data in MyISAM almost always has referential problems after any real world usage (like its been live for more than a month). Someone has deleted a customer, and now you have orders that are orphaned, someone ran a bad update that set the FK wrong in the orders table, and MySQL happily obliged, and now you've got orphaned data... I mean to me the most BASIC functionality of an Rdbms is that it enforces the relationships you give it. I don't even count MySQL w/MyISAM as a database. Might as well just use a bunch of flat comma separated files for tables.

Re:on the playground...

[Anonymous Coward]

SQLite has before insert triggers. Why doesn't MSSQL?