Explorations of VoltDB

I've started on a little project to see how VoltDB lines up against its promises.

The Unique Selling Point (sort of) of VoltDB is that you can horiztonally scale an OLTP database, in a shared-nothing environment. That's quite a trick.

Horizontally-scalable OLAP systems are well-known, even passe; but getting an OLTP system that scales elegantly has been hard/impossible for a reasonable cost. Could you scale out an Oracle RAC? Sure, if you have a few million bucks for licences and hardware. Can you scale out MySQL? Sure, if you have an army of programmers willing and able to implement sharding in their applications.

The idea is that VoltDB takes the headache of managing sharding and scalable storage, from the children developers, and hands it to the crazy people DBAs.

VoltDB has a base price of Free, which I like, so I decided to give it a go. The idea is to come up with a little benchmark problem, to stress its ACIDity. My example case is for a ticketing agency, such as those types who manage major events like rock bands that will sell-out a 100,000 person stadium. At this point, I'm just mocking up a little data, and a few queries. I hope I'll get around to building a little erlvolt app to go with it.

Getting up and running was a breeze. The documentation and build is excellent in this respect, I can't fault it. I took the hardest possible route and compiled the code from scratch (on Ubuntu) without reading the documentation (which I've since read) - and it went brilliantly well, with an idiot-resistant build that coaxed me towards the correct answer when I got things wrong (generally missing dependencies which are "ant" and "g++").

For sane people, you can just register on their website, download a binary, and install it. There's an (oh-so-cool) docker image available, but I got bored of playing with that in less time than it took to download, so install it, it's easy.

I've found that by being badly behaved, I can crash the VoltDB server (and/or my browser) on my ancient little laptop. This worries me (only) a little. I have been pretty rough on the DB, throwing all kinds of broken rubbish at it, but a DB really does need to be bullet-proof. In terms of running queries, procedures, etc. voltdb just fine, and does what it says on the tin.

If anyone from VoltDB reads this, please address bug ENG-2526 - it forces developers to build misleading interfaces, and that's a really bad idea.

VoltDB looks promising, so far, and has all kinds of interesting features. I'd like to see VoltDB extend with more data types, and functions, and maybe referential integrity enforcement... and a pot of gold, and a pony...

Empty an Oracle schema, leave an empty schema

What it says on the tin. Sufficient code analysis will reveal that some bizarre schema dependencies may not be destroyed by this; but experience will show that's not actually a problem :-)

The code lives as a gist over on github. A snapshot is below, for simplicity:

set echo off
set verify off
set serveroutput on size 100000

-- Hosted at http://lastinfinitetentacle.blogspot.com/2012/03/empty-oracle-schema-leave-empty-schema.html

-- Disable all contraints
BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

-- remove all objects
declare

cursor dropObjectsCusor is
select 'drop ' || object_type || ' ' || object_name as sqlDropStmt
from user_objects
where object_type <> 'TABLE' and object_type <> 'INDEX'
order by object_type;

cursor dropTablesCusor is
select 'truncate table ' || object_name as sqlTruncTbl,
'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
from user_objects
where object_type = 'TABLE'
order by object_type;

begin

for ob in dropTablesCusor
loop
begin
  execute immediate ob.sqlTruncTbl;
exception when others then dbms_output.put_line('Could not truncate a table.');
end;
begin
  execute immediate ob.sqlDropTbl;
exception when others then dbms_output.put_line('Could not drop a table.');
end;
end loop;

for ob in dropObjectsCusor
loop
begin
  execute immediate ob.sqlDropStmt;
exception when others then dbms_output.put_line('Could not drop some object.');
end;
end loop;


end;
/
purge recyclebin;