I'm working on alternative strategies to make the use and maintenance of a multi-terabyte data warehouse implementation tolerably fast. For example, it's clear that a reporting query on a 275-million row table is not going to be fun by anyone's definition, but that for most purposes, it can be pre-processed to various aggregated tables of significantly smaller sizes.

However, what is not obvious is what would be the best strategy for creating those tables. I'm working with MySQL 5.0 and Business Objects' Data Integrator XI, so I have a couple of options.

I can just CREATE TABLE ... SELECT ... to see how things work out. This approach is simple to try, but essentially unmaintanable; no good.

I can define the process as a BODI data flow. This is good in many respects, as it creates a documented flow of how the aggregates are updated, is fairly easy to hook up to the workflows which pull in new data from source systems, and …

Q from Sven - Are mysqlhotcopy and ibbackup only part of 6.0?
mysqlhotcopy is available in all releases. ibbackup is a separate offering that has been available for a long time

Q from Adam: Is the SQL based output portable to another database server engine e.g. MSSQL?
There are options of mysqldump that make the output as much generic as possible, and that is portable, but we can't guarantee it works with every RDBMS. The DDL is the less portable section

Q from Andrea: Is a hot mysqldump generally a good idea?
Unfortunately the answer is "it depends". With MyISAM, for example, this is not generally a good idea, since you have to lock the tables to make the dump consistent - i.e. it is not a hot dump anymore, you may call it "warm". With InnoDB the dump is consistent, provided we execute it with the Repeatable read isolation level.

…

Thanks to all of you who have attended! (and apologise for having posted this info after such a long time)
We have had lots of questions and very interesting feedback and suggestions.

You can find the slides and the Webex presentation here.

I would like to post more on this subject, specifically on snapshot backup, comparing the different options with NetApp, DRBD and LVM.

I’m reminded of an old painting, where there’s a meeting of minds. Since Heidelberg was largely informal (very few stand-up presentations with the audience sitting) with discussions, equally useful conversation and work were done over dinner, in hotel lobbies, and in-between sessions.

Meeting of the Minds: Kaj and Jeremy (large)

I particularly like this photo, as there’s lots of community contributors in the photo. Clockwise from Jeremy, we have Paul (Mr. PBXT, and now MyBS), Pascal (Mr. Yahoo!) and David (co-Founder).

I’m now uploading photos of birds, from our …

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I found.

My create table command and inserts ran …

I got a few comments about my last example not describing a wormhole, but a whitehole. Time to improve the picture a bit and getting data from another dimension on a shorter route than the long standard way.

To use the picture let's take a look at what has been done:

root@127.0.0.1:test> select * from finance where symbol = "MSFT";
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time          | trade_change | open_trade | max_trade | min_trade | volume   |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT   |      29.84 | 2007-10-05 00:00:00 |         0.13 |      29.84 |     29.99 |     29.73 | 45016520 | 
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+

root@127.0.0.1:test> select * from finance; …

Your comments on the [Advanced MySQL Features]() chapter were great. A lot of the questions I got (in the comments and via email) about chapter 6 are really addressed in chapter 5, “Query Performance Optimization,” so I’m posting its outline too. I have the same questions: are there things you’d like to see us cover? Do you have any favorite techniques you’d like to see us include? Any other comments or questions?

Hartmut was asking me some time ago how table discovery in the storage engine interface works. After reading through the code from ndb, archive and memcache I was a bit disappointed: all of them are just copying the definition in binary form around.

For the wormhole SE the lua-file has to create table structure on the fly. You only drop in the .lua into the db-folder and a SELECT will pick it up automaticly.

We use a small function which returns the table definition:

function discover()
        return {
                { name = "fld1", type = 1 },
                { name = "fld2", type = 2 },
                { name = "fld3", type = 15, length = 64 }
        }
end

A SHOW CREATE TABLE against the table shows us:

root@127.0.0.1:test> show create table foobar\G
*************************** 1. row ***************************
       Table: foobar
Create Table: CREATE TABLE `foobar` (
  `fld1` tinyint(4) …

I got a few comments about my last example not describing a wormhole, but a whitehole. Time to improve the picture a bit and getting data from another dimension on a shorter route than the long standard way.

To use the picture let's take a look at what has been done:

root@127.0.0.1:test> select * from finance where symbol = "MSFT";
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time          | trade_change | open_trade | max_trade | min_trade | volume   |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT   |      29.84 | 2007-10-05 00:00:00 |         0.13 |      29.84 |     29.99 |     29.73 | 45016520 | 
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+

root@127.0.0.1:test> select * from finance; …

Brewer's Conjecture: "Consistency, Availability, and Partition Tolerance. Pick two."

Database ACID is something nice to wish for, but for real loads in real environments, it's not there to be had. Suck it up, move on, and do something else.

Reference: Brewer's Conjecture and the Feasibility of Consistent Available Partition-Tolerant Web Services.