5 ms·
Scalable SQL: How do large-scale sites and applications remain SQL-based?
- grannyg00se 16y agoNote that this article has nothing to do with SQL vs non SQL systems. It's about relational vs non relational databases.
- delinka 16y agoScalable RDBMS is not cheap. Sure, you can use a MySQL or a PostgreSQL server that you didn't need to purchase a license for[1], but the design and implementation for Doing It Right take care, attention and time-- expenses that are not necessarily monetary. If you've built a fully scalable RDBMS-based web application, you won't be posting Show HN: My Weekend Project... Until someone builds a cloud hosting platform with RDBMS that can do the necessary magic bits, that is. [1] Yeah, yeah ... MySQL and their commercial licensing BS. It's been awhile but if memory serves and things haven't changed, you can't host a commercial web site backed my MySQL without A) releasing all your source or B) buying a license. IANAL.
- michaelchisari 16y agothe design and implementation for Doing It Right take care, attention and time Doesn't NoSQL require the same, only it shifts from being the job of a db engineer/sysadmin to that of the developers?
- jarin 16y agoThat's essentially the crux of NoSQL. When your company consists of 90% developers and you're moving at breakneck speed, who wants to wait for Bob the Cranky DB Consultant? Just add another node to the cluster and let the auto-sharding figure it out.
- prodigal_erik 16y agoAuto-sharding is when you have a RDBMS that sees "SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c2" and knows how to choose a plan which minimizes data going back and forth to make that happen. NoSQL instead makes you plow through n^2 records doing every join the hard way in the app, which is about as far from automatic as you can get. The problem is that the people who actually handle this correctly charge so much that I've never even seen their products in live use. Meanwhile free SQL databases are still really bad at it. But choosing an API that deliberately restricts you to only the worst possible query plans is not going to be part of the solution.
- mdasen 16y agoOne can run a commercial website backed by MySQL using the GPL license. The GPL only requires you to release your code if they link off each other and you're distributing the application. Web sites don't usually have their code distributed. Even if you are distributing the code, it's very likely that you don't need to distribute it as GPL, but that's a longer discussion. Suffice it to say, you don't need a commercial MySQL license to run a website. There's no weekend project that's going to be "fully scalable". Scaling isn't just grabbing a NoSQL backend and declaring victory. A lot of scaling is figuring out how you're going to use your data and creating data structures that allow for fast, efficient, and scalable access. Many NoSQL data stores force one to think about how you're going to access the data more than SQL databases since the NoSQL stores usually don't support things like joins. One still has to do a lot of work to design to implement it right.
- deleted 16y ago[deleted]
- jhherren 16y agoMost web sites can grow significantly without having to do data partitioning. Simple replication should be considered as a first option, especially if your read/write ratio is high. Also, you don't have to implement a lot of extra logic in your application; just write to your master and read from the slaves.
- zwischenzug 16y agoDamn straight. And cache appropriately (and dumbly).
- vyrotek 16y agoSQL Azure is working on something called Federations which I think looks awesome. Basically the idea is you can shard your data across multiple DBs based on a column value but your code doesn't have to know what's going on. You still have a single connection string and send one query. SQL Server then figures out which federation has the data you're looking for. Perhaps other DBs have similar systems, I'm not sure. But this is definitely going to change that way we scale our SQL at our company. http://blogs.msdn.com/b/cbiyikoglu/archive/2010/10/30/building-scalable-database-solution-in-sql-azure-introducing-federation-in-sql-azure.aspx http://blogs.msdn.com/b/cbiyikoglu/archive/2010/10/30/buildi...
- riffraff 16y agoi believe dbshards is a similar solution http://www.dbshards.com/dbshards/ http://www.dbshards.com/dbshards/
- icey 16y agoSQL Server Enterprise edition has this feature... but the licensing on it is pretty painful (Around $30k a processor, last time I checked).
- fleitz 16y agoA $30K license is only a problem if you're giving your services away for free. With a quad or 6-core processor (remember MS charges per socket not per core) you can power about 500-750 spindles. You're looking at $200-250K to build a single socket system that can be fully utilized. (eg. 128-256GB of RAM, 3 or 4 controllers, and 500-750 disks) The problem is that people stupidly put SQL EE on a commodity box with 8 GB of ram and 2 spindles and then complain that the software is 5 times as expensive as the box. Also, $30K is the retail price for the license, no one actually pays that, and if you can write a little bit of code you can easily use a SQL Std license for $2-5K.
- rbanffy 16y agoAs much as I dislike Microsoft, you have a point. When compared to serious RDBMS hardware costs, 30K is not much. And you won't go much further on an SQL database than SQL Server or Oracle can take you. If you plan to go further than that, you should have an exit NoSQL strategy ready.
- seiji 16y agoArchitecture of a site you probably never use: 60 mysql servers (30 master/master pairs) ranging from 2 core machines with 8 GB RAM to 8 core machines with 64 GB RAM and RamSan PCI SSDs. Some tables are sharded, others aren't. Some tables are imported into hadoop for hive queries, others aren't. Backups are run using a fragile program called zrm and an army of outsourced DBAs to manage it. The army of outsourced DBAs also deal with replication failures, figuring out why inexplicable deadlocks happen, tuning every inane innodb configurable, and "rebuilding" servers when mysql decides to slow itself down and yearns for a complete dump/reload of all tables. Friends don't let friends use mysql.
- rudiger 16y agoInteresting. I've never seen large-scale database deployments use SSDs (neither for the transaction log, nor for the data); I've only seen battery-backup and write-back caches on hard disk drives used.
- zwischenzug 16y agoNor have I. But it may be the future: cf rethinkdb.
- jpitz 16y agoYou will probably start to. The intel 320 series is getting some attention now [1] with a combination of competitive ( for SSD ) price and enough capacitors on board to flush the write cache on a power failure. You'd likely use SSDs first for indexes, then data. Transaction logs are usually a sequential workload and 7200 rpm drives often suffice. [1] http://archives.postgresql.org/pgsql-performance/2011-03/msg00342.php http://archives.postgresql.org/pgsql-performance/2011-03/msg...
- zwischenzug 16y agoI can't say too much but I work for a company that supplies software to businesses that need to take thousands of transactions a minute on data that's got to be current with real-time and frequent price changes streaming data to 10s of thousands of people and third parties that are themselves big companies. Downtime is not tolerated - I've been surgically torn apart by boards of major listed corporations over small amounts of downtime (it was something of a pleasure to be so professionally abused, strangely). For all this we have found the best solution to use a monolithic DB with SAN-based replication, C libraries to connect to the SQL db, and a process-based custom-built application server with an embedded (unfashionable) scripting language for ease. None of these things are fashionable, but no-one's beaten our performance. The trouble (from my perspective) with all these social sites is that downtime is tolerated (Facebook is always screwing up; I consider them a partial joke technically) and the data is easily distributed (think Google) because the demands on consistency are low. I'm sure our time will be up one day, but you go trendy at your peril. I'm an advocate of functional programming languages, but it's not realistic to get hundreds of these guys to switch seamlessly and reliably to a new paradigm without serious pain. Ditto NoSQL.
- fendale 16y agoCan you say what DB you are running? Is it Oracle?
- tom_b 16y agoNot unless they know some Oracle incantations I've not heard of. I'm going to guess some home-rolled column store similar to KDB. [EDIT] re: Oracle stuff I've not heard of - there is tons of Oracle scale/performance stuff I've never even had to think about. I've worked somewhere with KDB+ in place to handle streaming market price data and interviewed at another investment bank where it was used as well. So my guess is based more on what I've seen and read rather than knowing you couldn't build such a system on top of Oracle.
- zwischenzug 16y agoCustomers pressure us to use Oracle but we resist. I'm sure you can make it fast. Our argument is: why switch from what works; then it's "want to pay for it and be the first to use it"?
- Getahobby 16y agoI have heard multiple Facebook engineering discussions centered around moving things out of mySQL and into mongo, Cassandra and hadoop. I think this article over emphasizes the Facebook and mySQL connection. On an unrelated note, does ACM really still use Cold Fusion? And people like to knock PHP.
- benblack 16y agoOddly similar to my article in GigaOm last year. http://gigaom.com/cloud/nosql-is-for-the-birds/ http://gigaom.com/cloud/nosql-is-for-the-birds/
- yread 16y agoRelax consistency rules and don't use the DB for everything, flat text files are sometimes enough
- mscarborough 16y agoI was surprised to read through this great description about SQL systems at large scale, and not see a single mention of cache. I searched for 'cache' on the page to check I didn't miss something. SQL servers at this scale are so reliant on Memcache and other related solutions that it seems like a large omission.
- parasubvert 16y agoI think a lot of the scalability arguments between SQL and NoSQL has little to do with relational databases as a technology and a lot to do with "how to scale without paying for expensive software licenses". They miss that some of the largest commercial sites use commercial databases. Oracle, for example, to my understanding, is the standard database in use at Amazon.com for their services; there are alternatives for key services, but Oracle is pervasive (at least as of 2009, when I last had insight into this). Another interesting rumour I recently read (ymmv, of course): Apple runs the iTunes Music Store on Oracle's Exadata. Source is from a fairly trusted source in the Oracle DBA community (Daniel Morgan): http://forums.oracle.com/forums/thread.jspa?messageID=4255208&tstart=0 http://forums.oracle.com/forums/thread.jspa?messageID=425520... Clearly there are limitations to a shared-disk RDBMS or even a shared-nothing RDBMS, but the tech sphere somehow seems to be stuck looking only at the open source implementations of these things as the only "reality". For a startup, that's likely true, but for a larger company, there's a very different risk profile when evaluating a SQL vs. a NoSQL solution.