These days it is all the rage to diss on RDBMS. Just surf over to Digg or Reddit and you'll see lots of the following:
- They don't scale well
- There is relational/object impedance problems
- They use massive amounts of CPU time
- Performing table joins is slow
The list could go on and on. All of the cool kids are hyping new non relational databases such as SimpleDB and CouchDB, or even Googles BigTable.
I'm not in that camp. I do understand the shortcomings of a relational database, but I also know its strengths.
Personally, I don't use mySQL, and use SQL Server for my projects, and I have yet to have a scaling issue with it, so I didn't understand why people always claim that the database is the bottleneck in their application.
However, I finally understood because today Jeff Atwood blogged about Wordpress (One of the flagship LAMP products).
Wordpress in its unpatched and default state performs 20 queries to retrieve the posts on the front page.
In comparison, Codeblog performs 4, and I was unhappy with that, but that was due to trying SubSonic out instead of hand rolling my own database layer.
So, I gave Wordpress the benefit of the doubt, those 20 queries must be important right?
Wrong again, take this example from WordPress.
SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
WHERE 1=1
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_date DESC LIMIT 0, 10
And then:
SELECT FOUND_ROWS()
And then:
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE
post_id IN (3,1) ORDER BY post_id, meta_key
Can you count the WTF's there? Did the authors have no clue about normalization and writing efficient joins? There are no relationships between posts, comments and authors, instead they just keep mindlessly querying away.
Of course, the saddest part is that the solution is not to fix their database, its to instead install a caching plug in to hide the fact that their database and supporting code SUCKS.
The bottom line is don't tell me RDMBS can't scale if you can't write a decent query or design a normalized database schema.
Unless you are performing a complex 10 table join, its will still be cheaper than opening 10 database connections and then sorting it all out in your code.
Relational databases are not the bottleneck. Crappy programmers are, which is what I suspected all along.
42 comments:
Just a thought: Non-relational databases let even more crappy programmers do their thing and this is good. Cf. the WWW for reference. HTML is not exactly rocket surgery either and we wouldn't have any of that if people would still write in tex or postscript.
My point: Bad programmers might write crap software but getting more people on the boat gets us further in the end.
And a sidenote: I'd avoid WP for the security problems.
Cheers,
Jan
--
I'm not deeply familiar with Wordpress other than to know that a lot of people use it and people say it makes computing machinery grind to a near halt.
However, I am familiar with SQL.
Thanks for pointing out the metric ton of "Huh?" in Wordpress by using that query as an illustration.
Wow... Looks like someone didn't past Chapter 4 of their database book.
Well said! I'm tired of the RDBMS bashing. I have nothing against the super fast single tables DBs and they may scale in number of users extremely well, but I doubt they scale to large numbers of tables and complicated needs or real IT databases.
As with everything, they all have they're uses - you just have to determine the right when and where. There is STILL no such think as a silver bullet, one size fits all solution.
There are some situations where relational databases do break down though, not everything is solvable by a RDMS. As an example, the browse section of http://music.telusmobility.com/ .
That couldn't be done on a RDMS, ever. Luckily it can be done by dumping the data into memory and then performing integer set operations on it.
You are missing the point. Relational databases do not scale across tens to hundreds of machines. Having a database on more than one computer is a pain. That's why you have eight core data base servers with loads of ram.
Google and Amazon can provide structured data storage cheaper than anyone is offering with relational databases. This is going to push some work into the application layer but so what; there will be nice open source ruby and python libs to deal with that.
The relational database will survive for a while in the enterprise (along with Java, .NET, COBOL and other obsolete tech).
Florian,
I went to the telus music link, and I'm not seeing anything magical there.
Are you talking about being able to scroll through the 50,000 songs?
Thats easily done with a relational database...any database worth its salt has paging features, that website is using paging to preload results as you scroll through them.
Unless I'm missing something?
Anonymous: I suppose thats why sites such as MySpace don't use relational databases on huge clusters? Oh wait, they do. :)
I know nothing about WordPress, but I am guessing that it was designed to work with myISAM instead of InnoDB, and that does not support foreign keys (or ACID, or sanity, for that matter).
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html
A lot of PHP and MySQL apps are not designed at all, it's part of the culture from which they grew. Look at PHP and MySQL themselves - they're a mess, and the developers kept saying things like "implement constraints in application", "nobody needs transactions" and "nobody needs strict types or OOP or namespaces or packages or bytecode persistence/cache".
@Jonathan Holland
In total it's 803890 songs, 55544 Albums, 49781 Artists and 87 Genres over 38 years.
That gives a total meta-data count of 3'215'560 metadata rows.
That "widget" gives you the ability to "filter" these rows, say all meta-data types with counts for the year 2006 (except the year) --> http://music.telusmobility.com/shop/web/tags/attr?offset=0&limit=20&lang=en&4=24
Say we want to see which artists released songs in the year 2006 --> http://music.telusmobility.com/shop/web/tags/value?offset=0&limit=20&attr=1&4=24
And say we want to see all albums by +44 of the year 2006 --> http://music.telusmobility.com/shop/web/tags/value?offset=0&limit=20&attr=3&4=24&1=32358
To explain it simply, what you have here is a big set intersection, the sql equivalent of a stacked having clause with a subquery as a condition against 3 tables.
Believe me, I've been there, there is simply no way to make that work on RDMSes
Does it matter if you use a relational database ten times or a hundred times more efficiently than Wordpress? All it means it that you'll be able to support ten or a hundred times more users than Wordpress.
But if you want to support a thousand times or a million times more users then you need to make an architectural change away from a single box. Relational databases have historically made this transition quite difficult which is one reason why people are looking for alternatives.
Florian:
I'm assuming you developed that, since you seem so familiar with it.
In that situation, it would be useful to slightly denormalize (Combine Artist/Album instead of separate tables is what I would do) it, and then index the table, heavily.
With a proper indexing, paged queries through a simple 2-3 table join can be done on that dataset. Its actually not that complex, its just large, but thats why you build indexes.
I've seen RDBMS with much much larger datasets do just fine.
Florian: Remember, an index allows for quick searching because we can do it with simple pointer arithmetic.
Relational databases don't scale, that's why big sites like myspace or youtube who use relational database shard them up and split the data into chunks, so they can have users A-F on server X, G-L on server Y, M-R on server Z, etc. Once you start sharding, things like normalization and integrity constraints no longer work anyway.
Web servers scale, you can throw 100 more into a web farm and handle 100 times more load with little effort. You cannot throw 100 more servers at your database farm and get automatic scaling.
Databases are always the bottleneck when loads grow beyond what 1 machine can handle, no matter how powerful that machine might be, it can't handle Google and Amazon loads.
To say something scales, it needs to automatically work across constantly varying farms of machines that may reach into the thousands. You ever heard of a 1000 machine SQL Server or Oracle cluster on commodity hardware?
@Jonathan Holland
Yes I wrote that, I wrote the initial 3 rewrites of it using a RDMS attempting to squeeze out better performance, I wrote the in-memory replacement set machine and I supervised it's refinement into what it is.
I think you still don't fully apreciate the scale of the problem, so I'll try to explain.
Each "Document" being a Song has N metadata associations consisting of a type (artist, album, genre etc.) and a value (Madonna, Pop etc.)
In order to facilitate a drilldown into such data you have to have three views, the type view, the value view and the song view.
Each stage of the drilldown stacks one more filter to the condition. And unless you get to the song view, you get a count aggregate for each result which sums up how many results you'll find at at the next level.
Each filter is in relation to the set of songs, but the type and value views are the aggregate associations to the songs filtered out.
You can't do this in the where clause with an AND condition, because the filters operate on the metadata, but mean the songs. So what you really _want_ is the intersection of the sets found by getting all the associated songs for _each_ of the metadata filters.
Play around with the widget for a while, and you'll see what I mean, try to imagine how you'd do that with sql, and I promise, you'll quickly hit a terminal limitation that spells "slow" in big red letters.
once you denormalize a database and shard it over multiple servers you lose most of the benefits of using a relational database. why not switch to a database designed to be distributed at that point?
Amen!
Don't be so quick to judge about lack of normalization in wordpress, as it might be design. I have seen many times with high traffic sites that the tables are organized in such a way that the most queries that are used most (and these are usually read queries) use no or as little as possible live joins. Dirty? Yes. But if coded around it correctly, it works. Also, as pointed out above, there are issues concerning distributing a database among many servers.
Florian: Your example makes sense, I am curious as to what you are storing the dataset and metadata in, and if you are scaling it across multiple machines.
Honestly, it sounds like an enourmous amount of effort for a widget that won't be fully appreciated. I'd love if you could blog in detail about your solution...
Everyone else: I'm not saying relational databases are the silver bullet, I acknowledged that in my post.
I'm just tired of people advocating non relational databases for their Django site. I think mySQL's poor performance coupled with shoddy application coding/query design casts good RDBM's in a poor light, which is not the case.
I recently fixed a bug in a web application that was firing off 3,200 queries to a SQL Server database on a low spec server. Granted this was a low volume application, but you might think that number of queries would have some impact on the UI performance. But no, nothing perceptable at all.
Yes, minimizing trips to the database is important but we need to keep our perspective.
Jonathan, I'm not sure if you actually even read the SQL queries you posted there. If I was working with the same data model, I would use the same queries. Incidentally, FOUND_ROWS is a much better option than running the COUNT() query again to work out the number of matched rows (and practically free, save query parsing).
How are you suggesting they should be improved? Make all metadata keys extra columns in the wp_posts table? Have plugins adding their own tables and columns?
The idea of moving "away" from relational databases is silly. Dudes have been studying data for decades, and relational structures are a much "purer" way to represent it. The impedence between OOP and relational databases can be straining, but dismissing SQL as a result is a cop-out.
You are taking couple of facts like:
This particular application (Wordpress) sucks (true).
It does not scale (true). And then suddenly jumping to conclusion that RDBMS do scale and do not suck. WTF ? How did you manage to make that jump?
I work with RDBMS for more than 15 years, including all big names, Oracle, DB2, MSSQL. And they do suck and they do have scaling problems.
The problem with MySQL was that it doesn't supported stored procedures, fuctions, views, transacctions, commits... Some of them now are supported in V5, but a lot of hosting providers doesn't support this features, so applications like WordPress use intensively joins and querys, wich, of course, make the programs slow
Florian: meet OLAP. OLAP, meet florian. you two kids go have some good clean MDX fun, m'kay?
Relational databases CAN scale. Google didn't write BigTable for fun though. Is this article about the Wordpress devs writing a poor data-access layer or about proliferation of modern non-relational databases?
Regardless, one issue I think you're not considering is that a relational database is overkill for something like a blog. A blog doesn't require advanced data-mining and a document oriented database like CouchDB is going to provide at least comparable performance while not suffering the same impedance mismatch between an RDBMS and OOP languages, not to mention the horizontal scaling capabilities.
I believe the point is that in the case of a simple blog application, a RDBMS is more than capable. It happens to be that crappy programmers sometimes fail to use the RDBMS effectively. This should not be a reflection upon the RDBMS but on the programmers. Stop bashing the RDBMS and learn how to use it properly. If your particular application IS youtube, then perhaps you will need something other than an RDBMS.
The comment I was going to write here grew large enough that it's become a blog post on it's own now. :)
I think the problem is that people are using relational databases when they really just need storage. I don't think they get the 'relational' part. nor do they need it.
That gives a total meta-data count of 3'215'560 metadata rows.
haha, dude I've been doing billions of rows in Oracle for years now. You can setup a RAC cluster across 20 servers if you want with no problem. Then you can setup standby database too, piece of cake.
You can do bitmap indexes, reverse indexes, index organized tables, and all kinds of sweet stuff.
Some little site with the traffic you mention is a joke.
Not getting the relational part is the problem as is not thinking declaratively or working in sets.
Just the other day I was worked on a system where the average time for specific query was 18 mins. Just a slight change made it run in millisecs!
Like anything, once you know the ins and outs you learn to miss the traps and pitfalls.
Ramon :
some RDBMS actually scale quite well. Walmart for example use a few hundred Teradata node to host their datawarehouse and store individual purchases at item level. problem is it cost your first born.
so really the problem is not RDBMS doesn't scale .. the problem is more how much you are willing to pay for a generic scalability.
of course you probably be able to get much better scalability with domain specific solution .. which what google does, but it won't apply for all cases.
SQL-Server is moving toward ODBMS within RDBMS with the 'typed' (ie xsd defined) and 'untyped' xml field types. XQuery for searching within these and LINQ for binding to OO structures and objects.
I think it is the best way to go.
Any well-written app is going to take advantage of application-level data caching as well. Not everything can benefit from this, however there are some things that rarely change that can take advantage of this.
ASP.NET has some great application-level caching features, and can even watch the database to expire the cache when the records have been updated.
Relational databases will continue to be the mainstay for data storage for a long, long time.
I've been bloodied too many times to get on the "bleeding edge" of new data storage technologies. RDBMS ain't broke, no need to fix it, fix your crappy apps instead!
try working with datasets as large as myspace / facebook and you'll wish the app was coded with something other than SQL.
I promise with that kind of _scale_ all the advantages of databases are gone.
whenever people are talking about how RDBMSes don't scale they talk about that kind of domain obviously, so need to put yourself from that perspective first.
Regarding the wordpress sucky SQL code, come on, we can't expect all OS software to be the works.
florian i get the same performance using mysql with 40 million records and the PBXT engine. average seek time is a 10th of a sec. You are doing unnecessary complexity, now just imagine if you have to add a new column or relationship you are screwed
@Various depreciating anonymous comments
1) I don't need to add columns/relations because song meta-data is in the form of tags with types.
2) You obviously don't understand what it means to do successive data filtering/drilldown including sort and count aggregates on a set of documents associated with a much bigger set of metadata
@Jonathan and the rest
here's the blog post about the subject
From my experience with MySQL, medium-complex joins killed page load times. One example was a "recommends" feature. It checked to see what other users were interested in. The one table had over 100,000 records and it performed poorly during the join. I broke it out to three different SQL queries and it went from 6 seconds to .2 seconds.
Crappy RDBMS programming is not limited to the web. Take a look at ACCPAC's tables and queries on MS SQL Server for a good example. The application is clearly not designed to properly use the database even though it is supported. RDBMS, OLAP, or even application level manipulation will fail with improper coding.
I would gently suggest that people who say that relational databases don't scale well "up to the size of Facebook" are confusing difficulty with possibility. When you are dealing with that level of data, you need someone who is an actual, honest-to-goodness database architect. Not to put too fine a point on it, but relational databases are built around, well, relational math. When the database or queries get *complex* rather than merely *big,* getting the queries right can be a pretty non-trivial exercise. There are certainly data storage cases where an RDBMS is not the right solution, and perhaps Florian's is one of them, but an awful lot of "problems" people have with them come from the fact that a lot of people simply aren't very good at database architecture and design.
Florian, if I read your blog post right, you essentially had a metadata table with a many-to-one relationship with the songs, where each song could have an arbitrary number of metadata entries and the metadata itself could apparently be arbitrary, so filters are a series of TYPE=VALUE, TYPE=VALUE, etc. I suspect were it me, I wouldn't have tried to do that with one query with endless subqueries at all: the first stage of the drill down would generate something like
SELECT * FROM songs WHERE id IN (SELECT song_id FROM metadata WHERE type="artist" AND value="Don Henley");
...and those song IDs were simply saved as restrictions sets for further drill-downs:
SELECT * FROM songs WHERE id IN (SELECT song_id FROM metadata WHERE type="album" AND value="I Can't Stand Still" AND song_id IN (1,3,5));
This is certainly not a perfect solution, but I suspect it'd go a long way toward achieving the same effect with markedly better performance than trying to concatenate subqueries for new set of filters added.
@watts
the query you propose was one of the first things I tried, and the performance of that was worse then successive attempts using having clauses. The set test IN is quite slow.
Hmm. This of course immediately makes me want to try to "solve" this somehow in SQL to see whether I'm right in thinking it can be done acceptably fast, or to see whether I'd come to the same conclusion you did, i.e., that it can't. Fortunately I don't have the time to even seriously contemplate trying. :)
My general observation about many problems with SQL back-ends coming from people not really knowing queries is one I'd stand by, though. I suspect that the increasing prevalence of ORMs is probably going to compound that, because then one's relying on the ORM to always do the optimal thing, and this seems to rarely be a safe assumption.
Just want to point out that's it's a mistake to assume that people like ORM's because SQL is "scary" or something. Many of us ORM fans know SQL quite well, better than many a dba, but applications aren't written in SQL, they're written in a much more capable programming language and that creates a mapping problem.
No amount of joins, indexing, views, stored procedures, triggers, constraints, or functions in SQL addresses the single greatest problem faced by application programmers, which is the tons of code required to map data into objects. Object oriented programming dominates the application landscape and persisting those dominates development time.
ORM's often produce some naive queries but that's OK, every query doesn't need to be optimized, only the ones that become bottlenecks do. Programmers simply don't have the time to hand write or generate and maintain mapping code that uses optimized SQL because frankly, that's just not the priority in most cases.
Development time costs far more than hardware does and clients want the cheapest way out, which means optimizing development time saves far more money than optimizing the runtime efficiency of the code.
Look at Ruby on Rails and ActiveRecord and you'll see a framework that recognizes the *real* problems developers feel they have. A good ORM isn't about optimizing SQL, it's about optimizing programmer productivity at the cost of runtime efficiency *on purpose* because that's what matters to us.
Ramon: I understand -- I'm not criticizing the concept of ORMs, just observing that some are better than others. For example, from my (admittedly brief) time experimenting with Rails, its ActiveRecord implementation seems to be notably smarter than what I've seen in my (increasingly far too long) time with CakePHP.
It's simply that if I wear my SQL bigot hat for a moment, most ORMs don't take full advantage of fairly standard SQL features, and I don't think all of that is due to the impedance mismatch between objects and tables/records. It's possible that in some cases, Active Record isn't the best pattern to be using, either, although Rails has pushed it to an extremely high prominence. (I also have an unproven suspicion that developers who "grew up" using MySQL don't know everything that SQL can do, because for a very long time MySQL didn't know, either.)
I know the defense is that in the majority of cases, the efficiency loss is fractional enough that the development advantages more than make up for it, and I agree. My slightly cranky point was (a) all ORMs are not created equal, and (b) it behooves a developer to know at least basic database architecture. A lot of otherwise pretty good developers don't, and it's my impression that more developers than you might think assume that because they're using Rails or Cake or Django or what have you, that's all being taken care of for them.
Post a Comment