2013年2月25日月曜日

user@localhostとuser@127.0.0.1は別人?

ある日SH2先生がつぶやきました。
個人的にはMySQLのlocalhost ≠ 127.0.0.1と言われて育った世代なので、どうもしっくり来ない。。
mysqlクライアントもDBIで叩くときも(libmysqlclient.soの実装なのかDBI側の実装なのか知らないけど、PerlとPHPは少なくとも)
localhostだとsocketを叩きにいくし、127.0.0.1はTCPソケットを叩きにいく。
host部が違うのでパスワードも違うし、SHOW GRANTSの結果も違う = 別のユーザー って認識だったんですが。。

SH2さんがその流れで上げてくれたBugs

Bug #68436 user@127.0.0.1 is authorized partly as user@localhost.


と、マニュアル
http://dev.mysql.com/doc/refman/5.6/en/access-denied.html
http://dev.mysql.com/doc/refman/5.6/en/request-access.html


を読み比べてみると、
localhostは127.0.0.1ではないが、127.0.0.1はlocalhost(ややこし)
って感じだろうか。。

テストケースが付いているのでさそのまま試してみる。


GRANT ALL PRIVILEGES ON db1.* TO user@localhost IDENTIFIED BY 'pass1';
GRANT ALL PRIVILEGES ON db2.* TO user@127.0.0.1 IDENTIFIED BY 'pass2';
CREATE DATABASE db1;
CREATE DATABASE db2;
としてから、


$ mysql55 -h127.0.0.1 -uuser -ppass1
..
mysql55> select current_user();
+----------------+
| current_user() |
+----------------+
| user@localhost |
+----------------+
1 row in set (0.00 sec)

mysql55> show grants;
+-------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*22A99BA288DB55E8E230679259740873101CD636' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'user'@'localhost'                                                       |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


mysql55> use db2
Database changed

mysql55> create table tbl1 ( num serial );
Query OK, 0 rows affected (0.34 sec)

mysql55> show create table tbl1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1  | CREATE TABLE `tbl1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


おー、行った行った。変な感じ。
これって、skip-name-resolveのあるなしで、

【skip-name-resolveしてない】
$ mysql -uu1 -h192.168.199.131
..
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| u1@mysqlha01   |
+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------+
| Grants for u1@mysqlha01                            |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'mysqlha01'             |
| GRANT ALL PRIVILEGES ON `d1`.* TO 'u1'@'mysqlha01' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use d1;
Database changed

mysql> use d2;
Database changed


【skip-name-resolveあり】
$ mysql -uu1 -h192.168.199.131
..
mysql> select current_user();
+--------------------+
| current_user()     |
+--------------------+
| u1@192.168.199.131 |
+--------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------+
| Grants for u1@192.168.199.131                            |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'192.168.199.131'             |
| GRANT ALL PRIVILEGES ON `d2`.* TO 'u1'@'192.168.199.131' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use d1;
ERROR 1044 (42000): Access denied for user 'u1'@'192.168.199.131' to database 'd1'

mysql> use d2;
Database changed

やっぱりこうなるよねー。。
GRANT(mysqlスキーマ)の段階でカブリがあると、
skip-name-resolveのあるなしでアクセス可能な領域が変わってくる。
これ、IPアドレスの方がネットマスク形式だったりワイルドカード使ってたりすると結構悲惨かも。。

心当たりのある方は、運用途中でskip-name-resolveをつける時は…って、
mysql.userにホスト名書いてある状態でskip-name-resolveは設定しないか。。



更についでに、skip-name-resolveを付けたり外したりして再起動していたら、

$ mysql -uu1 -h192.168.199.131
..
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| u1@mysqlha01   |
+----------------+
1 row in set (0.00 sec)

mysql> show variables like '%resolv%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> use d1;
Database changed

mysql> use d2;
Database changed
..

$ mysql -uu1 -hmysqlHA01
mysql> select current_user();
+--------------------+
| current_user()     |
+--------------------+
| u1@192.168.199.131 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like '%resolv%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> use d1;
Database changed

mysql> use d2;
Database changed


おうい。。大丈夫かねこれ。。

↓↓理屈としてはこうらしいですが。。

これ`Not a Bug'らしいですよ!
マニュアルにもうちょっと詳細記載してよ!」って投稿でしたが、ヌルーされてますねー。。

SH2さんに「ネタにして良いよ!」と快諾いただいたのでネタにさせていただきましたm(_"_)m
ありがとうございますー。

2013年2月22日金曜日

information_schema.INNODB_BUFFER_PAGEを覗いてみる

MySQL 5.6から搭載、5.5.28, 5.1.66にもバックポートされたinformation_schema.INNODB_BUFFER_PAGEを覗いてみました。




さてさて。
マニュアル読んでもよく判らなかったので、テキトーに流してみる。


mysql> SELECT PAGE_TYPE, TABLE_NAME, INDEX_NAME, SUM(NUMBER_RECORDS) AS NUMBER_RECORDS, SUM(DATA_SIZE) AS DATA_SIZE, SUM(COMPRESSED_SIZE) AS COMPRESSED_SIZE, SUM(IS_OLD = 'NO') AS OLD_PAGES, SUM(IS_OLD = 'YES') AS NEW_PAGES FROM innodb_buffer_page GROUP BY PAGE_TYPE, TABLE_NAME, INDEX_NAME;
+-------------------+------------------------------+------------------------------+----------------+-----------+-----------------+-----------+-----------+
| PAGE_TYPE         | TABLE_NAME                   | INDEX_NAME                   | NUMBER_RECORDS | DATA_SIZE | COMPRESSED_SIZE | OLD_PAGES | NEW_PAGES |
+-------------------+------------------------------+------------------------------+----------------+-----------+-----------------+-----------+-----------+
| FILE_SPACE_HEADER | NULL                         | NULL                         |              0 |         0 |            8192 |        12 |         5 |
| IBUF_BITMAP       | NULL                         | NULL                         |              0 |         0 |           24576 |        32 |         3 |
| INDEX             | d1/t1                        | PRIMARY                      |          34089 |   1019474 |               0 |        47 |        37 |
| INDEX             | d1/t2                        | idx_t2_01                    |          57785 |    983901 |          704512 |        25 |        61 |
| INDEX             | d1/t2                        | PRIMARY                      |           1480 |     45464 |           32768 |         3 |         1 |
| INDEX             | d1/t2                        | uidx_t2_01                   |           1347 |     13470 |            8192 |         1 |         0 |
| INDEX             | d1/t3                        | PRIMARY                      |           3888 |    145319 |               0 |         8 |         7 |
| INDEX             | d1/t3                        | uidx_t3_01                   |          10264 |    164356 |               0 |         8 |         7 |
| INDEX             | SYS_IBUF_TABLE               | CLUST_IND                    |              0 |         0 |               0 |         1 |         0 |
| INODE             | NULL                         | NULL                         |              0 |         0 |            8192 |        16 |         7 |
| SYSTEM            | NULL                         | NULL                         |              0 |         0 |               0 |       128 |         0 |
| TRX_SYSTEM        | NULL                         | NULL                         |              0 |         0 |               0 |         1 |         0 |
| UNDO_LOG          | NULL                         | NULL                         |              0 |         0 |               0 |       456 |       148 |
| UNKNOWN           | NULL                         | NULL                         |              0 |         0 |               0 |       222 |         0 |
+-------------------+------------------------------+------------------------------+----------------+-----------+-----------------+-----------+-----------+
14 rows in set (0.16 sec)

何の役に立つのかイマイチ判らないな。。
Innodb_buffer_pool_pages_freeが減ってきたときに、目当てのテーブルがバッファプールに載ってるかとか、それくらい?;

ま、いっか。

2013年2月21日木曜日

MySQL5.6での新しい暗黙のデフォルトを改めて

使ってみたりBugsに色々上がったりしているのを見たのでメモ。

ネタ元はOracle公式のここ。 MySQL Server 5.6 defaults changes

・binlog_checksum
⇒5.6からの新規パラメータ。
暗黙のデフォルトはcrc32だが、
マスターが5.6、スレーブが5.5以下の(定石を無視した)環境ではnoneでないとI/O Threadが転ける

・innodb_buffer_pool_instances
⇒5.5ではデフォルト1が、デフォルトautosized8に。
autosizedではinnodb_buffer_pool_sizeが1300M以上の時はinnodb_buffer_pool_size/128Mに設定されるらしい。
木下さんが昔「5.5では1から動かさない方が良いよ」って書いていたけれど、
Dimitriさんが5.6でやったやつを見ると使い物になりそうだし、
Vadimさんが5.6.7でやったやつを見ても、バッファプールが100GB超えるまでは安定して動きそう。

・innodb_file_per_table
⇒暗黙のデフォルトがONに。やったね。

・innodb_stats_on_metadata
⇒暗黙のデフォルトがOFFに。やったね。

・query_cache_type
⇒暗黙のデフォルトが0(OFF)に。
query_cache_typeはオンラインで変更可能だけれど、
0で起動したインスタンスを1, 2に変更することはできないので注意

・sort_buffer_size
⇒暗黙のデフォルト2Mから256Kへ。
filesortしてるけど2Mありゃ良いかーと思ってた環境は明示しないとなぁ。。

・sync_master_info/sync_relay_log/sync_relay_log_info
⇒0から10000へ。10,000イベント記録するたびにfdatasyncする、という意味。
10000イベントって結構簡単に行きそうな気がする。I/Oに負荷行くかも。


取り敢えず気が付いたところだけー。


【2013/02/21 12:25】


mysql56> show variables like 'innodb_buffer_pool%';+-------------------------------------+----------------+| Variable_name                       | Value          |+-------------------------------------+----------------+| innodb_buffer_pool_dump_at_shutdown | OFF            || innodb_buffer_pool_dump_now         | OFF            || innodb_buffer_pool_filename         | ib_buffer_pool || innodb_buffer_pool_instances        | 8              || innodb_buffer_pool_load_abort       | OFF            || innodb_buffer_pool_load_at_startup  | OFF            || innodb_buffer_pool_load_now         | OFF            || innodb_buffer_pool_size             | 2147483648     |+-------------------------------------+----------------+8 rows in set (0.00 sec)

Σ(゚д゚lll) 俺のところでもなってない!


storage/innobase/handler/ha_innodb.ccの中を覗いてみると、
---GPLのコードです---

 3027         if (innobase_buffer_pool_instances == 0) {
 3028                 innobase_buffer_pool_instances = 8;
 3029
 3030 #if defined(__WIN__) && !defined(_WIN64)
 3031                 if (innobase_buffer_pool_size > 1331 * 1024 * 1024) {
 3032                         innobase_buffer_pool_instances
 3033                                 = ut_min(MAX_BUFFER_POOLS,
 3034                                         (long) (innobase_buffer_pool_size
 3035                                         / (128 * 1024 * 1024)));
 3036                 }
 3037 #endif /* defined(__WIN__) && !defined(_WIN64) */
 3038         }

---GPLのコードここまで---

「8. On 32 bit Windows only, if innodb_buffer_pool_size is greater than 1300M, default is innodb_buffer_pool_size / 128M」

………8。 ただし、32bit Windowsだけ、ほげほげ~。

8, On 32bit Windows only. if ...ってコンマとピリオドを逆に読んでた。。ごめんなさい。。


【2013/02/26 10:05】
そういえば元ネタにしたページには載っていなかったけど、performanse_schemaもデフォルトで有効になりました。
performanse_schemaをそんなにカジュアルに使うか? というのと、
Bug #68413 performance_schema overhead is at least 10% こんなのもVerifyされているので、
基本的には明示的にOFFにした方が良いと思われ。。


【2013/03/08 18:37】

・join_buffer_size
 ⇒5.5はデフォルト128k、5.6では256k。
  性能悪くなることはないだろー、とタカをくくってたけど、
  コネクション数が多い環境だと無視できない差だなと気付いたので追記。

2013年2月14日木曜日

innodb_print_all_deadlocksを試してみる

MySQL5.6の新機能(といいつつ5.5.30にもバックポートされてる)、
一部で念願だったデッドロックのログ機能を試してみる。

my.cnfにごにょごにょ。

[mysqld]loose-innodb-print-all-deadlocks

オプション名の先頭にloose-って付けると、
そのオプションが存在しないバージョンでもエラーにならずにワーニングで済むんですよ!
 ⇒出典 かみぽわーる さん

起動して、取り敢えずデッドロックさせてみる。


$ less error.log2013-02-14 17:55:31 7fc810773700InnoDB: transactions deadlock detected, dumping detailed information.2013-02-14 17:55:31 7fc810773700*** (1) TRANSACTION:TRANSACTION 5638, ACTIVE 12 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)MySQL thread id 6, OS thread handle 0x7fc810732700, query id 55 localhost root statisticsselect * from t1 where num = 2 for update*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 4 n bits 320 index `num` of table `d2`.`t1` trx id 5638 lock_mode X locks rec but not gap waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 0000000000000002; asc         ;; 1: len 6; hex 000000001548; asc      H;; 2: len 7; hex bd000001640120; asc     d  ;; 3: len 0; hex ; asc ;;
*** (2) TRANSACTION:TRANSACTION 5639, ACTIVE 6 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 1248, 2 row lock(s)MySQL thread id 8, OS thread handle 0x7fc810773700, query id 56 localhost root statisticsselect * from t1 where num = 1 for update*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 11 page no 4 n bits 320 index `num` of table `d2`.`t1` trx id 5639 lock_mode X locks rec but not gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 0000000000000002; asc         ;; 1: len 6; hex 000000001548; asc      H;; 2: len 7; hex bd000001640120; asc     d  ;; 3: len 0; hex ; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 4 n bits 320 index `num` of table `d2`.`t1` trx id 5639 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 0000000000000001; asc         ;; 1: len 6; hex 000000001548; asc      H;; 2: len 7; hex bd000001640110; asc     d  ;; 3: len 0; hex ; asc ;; 

*** WE ROLL BACK TRANSACTION (2)
おおお、吐いた!
SHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKと同じ内容がまるっと取れる。
これは便利…か?

pt-deadlock-loggerはテーブルに吐くから…と思っている時には良いかも。

MariaDBみたいにシンプルに[Error] Deadlock found~みたいに、ログをチェックする用途には向かないと思う。
いや、Deadlockはエラーじゃないから正常系だからせめてWarningで出せよとか思う訳ですがまあ。。

130214 18:16:27 [ERROR] mysqld: Deadlock found when trying to get lock; try restarting transaction
これってXtraDB側の仕様で、Percona Serverでもこうだったりする?

2013年2月12日火曜日

MySQL5.6のマスターにMySQL5.5(とそれ以前)のスレーブをぶら下げるとエラる

ぱっと見、誰も書いてなさそうなので。

MySQL 5.6ではバイナリログのチェックサム(binlog_checksum)が追加され、
しかもそれが5.6.5以降デフォルトで有効になっているので、
5.5をスレーブにして構成するとI/O ThreadがErr:1236で止まる。

mysql55> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: replicator
Master_Port: 64056
Connect_Retry: 60
Master_Log_File: bin.000007
Read_Master_Log_Pos: 4
Relay_Log_File: relay.000004
Relay_Log_Pos: 144
Relay_Master_Log_File: bin.000007
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 327
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'bin.000006' at 120, the last event read from './bin.000007' at 120, the last byte read from './bin.000007' at 120.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1056
1 row in set (0.00 sec)
ワークアラウンドはもちろんbinlog_checksum = noneで起動することだけれども、
mysql56> SHOW GLOBAL VARIABLES LIKE 'binlog_checksum';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| binlog_checksum | CRC32 |
+-----------------+-------+
1 row in set (0.00 sec)

mysql56> SHOW MASTER STATUS;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000007 |      462 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql56> SET GLOBAL binlog_checksum = none;
Query OK, 0 rows affected (0.03 sec)

mysql56> SHOW GLOBAL VARIABLES LIKE 'binlog_checksum';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| binlog_checksum | NONE  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql56> SHOW MASTER STATUS;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000008 |      120 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

チェックサムはバイナリログ単位で有効/無効化されるため、SET GLOBALで変えるとバイナリログがスイッチする。
もちろん、今までbinlog_checksum = crc32で記録されたバイナリログはチェックサム有効の状態で記録されているので、mysqlbinlogで吸い上げて当てるしかない。

わざわざ互換性の無い方をデフォルトにしなくても…とも思いつつ、



おっしゃるとおりです。。


【2013/02/14 12:46】
これもともとは循環レプリケーション内で1台だけやったら…という話がMLに上がっていたので試したメモなのです。
http://lists.mysql.com/mysql/228945

あと、MariaDB10.0のマルチソースレプリを試そうとしたら、MariaDB10.0は5.5ベースなので5.6にそのままぶら下げたらエラったのもありました。

レアケースですよね。


【2013/02/18 10:57】
更に前方互換性をなくす、ROWモードでのバイナリ非互換もあったみたい。。

@i_rethiさんがbinlog_rows_query_log_eventsのお試し中に気付いたそうな。

2013年2月8日金曜日

MySQL 5.6のオンラインALTER TABLEを試してたらinnodb_online_alter_log_max_sizeに遭遇した

本当はpt-online-schema-changeとオンラインALTER TABLEでどう違うかを実験したかったんだけど、
innodb_online_alter_log_max_sizeなんてものの存在を初めて知ったので取り敢えずメモ。

tpcc-mysqlを20WH, 20Connsで流しながらALTER TABLEをする at 5.6.10。


mysql> set foreign_key_checks = 0; ALTER TABLE stock ADD test_col int unsigned not null default 0, ADD KEY (s_ytd);
Query OK, 0 rows affected (0.08 sec)

ERROR 1799 (HY000): Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

mysql> show variables like 'innodb_online_alter_log_max_size';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.11 sec)


…へぇ。デフォルト128MiB。
対象テーブルはデータとインデックス合わせて800MiBくらい。

SELECT .. FOR UPDATEでロックしてからUPDATEがかかるトランザクションがごりごりかかるテーブル。


ALTER TABLE中に入ってきた更新を溜めておくテンポラリファイルっぽい。
更新量が多いとこれが溢れるのか。。
大きめに変えておいた方が良いかも。

誰か調べたら教えて下さい。