?

Log in

No account? Create an account

The Art of "What is going on inside of my database?"

« previous entry | next entry »
Mar. 12th, 2010 | 08:50 am

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.

Link | Leave a comment | Share

Comments {5}

That is both exciting and wildly useful!

from: anonymous
date: Mar. 12th, 2010 05:48 pm (UTC)
Link

Holy crap this is fantastic! Can't wait to take this for a spin myself because it looks amazing!

Reply | Thread

gipsieee

(no subject)

from: gipsieee
date: Mar. 12th, 2010 08:14 pm (UTC)
Link

LJ-cut please? You break the screenwidth on my friends page whenever you post the really wide tables.

Reply | Thread

TABLE_DEFINITION_CACHE

from: jobinau
date: Mar. 16th, 2010 02:20 am (UTC)
Link

Excellent feature..or a must have feature.
As a DBA, i would like to have LAST_DDL_TIMESTAMP.
For me it is more important than VERSION number.
Because wherever change management is strict. they will be cross-checking time.
This is something i frequently querying on Oracle. and helps me a lot.
will it be hard to implement?

Reply | Thread

Brian "Krow" Aker

Re: TABLE_DEFINITION_CACHE

from: krow
date: Mar. 17th, 2010 12:17 am (UTC)
Link

Done!

I've been meaning to add creation and last ddl to both schema and table. I don't know that it will make the build tomorrow, but it will certainly be in there in a couple of weeks.

Reply | Parent | Thread

Re: TABLE_DEFINITION_CACHE

from: jobinau
date: Mar. 18th, 2010 01:32 am (UTC)
Link

Thank you Brian for taking my request into account.
I think there is a discussion regarding the name - bikshed discussion. for a user "update" is different. so I am voting for "modified" :)

Reply | Parent | Thread