Brian "Krow" Aker's Idle Thoughts

Yesterday we were having a conversation on IRC about the need for more useful information about the internals of the database.

"SHOW STATUS" is just too primitive in its design to provide the sort of detailed information you need to do operations. Yesterday we got a bug request over the number of "open tables" found after a particular query. The user had assumed the number was off, but what they hadn't realized was that the number was accurate (in this particular case, MySQL fudges a number on open tables because it can't handle count its derived tables).

One of the patches coming into the tree right now fully exposes the contents of the table cache and table definition cache to the user. You can see who holds what locks on what tables, and you can see the actual count on table access per table.

drizzle> select * from TABLE_DEFINITION_CACHE;
+-----------------+------------------------+---------+-------------+----------------+
| TABLE_SCHEMA    | TABLE_NAME             | VERSION | TABLE_COUNT | IS_NAME_LOCKED |
+-----------------+------------------------+---------+-------------+----------------+
| data_dictionary | schema_names           |       1 |           1 | FALSE          |
| data_dictionary | table_definition_cache |       1 |           1 | FALSE          |
| data_dictionary | show_tables            |       1 |           1 | FALSE          |
+-----------------+------------------------+---------+-------------+----------------+
3 rows in set (0 sec)

drizzle> select * from TABLE_CACHE;
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
| SESSION_ID | TABLE_SCHEMA    | TABLE_NAME             | ARCHETYPE | ENGINE         | VERSION | IS_NAME_LOCKED | ROWS | AVG_ROW_LENGTH | TABLE_SIZE | AUTO_INCREMENT |
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
|          0 | data_dictionary | schema_names           | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            260 |          0 |              0 |
|          0 | data_dictionary | show_tables            | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            260 |          0 |              0 |
|          1 | data_dictionary | table_cache            | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |           1113 |          0 |              0 |
|          0 | data_dictionary | table_definition_cache | FUNCTION  | FunctionEngine |       1 | FALSE          |  100 |            559 |          0 |              0 |
+------------+-----------------+------------------------+-----------+----------------+---------+----------------+------+----------------+------------+----------------+
4 rows in set (0 sec)

drizzle> select * from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 1;
Empty set (0 sec)

drizzle> select * from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 0;
+-----------------+------------------------+---------+-------------+----------------+
| TABLE_SCHEMA    | TABLE_NAME             | VERSION | TABLE_COUNT | IS_NAME_LOCKED |
+-----------------+------------------------+---------+-------------+----------------+
| data_dictionary | schema_names           |       1 |           1 | FALSE          |
| data_dictionary | table_cache            |       1 |           1 | FALSE          |
| data_dictionary | table_definition_cache |       1 |           1 | FALSE          |
| data_dictionary | show_tables            |       1 |           1 | FALSE          |
+-----------------+------------------------+---------+-------------+----------------+
4 rows in set (0 sec)

drizzle> select count(*) from TABLE_DEFINITION_CACHE WHERE TABLE_COUNT  > 0;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0 sec)

The term "ARCHETYPE" is the base primitive about what sort of table was used. It is more detailed then the ANSI "TABLE_TYPE" that exists in I_S. We still have a debate on what exactly this term should mean. One of the things I enjoy about working on Drizzle? I am not stuck in a room full of people who will spend hours on this sort of bike shed decisions.

Version gives you the current definition count for the table. Right now that number is still based on "since opened" but we will soon be storing the metadata for this so you will know how many times in the life of an object it has been changed.

We are still working out the details to SHOW TABLE STATUS. Our SHOW commands are just query rewrites to tables.

Here is a partial example of the new table that is outputted from a SHOW TABLE STATUS:

+---------+--------+-------------------+-----------
| Session | Schema | Name              | Type
+---------+--------+-------------------+-----------
|       0 | Schema | b                 | STANDARD
|       0 | Schema | show_tables       | FUNCTION
|       1 | Schema | show_table_status | FUNCTION
|       0 | Schema | schema_names      | FUNCTION
|       0 | Schema | dfsdf             | STANDARD
|       1 | Schema | b                 | TEMPORARY
|       1 | Schema | a                 | TEMPORARY
+---------+--------+-------------------+-----------+
7 rows in set (0 sec)

Notice Session? Notice that type can be Temporary? With our system you can see the current owner of the open table and we now include whatever temporary tables you have in your own session. We have also included a larger table which you can see just your own temporary tables (and most likely I will soon create a table so that you can see all temporary tables open across all sessions). The current "Type" in SHOW TABLE STATUS is an Archetype so we will be changing that so that terms match up across the database. Consistency in design is an awesome thing :)

There is a lot more to come!

P.S. Just wait until I push the code for tracking locks in Drizzle, I demoed it at SCALE and got a lot of both positive and constructive feedback on it.