8 ms·
Welcome to MySQL in 1995. You'll be wanting to "manage" transactions next...
by gaius 16y ago
Welcome to MySQL in 1995. You'll be wanting to "manage" transactions next...
- jbooth 16y agoYeah, I do actually, because I want a storage system to be a storage system and an application layer to be an application layer. I've seen corporate code bases consisting of entirely PL/SQL before, it's a nightmare. No thanks, from both a maintenance perspective and a performance perspective. YMMV.
- gaius 16y agoYep, nothing says performance and reliability like doing everything in the app. Here's a neat trick - for every query SELECT * FROM TABLE, then filter and sort it in the application too.
- jbooth 16y agoHere's a better trick - treat a database as an indexed storage layer that's doing the minimum of CPU work or complicated transactional conditions that create lots of blocking and bottleneck conditions for threads on your monolithic back end. And then implement the app logic in some slow-ass web language that doesn't hold a candle to your database's optimized C. What happens when your web server can't keep up? Buy another web server. That's an easily solvable problem. A saturated DB, on the other hand, is very hard to fix, especially if all of your application logic is running in it and would require a rewrite to scale.
- gaius 16y agoThere's an awful lot more to computing than websites. FWIW I work on a "non-scalable" RDBMS that comfortably handles thousands of commits/sec, tens of thousands of selects/sec and tens of terabytes of data. All done with stored procs, one database, no need for "sharding" either. Will it scale indefinitely? No, but neither will anything else. The limits of the RDBMS are orders of magnitude greater than the NoSQL crowd think they are.
- mmt 16y agoThe limits of the RDBMS are orders of magnitude greater than the NoSQL crowd think they are. Agreed. There's a couple of orders of magnitude just from I/O improvement in hardware, using conventional[1] hardware, configured intelligently. There's at least another order of magnitude in optimizations that aren't possible in NoSQL's strawman[2], such as separate tablespaces for data and index, partial indexes, and a galaxy of query tuning from an EXPLAIN that actuall provides a query plan. [1] Meaning commodity-priced, nothing fancier than $400 RAID cards and spinning disks. [2] MySQL
- jbooth 16y agoThere is nothing approaching even a single order of magnitude in improvement in random I/O with conventional HDDs over the last, say, 20 years. Not even a 2X improvement. You're under 500 IOPS per HDD, period, use them wisely. Moore's law doesn't apply to RPMs of spinning disks.
- mmt 16y agoYou're under 500 IOPS per HDD, period This is only significant if one is limited to a trivial number of spinning disks. 20 years ago, with separate disk controllers, this was the case. If you run some benchmarks, I expect you'll find that, for random I/O, N disks perform better than N times one of those disks. SCSI provided (arguably) an order of magnitude for number of disks per system. Now, SAS provides another. $8k will buy 100 disks (and enclosures, expanders, etc). How many IOPS is that? ETA: The Fujitsu Eagle (my archetype of 20ish years ago disk technology) had, IIRC, an average access time of 28ms. If its sequential transfer rate was one 60-100th of modern disks, what fraction of a modern disk's 4k IOPS could it do?
- jbooth 16y agoYes, I agree that the solution is to throw more spindles at the problem. PL/SQL, though, with global data reach and advanced locking states for every single transaction, make it really hard to move off of a single host. So it's more and more work to get more disks attached to that host, and CPU is a hard upper limit.
- sp4rki 16y agoI don't get why you were downvoted, as this is so true. I'd rather throw hardware at a problem, than having to get developers to work on a rewrite on the database level. It's cheaper, easier, and totally not risky in comparison. I've always seen databases as an index storage layer and thought that "outsourcing" the apps job to the database while convenient in a lot of cases, are hell to maintain at scale.
- jbooth 16y agoHey, you got downvoted too. Gave you a bump. Dunno why everyone's taking this so personally. It's like they're personally threatened and their approach is to get angry rather than try and understand.
- gaius 16y agoIf I had to guess, I'd say that people are downvoting you because even the MySQL guys who spent the 90s arguing against it have now come around to thinking that foreign keys and transactions and stored procs are good things, and they can't be bothered to engage in the discussion again.
- jbooth 16y agoMy beef was PL/SQL and too much of the transactions/storedprocs thing. Foreign keys can be a good thing.
- sp4rki 16y ago^^ I don't get it either. If someone doesn't agree they might as well say so and give reasons to get a discussion going. Instead we get this "Ohh the article said X so you're wrong, I'll downvote and move along" attitude. I guess people don't appreciate debating anymore.
- ora600 16y agoYou are paying developers to rewrite features that already exist in the database and you think this is "throwing hardware at the problem"?
- deleted 16y ago[deleted]
- nailer 16y agoRe: performance and reliability. Mongo has some pretty good performance characteristics, according to people that have very, very large datasets being updated at a massive rate. As for reliability, I personally don't really see to much difference in the reliability of Python logic vs SQL logic. It's like you're comparing the reliability of English vs Spanish. "> Here's a neat trick - for every query SELECT * FROM TABLE, then filter and sort it in the application too." Huh? Mongo (my personal database of choice) doesn't do that. You specify what you want when you run find(). No other database does either. Again, if you have a point, make it. The last three posts you're written have been shoving words in to other peoples mouths, sarcasm and statements without any actual backup or citations.
- brunoc 16y agoBut isn't the nightmare a human, collaboration problem rather than a technical one? Assuming that the purpose of these stored procedures is to ensure data integrity at a lower level of abstraction and that you have appropriate staff to maintain that layer, I don't see where the nightmare part comes in. In my experience, and perhaps with my bias as someone who's very comfortable with (relational) databases, I have seen plenty of nightmarish scenarios involving, say, Hibernate.
- jbooth 16y agoThe maintenance issue is tractable, I'm just saying that PL/SQL doesn't make it easier and probably makes it worse by being hard to unit test, refactor, etc (although this is very subjective and depends on the strengths of your team). The scalability problem? Good luck getting 90%+ of your processing out of the database. Rewrite from scratch?
- benmccann 16y agoPL/SQL is an awful language to work with. What's up with having to declare variables before using them? Does it not even have multi-pass compilation?! And it works only with Oracle, which causes lock-in I'd rather avoid. I'd rather create a service layer on top of my DB and access it through that. Then I have more control over logging, managing access, pubsub, etc.
- konad 16y agoDeclaring variables isn't hard. However, in Postgresql one can use many languages to write stored procs. I prefer plpgsl but TCL, Python and Perl are also available in the stock distribution.
- jbooth 16y ago"isn't hard"? Yeah, writing it in Algol isn't "hard" either, but why? If something's harder to use and less performant, why would it even be on my list of things to maybe consider?
- rxin 16y agoWhy don't you get rid of the fault-tolerance part in a filesystem system (remove recovery, remove concurrency control). You are not too far away from interacting directly with harddrives' firmwares. SQL (and RDBMS) is good for three things: - The relational model can model the nature of our data well. - Transaction control, recovery, etc are notoriously hard to implement. Most people (I bet 99.99% of the programmers) would fail to design these systems. RDBMS takes care of these for you. - Physical data independence: The declarative nature of SQL enables you to specify what data you want, rather than how you want them. The optimizer picks a logical and physical execution plan (that doesn't suck) for you. The same query from the 70s running on IBM mainframes can still be running today on say 1000 Amazon EC2 nodes, although the underlying computer and storage architecture is poles apart. (There are political reasons why this is not as true as it should be, but that's another story.) Now of course, the features are double-edged swords. Some engineers dislike RDBMSes because they don't understand what is going on in the system. If you are Google, you have the engineering power and the business needs to design systems that precisely tailor to the nature of your data.
- jbooth 16y agoWell, there's 2 questions here (posting again to give whatever horribly lacking-in-perspective fanboys are out there another chance to downmod a discussion of frickin storage layers): 1) Are RDBMS useful at all, ever? Sure. If I don't have an extreme scaling need, I'm the first one to say throw MySQL on that box and call it a day, it's tested, it works, gives you a bunch of functionality for 5 minutes of installation, developers are used to it, easy. 2) Should you be putting application logic in the database? I argue no. I understand that this isn't unanimous, but you're sacrificing long term flexibility and scalability for some short-term convienence that isn't even that convenient. I mean, PL/SQL sucks. I'm not saying you shouldn't ever do a join in the database, or shouldn't ever normalize your data, but if you're writing a lot of PL/SQL code, it's probably gonna come back to bite you if you ever need to scale or change architectures. RE: that query from the 70s -- that query from the 70s that will actually run across different implementations contains exactly the subset of SQL that I consider acceptable and worth using (minus limit and a couple other small fry, I guess). If it's more complicated than that, it should be app code IMO.
- nailer 16y agoI never said I required, or tried to implement transactions. In fact I can tell you know I'm pretty sure a CMS and push based communications app are running quite fine without transactions. What I do have is: * One language which handles both app logic and relationships, as mentioned above. * A fidelity of types between storage and application (I'm using Mongo, so documents are stored as documents, not fields and rows) * Apparently, some speed benefits should I need it in future, at the expense of losing the aforementioned features I don't need. Please don't put words into my mouth: * It's not nice. * It's against the HN guidelines * If your argument was of substance you wouldn't need to do it. It saddens me this kind of nastiness gets upvotes.