6 ms·

Yeah, 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

by jbooth 16y ago

Yeah, 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 ago
Yep, 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 ago
  Here'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 ago
    There'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 ago
      The 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 ago
        There 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 ago
        You'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 ago
        Yes, 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.
    - jbooth 16y ago
      SSDs? A HDD is limited to well under a thousand reads per second, and there's multiple reads per SQL select, so I'll assume either yes or you're leaving something out. If the tens of thousands of reads are over a 4MB table that stays in memory and the rest of your 10TB are infrequently accessed, congrats, you have a single-node problem. If you actually need to deliver, say, 1,000 queries per second over a 10TB dataset? It's not happening in an RDBMS unless you get a whole bunch of SSDs. Also, RE: websites, of course, substitute your environment's front end if you'd like.
      - ora600 16y ago
        Its year 2010, we've invented RAID a while back. Stripe and mirror everything and you get decent performance without SSD.
        jbooth 16y ago
        Ok, you're up to 3k random reads per second at the absolute peak. Realistically more like 2k for 6x10k disks at RAID 0, and it's awfully hard to even fit 10TB on a RAID1+0 setup. What's that, 10x2TB? Good luck with the latencies on those 2TB disks. Still doesn't add up to 10k select statements per second over a 10TB dataset on a singlenode. Even without writes, that's not happening. I call BS on grandparent post.
        CrLf 16y ago
        That shows a lack of understanding of what kind of hardware is being used in the real world to handle this. With a half-decent SAN with 15k drives and 4Gbit fibrechannel connections, you can get 1000+ IOPS without the storage system even breaking a sweat. Under load it can easily give 10 times that. This is something that's everywhere in the business world. Pair this with a bunch of cores and a few GB of memory, and you can have an RDBMS that chews through impressive amounts of data. Unless, of course, you optimize nothing and swamp it with lame queries that do nothing that table scans. Funny enough, the same people that are fine with doing everything in code are the ones that can't be bothered to think more than one second about what kind of queries they are throwing at the database.
    - fleitz 16y ago
      Excellent response, it's amazing how many people have never seen a SQL server with 200 disks attached to it, and we're not even talking about SANs. A single quad core chip can easily drive 200 disks. All people see is a $200,000 price tag for a single system and ignore the fact that it will outperform 100 $2000 servers. AMD64 can scale so far these days there is little point to NoSQL for all but the highest end of web-scale. I bet if you look into the core of Google there are still mysql systems doing a lot of grunt work.
      - jbooth 16y ago
        Facebook still has MySQL doing a bunch of grunt work, but they're basically treating it like a NoSQL store - sharded key-value storage and that's it. They're moving to HBase now. And, yeah, damn right I'm hesitant about a single piece of hardware that costs $200k and still leaves you with a SPOF. Effectively you're saying it's a 400k piece of hardware after replication. Oh, and Oracle replication software costs you an extra several hundred thousand. So for about a million dollars, yeah, unless it's incredibly transactional in nature, I'd give other options a very serious look.
        fleitz 16y ago
        It costs $5k for SQL Server with replication, you can use SQL Express for the monitor so all you're looking at is the 5K license you'd need for SQL Server Standard, if you need Enterprise it's only 20K. This are all retail prices which no one actually pays.
        jbooth 16y ago
        Ok, and if you have 50GB of data and have made a determination that SQL Server has some features that you're willing to pay 5k for, fine. I still think embedding all your app logic together with your underlying data in PL/SQL or any equivalents is a bad idea but hey that's for anyone to do and find out themselves :)
    - SkyMarshal 16y ago
      >All done with stored procs >The limits of the RDBMS are orders of magnitude greater than the NoSQL crowd think they are. Wish I had more upvotes. Knowing what logic to put in stored procs, and what to put in the application code, so as to play to the strengths of the db and the strengths of the appserver, seems to be a lost art these days.
      - jbooth 16y ago
        Coprocessors are in the upcoming releases of both HBase and Cassandra - they'll be back :) It's not that there's anything fundamentally wrong with the relational model, it's that, as formulated, it's really hard to split up into multiple machines.
  - sp4rki 16y ago
    I 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 ago
      Hey, 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 ago
        If 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 ago
        My 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 ago
      You 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 ago
  Re: 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 ago
But 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 ago
  The 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 ago
  PL/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 ago
    Declaring 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 ago
Why 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 ago
  Well, 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.

by jbooth 16y ago

gaius 16y ago

Yep, 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 ago
Here'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 ago
  There'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 ago
    The 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 ago
      There 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 ago
        You'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 ago
        Yes, 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.
  - jbooth 16y ago
    SSDs? A HDD is limited to well under a thousand reads per second, and there's multiple reads per SQL select, so I'll assume either yes or you're leaving something out. If the tens of thousands of reads are over a 4MB table that stays in memory and the rest of your 10TB are infrequently accessed, congrats, you have a single-node problem. If you actually need to deliver, say, 1,000 queries per second over a 10TB dataset? It's not happening in an RDBMS unless you get a whole bunch of SSDs. Also, RE: websites, of course, substitute your environment's front end if you'd like.
    - ora600 16y ago
      Its year 2010, we've invented RAID a while back. Stripe and mirror everything and you get decent performance without SSD.
      - jbooth 16y ago
        Ok, you're up to 3k random reads per second at the absolute peak. Realistically more like 2k for 6x10k disks at RAID 0, and it's awfully hard to even fit 10TB on a RAID1+0 setup. What's that, 10x2TB? Good luck with the latencies on those 2TB disks. Still doesn't add up to 10k select statements per second over a 10TB dataset on a singlenode. Even without writes, that's not happening. I call BS on grandparent post.
        CrLf 16y ago
        That shows a lack of understanding of what kind of hardware is being used in the real world to handle this. With a half-decent SAN with 15k drives and 4Gbit fibrechannel connections, you can get 1000+ IOPS without the storage system even breaking a sweat. Under load it can easily give 10 times that. This is something that's everywhere in the business world. Pair this with a bunch of cores and a few GB of memory, and you can have an RDBMS that chews through impressive amounts of data. Unless, of course, you optimize nothing and swamp it with lame queries that do nothing that table scans. Funny enough, the same people that are fine with doing everything in code are the ones that can't be bothered to think more than one second about what kind of queries they are throwing at the database.
  - fleitz 16y ago
    Excellent response, it's amazing how many people have never seen a SQL server with 200 disks attached to it, and we're not even talking about SANs. A single quad core chip can easily drive 200 disks. All people see is a $200,000 price tag for a single system and ignore the fact that it will outperform 100 $2000 servers. AMD64 can scale so far these days there is little point to NoSQL for all but the highest end of web-scale. I bet if you look into the core of Google there are still mysql systems doing a lot of grunt work.
    - jbooth 16y ago
      Facebook still has MySQL doing a bunch of grunt work, but they're basically treating it like a NoSQL store - sharded key-value storage and that's it. They're moving to HBase now. And, yeah, damn right I'm hesitant about a single piece of hardware that costs $200k and still leaves you with a SPOF. Effectively you're saying it's a 400k piece of hardware after replication. Oh, and Oracle replication software costs you an extra several hundred thousand. So for about a million dollars, yeah, unless it's incredibly transactional in nature, I'd give other options a very serious look.
      - fleitz 16y ago
        It costs $5k for SQL Server with replication, you can use SQL Express for the monitor so all you're looking at is the 5K license you'd need for SQL Server Standard, if you need Enterprise it's only 20K. This are all retail prices which no one actually pays.
        jbooth 16y ago
        Ok, and if you have 50GB of data and have made a determination that SQL Server has some features that you're willing to pay 5k for, fine. I still think embedding all your app logic together with your underlying data in PL/SQL or any equivalents is a bad idea but hey that's for anyone to do and find out themselves :)
  - SkyMarshal 16y ago
    >All done with stored procs >The limits of the RDBMS are orders of magnitude greater than the NoSQL crowd think they are. Wish I had more upvotes. Knowing what logic to put in stored procs, and what to put in the application code, so as to play to the strengths of the db and the strengths of the appserver, seems to be a lost art these days.
    - jbooth 16y ago
      Coprocessors are in the upcoming releases of both HBase and Cassandra - they'll be back :) It's not that there's anything fundamentally wrong with the relational model, it's that, as formulated, it's really hard to split up into multiple machines.
- sp4rki 16y ago
  I 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 ago
    Hey, 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 ago
      If 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 ago
        My 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 ago
    You 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 ago
Re: 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 ago

But 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 ago
The 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 ago
PL/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 ago
  Declaring 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 ago

Why 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 ago
Well, 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.