November 2011 Archive

Moinsen,

hier die Slides zu meinen MySQL Vorträgen auf der DOAG 2011.
Partitionieren ueber Rechnergrenzen hinweg und MySQL kann auch NoSQL.

Viel Spaß
Erkan

MySQL hat seit einiger Zeit Previews auf neue Funktionalitäten in MySQL zum Anschauen zur Verfügung gestellt.
Folgend schauen wir uns mysql-5.6.4-labs-innodb-memcached an. Die Grundidee ist, dass quasi an MySQL vorbei direkt auf die Storage Engine zugegriffen wird. So wird der Overhead des SQL Parsers/Optimisers, wie auch der des Verbindungsaufbau gespart.

Ist das Paket installiert, muß das memcached Plugin noch installiert werden. Vorab sind die Verwaltungstabellen - welche sich in scripts/innodb_memcached_config.sql befinden - zu installieren. (mysql < scripts/innodb_memcached_config.sql )
Ein Blick in die Datei verrät, dass das memcached Plugin ein eigenes Schema (innodb_memcache) zum Verwalten des Zugriffes von Datenbankabfragen benötigt/erstellt.
Die Tabelle containers:

mysql> show create table containers\G
*************************** 1. row ***************************
       Table: containers
Create Table: CREATE TABLE `containers` (
  `name` varchar(50) NOT NULL,
  `db_schema` varchar(250) NOT NULL,
  `db_table` varchar(250) NOT NULL,
  `key_columns` varchar(250) NOT NULL,
  `value_columns` varchar(250) DEFAULT NULL,
  `flags` varchar(250) NOT NULL DEFAULT '0',
  `cas_column` varchar(250) DEFAULT NULL,
  `expire_time_column` varchar(250) DEFAULT NULL,
  `unique_idx_name_on_key` varchar(250) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from containers;
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table  | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| aaa  | test      | demo_test | c1          | c2            | c3    | c4         | c5                 | PRIMARY                |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)

Das Skript innodb_memcached_config.sql hat nicht nur diese Tabelle erstellt sondern - wie auch bei den folgenen Tabellen - auch noch einen Eintrag eingefügt.
Jede Zeile in der Tabelle containers ist definiert auf welche Tabelle memcached zugreift. Die Spalte name dient hier dazu auch mehrere (Ziel)Tabellen zu definieren. Derzeit wird nur eine Tabelle unterstützt. Mir ist auch nicht klar wie via memcached mehr als eine Tabelle unterstützt werden soll. (Ich habe gehört, dass dies dadurch erreicht werden soll, indem die keys aus name:memcachekey bestehen werden sollen.)

db_schema/db_table verweisen auf das konkrete Schema/Tabelle.Die Spalten key_columns, value_columns, flags, cas_column, expire_time_column sind dem memcached Protokoll geschuldet. Auf dieses gehe ich - unter anderem wegen meiner Unwissenheit bezüglich des Protokolls - nicht ein. Die Spalte unique_idx_name_on_key enthält den Namen des Indexes (welcher UNIQUE sein muß) auf key_columns (hier c1).
So ist ersichtlich, dass Tabellen, welche via dem memcached Plugin zugreifbar sein sollen, dem obigem Format entsprechen müssen. Memcached ist ein simplers Key (key_colums) Value (value_columns) Store. Das memcached Plugin erlaubt unter value_columns mehrere Spalten anzugeben. Da das memcached Protokoll aber nur Key Value kann, bietet das Plugin an einen Separator zu definieren. So wird via memcached ein String (Value) übergeben welches das Plugin nach dem Trenner teilt und in die passenden Spalten schreibt. Zu definieren ist der Trenner in der Tabelle config_optioins:

mysql> show create table config_options\G
*************************** 1. row ***************************
       Table: config_options
Create Table: CREATE TABLE `config_options` (
  `name` varchar(50) NOT NULL,
  `value` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from config_options;
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+

Es ist davon auszugehen, dass hier noch weitere Konfigurationsmöglichkeiten kommen werden. Eine weitere Tabelle ist die cache_policies:

mysql> show create table cache_policies\G
*************************** 1. row ***************************
       Table: cache_policies
Create Table: CREATE TABLE `cache_policies` (
  `policy_name` varchar(40) NOT NULL,
  `get_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `set_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `delete_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `flush_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  PRIMARY KEY (`policy_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Während in der Tabelle containers noch angelegt ist auf mehrere Tabellen zuzugreifen, ist dies hier nicht vorgeshen, die Einstellungen pro Tabelle vorzunehmen.
Daher ist das als Konfiguration für alle Tabellen zu interpretieren.
Die genaue Konfiguration wie auch eine auführliche Einleitung ist hier zu finden.
Ärgerlich ist, dass Änderungen in den cache_policies erst nach einem Restart des Servers ziehen.

Doch bisher haben wir die Installation des Plugin versäumt:

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * from  INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='daemon_memcached'\G
*************************** 1. row ***************************
           PLUGIN_NAME: daemon_memcached
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: DAEMON
   PLUGIN_TYPE_VERSION: 50604.0
        PLUGIN_LIBRARY: libmemcached.so
PLUGIN_LIBRARY_VERSION: 1.3
         PLUGIN_AUTHOR: Jimmy Yang
    PLUGIN_DESCRIPTION: Memcached Daemon
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

Folgende Variablen sind zu setzen:

mysql> show global variables like 'daemon_memcached%';
+----------------------------------+------------------+
| Variable_name                    | Value            |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog   | OFF              |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path |                  |
| daemon_memcached_option          |                  |
| daemon_memcached_r_batch_size    | 1048576          |
| daemon_memcached_w_batch_size    | 32               |
+----------------------------------+------------------+

Es handelt sich hierbei um read-only Variablen. So sind diese auch nicht zur Laufzeit zu ändern. Interessant ist die Option daemon_memcached_w_batch_size. Zum Steigern der Performance wird nur alles 32 Änderungen committed. Will man nun (in der Verbindung) auch die noch nicht committetten Anweisungen sehen, ist der Transaktions Level anzupassen:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Fazit:

Also mir erschließt sich der Mehrwert der Memcached Erweiterung nicht. Handlersocket z.B. erlaubt den Zugriff auf jede und mehrere Tabellen der Applikation. Das memcached Plugin verlangt eine Tabelle nach einem festgelegtem Format. Die Usability ist bescheiden. Und wenn man meint durch folgendes den mysqld nicht restarten zu müssen, crasht mysqld eben :)

mysql> uninstall PLUGIN daemon_memcached;
Query OK, 0 rows affected (2.00 sec)

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
Query OK, 0 rows affected (0.00 sec)

Viel Spaß
Erkan

Beim Rumspielen mit MySQL 5.6 - von der es Previewversionen gibt - fiel auf, dass es neue Tabellen im I_S gibt:

5.5.13:

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+

5.6.2:

mysql> SHOW TABLES LIKE 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     |
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     |
| INNODB_METRICS                         |
| INNODB_SYS_INDEXES                     |
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      |
| INNODB_SYS_TABLES                      |
+----------------------------------------+

Betrachten wir uns die INNODB_SYS_FOREIGN% Tabellen. Diese erlauben es für InnoDB Tabellen einfach an die Foreign Keys (FK) heran zu kommen. In der Datenbank existiert ein FK kind -> papa.

mysql> select * from INNODB_SYS_FOREIGN;
+------------------+-----------+-----------+--------+------+
| ID               | FOR_NAME  | REF_NAME  | N_COLS | TYPE |
+------------------+-----------+-----------+--------+------+
| test/kind_ibfk_1 | test/kind | test/papa |      1 |    1 |
+------------------+-----------+-----------+--------+------+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_FOREIGN_COLS;
+------------------+--------------+--------------+-----+
| ID               | FOR_COL_NAME | REF_COL_NAME | POS |
+------------------+--------------+--------------+-----+
| test/kind_ibfk_1 | id2          | id           |   0 |
+------------------+--------------+--------------+-----+
1 row in set (0.00 sec)

Über INNODB_SYS_FOREIGN erhalten wir die verknüpften Tabellen. TYPE = 1 meint hier ON DELETE CASCADE. Imho nicht wirklich lesbar, vielleicht hätte man sich bei TYPE für den Typ SET entscheiden sollen :)

Die Tabelle INNODB_SYS_FOREIGN_COLS verrät uns die Spalten des FK.
Beide verknüpft:  

mysql> select concat(FOR_NAME,":",FOR_COL_NAME," -> ",REF_NAME,":",REF_COL_NAME)  as FK from INNODB_SYS_FOREIGN_COLS JOIN INNODB_SYS_FOREIGN using(ID);
+-------------------------------+
| FK                            |
+-------------------------------+
| test/kind:id2 -> test/papa:id |
+-------------------------------+

Folgend sehen wir wie dies für MySQL 5.[1,5,6].x auch ohne die neuen Tabellen (annähernd) erledigt werden kann.

mysql> select * from TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | kind_ibfk_1     | test         | kind       | FOREIGN KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
 
mysql> select * from KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='kind_ibfk_1'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: kind_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: kind
                  COLUMN_NAME: id2
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test
        REFERENCED_TABLE_NAME: papa
       REFERENCED_COLUMN_NAME: id

mysql> SELECT concat(A.TABLE_SCHEMA,'/',A.TABLE_NAME,':',A.COLUMN_NAME,' -> ',A.REFERENCED_TABLE_SCHEMA,'/',A.REFERENCED_TABLE_NAME,':',A.REFERENCED_COLUMN_NAME) FK from KEY_COLUMN_USAGE A JOIN TABLE_CONSTRAINTS USING(CONSTRAINT_NAME) WHERE TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY';
+-------------------------------+
| FK                            |
+-------------------------------+
| test/kind:id2 -> test/papa:id |
+-------------------------------+

Wenn sich jemand merkt, wofür die jeweiligen Werte der Type Spalte in 5.6.2 stehen, sind im Zugriff auf INNODB_SYS_FROREIG% mehr Informationen zu holen. Letzterer Ansatz hat nicht nur denVorteil auf MySQL 5.1.x und 5.5.x zu laufen, sondern auch FKs auf Tabellen zu entdecken, welche nicht InnoDB sind.



Viel Spaß
Erkan

Moin, morgen beginnt die DOAG 2011. Auf der werden einige Vorträge gehalten werden.
Zwei (1,2) von mir. 
Jene, welche sich dort aufhalten, werden gebeten aufzuschlagen.

Viel Spaß
Erkan :)