Friday, 10 January 2014

On MySQL's Data Dictionaries

All MySQL versions share the same basic architecture: there's a "upper" part of the system, responsible for accepting connections, forking Server Threads, parsing (most of the) SQL, enforcing privileges, authenticating users, etc. The MySQL Developers refer to this component as the "SQL Engine".

On the other side of the counter, there's also a "lower" set of components we know as "Storage Engines" – MyISAM, InnoDB, TokuDB, Memory, Black-Hole, to cite a few examples. This components are responsible for storing and retrieving data from whatever storage media they use and support. This is a radically different architecture from any other implementation of Relational Database Management System (RDBMS) I've seen in my professional life. Usually RDBMSs want to have full control of their storage components, so they can easily implement tricks and shortcuts for profit and performance.

MySQL is still the only RDBMS that I've heard of with this kind of architecture. There are advantages (other storage engines can be included to support exotic data storage systems, and MySQL can easily be extended to become an SQL interface to other types of database.

Of course this architecture choice has consequences. Some of this consequences are… interesting. The one that caught my eye recently was the fact that InnoDB, in order to implement certain features, maintains it's own Data-Dictionary. InnoDB Storage Engine and the SQL Engine Data Dictionaries share nothing due to the very rigid interface between these two major components.

Also, MySQL's SQL Engine doesn't support Transactional DDL (see "13.3.2. Statements That Cannot Be Rolled Back", on MySQL documentation).

Now let's put it all together: there are two separated architecture components of interest in any MySQL database, the SQL Engine and the Storage Engine. InnoDB storage engine implements it's own Data-Dictionary, overlapping and complementing the SQL Engine Data Dictionary (to which it has no direct access due to the rigid interface between the SQL and the Storage Engine components); the SQL Engine DDL statements cannot be rolledback (and therefore are not transactional); InnoDB won't have an opportunity to change it's own Data Dictionary until the SQL Engine is done executing it. Therefore, if the RDBMS process crashes during the time a DDL change was executing, there's a fair chance that we'll end up with inconsistencies between the SQL Engine data-dictionary and the InnoDB Data Dictionary. 

What a neat and nice Theory. 

Now, how to prove this is true? Well, for starters, we need access to both the SQL Engine Data Dictionary, and the InnoDB Storage Engine Data Dictionary. This is not exposed in all available versions of MySQL. Specifically, we're looking for the INFORMATION_SCHEMA.INNODB_SYS_TABLES system table, and we want to compare the information there with the SQL Engine Data Dictionary exposed through the INFORMATION_SCHEMA.TABLES table.

For my Percona Server 5.1.69, I wrote the query below. You may have to adapt your query a bit as column names on those tables change a bit from version to version.

SELECT COUNT(*)
FROM (
    SELECT t.TABLE_SCHEMA AS "SCHEMA"
         , t.TABLE_NAME AS "TABLE"
    FROM INFORMATION_SCHEMA.TABLES t
    WHERE ENGINE = "InnoDB"
      AND NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES ist
            WHERE t.TABLE_SCHEMA = ist.SCHEMA
              AND t.TABLE_NAME = ist.NAME
    )
    UNION ALL
    SELECT ist.SCHEMA AS "SCHEMA"
         , ist.NAME AS "TABLE"
    FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES ist
    WHERE ( ist.SCHEMA, ist.NAME ) NOT IN
        ( ( "", "SYS_FOREIGN" ), ( "", "SYS_FOREIGN_COLS" ) )
      AND NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.TABLES t
            WHERE ENGINE = "InnoDB"
              AND t.TABLE_SCHEMA = ist.SCHEMA
              AND t.TABLE_NAME = ist.NAME
    )
) AS ddstats

This is a very expensive query (it will force statistics fresh of pretty much all your tables, and will acquire a lot of very expensive locks in the process). I advise planning your execution a little bit, so you're not forced to kill your query before you get some interesting results.

I was able to get inconsistencies between my tables in the SQL Engine and InnoDB Storage Engine Data Dictionary. How about you? Would you care to share some war stories with me?

1 comment:

  1. You can always set innodb_stats_on_metadata = 0 to avoid collecting statistics every time you query the information_schema and make the query less expensive.

    A consequence of these inconsistencies are the temporary tables that may end up in your file system if the system crashes during a long running ALTER TABLE. You can't delete them without corrupting the internal InnoDB dictionary, which will prevent your server from starting again. The only solution, if these tables take too much disk space, is to recreate the server from a backup.

    ReplyDelete