up to 3 copies of data (3*DataMemory)

+ 64MB * NoOfFragLogFiles (default=8)

+ UNDO log (dependent on update speed)

For example:

DataMemory=1024MB

idea on disk usage= 1024*3 + 64 * 8 =  3584MB + UNDO log

It’s very tempting to have a “SHOW ESTIMATES” command in the management client/server that performs all these equations (and the answers). I bet Professional Services would love me :)

EnterpriseDB : Open source based relational database, PostgreSQL based database management software

Oh, that’s right – it’s a proprietary database! “Based on PostgreSQL” – well, good for them – they got a bunch of stuff for free[1]. But without a commitment to having the source out there for every release (or even the development tree) how committed are they really?

How can anybody tell if their improvements are good and well written or just utter hacks that would make you loose your lunch? (not dissing their programmers here, just pointing out that you cannot know).

Meanwhile, over here at MySQL, every single time that a developer types bk commit, an email is sent (with the content of the patch) to a public list[2]. Also, the development trees are out there, as is the source to every release in a tarball. So you can get the complete revision history of the MySQL server.[3]

That’s called committment to freedom!

[1] I understand they do pump some stuff back into PostgreSQL, but it’s still a fork with non-public bits! This also isn’t a diss on PostgreSQL.
[2] Yes, people do actually read this. I have (personally) gotten replies from people out there in the big wide world about commits I’ve made to the source tree.

[3] We’re not perfect by any means – but IMHO we’re pretty good and there’s lots of people totally committed to making sure we get better.

In MySQL 5.1.6 there’s a new INFORMATION_SCHEMA table.

Currently, it only has information on files for NDB but we’re hoping to change that in a future release (read: I think it would be neat).

This table is a table generated by the MySQL server listing all the different files that are/could be used by a storage engine. Three (may) be table to file mappings (or not) depending on the engine.

Basically, NDB does files like so:

A table is stored in a tablespace.

A tablespace has datafiles.

Datafiles are of a set size.

Space is allocated in datafiles to tables in a unit called an extent.

If you don’t have any free extents you cannot have new tables store data on disk.

If you don’t have any free extents you may still be able to add data to a table as there may be free space in an extent allocated to that table.

Logs (used for crash recovery) are stored in logfiles.

logfiles are part of logfile groups.

A tablespace uses a logfile group for logging.

Try the following bits of code and running SELECT * from INFORMATION_SCHEMA.FILES between each statement.

CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 4M
ENGINE=NDB;


CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;

ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE = 4M
ENGINE=NDB;


CREATE TABLE t1
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
TABLESPACE ts1 STORAGE DISK
ENGINE=NDB;

INSERT INTO t1 VALUES (0, 0, 0);
SELECT * FROM t1;


DROP TABLE t1;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat'
ENGINE = NDB;

DROP LOGFILE GROUP lg1
ENGINE =NDB;

For a point of interest, these examples are taken from the ndb_dd_basic test (which can be found in mysql-test/t/ndb_dd_basic.test)