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.
12 July 2010
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.
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.
09 July 2010
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.
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.
05 July 2010
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.
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.
27 June 2010
Bill & Ted's Excellent Adventure, Part 2
Last weekend was too busy for computers, but this one hasn't been. 10.04 is up and running, requiring some additional libs in order to run my editor (so I can write these wonderful missives). The X-25 is installed, with DB2 9.7 up next. Life is looking up.
22 June 2010
Come the Revolution
Whenever something evil befell my upper lower class father he would pronounce, "Come the revolution, things'll be different". I can't find the original (Pop wasn't that creative), but today's announcement from Intel will do as an early salvo in the revolution.
SSD has gone truly Mainstream, Best Buy stocks them in stores. Mind, only up to 80 gig, but still. I know, most folk will tend toward using them as just faster spinning rust, just as your grandfather used disc as faster tape; that doesn't mean those of us who can push the envelope won't see them as further justification for BCNF datastores. No more of that "but normal users don't have SSD" baloney. Now, they do. Yee Ha.
SSD has gone truly Mainstream, Best Buy stocks them in stores. Mind, only up to 80 gig, but still. I know, most folk will tend toward using them as just faster spinning rust, just as your grandfather used disc as faster tape; that doesn't mean those of us who can push the envelope won't see them as further justification for BCNF datastores. No more of that "but normal users don't have SSD" baloney. Now, they do. Yee Ha.
15 June 2010
Bill & Ted's Excellent Adventure, Part 1
The Intel SSD has gotten low enough in price, Ubuntu 10.04 is out with SSD friendly file systems in the .32 kernel, and I'll put in the time. I've downloaded DB2 9.7 freebie; the earlier versions have been a pain to install, so I expect this will be no exception. All for you, dear readers.
This weekend will be start of the journey. Ubuntu upgrades are basically a joke; re-build the machine is about the only avenue. I tried the 9.04 live CD, and neither my keyboard (Das Keyboard) or mouse (Logitech laser) ran. From what I can find out, 9.04 used a HAL for such, and didn't work, so they went back to config files with 9.10, which does work but doesn't have the file system goodies kernel. I haven't tried 10.04 from the CD yet, but I'm told All Will Be Well. I sure hope so.
I'll be using Graeme Birchall's "DB2 Cookbook" schema and such for the tests. I highly recommend it, though he seems to have lost interest in DB2; he says on his site that the 9.7 version would be out Dec. 2009, but nada. I had a short email conversation with him a year or so ago. I was wondering whether he found DB2/LUW much in the wild, and he admitted not so much. And he laid the blame at the feet of IBM; they're more interested in moving COBOL/VSAM crap on the mainframe then promoting the LUW version, which is still my favorite database on linux, AIX, or windoze. Neither he nor I could figure out why IBM doesn't give a rat's sphincter. It might just be a coincidence, but a few years ago Gartner stopped publishing the PR version of their database market share report. Going back from at least 2000 until they stopped (2007?), IBM's "growth" was always in the mainframe. I guess IBM got tired of trying to convince folks that DB2 on LUW was going to take over. I still prefer it.
This weekend will be start of the journey. Ubuntu upgrades are basically a joke; re-build the machine is about the only avenue. I tried the 9.04 live CD, and neither my keyboard (Das Keyboard) or mouse (Logitech laser) ran. From what I can find out, 9.04 used a HAL for such, and didn't work, so they went back to config files with 9.10, which does work but doesn't have the file system goodies kernel. I haven't tried 10.04 from the CD yet, but I'm told All Will Be Well. I sure hope so.
I'll be using Graeme Birchall's "DB2 Cookbook" schema and such for the tests. I highly recommend it, though he seems to have lost interest in DB2; he says on his site that the 9.7 version would be out Dec. 2009, but nada. I had a short email conversation with him a year or so ago. I was wondering whether he found DB2/LUW much in the wild, and he admitted not so much. And he laid the blame at the feet of IBM; they're more interested in moving COBOL/VSAM crap on the mainframe then promoting the LUW version, which is still my favorite database on linux, AIX, or windoze. Neither he nor I could figure out why IBM doesn't give a rat's sphincter. It might just be a coincidence, but a few years ago Gartner stopped publishing the PR version of their database market share report. Going back from at least 2000 until they stopped (2007?), IBM's "growth" was always in the mainframe. I guess IBM got tired of trying to convince folks that DB2 on LUW was going to take over. I still prefer it.
Subscribe to:
Posts (Atom)