2016-11-29

MySQLのInnoDBセカンダリインデックスのロック

MySQL

MySQLのセカンダリインデックスのロックで嵌ったのでメモ。

isolationがREPEATABLE-READのときギャップロックやらネクストキーロックはセカンダリーインデックスの場合どうなるかよくわかってなかった。

例えば、こんなテーブル作ってcol2にインデックスを作成する。で、インデックスのロックを見る。

CREATE TABLE `t1` (
  `col_pk` int(11) NOT NULL AUTO_INCREMENT,
  `col2` int(11) DEFAULT NULL,
  `col3` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`col_pk`),
  KEY `col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

データはこんな。PKが3は空いている状態。

insert into t1(col_pk,col2,col3) values (1,10,'AAA');
insert into t1(col_pk,col2,col3) values (2,20,'AAA');
insert into t1(col_pk,col2,col3) values (4,40,'AAA');
insert into t1(col_pk,col2,col3) values (5,50,'AAA');
insert into t1(col_pk,col2,col3) values (6,60,'AAA');

+--------+------+------+
| col_pk | col2 | col3 |
+--------+------+------+
|      1 |   10 | AAA  |
|      2 |   20 | AAA  |
|      4 |   40 | AAA  |
|      5 |   50 | AAA  |
|      6 |   60 | AAA  |
+--------+------+------+

これで、
UPDATE t1 SET col3='ZZZ' WHERE col2=50;
した場合どの範囲でロックが取得されるか。 InnoDB Lock Monitorで確認する。

---TRANSACTION 14903, ACTIVE 3 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 71, OS thread handle 0x7fcab3430700, query id 957 localhost root cleaning up
TABLE LOCK table `oo`.`t1` trx id 14903 lock mode IX

★１
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 4; hex 80000005; asc     ;;

★２
RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap　
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000003a37; asc     :7;;
 2: len 7; hex 55000001cc0312; asc U      ;;
 3: len 4; hex 80000032; asc    2;;
 4: len 3; hex 5a5a5a; asc ZZZ;;

★３
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000003c; asc    <;;
 1: len 4; hex 80000006; asc     ;;

３つの種類のRECORD LOCKSが確認できる。まずその内容の理解。
まず★１

RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 4; hex 80000005; asc     ;;

index `col2`がcol2のインデックス
lock_mode Xがネクストキーロック
0: len 4; hex 80000032; asc 2;;がcol2の値の16進数(16進数で32は10進数で50)
よって、col2インデックスの値50に対してネクストキーロック取得。

続いて★２

RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000003a37; asc     :7;;
 2: len 7; hex 55000001cc0312; asc U      ;;
 3: len 4; hex 80000032; asc    2;;
 4: len 3; hex 5a5a5a; asc ZZZ;;

index `PRIMARY` がプライマリーキー
lock_mode X locks rec but not gap がレコードロック
0: len 4; hex 80000005; asc ;;がプライマリーキーの値の16進数(16進数で5は10進数で5)
よって、プライマリーキーの値5に対してレコードロック取得。

最後★３

RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000003c; asc    <;;
 1: len 4; hex 80000006; asc     ;;

index `col2`がcol2のインデックス
lock_mode X locks gap before recがギャップロック
0: len 4; hex 8000003c; asc <;;がcol2の値の16進数(16進数で3cは10進数で60)
よって、col2インデックスの値60までギャップロックを取得。

これをまとめると、

col2 40  ------------------------- 50   ---------------------------   60
       |                             |                             |
     　   値50のネクストキーロック          値60ギャップロック    


primary key 5にレコードロック

col2への挿入は問答無用で41～59まではロックされるのはわかっていたが、40と60でも挿入できないパターンがあった。
別トランザクションを起動して歯抜けだったPKの3と最大値の7でそれぞれ実行できるか待機するか確認。

SQL	col2の値	結果
insert into t1(col_pk,col2,col3) values (7,39,'AAA');	39	OK
insert into t1(col_pk,col2,col3) values (7,40,'AAA');	40	待機
insert into t1(col_pk,col2,col3) values (7,41,'AAA');	41	待機
insert into t1(col_pk,col2,col3) values (7,59,'AAA');	59	待機
insert into t1(col_pk,col2,col3) values (7,60,'AAA');	60	OK
insert into t1(col_pk,col2,col3) values (3,40,'AAA');	40	OK
insert into t1(col_pk,col2,col3) values (3,41,'AAA');	41	待機
insert into t1(col_pk,col2,col3) values (3,59,'AAA');	59	待機
insert into t1(col_pk,col2,col3) values (3,60,'AAA');	60	待機
insert into t1(col_pk,col2,col3) values (3,61,'AAA');	61	OK

PKの3に対してはcol2の41～60が待機
PKの7に対してはcol2の40～59が待機
という結果に。41～59だけだと思ってたのでここで嵌った。

これは、セカンダリーインデックスはリーフノードにプライマリーキーを持つのでロックの範囲がそのプライマリーキーも意識しないとだめなようだった。

         　     値50のネクストキーロック          値60ギャップロック       
col2      40  ------------------------- 50   ---------------------------   60

          |                             |                                  |

col_pk    4  -------------------------  5   -----------------------------  6
       　　　　　↑　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
            (7,40,'AAA')待機                                         (3,60,'AAA')待機
　　　↑                                                      　　　                      ↑
  (3,40,'AAA')OK　　　　　　　　　　　　　　　　　　　　　　　　　    　　　　　　　(7,60,'AAA')OK

こうなっているわけだから、
(7,40,'AAA')はcol_pk:4より大きいため待機するが(3,40,'AAA')はcol_pk:4より小さいので挿入可能。
(3,60,'AAA')はcol_pk:6より小さいため待機するが(7,60,'AAA')はcol_pk:6より大きいので挿入可能。

参考
ギャップロックやらネクストキーロックはこちらの記事で大変理解できました。
InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる

2016-09-26

MySQLにgdbアタッチするメモ

MySQL

MySQLにgdbアタッチしてソースコードからいろいろ追う方法を教わったので自分メモ。

gdbアタッチ

gdb -p mysqldのプロセス番号

この時点でmysqldが一時停止される。

ブレイクポイント

(gdb) b function_name

mysqld再開

(gdb) c

ステップ実行

(gdb) n

変数確認

(gdb) p 変数名
(gdb) display 変数名

thread確認

(gdb) thread apply all bt
(gdb) frame スレッド番号
(gdb) bt

その他

innodb_prefixのサーバ変数はha_innodb.ccを見る。あとは変数名をもとに関数のあたりをつける。
print do_command::thd->query_string.string.strクエリ確認。
ファイルディスクリプター/proc/プロセス番号/fdやlsof -p プロセス番号で確認してstraceで追うstrace -f -e write,open -p プロセス番号 2>&1 | egrep -v 'write\(4[01], 'みたいな。

追記 20190530

MySQL5.7以降ぐらいからクエリ確認方法は

print do_command::thd->m_query_string

文字列が削られる場合はをつける

 set print elements 0

2016-09-14

Oracle SEでの運用いろいろ@JPOUG in 15 minutes #1

ORACLE SE

JPOUG in 15 minutes #1 | Japan Oracle User Group (JPOUG)で自分がやってきたOracle SEでの運用方法について話をさせてもらいました。

Oracle Database Standard Editionでの運用いろいろ from kenken0807

www.slideshare.net

主な話としてはSEでASHっぽく分析する仕組みを作った話だったのですが、
使用したツールはMySQLでデータストアしてMUNINでグラフ化なんですが確実に最近の流行りな方法ではないです。。ｗ

あとは、最後にちょこっとご指摘いただいた
本番のOracle側でSQLを実行してるのか、セッション情報をストアしてるMySQL側でSQL実行しているか説明不足でしたので追記しました。
人に説明するのはやっぱり難しいなと痛感しました。

ともかく、今回でJPOUGで３回も登壇させてもらったのでSEネタの持ちカードはもうないっっ。。

2016-07-27

MyDumperを使ってみた

MySQL

MySQLのデータインポートツールMyDumperを試したのでメモ。

mysqldump・・・データのエクスポート・インポートをシングルスレッドで実行
mydumper・・・データのエクスポート・インポートをパラレルスレッドで実行

エクスポートしたファイルごとにパラレルで実行する。デフォルトはテーブル単位でパラレル化。
巨大なテーブルがあればデータをチャンクごとに分けファイルを分散することで高速に実行してくれる。

インストール

yumでインストール

yum install https://twindb.com/twindb-release-latest.noarch.rpm
yum install mydumper

確認

$ mydumper --version   
mydumper 0.6.2, built against MySQL 5.6.25

もし、libmysqlclient.so.18がないよ。って言われた場合はyumでMySQL5.6をインストール（5.1ではだめ）。
または、libmysqlclient.so.18がある場所にLD_LIBRARY_PATHで設定。

コマンド

mydumper・・・export用コマンド
myloader・・・import用コマンド

試す

DB容量：280G（内130Gが１つのテーブル）

export DB設定
- MySQL5.6
- stop slaveして実行する
import DB設定
- MySQL5.6
- --skip_innodb_doublewrite
- innodb_flush_log_at_trx_commit=0
- slow_query_log=OFF
- log_bin=OFF
- innodb_log_buffer_size=128M
- innodb_log_file_size=1G
- innodb_log_files_in_group = 3

EXPORT

10スレッドでGZ圧縮してチャンクサイズ10000000行で取得。
注意：exportはスキーマの指定はフルまたは1つのスキーマになってしまうので複数スキーマを指定することはできない。
-Tオプションでテーブル単位で指定可能。information_schema.tablesで複数のスキーマから取得する方法を教わったのでそちらで実行。

time mydumper -u root -p 'xxxxxxx' -S /var/lib/mysql/mysql.sock -e                   
     -t 10 -c -o /tmp/dump_dir 
     -r 10000000 
     -s 5000000 
     -T $(mysql  
        -sse"set group_concat_max_len=10240; 
        select aa from  (select 1,group_concat(concat(table_schema,'.',table_name)) as aa
        from information_schema.tables where table_schema in
       ('main','sub1','sub2','sub3','sub4') group by 1) a")

real    43m41.139s
user    220m16.990s
sys     3m0.312s

約44分

１スレッドかつチャンク分けなしでやった場合は約4時間20分かかった。

出力ファイル確認

できたファイルはこんな感じで分かれている。

$ ls -ltr /tmp/dump_dir 
合計 45721788
・
・
-rw-rw-r-- 1 mysql mysql        679  6月  1 16:00 2016 main.table1.sql.gz
-rw-rw-r-- 1 mysql mysql        169  6月  1 16:00 2016 main.table2.sql.gz
-rw-rw-r-- 1 mysql mysql        185  6月  1 16:00 2016 main.table3.sql.gz
-rw-rw-r-- 1 mysql mysql      76932  6月  1 16:00 2016 main.table4.sql.gz
-rw-rw-r-- 1 mysql mysql     208327  6月  1 16:00 2016 main.table5.sql.gz
-rw-rw-r-- 1 mysql mysql    1505960  6月  1 16:00 2016 sub1.subtable1.sql.gz
-rw-rw-r-- 1 mysql mysql  768276450  6月  1 16:06 2016 sub1.subtable2.00000.sql.gz
-rw-rw-r-- 1 mysql mysql  750226866  6月  1 16:06 2016 sub1.subtable2.00001.sql.gz
-rw-rw-r-- 1 mysql mysql  770132442  6月  1 16:07 2016 sub1.subtable2.00002.sql.gz

通常はテーブル単位でのファイルだが、チャンク分けしていると連番でファイルも分かれている。

スレーブとして起動したい場合はmetadataファイルを確認するとポジション情報が記載してある

$ vim /tmp/dump_dir/metadata

  1 Started dump at: 2016-06-01 16:00:13
  2 SHOW MASTER STATUS:
  3         Log: mysql-bin.000275
  4         Pos: 120
  5
  6 SHOW SLAVE STATUS:
  7         Host: 192.168.1.2
  8         Log: mysql-bin.000438
  9         Pos: 944084446
 10
 11 Finished dump at: 2016-06-01 16:39:00

IMPORT

16スレッドで実行

$ time myloader -d /tmp/dump_dir  -u root -p 'xxx' -S /var/lib/mysql/mysql.sock -t 16 

real    525m31.015s
user    299m14.828s
sys     3m8.467s

約8時間45分

チャンク分けなしのものを16スレッドで実行した場合。結局全DB量の半分近くを占めているテーブルがシングル実行になるので遅い。

$ time myloader -d /tmp/dump_dir  -u root -p 'xxx' -S /var/lib/mysql/mysql.sock -t 16 

real    1541m14.572s
user    245m43.989s
sys     2m23.104s

約25時間

よって、チャンク分けをうまくすればmydumper結構使える。

注意

ibdata1のサイズが大きくなる。
50%はinsert bufferが占めていた。セカンダリインデックスを後で貼るようにしてくれたらいいのにな。

 *8.1G*  6月  3 01:01 2016 ibdata1   こうなるので注意。

export時にテーブル指定でもimport時データベース作成してしてくれる。
ファイル名の先頭を読んでcreate databaseする。↓のような感じ。
main.table1.sql.gz → create database main;
となるとデータベースごとにキャラクタセットが違うと危険かも・・・

2016-07-22

OEM再作成のエラー対処方法

ORACLE SE

RMANで別ホストに移行した時にOEMの作成でいろいろ嵌ったのでメモ。

emca -config dbcontrol db -repos recreate実行時

パターン１

設定: ORA-01031: ???????-??????????
・
・
・
2014/10/09 9:58:40 oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
設定: SQLEngine created successfully and connected
2014/10/09 9:58:40 oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
設定: ORA-01031: ???????-??????????
2014/10/09 9:58:40 oracle.sysman.emcp.EMConfig perform
情報: この操作は/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logでロギングされています。
2014/10/09 9:58:40 oracle.sysman.emcp.EMConfig perform
致命的: リスナーが起動していないか、データベース・サービスがリスナーに登録されていません。リスナーを起動し、データベース・サービスを登録し、EM Configuration Assistantを再度実行してください。
詳細は、/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logにあるログ・ファイルを参照してください。
構成を完了できませんでした。 詳細は、/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logにあるログ・ファイルを参照してください。

設定: ORA-01031: ???????-??????????→権限が不足しているエラー
パスワードファイルがなかったのでをコピーまたは作成してあげて再実行

パターン２

・
・
2014/10/14 18:59:33 oracle.sysman.emcp.ParamsManager getLocalListener
警告: oracle-test-dbのリスナーを取得中にエラーが発生しました
2014/10/14 18:59:37 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
情報: Database Controlの保護中(少し時間がかかります)...
・
・

listener.oraからデフォルトリスナーLISTENER(1521)の記述がなかった。

パターン３

2014/10/14 18:59:37 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
情報: Database Controlの保護中(少し時間がかかります)...
2014/10/14 18:59:41 oracle.sysman.emcp.util.PlatformInterface executeCommand
警告: /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -host oracle-test-db -sid orcl の実行中のエラ ー
2014/10/14 18:59:41 oracle.sysman.emcp.EMDBPostConfig performConfiguration
警告: Database Controlの保護中にエラーが発生しました。
2014/10/14 18:59:41 oracle.sysman.emcp.EMDBPostConfig setWarnMsg
情報: Database Controlの保護中にエラーが発生しました。Database Controlが非セキュア・モードで起動されています。Database Controlを保護するには、次のコマンドを実行してください。

 1) 環境変数ORACLE_UNQNAMEを一意のデータベース名に設定します
 2) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl stop dbconsole
 3) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -repos -sysman_pwd < SYSMANユーザーのパスワード >
 4) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -sysman_pwd < SYSMANユーザーのパスワード >
 5) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl start dbconsole

 EMキーを保護するには、/opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < SYSMANユーザーのパスワード >を実行します
2014/10/14 18:59:41 oracle.sysman.emcp.util.DBControlUtil startOMS
情報: Database Controlの起動中(少し時間がかかります)...
2014/10/14 18:59:51 oracle.sysman.emcp.EMDBPostConfig performConfiguration
情報: Database Controlは正常に起動されました
2014/10/14 18:59:51 oracle.sysman.emcp.EMDBPostConfig performConfiguration
情報: >>>>>>>>>>> Database ControlのURLはhttp://oracle-test-db:1158/emです <<<<<<<<<<<


Database Controlの保護中にエラーが発生しました。Database Controlが非セキュア・モードで起動されています。Database Controlを保護するには、次のコマンドを実行してください。

 1) 環境変数ORACLE_UNQNAMEを一意のデータベース名に設定します
 2) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl stop dbconsole
 3) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -repos -sysman_pwd < SYSMANユーザーのパスワード >
 4) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -sysman_pwd < SYSMANユーザーのパスワード >
 5) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl start dbconsole

 EMキーを保護するには、/opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < SYSMANユーザーのパスワード >を実行します

OEMの起動はできたがレポジトリが壊れていて接続できなかった。
- emca -deconfig dbcontrol db -repos drop
- emca -config dbcontrol db -repos createで解決

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

MySQLのInnoDBセカンダリインデックスのロック

MySQLにgdbアタッチするメモ

その他

追記 20190530

Oracle SEでの運用いろいろ@JPOUG in 15 minutes #1

MyDumperを使ってみた

インストール

コマンド

試す

EXPORT

出力ファイル確認

IMPORT

注意

OEM再作成のエラー対処方法

emca -config dbcontrol db -repos recreate実行時

パターン１

パターン２

パターン３