Relational Databases and Solid State Memory: An Opportunity Squandered?

The relational model was devised long before computer hardware was able to deliver an RDBMS that could deliver a fully normalized database with no performance deficit. Now, with reliable SSDs falling in price, we can reap the benefits, instead of getting distracted by NOSQL with its doubtful compromise of 'eventual consistency'.

‘But the hour cometh, and now is’John 4:23 Those who cannot remember the past are condemned to repeat it.George Santayana, 1905

I would remind you that extremism in the defense of liberty is no vice! And let me remind you also that moderation in the pursuit of justice is no virtue.Barry Goldwater, 1963 I’m not sure I’ve even got the brains to be President.Barry Goldwater, 1964

I first became aware of Solid-State drives (SSDs) more than a decade ago, when I came across the website for Texas Memory Systems. At that time, SSDs were, by and large, expensive dynamic memory (DRAM) devices used as accelerators, largely for database systems. Flash SSD was used, to a small but noticeable extent, for military standard (MIL-SPEC), and other ruggedized embedded usage.

About five years ago, STEC emerged as the leading boutique vendor of “Enterprise” flash SSD; building everything but the NAND flash memory themselves and using only single-level cells (SLC). These were “Enterprise Serious” parts, not the slower-than-HDD nonsense that was showing up in laptops. STEC were the major supplier to EMC – sole supplier for a period, according to legend. At first, their stock price soared, then didn’t, then soared again. But STEC have recently reported for the quarter and life isn’t soaring. Why might this be, and what effect, if any, might there be on the fit between SSD and relational database systems?

On the whole, I’m afraid the game is over. There is historical precedent, alas. In the 1960s, IBM released Direct Access Storage Device (DASD) disc drives, which were integrated with the System/360 architecture, and for which they defined direct access file methods. COBOL coders, however, ignored the direct aspect of the device, and treated it as faster tape. Sequential file-processing continued on its merry way, not least because there was already nearly a decade’s worth of existing COBOL (and Autocoder and FLOW-MATIC and others) out there that no one wanted to re-build. Codd, on the other hand, realized what random access meant to datastores, and wrote his first paper.

Likewise, some of us saw the hand-in-glove fit of SSD and RDBMS early on when flash SSD was first emerging. On more than one occasion, I wrote to vendors pleading with them to see where the market for flash SSD really was. But, again, coders saw flash SSD as a cheaper way to speed up their ‘Row-by-agonising-row’ (RBAR) code, just as their grandfathers used DASD 40 years earlier. Early on in STEC’s push into the arena, there was much talk of one-for-one replacement of HDD with SSD. Those with more level heads dismissed this. Not only did it fail to happen but, outside of sub-desktops where an SSD may be the sole storage, flash SSD has largely been relegated to Tier 1 storage, a term invented for SSD, so far as I can tell.

This all would make no sense in a rational world. In such a world you’d find that Fifth-normal-form (5NF) catalogs, made feasible by SSD storage, would become the norm, because of nearly cost free joins and perhaps an order of magnitude less data to process: But the SSD vendors haven’t taken this as a selling point for their devices. They seem content to sit on their hands and let the clients tell them what to do. While the rule that “the customer is always right” applies appropriately to restaurants, it surely doesn’t apply to venues which depend on science and technology: Would one, for example, argue with the neurosurgeon over how to remove that pesky tumor? There was a time when vendors and consultants actually brought more experience and knowledge to the table (sigh). But it would appear that the vendors, both the STECs and the EMCs to whom they sell, have consulted their abacuses and decided that the risk of any reduction in sales that might result from intelligent use of SSDs would be more harmful than only selling handfuls of them for Tier 1 intermediate storage.

I think they’re wrong. I think the big market lies in harnessing the fully relational database, and locking in clients to 5NF on SSD. Hard-disk Drive (HDD) would never be able to catch up. But the IBMs and EMCs clearly don’t want to do that. I suppose they see a threat in the reduced data footprint of high-number normal schemas. By analogy: the RDBMS vendors “extend” the ANSI standard with what they assert to be useful features, and then encourage developers to use them. Oracle added the CONNECT BY syntax decades ago to simplify hierarchical structures (the ANSI syntax we have today was concocted by IBM, likely to avoid any reparations to Oracle); Bill-of-Material (BOM) processing has been a significant requirement in commercial software from the beginning of time and BOM data has a (sort of) hierarchical structure. There’s a very good reason for an STEC to “partner” with Microsoft or IBM or Oracle to promote this method of using SSD: it’s different from RBAR processing and better in most cases.

The search-dominated thinkspiel in application development will pass. After all, there’s only so much need for toy applications, such as Facebook and Twitter, which generate infinite bytes; corporate level systems, even with 5NF, are likely to stay at the gigabyte level. For transaction dependent applications, Codd is still right. “Eventual consistency” is a farce in which half the audience don’t understand the jokes.

1421-Fusionio.jpg

I put fingers to keyboard for this missive a few days ago. I’ve just now gotten this possible reprieve from my dour foreshadowing of SSD’s future. While it won’t be cheap, Fusion-io has built an SSD large enough to be the primary store for corporate level systems, one that should handle many OLTP databases whole.

To some extent, more so when I’m depressed, I blame database developers for not having the raw guts to stand up to the hectoring of the NoSql, xml, and all the other RBAR/flatfile zealots. If we abrogate our professional training and experience to praise whichever New Clothes the Emperor is wearing, are we little more than tenant farmers? Instead, we should be exploiting these new technologies to the hilt by creating uncompromising relational databases now that SSD has at last given us the hardware to catch up with Codd’s original relational vision.

References

Codd, E.F. (1970). “A Relational Model of Data for Large Shared Data Banks“. Communications of the ACM 13 (6): 377-387. doi:10.1145/362384.362685

Codd, E.F. (1990). The Relational Model for Database Management (Version 2 ed.). Addison Wesley Publishing Company. ISBN 0-201-14192-2.

Tags: , , , , , , , ,

  • 22247 views

  • Rate
    [Total: 0    Average: 0/5]
  • SAinCA

    Right on!
    Couldn’t agree more, Robert, very well said. IO is still the biggest hurdle to overcome when handling 100GB+ databases. SSD needs to become the normal DB storage medium. I have 80% CPU available if only I could get the data in and out fast enough!

    I hope your message gets heard loud and long by the ears of DBMS and hardware vendors alike. I’d venture to suggest that we (RDB-centric organizations) don’t need more cores and more GHz, we need IO channels and storage devices to be ready to out-perform current CPU and memory.

    IBM’s of the mid-70’s and 80’s (when I started out) had it right – data throughput was phenomenal, easily out-performing SCSI-based solutions. A return to data-throughput being at the same level of importance as CPU, Memory and bus Ghz is long overdue. Come on SSD manufacturers et al, give us all a hand up…

  • artsrc

    SSD’s don’t make a difference and SQL databases are not relational
    We are coming from such different places that it may take some dialog for us to understand each other.

    If frequently accessed data is in memory then an SSD does not make a difference. Access to memory is faster than access to an SSD and writes to the log a sequential not random.

    Today’s SQL technology is inefficient. Storing data that is accessed together, together is more efficient, whether you are using SSD’s, hard disks, or paper cards.

    Both current NoSQL and truly relational database support this. A pure SQL model requires you to take your car apart every time you park it in the garage. It makes no sense and has cost. A truly relational model would let you do the right thing and look after data integrity. A NoSQL solution leaves you to look after this yourself.

    Lastly the cheap deployment technology is distributed, and accessing data in another data center costs more than accessing hard disk, so the new world is worse than the old one.

    The goal is distributed, the relational model can’t be supporting in that world. Yes it is problematic and difficult. Yes we will dig ourselves into some holes. But it is better than the alternative.

    We may need a few beers before we can understand each other, but it might be worthwhile, let me know what you think.

  • Phil Factor

    SQL Databases
    @artsrc As far as I can see, Robert carefully makes no mention at all of SQL Databases, but refers to RDBMS in general. SQL database systems can certainly be used to build relational databases but you can do many other things with them.

    You can bash someone over the head with a frying pan, but you can’t call it cooking.

  • Robert young

    No Mas, No Mas
    OK, so that was Roberto Duran, who was not one half of Duran Duran, for those too young to have seen either.

    @artsrc and @Phil:
    While my argument doesn’t depend on SQL; yes, those are far and away the most prevalent forms of RDBMS. But it doesn’t alter the argument by even a cat’s whisker.

    Rather than extend this with copies, I’ll just use para numbers for artsrc post. So.

    2) This assumes that whole NF relations, for all connected clients, can be stored in memory. In memory databases are also, not too surprisingly, also au courant, although to a lesser extent. Oracle recently added Times-Ten during another of its buying sprees and IBM did so with solidDB. Does Denali qualify? May be.

    3) This is the IMS argument. Rubbish. Such stores support *a single* optimized access path. All others are either difficult, or impossible. Even the canonical example, the org chart, isn’t non-relational; every one I’ve ever been a part of has multiple supervisors for employees. This practice even has a name: matrix management. Moreover, all industrial strength SQL databases support physical structuring of related rows through tablespaces, Which, by the way, is only marginally important with SSD storage, since by definition, the bytes are leveled across all NAND by the controller, and shuffled by the controller even if static.

    4) I don’t see what we need to dialog about. The less the coder has to look after himself, the more he can look after application issues.

    5) Utterly wrong. “The network is the computer” meme, uttered most famously by Scott McNealy (unemployed, is he not?) grew out of scientific workstation local nets. That is a distinction with a difference. SUN (which means Stanford University Network) did networking where one had a physically compact network of high powered (in a number crunching sense) computers doing lots of cpu (largely FP arithmetic) work on tiny datasets. For such workloads, distribution makes sense. What we have today, thanks in no small measure to the FlatEarth… er FlatFile crowd is masses of data shipped between computers which do little more than format it. In a nutshell: if you’ve got lots of compute heavy / data light tasks, then distribution helps. Otherwise, not. One can argue that The Cloud is centralization/consolidation on Speed.

    6) Distributed databases (even heterogeneous ones; Progress, not very relational, did so in 1989) have been supported by Oracle and DB2 for more than a decade. SQL Server, not quite so much. But, on principle, the work needed to manage distributed data is not a function of who does the work, a database engine or application code. The work is the work. The more encapsulated the better, to use popular jargon. The engine does that encapsulation.

  • Robert young

    Right Angles
    Just in case it wasn’t clear, I find artsrc’s comments both prevelant and worth responding to. However, they are not generally relevant to the point of the piece, which is that RDBMS (SQL or otherwise) fit hand-glove (or horse and carriage) with SSD, but that vendors seeking to move more kit, and coders seeking to move more LoC, have been successful in stifling a transition; aided and abetted by wimpy database folk. This perfect fit of hardware tech with software tech is true for any applications deemed worthy of relational structure. For some, that may be none.

  • timothyawiseman@gmail.com

    Some good points, but some points of contention
    I think you have some excellent points, but I do have a few minor points of contention.

    First, I think it unwise to call Facebook or Twitter toys. They certainly can be used that way, but they are also useful communications platforms for the end users. More significantly and relevantly, Facebook is a highly successful and profitable business in its own right that makes extensive use of NoSQL. While I think many NoSQL advocates attempt to take it much too far in their wild claims that various flavors of NoSQL could replace RDBMS’ everywhere, there certainly are numerous places where eventual consistency is sufficient, just as there are certainly many more where immediate consistency and all of ACID is absolutely necessary.

    Also, while I am normally a huge advocate of normalization, I think there are few times where the efforts of going from 3NF to 5NF are worthwhile (though it is often true that the process of normalizing to 3NF will simultaneously place the database in 5NF)

  • Robert young

    Don’t Be A Dupe
    @tim…

    With regard to NoSql, I’ve noted an increasing confluence with the DeDuplication crowd. This site ( http://nosql.mypopescu.com/post/6144030530/paper-a-study-of-practical-deduplication ) is one example. For those who don’t follow, it has this to say:
    “With BigData comes BigStorage costs. One way to store less is simply not to store the same data twice.”

    One might wonder whether the author gets the irony.

  • Anonymous

    hallelujah
    I love you! God loves you! You’re awesome. I enjoyed reading this and I optimistically look forward to a pleasing relationally sound future.

  • artsrc

    Still not convinced
    > 2) This assumes that whole NF relations, for all connected clients, can be stored in memory.

    Many databases I have worked on have fit entirely in memory, or if they have not, the exceptions are very small minority of tables.

    For applications where this is true SSD’s don’t radically reduce the costs of normalization. So they don’t make 5NF catalogs more feasible.

    The same process advances that make SSD’s possible have also decreased the cost of memory. 40 years ago this would not have been true.

    Bottom line my analysis is that for many applications, SSD’s don’t change anything.

    > 3) This is the IMS argument. Rubbish. Such stores support *a single* optimized access path. All others are either difficult, or impossible.

    The relational model does not magically create new access paths. Access paths can be maintained and used by a relational engine or by other mechanisms. Indexes predate the relational model. CouchDB indexing is more powerful than it is in MS SQL server.

    Using or maintaining access paths in an explicit way has advantages and disadvantages.

    > 5) Utterly wrong. “The network is the computer” meme, uttered most famously by Scott McNealy (unemployed, is he not?) grew out of scientific workstation local nets

    Large amounts of storage and CPU is simply more economical in the distributed form than in one big machine. You can run Oracle in a distribute form. However it is in no way designed to run on economical hardware. If you can’t structure your work load to leverage economical hardware, then you are at a cost disadvantage.

    > Moreover, all industrial strength SQL databases support physical structuring of related rows through tablespaces,

    I don’t know any SQL database that can store an order and its related order items in one hit. A hash based key value store can do this, one disc hit per data center. The SQL one needs something like one for the orders table, one of the items table, one for the orders PK, one for the items PK, and one for log. Then the same again on a replica in a remote data center. And the more relational the model, the worse it gets.

    All databases require user access paths to be maintained. Relational models in current SQL databases require model access paths to be maintained also, and this is simply not free.

    > 4) I don’t see what we need to dialog about. The less the coder has to look after himself, the more he can look after application issues.

    > The more encapsulated the better, to use popular jargon. The engine does that encapsulation.

    If API provided to the encapsulated features is sufficiently problematic then it is better not have the encapsulation. Whole problematic technologies (ORM etc.) have been created to try to deal with the real issues of interfacing to SQL databases. For many applications it is simply not worthwhile.

    Lastly on the toy application issue. For most serious, mission critical applications at large companies can and do waste heaps of money on inefficient use of hardware and development time because the return is high and well understood.

    Marginally beneficial and toy applications demand real innovation and careful engineering or the entire value proposition is destroyed. Facebook and would be simply uneconomic if built with traditional thinking. The entire industry exists because of the development of new approaches to computing have been deployed.

  • AlexK

    you are barking the wrong tree
    Robert,

    You are barking the wrong tree here. You are not addressing the following common reasons why NoSQL is sometimes a better tool than RDBMS:

    1. The assumptions relational theory is built on sometimes do not meet real life requirements well enough, such as hierarchies, time series and such. In such cases RDBMS is just not a very good tool, as it does not have good built in support for such cases and as such is slow.

    For example, instead of writing a complex and slow set based query involving time series, we can just read the data to the app server, and write a few simple loops in C#. The result can be hundreds of times faster with little effort, and SSDs would not make any difference at all, they cannot compensate for lack of built in support for time series in RDBMS.

    2. RDBMS are very slow to develop against, and very slow to refactor. As such, they are frequently not profitable to work with when speed of development is essential.

    3. Learning curve is too steep (although if SQL did not have that many inconsistencies and redundancies, it would take less time to master it). Because Agile developers wear many hats, for them it may be feasible to go for a simpler technology.

    Overall, NoSQL grows in popularity because it meets a strong demand – it is simply a better tool for a certain range of problems.

  • Bruno

    not convinced
    Hi Robert,

    I’ve been following your blog for some time, but I don’t think your points are accurate. Your presentation is confusing and all over the place, referencing technology from the 70s that just removes useful shared context with your readers. If you could present your point succinctly and clearly, someone more knowledgeable than me could address your points.

    Applying 5NF to real data won’t reduce size by an order of magnitude. Most DBs are reasonably normalized in practice, or updates wouldn’t work at all.

    Also, SSDs are not that random access. Sequential reads are still much faster than random reads. Random writes are SLOW and getting slower as the block size grows. The solution is to apply smart indexing technology as in http://www.tokutek.com/ or http://www.acunu.com/

    Of course these algorithms improve SSD and HDDs, RDBMS, NoSQL and file systems. The whole real relational thing is no more than No true Scotsman fallacy.

  • Robert young

    As long as the piece (sigh)
    For those of a certain age, this verse resonates: “Why’s everybody always pickin’ on me???” I’ll only deal with the biggest issues. The point of the piece was that NoSql is a lousy replacement for application types implemented in RDBMS, not necessarily the other way round; although a case can be made that it’s true the other way round (as I have written in other venues). The editors offered the blocked comment at the head of the article, as is their wont to do. I don’t disagree, of course.

    – if in-memory databases are one’s norm, all the more reason to have high normal form (minimum footprint and maximum integrity)

    – the RM/SQL does support more than one access path, and is easily extended (if one avoids ‘SELECT * FROM FOO…’), and hierarchical structures don’t. anyone who’s had to use IDREF in xml to simulate relations knows this (and that’s only within one doc)

    – again, industrial strength databases, DB2 and SQL Server among them, support tablespaces with developer defined tables into a tablespace

    – with the CTE structure of industrial strength databases, hierarchies are supported; I’ve been unable to find the cite, but my favorite take on this is: “there are more hierarchies in code than in the real world” (could be Date or CELKO)

    – too slow to develop? once established, a high normal form schema/catalog requires no code at all, DRI takes care of it (modulo user friendly error messages). there’s less code, so coders feel bad; not my problem

    – learning curve? a one semester course in databases is enough; yet coders are always crowing about the Language du Jour which the rest of us just have to learn. why do languages procreate like rabbits on Viagra? can’t seem to do it right the first time?

    – I sure wish I’d worked only on databases Bruno has; those I could build were normal, lots built by coders sure haven’t been

    – SSDs are, by definition, random access at the NAND level; there are no sequential operations in the sense of a r/w head on rust. after any length of time, the controller will have moved bytes all around. in fact, one of their selling points is that better controllers implement physical parallelism and a kind of RAID on the NAND. the numbers one sees in reviews are not apples to apples; the sequential operations are at the byte level, while the random are IOPS, not same-same

    – latest SSDs (consumer, prosumer, and enterprise) are getting closer to same-same read/write speeds (AnandTech has many years of results which show the progression) and the difference even in consumer drives is much smaller than even two years ago (well, if you avoid bad controllers). there is a question, for me, whether the 2Xnm geometry is much of a win.

    – footprint advantage. order of magnitude is 10x. here’s a quote: “a 100 MB storage demand could be reduced to only one MB.” from here: http://searchstorage.techtarget.com/definition/data-deduplication. that’s a particular example, but is just the context of normal form data reduction. not all situations would be 10x going from flatfile to high normal form, but it’s not too difficult. Here’s another site: http://www.purestorage.com/blog/not-your-momma%E2%80%99s-deduplication/ . perhaps not intentionally, it makes the case for the RM, and SSD, from the start, since getting rid of the data in situ is such a pain in the bollocks. an ounce of prevention, and all that.

    I remain among those who’ve concluded that the issue lies not in objective technological considerations, but turf wars (the demand is from coders worried about theirs). Yes, there are situations where, if one is willing to let go of data integrity, flatfiles will be easier for coders, but flatfiles remain inferior in all other aspects; eventually all data is shared (or duplicated) and thus in any horizon other than very short term, the RM/RDBMS wins. In the end, as Huckleberry Finn reminds us: “You pays your money and you takes your choice.”

  • AlexK

    I am not with you on this
    “too slow to develop? once established, a high normal form schema/catalog requires no code at all”

    1. It takes a lot of time to establish.
    2. When requirements change, which you somehow are not considering, it takes a lot of time to change.

    “learning curve? a one semester course in databases is enough”

    Not everyone is as talented as you. Normal people like me cannot write high quality performant T-SQL after one semester, we need several times more time to learn.

    “why do languages procreate like rabbits on Viagra? can’t seem to do it right the first time?”

    This is how our civilization works: we do not do it right the first time, we take risks building an imperfect thing on limited resources and see how it works. Later on we can improve it.

    Vikings’ ships, however superior for their time, were not perfect. Columbus’ caravellas were not perfect either. Many iterations later the steamboat sailed. Should our ancestors want to “do it right the first time”, we would never cross any sea.

    Wright brothers did not attempt to build a Boeing 737 – they built an imperfect but working thing.

    Same thing is happening with computer languages – we keep incrementally improving and innovating.

    Come to think of it, I think we humans are the result of a long evolution, millions and millions of iterations.

    Robert, do you by any chance believe that someone “did us humans right the first time”?

  • artsrc

    Real RAM is not ‘Random Access’ Memory
    Continuing to deny the inefficiency of the physical storage model instead of fixing it, forces a choice between a slower declarative solution, and a faster, low level solution. Both are stuck in a local minimum.

    The inefficient access model will always be less efficient no matter what number you multiply the underlying performance by.

    > – SSDs are, by definition, random access at the NAND level; there are no sequential operations in the sense of a r/w head on rust

    Memory is moved between the Cache and the RAM in blocks. If you read an item one byte after one you have just read it will most likely be in the CPU cache and if you read a random byte it most likely won’t be.

    Similarly data is read and written to SSD’s in blocks.

    Predictable, linear access can be pre-fetched. The more latency there is relative to bandwidth the worse random access is compared to sequential access.

    > – again, industrial strength databases, DB2 and SQL Server among them, support tablespaces with developer defined tables into a tablespace

    You are not getting my meaning. Maybe you need more beers :). Count the IO’s.
    Putting two tables in the same tablespace won’t put the right two rows from those two tables on the same page. The point is person A’s order items may not be optimally stored with person B’s order items, they may be better stored with person A’s order.

    Non-SQL databases can do this with one logical IO.

    select *
    from
    orders o join
    orderitems i on
    o.orderid = i.orderid
    where o.orderid = 1
    go

    Operations MS SQL takes at least two logical reads, one against orders, one against items.

    Table ‘orderitems’. Scan count 1, logical reads 2 …
    Table ‘orders’. Scan count 0, logical reads 2 …

    Non-SQL solutions are not just easier for coders, they offer more possibilities for optimization than relational models on SQL databases.

    > – the RM/SQL does support more than one access path, and is easily extended (if one avoids ‘SELECT * FROM FOO…’), and hierarchical structures don’t. anyone who’s had to use IDREF in xml to simulate relations knows this (and that’s only within one doc)

    Underneath the SQL database is the same machine I use without it. Any trick it does, I can do. Not every non-sql system is a 50 year old limited hierarchical structure.

  • Anonymous

    Oracle Exadata?
    The Oracle EXADATAs come with 5TB of SSD and the software required to leverage it:

    http://www.oracle.com/us/products/database/exadata/overview/index.html

    It seems like exactly the sort of thing that you are lamenting doesn’t exist…?

    That said, it isn’t exactly “commodity” hardware…

  • Ian

    An ISV has to move with the slowest customer…
    You are missing a big point, most software these days are written by independent software venders and sold to many customers. We cannot be sure that all our current customers and “sales leads” are willing to use directly connected SSD (some of our biggest customers are in love with their SANs). We also have a large code base so can’t change our database schemas without a lot of effort.

    So we are just trying to get people to use flush PCI bus cards for their complete database or just temp db.

    In 5 years’ time when everyone is using fast directly connected SSDs, it may be possible to move to Fifth-normal-form (5NF) catalogues, but I think they will still be too slow as the data sizes are growing so fast..

    At present most of our “performance/scale” effort is going into caching using something like memcache maybe backed with flush, partly as SQL Server licencing cost are a limiting factor.

    Also we know the operations are users are likely to be doing the next day, using data that is mostly changed in the overnight batch – each operation has to read lots of data from lots of different tables. So we can precache the data as part of the nightly batch and just have a few reads from memcache…

  • Bruno

    Oracle Table Clusters
    artsrc,

    Some RDBMS do support storing orders and items together as in NOSql stores. Oracle has table clusters:

    http://docs.oracle.com/cd/E11882_01/server.112/e10713/tablecls.htm#i25478

    There’s also http://www.akiban.com/ for MySQL.

  • Alex V

    SSD are obsolete
    I worked in COBOL, PL1 on IBM 360/370(and about 20 something languages) and I remember we used Direct Access Methotd, ISAM with full force. There are databases which take into account that the whole database can be swallowed into the memory. Very soon (I think abput 2-3 years) the architecture of computer has to change radically with emerging of new enery independent memory (can save its state with no power) which will be cheaper 10 times and 10 times faster than any DASD. So far sowtware is not ready for this type of innovation. We will need new OSes, new DBMSes.
    PC can be switched on/off like light bulb.
    SSD are obsolete.

  • IBMJunkman

    DASD
    The S/360 was not the first to have DASD. The 1301 disk drive was for the 1401. And before that the 650 had a drum drive with a whopping 2000 words of storage. I ‘grew’ up on the S/360. This COBOL programmer use ISAM and then later VSAM for random access. I am sure many COBOL apps used sequential files on DASD mainly because the app did not need random access and there was a big speed improvement using DASD. Plus if a sequential file was on DASD you did not have to wait for a tape mount.

  • Robert young

    Re: DASD
    I didn’t mean to imply that DASD for S/360 were the first ever disc drives, only that the 360 family leveraged discs as no other before it. The first operating system for the 360 was DOS, aka Disc Operating System, what folks called Dee-Ohh-Ess. It wasn’t until MS came along that daus entered the world.

    — you did not have to wait for a tape mount.
    or a rewind 🙂

    Note too, that ISAM isn’t really random access; it’s addressed to the “page” level and read sequentially from the base address. VSAM is essentially the same. A few current database engines do much the same internally.

  • Robert young

    Dance Together, Hand in Hand
    — Memory is moved between the Cache and the RAM in blocks.

    Yes, but that block the Application/OS moves won’t be a block the controller writes (necessarily) to the NAND. The controller, some using DRAM caches and others not, assembles the request from the OS/Application and optimizes it in the way the controller vendor decides (and may not be fully documented). Endurance, speed, and data retention are among the desiderata which are traded off amongst themselves (TANSTAAFL). There are (not so wide as earlier) differences in the performance of controllers based on the weight given to each factor and the general competence of the vendor (JMicron comes to mind).

    — Underneath the SQL database is the same machine I use without it. Any trick it does, I can do.

    And, the point of this piece was: *should* one? What’s the best division of brain labour? The logical conclusion of that point of view is that we should all revert to assembler and I/O to raw devices. No one will do that, of course, except for some OS writers.

    But, this reinforces my belief that NoSql/RM/RDBMS/SQL is a turf war, not a tech war, from the NoSql camp, just to be clear. The RM folks are only interested in Doing The Right Thing. To Arms!

    So, where to draw the division of brain labour line? I (and others) offer a more efficient, long term development, place to put that line based on evolving hardware. For the two guys in a garage who want to market a FooBar tomorrow, and nothing else matters, well may be not. If they do get their FooBar made, they’ll have a devil of a time later.

    Spolsky, when he was writing regularly, was forceful in promoting BDUF. Last time I checked, he’s been rather successful. He’s more than two guys in a garage now, but he’s been consistent from the beginning.

  • timothyawiseman@gmail.com

    Normalization
    @artsrc “Many databases I have worked on have fit entirely in memory, …For applications where this is true SSD’s don’t radically reduce the costs of normalization. So they don’t make 5NF catalogs more feasible.”

    If I may, a even if that database can fit entirely in memory, it still not actually get loaded into memory if the server is under memory strain from other requirements. At least from what I have seen, it is rare to both have a database small enough to fit into memory and have that database sitting on a server without other major requirements on it.

    And if the database was loaded entirely in main memory, I would find that a compelling arguement for normalization at least to 3NF since you would have the major advantages of consistency and lack of redundancy without the one major disadvantage of normalization that it can require more joins and thus more physical reads for a query.

  • Anonymous

    title
    Excellent article, and you’ve just won an ally, but the term ‘Solid State Memory’ is misleading. Wouldn’t ‘Solid State Mass Storage’ be more appropriate?

  • artsrc

    We are not there yet
    @Bruno Thanks for finding those. That feature is targeted at “group of tables that share common columns”. I am talking about rows that are accessed together, such as a person and their addresses, or an order and its items. These do not have common columns.

    @Timothy

    Agree completely hat more relational models on SQL databases work better when they are entirely in memory.

    Also agree that most companies are sufficiently bad at buying economical hardware that this can be problematic to achieve in practice.

    @Robert

    > — Memory is moved between the Cache and the RAM in blocks.

    > Yes, but that block the Application/OS moves won’t be a block the controller writes (necessarily) to the NAND

    The point is that keeping data that is accessed as one together is a sane physical model whether you are using paper cards, disc, ssds, or ram.

    The physical model SQL databases gives you for relational models is simply wrong. You can paper over wrong with hardware, and it won’t matter for some applications. You will get the benefits of the relational model with acceptable performance. But you still have the wrong (suboptimal) physical model and you are still paying a performance price.

    >> the RM/SQL does support more than one access path

    > — Underneath the SQL database is the same machine I use without it. Any trick it does, I can do.

    > And, the point of this piece was: *should* one? What’s the best division of brain labour?

    This is about understanding that SQL database can’t make up new physical access paths out of the either.

    Certainly both SQL and non-sql stores can present high level access paths to data.

    > The RM folks are only interested in Doing The Right Thing. To Arms!

    The RM folks don’t exists. The SQL folks do. And they don’t understand that the RM was designed as a logical model and is not a sane physical model. They have been leading us up the garden path for decades. They are the reason that databases can not be in 5NF.

    > Spolsky, when he was writing regularly, was forceful in promoting BDUF. Last time I checked, he’s been rather successful. He’s more than two guys in a garage now, but he’s been consistent from the beginning.

    If you pick the right problem you can use vastly sub optimal technical solutions are still win.

    Stack Overflows “Intel Inside” is the data and the community, not the technology.

  • Robert young

    A Rose By Any Other Name
    — Wouldn’t ‘Solid State Mass Storage’ be more appropriate?

    Well, I missed it when the final galley came by; the text is an odd color on my system. The Editors provided the title as I didn’t come up with a catchy poetic one. SSD where D is Disc. Sorry about that.

  • Robert young

    Let’s Get Physical
    — This is about understanding that SQL database can’t make up new physical access paths out of the either.

    Yes, they (users, developers) can and do all the time. An index is not a prerequisite for access. Part of database/application tuning is to do FK access before creating supporting indices for new data uses (not new data) to determine whether the new use is worth the overhead of additional indexing. If infrequent, and not time sensitive, such FK access will be done with (non-)existing index support.

  • artsrc

    Flat files and Hadoop do table scans pretty well
    >> – This is about understanding that SQL database can’t make up new physical access paths out of the either.

    > Yes, they (users, developers) can and do all the time. An index is not a prerequisite for access. Part of database/application tuning is to do FK access before creating supporting indices for new data uses (not new data) to determine whether the new use is worth the overhead of additional indexing. If infrequent, and not time sensitive, such FK access will be done with (non-)existing index support.

    SQL Engines did not invent table scans, they just made accidental ones popular.

  • Anonymous

    Wrong physical model
    “The physical model SQL databases gives you for relational models is simply wrong.”

    It is only wrong if you don’t built upon it correctly. The physical model SQL gives you is the highest in flexibility for ad-hoc queries. All the major SQL providers have indexed views or some other caching mechanism which allows you to store your data properly for your primary use cases when ad-hoc approaches are not sufficient.

    Additionally, financial companies want all the letters in ACID. “Eventual consistency” is just another word for “timing attack” when money is on the line.

    For these reasons, I find NoSQL to be an early optimization. If I want to bag my data, I will use some higher level mechanism than my relational model… that can be indexed views, cache tables updated via triggers or a caching layer.

    The reason is simple: I get to retain my ad-hoc capabilities and I get the performance on the read side via the higher level abstractions. This becomes critical when many systems must communicate with one database, with different use-cases for each.

  • Bruno

    Table Clusters
    @artsrc

    An order and its items share the orderID column. Table Clusters eliminate seeks from master/detail joins.

  • AlexK

    Do you consider Google a toy system?
    You are dismissing “toy applications, such as Facebook and Twitter”. Do you consider Google a toy system too?

  • artsrc

    Is NoSql premature optimization?
    The tool many people use to fix the physical model is a big blob of database opaque XML.

    > It is only wrong if you don’t built upon it correctly. The physical model SQL gives you is the highest in flexibility for ad-hoc queries.

    A physical model should not change the set of logical queries you can execute. If you are saying the SQL physical model it is a good compromise in practice, then I disagree.

    > All the major SQL providers have indexed views or some other caching mechanism which allows you to store your data properly for your primary use cases when ad-hoc approaches are not sufficient.

    These can be useful. They do not approach the simplicity and effectiveness of the NoSQL options. For a start this does not help your writes.

    > Additionally, financial companies want all the letters in ACID. “Eventual consistency” is just another word for “timing attack” when money is on the line.

    The last fund manager I worked for had a core architecture of a master write SQL database and a collection of read replica’s. We tracked how far behind the read replica’s were. Applications interacted with both and were exposed the inconsistencies.

    > For these reasons, I find NoSQL to be an early optimization.

    SQL databases are not very good at supporting data center failure without loss of data (synchronous replication) in an way that interacts well with reasonable performance.

    We would need a longer discussion about the system you are planning and its requirements before thinking about a technology migration path.

  • Robert young

    Google? Not so much
    The question is not generally germane to NF and SSD interaction, but since you asked, I’ll have some fun …

    Pretty much. Look closely at Google, and all you’ve got left is a massive advert agency. Adverts are important to life? Advert engines advance civilization? Of course not. Rather than making things for ourselves, we make insubstantial playthings. Psychologists call this behaviour cognitive dissonance.

    For a more formal, and literate, take on the issue, read up Nick Carr. He also has an infrequently posted blog.

    From a purely systems point of view, Google is a re-hash of code/file systems from the 1960’s. Size isn’t distinguishing. Read the Wikipedia write up on Map/Reduce.

    Wikipedia is a more important piece of software.

  • tonyrogerson

    Vendors profits, GPUs
    Hardware vendors really don’t want folk using SSD’s, a single SSD can replace half a dozen or more 15Krpm disks on a fairly random work load in BI, they make money through storage arrays, disk controllers, NV cache, SAN’s etc. A lot of that disappears if you just shift paradigm a moment and go back to DASD in the guise of PCi based flash and for redundancy use existing and proven replication software.

    My masters dissertation (http://www.reportingbrick.com) shows how just a small amount of inexpensive and readily available commodity flash and game machine spec processor, memory and motherboard can deliver extremely high performance – I managed over 530K rows per second (a row average of 241 bytes) from flat file into SQL Server on a 6 core commodity machine that cost less than £2K to make.

    The real gap that isn’t readily that I can see being looked at and also not mentioned in your article is how GPU can work in synergy with flash storage, you couple the massive parallel processing performance of GPUs with the low latency random access times of flash to perform table joins in true parallelism instead of the data streaming across cores that occurs at the moment.

    The other thing not mentioned here is the impact that column compression and that method of “implementation” of the relational model is having on query performance.

    Good food for thought though; personally we are up against a marketing wall in terms of ridding the world of 15Krpm disks and SAN’s – hardware vendors don’t want to take such a hit on profits; look at the stock price of OCZ and FusionIO – you can see where the future is, where people are investing – it will happen but we are at least 5 years away from that reality in my honest opinion.

    Lastly but most contensiously, the more popular RDBMS and I use the “R” loosely are all bringing in implementations of a NoSQL flavour. In our world we are limited by ACI[D] which makes implementing distributed databases in the real world difficult, we have to use partitioning etc. NoSQL don’t – if you look at VoltDB that is where I see we “should” be at – legacy stuff is just stuck on an old coding model, old designs going back decades – its interesting times at the moment.

    All the best, Tony Rogerson, SQL Server MVP
    @tonyrogerson

  • Robert young

    You Found The Missing Link
    — … also not mentioned in your article is how GPU can work in synergy with flash storage, you couple the massive parallel processing performance of GPUs with the low latency random access times of flash to perform table joins in true parallelism instead of the data streaming across cores that occurs at the moment.

    I haven’t seen the GPU based machine yet make it into “mainstream” computing (AMD and Nvidia have put a toe in), that’s why it didn’t make it to the piece. What I’ve seen agrees with your experience. But you’re quite right, so far as I’m concerned.

    So far as distributed/horizontal arguments go, I remain unconvinced. Being highly promiscuous with regard to databases, I’ve used ones that distribute without a whole lot of fuss. In the end, if you want transaction integrity, you (the coder) have to re-invent a TPM for your files, distributed or not. That’s what RDBMS engines are for.

    As to Big Data; search is sui generis, and not transaction constrained. What works for search is not predictive of what works for “real” applications.

    Since the first draft, Fusion-io has done a STEC in the current quarter, partly due to not so much uptake as expected. May be they should consider a Killer App?