D.Horiyama's output

MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- memcached API

MySQLmemcached勉強メモ

MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]

memcached API

[https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-setup.html:embed:cite]

【補】環境構築

こんな感じに環境を用意する
5.6だとすぐ使えなそうだったので5.7にしちゃった

docker-compose.yml

version: "3"
services:
  mysql5.7:
    build:
      context: ./image/5.7
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
    tty: true

Dockerfile

FROM mysql:5.7

ADD ./1_setup_memcacmed.sh  /docker-entrypoint-initdb.d

1_setup_memcached.sh

#!/bin/sh

echo 'setup'
mysql -u root -proot -vvv < /usr/share/mysql/innodb_memcached_config.sql
echo 'setup done'
echo 'install'
mysql -u root -proot -vvv <<EOF
INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
SELECT 'OK' FROM DUAL;
EOF
echo 'install done'
  • 起動ログ
...
mysql5.7_1  | 2020-06-06T00:09:22.282706Z 0 [Note] mysqld: ready for connections.
mysql5.7_1  | Version: '5.7.27'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server (GPL)
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql5.7_1  | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql5.7_1  | 
mysql5.7_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1_setup_memcacmed.sh
mysql5.7_1  | setup
mysql5.7_1  | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1  | --------------
mysql5.7_1  | create database innodb_memcache
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `cache_policies` (
mysql5.7_1  | 	`policy_name` VARCHAR(40) PRIMARY KEY,
mysql5.7_1  | 	`get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  | 	 NOT NULL ,
mysql5.7_1  | 	`set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
mysql5.7_1  | 	 NOT NULL ,
mysql5.7_1  | 	`delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  | 	 NOT NULL,
mysql5.7_1  | 	`flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1  | 	 NOT NULL
mysql5.7_1  | ) ENGINE = innodb
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `containers` (
mysql5.7_1  | 	`name` varchar(50) not null primary key,
mysql5.7_1  | 	`db_schema` VARCHAR(250) NOT NULL,
mysql5.7_1  | 	`db_table` VARCHAR(250) NOT NULL,
mysql5.7_1  | 	`key_columns` VARCHAR(250) NOT NULL,
mysql5.7_1  | 	`value_columns` VARCHAR(250),
mysql5.7_1  | 	`flags` VARCHAR(250) NOT NULL DEFAULT "0",
mysql5.7_1  | 	`cas_column` VARCHAR(250),
mysql5.7_1  | 	`expire_time_column` VARCHAR(250),
mysql5.7_1  | 	`unique_idx_name_on_key` VARCHAR(250) NOT NULL
mysql5.7_1  | ) ENGINE = InnoDB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.02 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE  TABLE IF NOT EXISTS `config_options` (
mysql5.7_1  | 	`name` varchar(50) not null primary key,
mysql5.7_1  | 	`value` varchar(50)) ENGINE = InnoDB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO containers VALUES ("aaa", "test", "demo_test",
mysql5.7_1  | 			       "c1", "c2",  "c3", "c4", "c5", "PRIMARY")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
mysql5.7_1  | 				  "innodb_only", "innodb_only", "innodb_only")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO config_options VALUES("separator", "|")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO config_options VALUES("table_map_delimiter", ".")
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE DATABASE IF NOT EXISTS test
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | CREATE TABLE demo_test (c1 VARCHAR(32),
mysql5.7_1  | 			c2 VARCHAR(1024),
mysql5.7_1  | 			c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
mysql5.7_1  | ENGINE = INNODB
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0)
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | Query OK, 1 row affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | Bye
mysql5.7_1  | setup done
mysql5.7_1  | install
mysql5.7_1  | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1  | --------------
mysql5.7_1  | INSTALL PLUGIN daemon_memcached soname "libmemcached.so"
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | InnoDB MEMCACHED: Memcached uses atomic increment 
mysql5.7_1  | Query OK, 0 rows affected (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | --------------
mysql5.7_1  | SELECT 'OK' FROM DUAL
mysql5.7_1  | --------------
mysql5.7_1  | 
mysql5.7_1  | +----+
mysql5.7_1  | | OK |
mysql5.7_1  | +----+
mysql5.7_1  | | OK |
mysql5.7_1  | +----+
mysql5.7_1  | 1 row in set (0.00 sec)
mysql5.7_1  | 
mysql5.7_1  | Bye
mysql5.7_1  | install done
mysql5.7_1  | 
...

  • やっていること

    • innodb_memcacheデータベースのセットアップ

    • memcached APIに必要なコンフィグテーブルのセットアップ

      • cache_policies
      • containers
      • config_options
    • テストデータベースtestのセットアップ
  • コンフィグテーブルとデモテーブルの確認
mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.00 sec)


mysql> SELECT * FROM test.demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)
  • innodb_memcache.containersには、どのスキーマのどのテーブルをどのように解釈するかの情報が格納される
  • test.demo_testの定義と照らす
mysql> DESC test.demo_test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1    | varchar(32)         | NO   | PRI | NULL    |       |
| c2    | varchar(1024)       | YES  |     | NULL    |       |
| c3    | int(11)             | YES  |     | NULL    |       |
| c4    | bigint(20) unsigned | YES  |     | NULL    |       |
| c5    | int(11)             | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
  • PK, VARCHARなキーが必要

  • 3つの管理カラムが必要

    • フラグ
    • 連番
    • expiration timestamp

動作確認

  • telnetでササッと

    • mysqlコンテナにはtelnetが入っていないので適宜aptで入れる
root@db45fbb2747e:/# telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
  • AAキーによるデータ取得
get AA
VALUE AA 8 12
HELLO, HELLO
END
  • BBキーによるデータ挿入
set BB 10 0 16
GOODBYE, GOODBYE
STORED
  • SQLで読み取ってみる
mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2               | c3   | c4   | c5   |
+----+------------------+------+------+------+
| AA | HELLO, HELLO     |    8 |    0 |    0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |    0 |
+----+------------------+------+------+------+
2 rows in set (0.00 sec)
  • memcached APIのsetの意味
set BB 10 0 16
GOODBYE, GOODBYE
  • BBキーのレコードの値にGOODBYE, GOODBYEをセット

  • 10: 操作のフラグ

    • memcachedには無視される
  • 0: expiration time (TTL)
  • 16: ブロックバイト長
  • expiration timeのテスト
set CC 10 10 15
EXPIRATION DEMO
STORED
  • 10秒すると読めなくなる
get CC
VALUE CC 10 15
EXPIRATION DEMO
END

get CC
VALUE CC 10 15
EXPIRATION DEMO
END

get CC
END
  • SQLで読むと
mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------------+
| c1 | c2               | c3   | c4   | c5         |
+----+------------------+------+------+------------+
| AA | HELLO, HELLO     |    8 |    0 |          0 |
| BB | GOODBYE, GOODBYE |   10 |    1 |          0 |
| CC | EXPIRATION DEMO  |   10 |    2 | 1591403745 |
+----+------------------+------+------+------------+
3 rows in set (0.00 sec)

  • c4: 連番

    • setするたびに増える
  • c5: EXPIREする時刻のUNIXタイムスタンプ

【補】memcached APIのトランザクション分離レベル

  • READ UNCOMMITTED.

    • Dirty Readがおきるので注意する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.demo_test VALUES('DD', 'INSERTED VIA SQL', 12, 8, 0);
Query OK, 1 row affected (0.00 sec)
  • commitしない状態でmemcached APIからgetで読める
get DD
VALUE DD 12 16
INSERTED VIA SQL
END
  • rollbackするともちろん読めなくなる
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
get DD
END