enough said.

“There’s a lot of cool shit going on”

– Anonymous source at DevConf :)

Arjen’s MySQL Community Journal – A million tables

$ time ~/mysql_create_table_torture
50234

real 6m11.927s
user 0m2.347s
sys 0m1.578s
(i hit ctrl-c at 50,000 as i did want to get back to real work).

No sign of slowdown. Assume it would take about 60mins on XFS. Seems to be metadata limited here… disk going constant, not CPU.

Of course the real benefit with XFS will be sane lookup times.

Delete wasn’t bad -  under 2 mins.

Also would be better on a less abused FS than my laptop :)

I (relatively) recently added the FILES table to INFORMATION_SCHEMA (check out the patch). This was so that we could provide an SQL interface to check how much free space their was in ndb disk data files. You can see how to use this in my post entitled INFORMATION_SCHEMA.FILES (querying disk usage from SQL). We’ll have stuff hitting the manual at some point soon.

So how is an INFORMATION_SCHEMA table implemented in MySQL? Well, the first thing to realise is that INFORMATION_SCHEMA (herein refered to as I_S) tables aren’t “real” tables – they are not created by the user (in fact, the user shouldn’t – or can’t – do DDL on the I_S database). Instead, the I_S database contains tables generated on-the-fly by the MySQL Server. Internally, we create MEMORY tables and convert them to MyISAM if they get too big and then run queries on them.

So, into the code. Bring up sql_show.cc and have a look. Specifically, scroll down to ST_SCHEMA_TABLE schema_tables[] and see that it’s the same as what SHOW TABLES gets you on the I_S database.

For adding the FILES table, you can see from my patch that we just add an item to this structure. (the hunk is below)

@@ -4655,6 +4726,8 @@
fill_schema_column_privileges, 0, 0, -1, -1, 0},
{"ENGINES", engines_fields_info, create_schema_table,
fill_schema_engines, make_old_format, 0, -1, -1, 0},
+  {"FILES", files_fields_info, create_schema_table,
+   fill_schema_files, 0, 0, -1, -1, 0},
   {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0},
{"OPEN_TABLES", open_tables_fields_info, create_schema_table,

As the comment indicates (oh my, a comment that’s correct!) you need to add an item to the enum in sql/table.h for the files table.
We’ll need to create the files_fields_info structure. This, as you guessed, describes the fields in the table. Above the schema_tables[] definition in sql_show.cc you can see the existing definitions for existing tables. Our (rather long) definition for FILES is:

ST_FIELD_INFO files_fields_info[]=
{
{"FILE_ID", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"FILE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"FILE_TYPE", 20, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLESPACE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_CATALOG", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"LOGFILE_GROUP_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"LOGFILE_GROUP_NUMBER", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"ENGINE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"FULLTEXT_KEYS", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"DELETED_ROWS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"UPDATE_COUNT", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"FREE_EXTENTS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"TOTAL_EXTENTS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"EXTENT_SIZE", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"INITIAL_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"MAXIMUM_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"AUTOEXTEND_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"CREATION_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"LAST_UPDATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"LAST_ACCESS_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"RECOVER_TIME", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"TRANSACTION_COUNTER", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"VERSION", 21 , MYSQL_TYPE_LONG, 0, 1, "Version"},
{"ROW_FORMAT", 10, MYSQL_TYPE_STRING, 0, 1, "Row_format"},
{"TABLE_ROWS", 21 , MYSQL_TYPE_LONG, 0, 1, "Rows"},
{"AVG_ROW_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Avg_row_length"},
{"DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Data_length"},
{"MAX_DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Max_data_length"},
{"INDEX_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Index_length"},
{"DATA_FREE", 21 , MYSQL_TYPE_LONG, 0, 1, "Data_free"},
{"CREATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Create_time"},
{"UPDATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Update_time"},
{"CHECK_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Check_time"},
{"CHECKSUM", 21 , MYSQL_TYPE_LONG, 0, 1, "Checksum"},
{"STATUS", 20, MYSQL_TYPE_STRING, 0, 0, 0},
{"EXTRA", 255, MYSQL_TYPE_STRING, 0, 0, 0},
{0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
};

So now we’ve added a table to I_S and defined the table. The universal create_schema_table will take care of creating the table for us – we just need to write the function to fill the table now!

If we want to test where we’re up to now, an empty function will do. For example, you could add something like this to sql_show.cc:

int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)
{
return 0;
}

Now you can build, run and test, but the real function is a lot more interesting. Since each storage engine can have its own files, we create a handlerton (the data structure for a table handler – see handler.h) call for the handler to fill out its bit of the FILES table.

struct run_hton_fill_schema_files_args
{
TABLE_LIST *tables;
COND *cond;
};

static my_bool run_hton_fill_schema_files(THD *thd, st_plugin_int *plugin,
void *arg)
{
struct run_hton_fill_schema_files_args *args=
(run_hton_fill_schema_files_args *) arg;
handlerton *hton= (handlerton *) plugin->plugin->info;
if(hton->fill_files_table)
hton->fill_files_table(thd, args->tables, args->cond);
return false;
}

int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)
{
int i;
TABLE *table= tables->table;
DBUG_ENTER("fill_schema_files");

struct run_hton_fill_schema_files_args args;
args.tables= tables;
args.cond= cond;

plugin_foreach(thd, run_hton_fill_schema_files,
MYSQL_STORAGE_ENGINE_PLUGIN, &args);

DBUG_RETURN(0);
}

So, the fill_schema_files() function is what the I_S code calls. Since in MySQL 5.1 we have a much improved plugin infrastructure, we use the plugin_foreach function which iterates over the loaded storage engines in a safe way. For each storage engine, we end up calling run_hton_fill_schema_files with the args structure as a parameter. As you can see, if the handlerton has a fill_files_table function, we call it.

Now that we’re here, we need to implement fill_files_table for some table handlers. For storage engines we’re not going to implement it, we need to make sure they set it to NULL (there’s a bunch of this going on in the aforementioned patch).

Since for the NDB table handler (ha_ndbcluster.cc) we have a lovely sane init function (ndbcluster_init) it’s much easier to support new handlerton calls:

h.fill_files_table= ndbcluster_fill_files_table;

That’s all that’s needed (apart from the implementation of course :)

So, what does the implementation look like? It’s easy to see how it works from the source, so I won’t go into details here. Basically, we fill out a row using data retreived via the NDBAPI and call schema_table_store_record(). How you construct the function for your storage engine may be quite different, enjoy!

One useful query that jeb is using in some of the tests is:

SELECT FILE_NAME, (TOTAL_EXTENTS * EXTENT_SIZE) AS 'Total Extent Size',
(FREE_EXTENTS * EXTENT_SIZE) AS 'Total Free In Bytes',
(((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
AS '% Free Space' FROM INFORMATION_SCHEMA.FILES
+ WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';

You may want to CREATE VIEW blah AS … for this as it could be a useful query to run regularly

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)

MySQL: Can MySQL be run from a CDROM?

The question is asked. The answer is – yes!

Temporary tables can be store anywhere – e.g. the system’s temporary directory (/tmp on unix, probably c:\temp or something on windows… i’m sure there’s one of those %FOO% things for it).

IIRC you may need to start it with a flag or something – but the embedded library – libmysqld (where the mysql server runs within your process – you link to libmysqld instead of libmysql).

Of course, if you’re linking mysql with non-gpl code, you’ll need a license from us.