Brian "Krow" Aker (krow) wrote,
Brian "Krow" Aker

Data Dictionary VS Information Schema

By default Drizzle has two schemas in the local catalog.

drizzle> SHOW SCHEMAS \G
*************************** 1. row ***************************
*************************** 2. row ***************************
*************************** 3. row ***************************
Database: version_test
3 rows in set (0 sec)

The tables in INFORMATION_SCHEMA are just what is found in the ANSI SQL standard. We have added zero custom columns or tables to the schema, it is just the standard. The goal of this was to make it easy for tool vendors to support Drizzle via their current tools without having to make special exceptions for Drizzle. The tables are populated with information via Table Functions (which are completely new to Drizzle). Table Functions generate data on an as needed basis and do not materialize tables within queries (though many of the table functions get their information from caches from within the server).

In Drizzle all SHOW commands also gain their information from table functions as well (with the exception of the warnings and errors show commands). For Drizzle a SHOW command is rewritten into a simple SELECT statement, with wild cards on the statements pushed to the table functions.

DATA_DICTIONARY on the other hand is where we keep additional tables that are designed for Drizzle. The tables SCHEMAS and TABLES hold additional information beyond what the SQL standard requires us to provide (in the next beta you will be able to see both the version of the schema/table and the UUID we use to track the identity of the object). Additional tables can be added via --plugin-add. For instance if you load the performance_schema plugin, you can gain access to SESSION_USAGE which provides you with information on the last five queries you executed. Via that table function you can find out how much time was used up by a query for page faults, etc... (this is similar to the SHOW PROFILES command that was added to the MySQL community edition but that was never added to the Enterprise edition).

Drizzle also has a SCOREBOARD_STATISTICS table function that provides information on our "scoreboard". This is similar to the Apache concept where pre-allocated memory is used to track session data. The Drizzle scoreboard provides most of the information that we provide and aggregate on a per user basis (you can find out more about it here).

There are currently 42 default tables in DATA_DICTIONARY. Table exist for looking at the current statics on the different protocols that we respond too (you can see information on the MySQL protocol via SELECT * FROM DATA_DICTIONARY.MYSQL_PROTOCOL_STATUS). You can find out what is in the table definition cache by querying TABLE_DEFINITION_CACHE or see who has what table locked by looking into the TABLE_CACHE table. We provide eight tables that specifically provide information on the state of Innodb.
  • Post a new comment


    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded