Oracle Promises 100x Faster DB Queries With New In-Memory Option

Hugh Pickens DOT Com writes "ZDNet reports that Oracle's Larry Elison kicked off Oracle OpenWorld 2013 promising a 100x speed-up querying OTLP database or data warehouse batches by means of a 'dual format' for both row and column in-memory formats for the same data and table. Using Oracle's 'dual-format in-memory database' option, every transaction is recorded in row format simultaneously with writing the same data into a columnar database. 'This is pure in-memory columnar technology,' said Ellison, explaining that means no logging and very little overhead on data changes while the CPU core scans local in-memory columns. Ellison followed up with the introduction of Oracle's new M6-32 'Big Memory Machine,' touted to be the fastest in-memory machine in the world, hosting 32 terabytes of DRAM memory and up to 384 processor cores with 8-threads per core."

Comments

Oracle gains speed

[rossdee]

Especially upwind, but not 100x

still Emirates Team NZ only need to win one more race..to take back the Americas cup

Re:Oracle gains speed

[Chrisq]

At first glance, one would think "Oracle" is a company devoted to catering high end golf outings and boat racing.

They make software, right?

Only as a means of raising the money for high end golf outings and boat racing.

Re:Oracle gains speed

[Joce640k]

Anybody know how much this costs?

(It's Monday morning and I need a good laugh...)

Re:Oracle gains speed

[pinkstuff]

An estimated $100m USD. The winner sets the rules for the next one, so if New Zealand wins they will lower the cost, allowing more teams to able to compete.

Re:Oracle gains speed

[ae1294]

At first glance, one would think "Oracle" is a company devoted to catering high end golf outings and boat racing.

They make software, right?

Only as a means of raising the money for high end golf outings and boat racing.

With blackjack and hookers....

Re:Oracle gains speed

[ae1294]

You only live once but well I hear if you goto hell you program in Fortran all day everyday, forever... They phased out the COBOL because it was much too evil.

Re:Oracle gains speed

[ooshna]

In fact forget the boat racing and the blackjack.

Re:Oracle gains speed

[ae1294]

no no no.. I mean no disrespect, please please do not send me to the data mines... I have a wife with child!

Beowulf

[shri]

" hosting 32 terabytes of DRAM memory and up to 384 processor cores with 8-threads per core. "

Let me be the first to point out the Beowulf possibilities with a few hundred of these clustered together. :)

Re:Beowulf

[sjwt]

Finally a cluster that can Slashdot slashdot.org

Re:Beowulf

[Anonymous Coward]

But does it run on Solaris?

Sadly, no. In fact it won't even function as a Minecraft server without some patches and a Java update. I hear that Oracle is still waiting on the vendor for Java update.

Re:Beowulf

[ae1294]

Finally a cluster that can Slashdot slashdot.org

Not running oracle it can't!
{I never worked with it. They seemed to much like a drug dealer when they came around, they had a building here in SE-VA in Chesapeake}

Great news for NSA

[Anonymous Coward]

With increasing surveillance on American citizens such database will provide security forces with instant profile of each person. Let's combine that with license plate scanning, cell phone tracking, sexual preferences and health records.

Now we can sleep well at night, our children are safe.

One Big Memory Machination

[VortexCortex]

"Big Memory Machine"... So, they finally built Deepthought?

In-memory IO is grand, when that's your're bottleneck. Mine tends to be in the network level, so I use a local daemon for query result caching at the application level as "in-memory" speedup. The speedups are nice, but pricey. Color me unimpressed -- that's pink, BTW; I'm a Caucasoid your colors may vary, but only up to VARCHAR(20);

Uhg. Is "in memory" now just another buz-word? I guess we've come full circle back to Mainframe? Big memory banks are faster and better for a while, but then the bandwidth goes up and the price, reliability and scalability will favor distributed systems (as currently). I wonder which phase of the cycle quantum computing will favor: distributed / localized? You have to take into consideration your user distribution too...

So, eventually you'll want a hybrid system where the memory is distributed and cloned at each query-able interface, but still maintaining the entire dataset "in memory"...
SELECT * FROM earth WHERE answer LIKE "everything";
...
42 rows returned

Re:One Big Memory Machination

[Anonymous Coward]

If you used ECC memory, the answer wouldn't have been 42.

Re:One Big Memory Machination

[doomsayerxero]

That should be Varchar2(20).

Again

[hebertrich]

As long as they keep with the extorsion techniques Oracle is famous for they can keep their hardware.

This merely allows poor code to suck less.

[pla]

First, let me say that I would love to have a table option to keep a particularly heavily-hit table always in memory.

This ain't it.

From TFA, "Maintaining those indexes is expensive and slows down transaction processing. Let's get rid of them," Ellison remarked. "Let's throw all of those analytic indexes away and replace the indexes with in-memory column sort."

This merely minimizes the penalties of poor indexing and RBAR by making complete table scans on arbitrary columns faster. Apparently Mr. Ellison has forgotten his algoithmics and combinatorics - Oh, wait, no he didn't, he dropped out as a sophmore. Pity, because had he stayed, he would have learned that even with a 1000x slower storage medium, an O(log N) algorithm (index seek) will eventually beat an O(N log N) algorithm (column sort).

Thanks, Larry, but you want to make Oracle faster? Remove cursors from the core language, and although that alone won't "fix" it, you'll see all the hacks who can't think in set-based logic drop out overnight.

Re:This merely allows poor code to suck less.

[homb]

From TFA, "Maintaining those indexes is expensive and slows down transaction processing. Let's get rid of them," Ellison remarked. "Let's throw all of those analytic indexes away and replace the indexes with in-memory column sort."

This merely minimizes the penalties of poor indexing and RBAR by making complete table scans on arbitrary columns faster. Apparently Mr. Ellison has forgotten his algoithmics and combinatorics - Oh, wait, no he didn't, he dropped out as a sophmore. Pity, because had he stayed, he would have learned that even with a 1000x slower storage medium, an O(log N) algorithm (index seek) will eventually beat an O(N log N) algorithm (column sort).

I think you misunderstand the way columnar databases work. They are not doing a column sort the way you think. The column itself is an index.
Of course the inanities coming out of Ellison's mouth don't help explain things correctly. No Larry, you don't do away with indexes. You mostly store indexes on everything, automatically.

Thanks, Larry, but you want to make Oracle faster? Remove cursors from the core language, and although that alone won't "fix" it, you'll see all the hacks who can't think in set-based logic drop out overnight.

Can't argue there!

Re:This merely allows poor code to suck less.

[oranGoo]

From TFA, "Maintaining those indexes is expensive and slows down transaction processing. Let's get rid of them," Ellison remarked. "Let's throw all of those analytic indexes away and replace the indexes with in-memory column sort." This merely minimizes the penalties of poor indexing and RBAR by making complete table scans on arbitrary columns faster. Apparently Mr. Ellison has forgotten his algoithmics and combinatorics - Oh, wait, no he didn't, he dropped out as a sophmore. Pity, because had he stayed, he would have learned that even with a 1000x slower storage medium, an O(log N) algorithm (index seek) will eventually beat an O(N log N) algorithm (column sort).

RTA - the improvement is there specifically for real time analytic workloads. In these kind of workflows the optimal algorithm is O(n) in general case and indexes are useless (query optimizing engine will always choose scans as you need to visit a lot of data). You might know a thing or two about algorithms, but you should brush up on problem analysis 101.

Other mistakes in logic: Index seek and column sort are not different algorithms for the same task so comparing them brings little insight (without considering some other details of the query optimizer). This leads you to nonsensical claim that O(log N) will eventually beat or be equal to O(N log N). It is not eventually, the first will be always faster or equal.

Re:This merely allows poor code to suck less.

[Anonymous Coward]

This leads you to nonsensical claim that O(log N) will eventually beat or be equal to O(N log N). It is not eventually, the first will be always faster or equal.

Er... no, not if you think about constant terms. Consider 8 * (log N) + 3 > 2 * ( N log N) for N=4 for example. Big O notation is about the asymptotic performance for large N. It's quite common that the most efficient algorithms for large data sets may provide no benefit for trivial cases and may in fact be slower.

Re:This merely allows poor code to suck less.

[pla]

Okay, you'll have to 'splain it too me as well then, because I don't see the joke (and only refrained from posting substantially the same response because an AC beat me to it).

Memory runs roughly 1000x faster than disk (it can get down to around 50x on an array of SSDs, but up to 100,000x for random seeks across physical platters). Holding all else equal, 1000*O(log N) will take longer than 1*O(N log N) until N=1000, despite the lower time complexity. Additionally, the AC made a good point about the relative constant factor of the algorithms themselves, in that a binary search of a sorted list has virtually no overhead, while a good general purpose sorting algorithm does.


And all that said, yes, I see now that I made an error because this change applies to columnar rather than row-oriented data; O(log N) will still eventually beat O(N), however.

Oracle-friendly site(s)

[SigmundFloyd]

14 hours ago, itnews.com.au runs a story (promptly picked up by /.) about how the social networks are staying with MySQL. In the article, it is suggested that the switch to MariaDB by some Linux distros is a "political move", and that Google's switch might be a retaliation against an unrelated lawsuit from Oracle. Also, it's mentioned (twice, with the same wording) that the Mozilla foundation is "upgrading from MariaDB to MySQL 5.6" (emphasis added).

7 hours ago, itnews.com.au runs a story (promptly picked up by /.) about how Oracle's 12C database will be 100x faster, despite the fact that we only have Oracle's CEO word for it.

Now that's what I call an Oracle-friendly site (or two?)

Re:Oracle-friendly site(s)

[rtaylor]

MongoDB makes a great caching layer.

Write your data to Pg into a permanent data-store. Use a trigger to push the data into MongoDB (foreign data wrapper).

Enjoy the query benefits of Mongo and the reliability of Pg with the caveat that you need to occasionally rebuild your cache (MongoDB copy).

Re:Oracle-friendly site(s)

[bluefoxlucid]

No, I mean MongoDB will take a 3 database cluster and let you "Replica Acknowledge" a transaction with "Majority" count. Once it hits 50%+1 servers, it's 100% guaranteed solid unless you lose both servers. If both servers suffer a power drop at that point, the last server refuses to accept writes; when those servers come back, they will replay their oplog back to the last server to synchronize it. There's one flaw here: there's no "Replica Journal Acknowledge", so it's theoretically possible to lose that transaction anyway; both servers have to suffer a system failure (power drop, kernel panic) within 100mS of receiving the operation, since they write out their data to disk every 100mS. In practice this is extremely unlikely.

That means once you've sent it and gotten back that it's written, it is written. You'd have to lose both (or more--3 servers in a cluster of 5, etc.) servers' power or hard drives (corruption, failure) before the data is propagated further.

By contrast, Percona and MariaDB have XtraDB. XtraDB does optimistic locking: in normal autocommit, the transaction might get rolled back silently--it will write successfully to one server and return success, but if another server simultaneously gets a write that conflicts and starts propagating it then the transaction will be silently rolled back (i.e. undone, removed, lost, failed). With BEGIN-COMMIT transactions, you may get a Deadlock on "COMMIT" and then you're informed that it did in fact roll back the transaction and you must re-submit (i.e. do this if you actually care about durability of the data). With autocommit, as well as with any transactions (even explicit COMMIT) on MySQL master-slave replication or PostgreSQL WAL replication, you may in fact be informed that the transaction is 100% committed and then have that server FAIL and the slave comes up without that transaction--unavoidable silent data loss.

The failure mode expressed by MySQL master-slave replication and PostgreSQL WAL replication in the default asynchronous streaming replication mode is the same failure mode as with "Journaled" write concern in MongoDB. When running "Journaled" rather than "Replica Acknowledged," you write to exactly one server and are told it's committed when it's written to disk--it's durable on that server, but not necessarily replicated. If that server power drops and comes back up, it may find new operations have made its non-replicated operations invalid; it will then silently roll those back.

Therefor, in cluster layouts, it is possible for MongoDB to have a negligible reliability advantage over PostgreSQL's most common replication methods. PostgreSQL has settings that make up that last bit of reliability, putting it roughly on par with MongoDB. MongoDB has a guaranteed "It has reached enough servers that it is valid on the cluster unless God hates you" write concern by which the data is likely to actually be there if it tells you it's there, unless a very specific subset of servers experience a catastrophic failure in an extremely small (tenths of a second) window--a subset large enough to take down your entire cluster.

Short version: MongoDB allows you to, on a per-query basis, write data into the database at any level of reliability that MySQL and PostgreSQL provide. Single-server Journaled (WAL log shipping, WAL asynchronous streaming, MySQL master-slave replication), multi-server Replica Acknowledged (PostgreSQL WAL synchronous streaming), and a single-server "Acknowledged" mode that is faster but gives a weaker data durability guarantee (transaction is valid, not yet to disk, and not replicated).

*"PostgreSQL streaming replication is asynchronous by default. If the primary server crashes then some transactions that were committed may not have been replicated to the standby server, causing data loss. The amount of data loss is proportional to the replication delay at the time of failover."

Re:Like Microsoft SQL Server

[homb]

I don't think it's in the same ballpark. The SQL Server column store seems to be purely for read-only:

Keep in mind that once you add a column store to a table, though, you cannot delete, insert or update the data – it is READ ONLY.

That's nowhere near the complexity of what Oracle is doing, simultaneously providing both a row and column based access to the data. Not that I think this is a good thing, I don't. In most cases you're much better off using a kickass columnar db and handling the batch updates from the upstream app servers. When you plan for building a col-based architecture, you can be much more efficient. Just look at kdb & co.

32TB of RAM = Everest-sized UPS

[Rosco P. Coltrane]

Just you hope Oracle maintains the batteries properly, especially since an emergency save-to-disk is going to take more than a few minutes...

Re:32TB of RAM = Everest-sized UPS

[KiloByte]

Short of a meteor hitting the building the power will never go out

A small fire in a strategic place... Or perhaps, just some minor failure of something you thought was separated enough.

Re:32TB of RAM = Everest-sized UPS

[larien]

*facepalm*

No, you don't use main memory as a write cache, that would be idiotic. You use it for read cache. Any updates will be written to disk (and memory) before acknowledging to the client.

In any case, it doesn't matter how good your batteries/UPS are if your server or DB software crashes for any reason and you have uncommitted writes in memory, hence why caching writes on a database is a Bad Idea (TM).

MonetDB/X100 and Vectorwise

[Skinkie]

Columnstore databases such as MonetDB and their commercial spinoff Vectorwise (now Actian) already showed this can be achieved with open source and proprietary code.

Re:Like Microsoft SQL Server

[SDrag0n]

Until next year http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/17/what-s-new-for-columnstore-indexes-in-sql-server-2014.aspx

Re:OTLP?

[Megane]

One Top Lap Per child.