C3PO's Basket

Tim Bray's blog is one I read for reasons opaque, even to me, and has this recent posting. After all, he's the source of the scourge of mankind, xml. But I visit anyway. In the course of the comments, I made the following:

@Ted Wise:

languages that don't expend too many CPU cycles or chew up too much memory since the first will kill your battery and the second is a limited resource.

Perhaps the answer is Flash with a SSD driver under SQLite (or similar). You move local data to the SQL engine on dense and cheap Flash, saving DRAM for code. This would entail writing RDBMS explicit code in the applications, which may not be to the liking of typical client coders. The 25nm parts are due in a few months.

The post questions which, if any, other language is appropriate to the 'Droid, and Tim Wise questions whether anything other than java (or other C-like compiled) is appropriate. Which assertion led to my comment, the relevant part is above. I had been thinking for sometime that always connected, sort of, devices on a network are semantically identical to the VT-220/RS-232/database/unix systems of my youth. In such a semantic, with appropriately provisioned multi-core/processor/SSD machines, BCNF databases with server-side editing of screens is perfectly, well, appropriate. Back to a future worth living (as opposed to the 3270 old future of the current web).

Wise raises a valuable question: can the Phone be provisioned with enough cpu/DRAM to support any dynamic (interpreter implemented) language using conventional local data? If not, then why not off-load the data to a lightweight (in both cpu and DRAM) database engine? With data stored in SSD/Flash, there is need for little data buffering in the database engine (DRAM), and since this is a single user, although likely multi-tasked, application, concurrency requirements can be ameliorated by segregating each application's data in the "database".

Sounds like a fine idea, to me.

Railing at Rails

I try not to perpetuate the Blogsphere Echo Chamber, by merely linking to other's writings, but sometimes the urge can't be resisted. Today is such a day. I don't know Andrew Dunstan, beyond surfing to his blog when it is linked from the PostgreSQL page. Today is such a day. He takes up Hansson's diatribe against RDBMS (not the first or only time Hansson has printed such idiocy), as I have on more than one occasion. Stupid is as stupid does.

I'll laugh out loud when Hansson finally realizes that the database centric paradigm, aided and abetted by SSD's driven by multi-core/processor machines, puts him in the "legacy, we don't do that anymore except for maintenance" bucket. Client driven applications are the New Dinosaurs(tm), just like the Olde Dinosaurs(tm) -- all that COBOL/VSAM code from the 1960's these young-uns think they're way, way beyond and better than.

Linus was Right (and so am I)

If you've visited more than once or twice, you've noticed that the first quote, chronologically, is from Linus Torvalds. In his 23 July entry, Zsolt talks with Fusion-io about a specific to SSD filesystem access. They call it a Virtual Storage Layer (VSL).

"The thinking behind the VSL is to provide software tools which enable developers to communicate in the new language of directly accessible flash SSD memory in a way which breaks away from the cumbersome restrictions and limitations of 30 year old software models which are layered on legacy hard disk sectors."

While Fusion-io initially named their devices as SSD, they eventually stopped doing so, and explicitly say that their devices are not disk drives.

It begins to look like Linus was right. The main issue now is that no standard exists, and reading between the lines, Fusion-io would be pleased if developers wrote to their protocol. They don't call it lock-in, but a rose is a rose is a rose. We'll see.

Is the Death of COBOL Finally Happening?

Could it be? The wicked witch is dead? We can stroll down the Yellow Brick Road?

Some recent announcements hint that may be happening. COBOL may, finally, be melting. First, here is what Larry has to say. Then we have IBM's z machine announcement. In both cases, the emphasis is on analytics and databases, not COBOL, which has been IBM's bread and butter for decades. IBM bought SPSS recently, I gather because they couldn't get SAS, and the z announcement stresses analytics.

Larry, on the other hand, is doing essentially the same thing: stressing hardware for databases, and java. As I wrote when the Sun deal was in the making, Larry sees the mainframe business as the last piece of fruit on the tree. There is mighty opportunity to rebuild all that stuff out of COBOL into something else; could be java, but it will be real database oriented. Don't forget that Sun and Oracle have been really interested in SSD.

Yummy.

Bill & Ted Return Home to Find BI Corpses

The purpose of this simple test of SSD versus HDD for the basic structure of the BCNF datastore, the inner join, was to show that joins are, effectively, costless. Some object to the word "costless", asserting that joins always have some cost. But all queries have cost, of course. The root question is whether joins cost more or less relative to the flatfile image alternative. For real applications, I believe the answer to be costs less.

There is also the byte bloat aspect. My vision of the not too distant future looks like the BI vendors will be in bad shape. The entire point of BI schemas, and code to process same, was that joins were "too expensive" for operational datastores. The reaction was to extract operational data, explode it, then scan it against odd indexes. Odd from the point of view of operational data, that is. But now that SSDs can handle joins with aplomb, is there much point to all of that effort? You need additional servers, software to do the ETL exercise (even if you're smart and just use SQL), the BI software to create all those not-really-SQL-schemas-but-just-as-arcane (if not more so, see Business Objects) data definitions, and separate "experts" in the specific BI product you've bought into.

What if someone showed you how you can do all that BI reporting from a normalized, compact, schema; just like the normalized, compact, schema you've now got for your OLTP operational application on SSDs? Why spend all that effort to, quite literally, duplicate it all over again? Aside: there is software marketed by storage vendors, and some separately, which does nothing but remove redundant data before writing to storage; I find that one of the funniest things in life. Rube Goldberg would be so proud.

Yesterday I read in an article about the origin, disputed, of the term "disruptive technology". The point of that part of the article was that what gets label "disruptive" mostly isn't, just mostly marketing hype. Well, SSD/multi machines aren't just hype. The retail SSD was invented to simplify laptops, mostly reduce power draw and heat. Thus we have the 2.5" form factor persisting where it makes little sense, the server and power user desktop. Once the laggards in the CTO/CIO corner offices finally get beaten up enough by their worker bees (you do know that leaders, of techies, mostly follow, yes?) to use SSDs to change structure, not merely as faster spinning rust, the change will be rapid. Why? Because the cost advantages of going to BCNF in RDBMS is just so massive, as TCO calculation, that first adopters get most of the gravy.

Remember the 1960's? Well, remember what you read ABOUT the 1960's? There was a gradual shift of wifey from the kitchen to, initially, professional occupation. For those families that had a working wifey early on, they made out like bandits, since the economy overall was structured to a single income household. As time went on, the economy overall shifted (through inflation caused by all those working wifeys) to requiring a two income household to maintain the previous level.

And so it will go with SSD. There is a significant difference, one hopes, between this transition to that from tape to disc. The early disc subsystems were explicitly called Random Access Storage, but COBOL was the lingua franca of the day, and had already accumulated millions, if not billions, of lines of sequential (tape paradigm) processing code and an established development paradigm. So disc ended up being just a more convenient, and a bit faster, tape. Today is different. Today we have the SQL (almost Relational) database which can exploit SSD in a way that the byte bloat flatfile paradigm can't. It's a good time to be young and disruptive. Cool.

Bill & Ted's Excellent Adventure, Part 6

Porting to Postgres will be no big deal. Well, not so far. Here are the changes needed just to create the tables:

CREATE TABLE personnel
(emp INTEGER NOT NULL
,socsec CHAR(11) NOT NULL
,job_ftn CHAR(4) NOT NULL
,dept SMALLINT NOT NULL
,salary DECIMAL(7,2) NOT NULL
,date_bn DATE NOT NULL --WITH DEFAULT
,fst_name VARCHAR(20)
,lst_name VARCHAR(20)
,CONSTRAINT pex1 PRIMARY KEY (emp)
,CONSTRAINT pe01 CHECK (emp > 0)
--,CONSTRAINT pe02 CHECK (LOCATE(' ',socsec) = 0)
--,CONSTRAINT pe03 CHECK (LOCATE('-',socsec,1) = 4)
--,CONSTRAINT pe04 CHECK (LOCATE('-',socsec,5) = 7)
,CONSTRAINT pe05 CHECK (job_ftn <> '')
,CONSTRAINT pe06 CHECK (dept BETWEEN 1 AND 99)
,CONSTRAINT pe07 CHECK (salary BETWEEN 0 AND 99999)
,CONSTRAINT pe08 CHECK (fst_name <> '')
,CONSTRAINT pe09 CHECK (lst_name <> '')
,CONSTRAINT pe10 CHECK (date_bn >= '1900-01-01' ));


CREATE UNIQUE INDEX PEX3 ON PERSONNEL (DEPT, EMP);


Beyond the commented out stuff, Postgres won't accept # in names, so those appended had to go. Had to do the same for Dependents and PersonnelFlat. The Insert routine needed a bunch of changes and ends up not looking at all like the DB2 data, but is populated, which is all I care about at this point (OK, this is a big deal; I wish I'd have sprung for SwisSQL then, and it turns out, there is a review ).


Now for the results:

select * from personnel where 1090000 < emp and emp < 1100000 -- for the join-ed table on SSD
28ms

SELECT p.*
,d.dependent
,d.dep_fst_name
,d.dep_lst_name
FROM personnel AS p
JOIN dependents AS d ON d.emp = p.emp
WHERE 1090000 < p.emp# AND p.emp# < 1100000
60ms

Not quite up to DB2, but not tuned, either. Still rather faster than HDD. Now, where's that Yellow Brick Road?

You're My One and Only

I wonder how the "MySql is all the database you'll (and I'll) ever need" folks are feeling now?

Organic Ribald Machines

These are the worst of times, these are the worst of times. I saw that somewhere the last day or two, but I don't remember where. Anyway, I'm reading these slides from a link on the PostgreSQL site. You *have* to see this. Eviscerates the ORM crowd. As much fun as a barrel of monkeys.

A quote or two (which I've said more than once):

ORMs encourage pathological iteration.

ORMs generally have bizarre transaction models.

ORM-think is one of the driving forces behind "NoSQL" databases.

Bill & Ted's Excellent Adventure, Part 5

In today's episode, our fearless heroes set out to build some larger tables. The Sample database tables, and The Mother Lode, both demonstrate the thesis. But let's create some more complex, and larger, scenarios just to see for ourselves.

We'll start with Birchall's figures 989 and 990, wherein we'll use the magic of CTE to populate our test data, running to 100,000 rather than 10,000 as he does. Aside: I've always wished that CTE could be used to insert both independent and dependent at one go, but alas, not that I can divine. One can buy software (Red Gate, the folks who run simple-talk, are one vendor) which generates test data; if that's not niche I don't know what is.

We start with the base single table:

CREATE TABLE personnel
(emp# INTEGER NOT NULL
,socsec# CHAR(11) NOT NULL
,job_ftn CHAR(4) NOT NULL
,dept SMALLINT NOT NULL
,salary DECIMAL(7,2) NOT NULL
,date_bn DATE NOT NULL WITH DEFAULT
,fst_name VARCHAR(20)
,lst_name VARCHAR(20)
,CONSTRAINT pex1 PRIMARY KEY (emp#)
,CONSTRAINT pe01 CHECK (emp# > 0)
,CONSTRAINT pe02 CHECK (LOCATE(' ',socsec#) = 0)
,CONSTRAINT pe03 CHECK (LOCATE('-',socsec#,1) = 4)
,CONSTRAINT pe04 CHECK (LOCATE('-',socsec#,5) = 7)
,CONSTRAINT pe05 CHECK (job_ftn <> '')
,CONSTRAINT pe06 CHECK (dept BETWEEN 1 AND 99)
,CONSTRAINT pe07 CHECK (salary BETWEEN 0 AND 99999)
,CONSTRAINT pe08 CHECK (fst_name <> '')
,CONSTRAINT pe09 CHECK (lst_name <> '')
,CONSTRAINT pe10 CHECK (date_bn >= '1900-01-01' ));

-- CREATE UNIQUE INDEX PEX2 ON PERSONNEL (SOCSEC#); we'll skip this one from Birchall, in that it generates collisions and we really don't need it for these purposes
CREATE UNIQUE INDEX PEX3 ON PERSONNEL (DEPT, EMP#);


Next, we need both the flatfile version and the joined version of some dependent table, and Dependents is the obvious candidate. We'll use 12 for the number of Kids (we'll ignore Spouses for this exercise; we could just add an Attribute...), they're cheaper at that amount.


Here's, truncated, the PersonnelFlat table:

CREATE TABLE PersonnelFlat
(emp# INTEGER NOT NULL
,socsec# CHAR(11) NOT NULL
,job_ftn CHAR(4) NOT NULL
,dept SMALLINT NOT NULL
,salary DECIMAL(7,2) NOT NULL
,date_bn DATE NOT NULL WITH DEFAULT
,fst_name VARCHAR(20)
,lst_name VARCHAR(20)
,dep1ID VARCHAR(30) NOT NULL
,dep1_fst_name VARCHAR(30)
,dep1_lst_name VARCHAR(30)
...
,dep12ID VARCHAR(30) NOT NULL
,dep12_fst_name VARCHAR(30)
,dep12_lst_name VARCHAR(30)
,CONSTRAINT pexf1 PRIMARY KEY (emp#)
,CONSTRAINT pe01 CHECK (emp# > 0)
,CONSTRAINT pe02 CHECK (LOCATE(' ',socsec#) = 0)
,CONSTRAINT pe03 CHECK (LOCATE('-',socsec#,1) = 4)
,CONSTRAINT pe04 CHECK (LOCATE('-',socsec#,5) = 7)
,CONSTRAINT pe05 CHECK (job_ftn <> '')
,CONSTRAINT pe06 CHECK (dept BETWEEN 1 AND 99)
,CONSTRAINT pe07 CHECK (salary BETWEEN 0 AND 99999)
,CONSTRAINT pe08 CHECK (fst_name <> '')
,CONSTRAINT pe09 CHECK (lst_name <> '')
,CONSTRAINT pe10 CHECK (date_bn >= '1900-01-01' ));

-- CREATE UNIQUE INDEX PEXF2 ON PERSONNELFlat (SOCSEC#); as above
CREATE UNIQUE INDEX PEXF3 ON PERSONNELFlat (DEPT, EMP#);


(For the loads, I had to put the bufferpools back to 1,000, otherwise DB2 locked up for both versions.)
Here's the, truncated, data generator:

INSERT INTO personnelFlat
WITH temp1 (s1,r1,r2,r3,r4) AS
(VALUES (0
,RAND(2)
,RAND()+(RAND()/1E6)
,RAND()* RAND()
,RAND()* RAND()* RAND())
UNION ALL
SELECT s1 + 1
,RAND()
,RAND()+(RAND()/1E6)
,RAND()* RAND()
,RAND()* RAND()* RAND()
FROM temp1
WHERE s1 < 100000)
SELECT 1000000 + s1
,SUBSTR(DIGITS(INT(r2*988+10)),8) || '-' || SUBSTR(DIGITS(INT(r1*88+10)),9) || '-' || TRANSLATE(SUBSTR(DIGITS(s1),7),'9873450126','0123456789')
,CASE
WHEN INT(r4*9) > 7 THEN 'MGR'
WHEN INT(r4*9) > 5 THEN 'SUPR'
WHEN INT(r4*9) > 3 THEN 'PGMR'
WHEN INT(R4*9) > 1 THEN 'SEC'
ELSE 'WKR'
END
,INT(r3*98+1)
,DECIMAL(r4*99999,7,2)
,DATE('1930-01-01') + INT(50-(r4*50)) YEARS
+ INT(r4*11) MONTHS
+ INT(r4*27) DAYS
,CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))|| CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97))
,CHR(INT(r2*26+65))|| TRANSLATE(CHAR(INT(r2*1E7)),'aaeeiibmty','0123456789')

,'A'
,CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))|| CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)) || 'A'
,CHR(INT(r2*26+65))|| TRANSLATE(CHAR(INT(r2*1E7)),'aaeeiibmty','0123456789') || 'A'
...
,'L'
,CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))|| CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)) || 'L'
,CHR(INT(r2*26+65))|| TRANSLATE(CHAR(INT(r2*1E7)),'aaeeiibmty','0123456789') || 'L'

FROM temp1;


Now, we need the table for the dependents, named, Dependents:

CREATE TABLE Dependents
(
emp# INTEGER NOT NULL
,dependent# CHAR(1) NOT NULL
,dep_fst_name VARCHAR(20)
,dep_lst_name VARCHAR(20)
,CONSTRAINT dep1 PRIMARY KEY (emp#, dependent#)
)


To keep the joined and flat file tables in sync, we'll load both the Personnel and Dependents data from PersonnelFlat.

INSERT INTO personnel
SELECT emp#, socsec#, job_ftn ,dept ,salary ,date_bn ,fst_name , lst_name FROM personnelflat;


And, the, truncated, data loader for Dependents (the NOT NULL is there from habit, not really needed here since they all got a dozen kids, but would be when converting some arbitrary flat file):

INSERT INTO dependents (emp#, dependent#, dep_fst_name, dep_lst_name)
SELECT emp#,
dep1ID AS dep,
dep1_fst_name,
dep1_lst_name
FROM personnelFlat
WHERE dep1ID IS NOT NULL
...
UNION ALL
SELECT
emp#,
dep12ID AS dep,
dep12_fst_name,
dep12_lst_name
FROM personnelFlat
WHERE dep12ID IS NOT NULL
ORDER BY emp#, dep;


For grins, here's the timing (same buffering, self-tuning on, and all logging is on a HDD directory) for the Dependents data:
SSD: 21s
HDD: 2m 17s

Now, return to 5 bufferpools, 1 prefetch, self-tuning off. Let's do a simple range select on Personnel.

select * from personnel where 1090000 < emp# and emp# < 1100000 -- for the join-ed table on SSD
19ms

select * from personnelflat where 1090000 < emp# and emp# < 1100000 -- for the flat table on HDD
140ms

Lastly, the joined select:

SELECT p.*
,d.dependent#
,d.dep_fst_name
,d.dep_lst_name
FROM personnel AS p
JOIN dependents AS d ON d.emp# = p.emp#
WHERE 1090000 < p.emp# AND p.emp# < 1100000
25ms -- SSD
257ms -- HDD


While not TPC-C exercising, more evidence that SSD/BCNF datastores are more better. With transactional data that would otherwise span hundreds of gigs in flatfiles, one can have a few gigs in relational. And faster, too.

In this example, I didn't fake up a large independent table to couple to a small dependent table. In the canonical customer/order/orderline example, the size difference between them is significant; at least in the systems I've worked on over the years. This is where much of the byte bloat is saved. Note, too, that since I load a dozen kids across the board, the Dependents table is full; in the Real World, the flatfile would have, depending on how the engine works, allocated space for the dozen as either empty space or NULLs, but the Dependents table would have only allocated for living Kids.

The Mother Lode

I've found, inspired by Part 4 just published, this review/test of SSD's and relational databases. Good news. They work.

And in this test, using higher quality retail parts (STEC, EMC, 3Par, etc. aren't used) versus 15K HDD shows how joins are faster, since they test using a TPC-C variant.

Follow, follow, follow, follow the Yellow Brick Road.

Bill & Ted's Excellent Adventure, Part 4

Getting Postgres up and running was a bit tiresome, but I've now got the figure 675 results for the SSD database. I don't have results for HDD simply because Postgres doesn't have the straightforward capability to specify storage by database the way DB2 does. There are advantages to a true industrial strength database.

with the where: .005
no where: .002

The magnitude difference is in the same ballpark as DB2.

I've got Oracle 10g up and running, and am considering testing that too, although it should work about the same as Postgres, since both are MVCC, as opposed to DB2, a locker.

Coincidentally, Phil Factor of simple-talk fame, has a posting today displaying code generation, DBA variety, for SQL Server. Which brings up, again, the use of code generation with BCNF databases. The earliest I recall seeing in the java world was middlegen (now moribund, alas), while the most current I paid attention to was Firestorm. It will be instructive to see whether those in the code generation world realize that SSD/BCNF storage is their saviour.

I Need to Get Out More

I need to get out more. Josh Berkus, he of PostgreSQL fame, does and has just posted this. I love the title: RunningWithScissorsDB. Yet another example of clueless coders wagging the dog.

Here is the money quote:
...just before pgCon this year I brushed elbows with three different companies who were using PostgreSQL as a cache for Hadoop/HBase (or similar): Mozilla, Factual.com, and one other. They have large, non-relational document stores which they use for data processing, and need a highly relational database like PostgreSQL in order to be able to present data to the user.

He goes on to discuss the notion of transaction-less PostgreSQL, not such a great idea, in my opinion. What's being suggested is, in the end, what MySql was before Innodb: a SQL parser fronting the native file system. Whether such a "database" can be faster than bespoke file I/O written into an application is debatable. I think not. But having a "standard" I/O syntax does simplify coding development. Again, debatable whether it's useful. If the data in question is throwaway, in any sense, then ACID is irrelevant, and you won't build such in your code, either, so using a "database" which doesn't provide such services could be a wise decision. But this isn't database development, just application coding.

Bill & Ted's Excellent Adventure, Part 3

DB2 9.7 is up and running. Woo hoo. The next steps are to build three copies of Birchall's test database (I'll be using the standard DB2 Sample scripts, in that I misremembered what Birchall did; my recollection was that he had expanded the size of some tables, but in fact he did the contrary): one on SSD using his schema (ssdtest1), one on rotating rust using his schema (ssdtest2), and one on rotating rust using a flat file version of the most complex joined tables (ssdtest3, not yet, though).

In order to test the rust/SSD/join aspect (which is the goal, after all of BCNF datastores), the bufferpools will be kept to very small (5 8K pages), thus forcing I/O through storage as much as possible.

I did install DB2 into the SSD, so the engine is running lickety split.

So, for a first approximation, here's the result of a straight select on EMPMDC (10,000 rows):

on ssdtest1: .014 seconds
on ssdtest2: .083 seconds

I'm using RazorSQL, since my beloved AQT will run in wine, but I've not yet been able to get the CLI driver to work again since the upgrade. Razor is almost AQT, just jdbc based, and without many of the DBA focused features that the Extended version of AQT has. I don't get any coin from New Zealand if folks take my advice and adopt AQT, and being that it's windoze (and .NET at that) hosted, the *nix fans in the audience likely couldn't care less.

So, let's run one of Graeme's joins. The code samples are here. This is an html file, so Save As, and just strip the beginning and ending html wrapper stuff. I'll be including the text here, in any case, just to add clarity (well, and make these missives longer and more impressive). Note: you may need to do a search and replace on html entities; gt, lt, and such.

Let's run a complex join (Figure 675):

SELECT eee.empno
,aaa.projno
,aaa.actno
,ppp.photo_format AS format
FROM employee eee
LEFT OUTER JOIN
emp_act aaa
ON eee.empno = aaa.empno
AND aaa.emptime = 1
AND aaa.projno LIKE 'M%1%'
LEFT OUTER JOIN
emp_photo ppp
ON eee.empno = ppp.empno
AND ppp.photo_format LIKE 'b%'
WHERE eee.lastname LIKE '%A%'
AND eee.empno < '000170'
AND eee.empno <> '000030'
ORDER BY eee.empno;

ssdtest1: .016
ssdtest2: .141

I'm getting impressed.


If we remove the join constraints,

SELECT eee.empno
,aaa.projno
,aaa.actno
,ppp.photo_format AS format
FROM employee eee
LEFT OUTER JOIN
emp_act aaa
ON eee.empno = aaa.empno
AND aaa.emptime = 1
AND aaa.projno LIKE 'M%1%'
LEFT OUTER JOIN
emp_photo ppp
ON eee.empno = ppp.empno
--AND ppp.photo_format LIKE 'b%'
--WHERE eee.lastname LIKE '%A%'
-- AND eee.empno < '000170'
-- AND eee.empno <> '000030'
ORDER BY eee.empno


we get:

ssdtest1: .005
ssdtest2: .072

So, for these tests, SSD joins are 10 times as fast, modulo buffering. It will take some time to gin up the flat file schema; that's next on the agenda. So far, no surprises.