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> 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)
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 | | |
+-----------+-------+
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
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)
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
|
+----------------------------------+------------------+
SET SESSION TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED;
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)
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
|
+----------------------------------------+
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
|
+----------------------------------------+
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)
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 |
+-------------------------------+
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 |
+-------------------------------+