Tag Archives: data_dictionary

Drizzle metadata tables

Posted on by
Reply

Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I’d have a look at what we have in Drizzle. It’s pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view:

drizzle> select table_schema,count(table_name)
    ->  from information_schema.tables
    -> group by table_schema;
+--------------------+-------------------+
| table_schema       | count(table_name) |
+--------------------+-------------------+
| DATA_DICTIONARY    |                53 |
| INFORMATION_SCHEMA |                20 |
+--------------------+-------------------+
2 rows in set (0 sec)

In Drizzle it’s important to note that there is a differentiation between SQL Standard INFORMATION_SCHEMA tables (found in the INFORMATION_SCHEMA schema) and the extensions and extra information available from Drizzle that is Drizzle specific (found in DATA_DICTIONARY). Since I know that the PostgreSQL version I have on my laptop (8.4) also implements INFORMATION_SCHEMA, I can run this query there as well:

stewart=# select table_schema,count(table_name)
 from information_schema.tables
 group by table_schema;
    table_schema    | count
--------------------+-------
 information_schema |    55
 pg_catalog         |    78
(2 rows)

If we had written the query to the Drizzle DATA_DICTIONARY tables, it only may have been portable – and as we can see, certainly wouldn’t have run unmodified on PostgreSQL. Personally, I really like this feature, and wish more systems did something like it.

Embedded InnoDB: InnoDB Status

Posted on by
2

Using the Embedded InnoDB plugin I’m working on, you can use the INNODB_STATUS table function in the data_dictionary, you can do pretty neat things.

For example, we can see that each autocommit transaction causes an fsync and if you insert multiple rows ina  single statement, you still only get 1 fsync:

drizzle> SELECT * FROM DATA_DICTIONARY.INNODB_STATUS
    -> WHERE name="fsync_req_done";
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| fsync_req_done |    25 |
+----------------+-------+
1 row in set (0 sec)

drizzle> insert into t1 values (1);
Query OK, 1 row affected (0.05 sec)

drizzle> SELECT * FROM DATA_DICTIONARY.INNODB_STATUS WHERE name="fsync_req_done";
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| fsync_req_done |    26 |
+----------------+-------+
1 row in set (0 sec)

drizzle> insert into t1 values (1),(2),(3),(4);Query OK, 4 rows affected (0 sec)
Records: 4  Duplicates: 0  Warnings: 0

drizzle> SELECT * FROM DATA_DICTIONARY.INNODB_STATUS WHERE name="fsync_req_done";
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| fsync_req_done |    27 |
+----------------+-------+
1 row in set (0 sec)

Embedded InnoDB: querying the configuration

Posted on by
1

I am rather excited about being able to do awesome things such as this to get the current configuration of your server:

drizzle> SELECT NAME,VALUE 
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION
    ->  WHERE NAME IN ("data_file_path", "data_home_dir");
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| data_file_path | NULL  | 
| data_home_dir  | ./    | 
+----------------+-------+
2 rows in set (0 sec)

drizzle> SELECT NAME,VALUE
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION
    -> WHERE NAME IN ("data_file_path", "data_home_dir");
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| data_file_path | NULL  | 
| data_home_dir  | ./    | 
+----------------+-------+
2 rows in set (0 sec)

drizzle> SELECT NAME,VALUE 
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION 
    -> WHERE NAME = "io_capacity";
+-------------+-------+
| NAME        | VALUE |
+-------------+-------+
| io_capacity | 200   | 
+-------------+-------+
1 row in set (0 sec)

Coming soon: status in a table.

(this is for the upcoming embedded_innodb plugin, which using the API provided by Embedded InnoDB to implement a Storage Engine for Drizzle)