クエリーキャッシュはカレントデータベースやキャラクターセットも区別する

クエリーキャッシュに載ってるかどうかの判定はSQLがパースされる前に比較されるから、SELECTステートメントは一字一句同じでないとクエリキャッシュ利かないよ、というのは有名な話。

http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

取り敢えず↑に答えが書いてあったがメモ。

MariaDBのinformation_schema.QUERY_CACHE_INFOをいじっていて気が付いたんだけど、クエリーキャッシュってカレントデータベースも区別してクエリキャッシュに登録してる。
（↓の出力は、そのプラグインをMySQL5.5向けに書き直したやつだけど）

mysql55> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql55> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql55> USE d1; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> USE information_schema; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> USE mysql; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> SELECT * FROM information_schema.query_cache_info;
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
| STATEMENT_SCHEMA   | STATEMENT_TEXT              | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED |
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
| d1                 | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| information_schema | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| mysql              | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
3 rows in set (0.01 sec)

mysql55> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 3     |
+-------------------------+-------+
1 row in set (0.01 sec)

とはいえこれはよく考えれば当たり前で、↑のクエリはたまたまデータベース名まで修飾してるけど、テーブル名だけの場合はカレントデータベースが違えば当然別のクエリだもんね。
SQLステートメントの大文字小文字が違っただけで別のクエリーとしてキャッシュに押し込むsql/cache.ccが、データベース名まで修飾されてるからってよしなにやってくれるとは当然思えないので、これはこれで良い。

でもでも。

さっきのマニュアル、キャラクターセットやプロトコル(MySQLプロトコル)のバージョンも区別するって書いてあるぞ。。。

mysql55> SET NAMES sjis;
Query OK, 0 rows affected (0.00 sec)

mysql55> USE d1; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> USE information_schema; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> USE mysql; SELECT * FROM d1.t1 LIMIT 2;
Database changed
+-----+------+
| num | val  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql55> SELECT * FROM information_schema.query_cache_info;
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
| STATEMENT_SCHEMA   | STATEMENT_TEXT              | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED |
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
| d1                 | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| information_schema | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| mysql              | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| information_schema | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| d1                 | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
| mysql              | SELECT * FROM d1.t1 LIMIT 2 |                   1 |                512 |                     177 |
+--------------------+-----------------------------+---------------------+--------------------+-------------------------+
6 rows in set (0.02 sec)

mysql55> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 6     |
+-------------------------+-------+
1 row in set (0.00 sec)

うええええ、ホントだ。これ知らなかった。
確かにbyte単位でSQLステートメントを比較してるだけなら、キャラクターセットが違ったら一致しないねぇ。

プロトコルバージョンの違いは、どのバージョンで変わってるのかよく判らなくて断念。
取り敢えず4.0.30だとプロトコルバージョン違いそうなんだけど、Authのアレで蹴られるので試せず。

最近クエリーキャッシュ漬け。

CREATE TEMPORARY TABLEとレプリケーション

マスターでこんな処理をしている時の話。

mysql> CREATE TEMPORARY TABLE this_is_tmp_table (..);
mysql> INSERT INTO this_is_tmp_table SELECT .. FROM this_is_origin_table WHERE ..;
mysql> UPDATE this_is_tmp_table SET ..;
mysql> INSERT INTO this_is_summary_table SELECT .. FROM this_is_tmp_table;
mysql> DROP TEMPORARY TABLE this_is_tmp_table;

テンポラリーテーブルを使ってごにょごにょした後に、
テンポラリーじゃないテーブルにデータを書き出すような処理。

テンポラリーテーブルは
・datadirではなくtmpdirにデータ実体が吐かれる(ibdata1は別として)
・CREATE TEMPORARY TABLEしたスレッド以外からは見えない
・CREATE TEMPORARY TABLEしたスレッドが接続を閉じれば消される
ような特性を持っている関係上、↑のような処理をレプリケーションしていると、
CREATE TEMPORARY TABLEからDROP TEMPORARY TABLEの間で
SQL_Threadが終了(STOP SLAVEやmysqldの再起動を含めて)してしまうと、
ステートメントベースのレプリケーションではそのままレプリケーションが再開できなくなる。

たとえば、↑の3行目の処理中にSTOP SLAVEしたりすると、
次にSTART SLAVEした時に

2013-06-18 21:44:30 8865 [ERROR] Slave SQL: Error 'Table 'd1.this_is_tmp_table' doesn't exist' on query. Default database: 'd1'. Query: 'INSERT INTO this_is_summary_table SELECT * FROM this_is_tmp_table', Error_code: 1146

となる。
この動作はbinlog_format = STATEMENTの時に起こり得る。

binlog_format = MIXEDだと、

$ mysqlbinlog -vv relay.000018
..
### INSERT INTO `d1`.`this_is_tmp_table`
### SET
###   @1=999990 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* LONGINT meta=96 nullable=1 is_null=1 */
..

のように、テンポラリーテーブルを使ってる周りはレプリケーションアンセーフだと判定して、
ROWモードでの書き出しになる。
なので、この場合は途中で止めてもなんとかなる。

5.1で推しだったbinlog_format = MIXEDは、5.5からは暗黙のデフォルトでは`ない'ので注意。
(binlog_formatの暗黙のデフォルトはSTATEMENTに戻った。5.1の中盤～後半だけ暗黙のデフォルトがMIXED)

時間泥棒なMySQL Clusterのmemcached APIを起動してみる

ちょっと触ってみた。
使ったのはMySQL Cluster 7.3.1-m1(開発版)

( ´-｀).oO（って書いてる間に、7.3.2がGAってリリースノートを見た。まだDownloadには来てない。


取り敢えずconfig.iniを最小限で作ってみる。

$ vim config.ini
[NDB_MGMD]
NodeId=49
HostName=localhost
DataDir=/home/yoku/mysql/ndb_7.3.1/mgmd
Portnumber=1186

[NDBD]
NodeId=1
HostName=localhost
DataDir=/home/yoku/mysql/ndb_7.3.1/ndbd/1

[NDBD]
NodeId=2
HostName=localhost
DataDir=/home/yoku/mysql/ndb_7.3.1/ndbd/2

[MYSQLD]
NodeId=51
HostName=localhost

[API]
NodeId=52
HostName=localhost

ディレクトリは自動で作ってくれないので自分で作ってから、ndb_mgmd起動。

$ mkdir -p mgmd ndbd/1 ndbd/2
$ bin/ndb_mgmd --initial --ndb-nodeid=49 --config-file=./config.ini

起動すればそれだけでバックグラウンドに回ってくれる。
続いてndbd。2つ。

$ bin/ndbd --initial --ndb-nodeid=1 --ndb-connectstring=localhost
$ bin/ndbd --initial --ndb-nodeid=2 --ndb-connectstring=localhost

おなじみmysqld。

$ vim my.cnf
[mysqld]
loose-ndbcluster
loose-ndb-connectstring= localhost:1186
log-error= error.log
log-bin= bin

$ scripts/mysql_install_db --datadir=./data
$ bin/mysqld_safe --defaults-file=./my.cnf --datadir=./data &

$ bin/ndb_mgm
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @127.0.0.1  (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master)
id=2    @127.0.0.1  (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=49   @127.0.0.1  (mysql-5.6.10 ndb-7.3.1)

[mysqld(API)]   2 node(s)
id=51   @127.0.0.1  (mysql-5.6.10 ndb-7.3.1)
id=52 (not connected, accepting connect from localhost)

ここまではOK。。（このあとかなりハマった。。）

NDB memcached EngineはInnoDB Memcached Pluginと同じく、
予めそれ用のテーブルを作っておく必要があるので、さっくり食わせる。

$ bin/mysql -uroot < share/memcache-api/ndb_memcache_metadata.sql
$ bin/mysql -uroot -e "SELECT * FROM ndbmemcache.containers"
+------------+-------------+------------------+-------------+----------------+-------+------------------+------------+--------------------+-----------------------------+
| name       | db_schema   | db_table         | key_columns | value_columns  | flags | increment_column | cas_column | expire_time_column | large_values_table          |
+------------+-------------+------------------+-------------+----------------+-------+------------------+------------+--------------------+-----------------------------+
| demo_table | ndbmemcache | demo_table       | mkey        | string_value   | flags | math_value       | cas_value  | NULL               | NULL                        |
| demo_tabs  | ndbmemcache | demo_table_tabs  | mkey        | val1,val2,val3 | flags | NULL             | NULL       | expire_time        | NULL                        |
| demo_ext   | ndbmemcache | demo_table_large | mkey        | string_value   | flags | NULL             | cas_value  | NULL               | ndbmemcache.external_values |
+------------+-------------+------------------+-------------+----------------+-------+------------------+------------+--------------------+-----------------------------+

InnoDB Memcached Pluginと同じように、メタデータを放り込むテーブルに色々定義してやる感じ。
InnoDBのアレはINSTALL PLUGINして再起動だけど、こっちはmemcachedが別プロセスになるのでひょっこり起動してやる。

$ bin/memcached -E lib/ndb_engine.so -e "connectstring=localhost:1186" &
18-Jun-2013 14:41:41 JST NDB Memcache 5.6.10-ndb-7.3.1 started [NDB 7.3.1; MySQL 5.6.10]
Contacting primary management server (localhost:1186) ...
Connected to "localhost:1186" as node id 52.
Retrieved 4 key prefixes for server role "default_role".
The default behavior is that:
    GET uses NDB only
    SET uses NDB only
    DELETE uses NDB only.
The 3 explicitly defined key prefixes are "b:" (demo_table_large), "mc:" () and "t:" (demo_table_tabs)
Server started with 4 threads.
Priming the pump ...
   Failed to grow connection pool.
Scheduler: using 1 connection to cluster 0
Scheduler: starting for 1 cluster; c0,f0,g1,t1
done [14.838 sec].

なぜか-dオプションでバックグラウンド起動させると上手くいかない(´･ω･｀)

$ bin/ndb_mgm -e "SHOW"
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @127.0.0.1  (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0, Master)
id=2    @127.0.0.1  (mysql-5.6.10 ndb-7.3.1, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=49   @127.0.0.1  (mysql-5.6.10 ndb-7.3.1)

[mysqld(API)]   2 node(s)
id=51   @127.0.0.1  (mysql-5.6.10 ndb-7.3.1)
id=52   @127.0.0.1  (mysql-5.6.10 ndb-7.3.1)


$ less/mgmd/ndb_49_cluster.log
..

2013-06-18 14:41:40 [MgmtSrvr] INFO     -- Node 1: Communication to Node 52 opened
2013-06-18 14:41:41 [MgmtSrvr] INFO     -- Node 2: Communication to Node 52 opened
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Nodeid 52 allocated for API at 127.0.0.1
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Node 52: memcached
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Node 1: Node 52 Connected
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Node 2: Node 52 Connected
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Node 2: Node 52: API mysql-5.6.10 ndb-7.3.1
2013-06-18 14:41:42 [MgmtSrvr] INFO     -- Node 1: Node 52: API mysql-5.6.10 ndb-7.3.1
2013-06-18 14:41:45 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 127.0.0.1. Returned eror: 'No free node id found for mysqld(API).'
2013-06-18 14:41:48 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 127.0.0.1. Returned eror: 'No free node id found for mysqld(API).'
2013-06-18 14:41:51 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 127.0.0.1. Returned eror: 'No free node id found for mysqld(API).'
2013-06-18 14:41:54 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 127.0.0.1. Returned eror: 'No free node id found for mysqld(API).'
2013-06-18 14:41:57 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 127.0.0.1. Returned eror: 'No free node id found for mysqld(API).'

コネクションプールが作れないとか行ってたのは、APIノードの定義を2つしか作ってないから、
mysqldで1個、memcachedで1個しか使えなくてこうなってるのかな。


ともあれこれで

$ telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
set name 0 0 8
yoku0825
Pipeline 0 attached to S scheduler.
STORED
get name
VALUE name 0 8
yoku0825
END
^]
telnet> quit
Connection closed.

$ bin/mysql -uroot -e "SELECT * FROM ndbmemcache.demo_table"
+------+------------+-------+---------------+--------------+
| mkey | math_value | flags | cas_value     | string_value |
+------+------------+-------+---------------+--------------+
| name |       NULL |     0 | 6129992073216 | yoku0825     |
+------+------------+-------+---------------+--------------+

ｲｪｰｲ。

MySQL 5.7 クライアントの新機能（？）

ちょっと感動したMySQL 5.7.1 クライアント(mysqldではない)の新機能。


まずは取り敢えずこのBugs。

MySQL Bugs: #66583: Ctrl-C behavior violates principle of least astonishment http://bugs.mysql.com/bug.php?id=66583

mysqlコマンドラインクライアントに向かってCtrl+Cを叩くとmysqlコマンドラインクライアントが落ちるという、
ごくごく馴染みの動作に対するFeature Request。

MySQL 4.1.6からは--sigint-ignoreというオプションで(そんな昔からあったのか)
SIGINTそのものを無効化してmysqlコマンドラインクライアントが終了しないようにできるものの、
これだとクエリを止めようと思ってもCtrl+Cで止まらなくなる。

$ mysql56 --sigint-ignore
..
mysql56> SELECT SLEEP(10);
^C^C^C^C^C^C^C^C^C+-----------+
| SLEEP(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

(´･ω･｀) ｼｮﾎﾞｰﾝ

だが、MySQL 5.7.0以降では、SIGINTは無効化せず、
SIGINTでmysqlコマンドラインクライアントが終了する動作だけをハンドルしてくれるようになったのだ！
（何もオプション要らない。デフォルト。むしろ--sigint-ignore使うと↑と同じ動作になる）

$ mysql57 -S/usr/mysql/5.6.12/data/mysql.sock
..
mysql57> SELECT SLEEP(10);
^C^C -- query aborted
+-----------+
| SLEEP(10) |
+-----------+
+-----------+
1 row in set (0.51 sec)

mysql57> ^C
mysql57> ^C

mysqldの機能ではなく、mysqlコマンドラインクライアントの機能です。


ところでMySQL 5.7.1って、もうm11(milestone 11)なのね。
MySQL 5.6.6がm9でその次がMySQL 5.6.7-rc(release candidate)だったので、
結構もりもりやってると期待して良いのかしら。


【2013/06/13 17:44】
くだらないこと書いてやったぜー、と思っていたら、御大が既に書いていらっしゃった。。orz

http://nippondanji.blogspot.jp/2013/05/mysqlmysql-57.html

Amazon RDSでできないかも知れないことメモ

Amazon RDSのrootユーザーからは
SHUTDOWN, FILE, SUPER, REPLICATION SLAVE, CREATE TABLESPACEが
取り上げられているので、これでできなくなるであろうことのメモ。


Shutdown_priv
　mysqladmin shutdownでmysqldをシャットダウンできない。

File_priv
　LOAD DATA INFILEステートメントが使えない。
　SELECT .. INTO OUTFILEステートメントが使えない。
　LOAD_FILE関数が使えない。
　　このページを見ていて知ったんだけれど、mysqldに--secure-file-privというオプションがあって、
　　これにディレクトリ名を渡すと、
　　そのディレクトリ{から|に}だけ{LOAD DATA|SELECT .. INTO OUTFILE|LOAD_FILE}できる
　　というように制限をかけられるんだそうな。
　　コンマ区切りとか2行書くとか試してみたけど、複数指定は不可能ぽい。

Super_priv
　レプリケーションスレーブになれない。
　　(CHANGE MASTER TO, {START|STOP} SLAVEできない)
　自分のスレッド以外をKILL(ステートメントの方ね), mysqladmin killできない。
　　（自分のスレッドはSUPERなしでもできるのか。。
　PURGE MASTER LOGSできない。
　SET GLOBALできない。
　mysqladmin debugできない。
　DEFINER属性を指定できない。
　max_connectionsに到達したときの+1コネクションがない。
　binlogが有効な場合、ストアドファンクションの{CREATE|DROP}ができない。
　　（log-bin-trust-function-creators使えば作れる）
　SET @@sql_log_bin, SET @@binlog_formatできない。
　{CREATE|ALTER|DROP} SERVERできない。
　　（いや、FEDERATEDエンジン使えるのか知らないけど）
　
Repl_slave_priv
　SHOW BINLOG EVENTSできない。
　SHOW SLAVE HOSTSできない。
　レプリケーションスレーブ作れない（GRANTできないから）

Create_tablespace_priv
　CREATE TABLESPACEできない…けどこれndbclusterのやつだから特に困らないはず。


少なくともこれだけの動作が(自前のMySQL rootに比べて)できなくなると思う。

なんかやたら推し量る系の言い回しなのは、RDSなんか変だから。
DROP USERした瞬間からそのユーザーの権限が無効になったり
　（フツーのMySQLは次にログインしたときかUSEでデータベース変えた時に効くはず）
なんか手を入れてる気がする。
バージョンもSource Distributionだし。。

MySQL 5.6.12の--connect-expired-passwordオプション

MySQL 5.6.12のChangeLogを読んでいて不思議に思った--connect-expired-passwordオプションのメモ。

オプションの説明を読んでもいまいちピンと来なかった。

どうやらpassword_expiredが'Y'になっている(rpmとかでインストールした時はこうなる)と、
非対話モードでのログインができないらしい。

とりあえずためす。

mysql56> grant all on *.* to test;
Query OK, 0 rows affected (0.05 sec)

mysql56> alter user test password expire;
Query OK, 0 rows affected (0.00 sec)

$ mysql -utest -e "select current_user()"
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

# mysql -utest --connect-expired-password -e "select current_user()"
ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement

おおー、ホントだー。

これがあるからmysql_secure_installation
(Perl、内部でsystem("mysql .. < $command");って呼び方をしてる)
が動かなくて、今回動くようになったっていうBug Fixなのか。。

にしても、mysql_secure_installation使ってる人いるのかな？
個人的には手間がなくて便利なんだけれども、gtid-mode=ONにしてると怒られるから
(MyISAMなmysql.userテーブルを直接叩くので、gitd-mode=ONだとunsafeって言われて転ける)
使わなくなっちゃった感じです。5.5までは常用してた。

mysqld_safeは如何にしてmysqldのダウンを検知するのか

LinuxとかUnixのMySQL-serverにくっついてくるmysqld_safe、コイツは内部でmysqldを呼ぶ

シェルスクリプトになっていて、環境変数の面倒とかulimitの面倒とかumaskの面倒とか見てくれたりする。

mysqldが落ちるとmysqld_safeがそれを検知して起動してくれるんだけど、

今までコイツがどうやってmysqldダウンを検知してるんだか知らなかった。

で、よくよく読んでみると、なんと

　フ　ォ　ア　グ　ラ　ウ　ン　ド　で　m　y　s　q　l　d　を　起　動　し　て　る

から、mysqldが落ちた途端にmysqld_safeに制御が戻って、

(mysqldを起動した(=eval_log_error関数)直後にリスタートの処理が書いてある)

ほぼノータイムでmysqldのダウンを検知する。

まさかこんなに当たり前でシンプルな作りだとは。なんかシグナルもらってるんだとずっと思ってたよ。。

あと、mysqld_safeってSIGTERMで落ちないようになってるのね。

そういえばオプション無しのkillで落ちなかった気がする。