![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MYSQLの運用・管理ツール検証
最新版のPercona Toolkitをダウンロード
http://www.percona.com/downloads/percona-toolkit/LATEST/
[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz --2013-01-31 14:03:47-- http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz www.percona.com をDNSに問いあわせています... 74.121.199.234 www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 302 Found 場所: /downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz [続く] --2013-01-31 14:03:47-- http://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz www.percona.com:80 への接続を再利用します。 HTTP による接続要求を送信しました、応答を待っています... 200 OK 長さ: 1477253 (1.4M) [application/x-gzip] `percona-toolkit-2.1.8.tar.gz' に保存中 100%[=======================================================================>] 1,477,253 441K/s 時間 3.3s 2013-01-31 14:03:51 (441 KB/s) - `percona-toolkit-2.1.8.tar.gz' へ保存完了 [1477253/1477253] [root@HOME001 mysql]#
percona-toolkitのインストール
[root@HOME001 mysql]# tar -zxvf percona-toolkit-2.1.8.tar.gz percona-toolkit-2.1.8/ percona-toolkit-2.1.8/MANIFEST percona-toolkit-2.1.8/INSTALL percona-toolkit-2.1.8/Changelog percona-toolkit-2.1.8/docs/ percona-toolkit-2.1.8/docs/percona-toolkit.pod percona-toolkit-2.1.8/Makefile.PL percona-toolkit-2.1.8/README 省略... percona-toolkit-2.1.8/bin/pt-slave-restart percona-toolkit-2.1.8/bin/pt-query-digest percona-toolkit-2.1.8/bin/pt-online-schema-change percona-toolkit-2.1.8/bin/pt-fk-error-logger percona-toolkit-2.1.8/bin/pt-pmp percona-toolkit-2.1.8/bin/pt-stalk percona-toolkit-2.1.8/bin/pt-show-grants percona-toolkit-2.1.8/bin/pt-config-diff percona-toolkit-2.1.8/COPYING percona-toolkit-2.1.8/lib/ [root@HOME001 mysql]# [root@HOME001 mysql]# cd percona-toolkit-2.1.8 [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# perl Makefile.PL Checking if your kit is complete... Looks good Warning: prerequisite DBD::mysql 3 not found. Writing Makefile for percona-toolkit [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# cpan -i DBD::mysql CPAN: Storable loaded ok (v2.20) Going to read '/root/.cpan/Metadata' Database was generated on Thu, 31 Jan 2013 05:07:28 GMT Running install for module 'DBD::mysql' CPAN: Data::Dumper loaded ok (v2.124) 'YAML' not installed, falling back to Data::Dumper and Storable to read prefs '/root/.cpan/prefs' Running make for C/CA/CAPTTOFU/DBD-mysql-4.022.tar.gz CPAN: Digest::SHA loaded ok (v5.47) CPAN: Compress::Zlib loaded ok (v2.02) Checksum for /root/.cpan/sources/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.022.tar.gz ok CPAN: Archive::Tar loaded ok (v1.58) DBD-mysql-4.022/ 省略・・ Appending installation info to /usr/lib/perl5/perllocal.pod CAPTTOFU/DBD-mysql-4.022.tar.gz /usr/bin/make install -- OK Warning (usually harmless): 'YAML' not installed, will not store persistent state [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# perl -MDBD::mysql -e 'print "$DBD::mysql::VERSION\n"' 4.022 [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# perl Makefile.PL Writing Makefile for percona-toolkit [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# make cp bin/pt-mysql-summary blib/script/pt-mysql-summary /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mysql-summary cp bin/pt-kill blib/script/pt-kill /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-kill cp bin/pt-online-schema-change blib/script/pt-online-schema-change /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-online-schema-change cp bin/pt-trend blib/script/pt-trend 省略... Manifying blib/man1/pt-variable-advisor.1p Manifying blib/man1/pt-index-usage.1p Manifying blib/man1/pt-tcp-model.1p Manifying blib/man1/pt-duplicate-key-checker.1p Manifying blib/man1/pt-config-diff.1p Manifying blib/man1/pt-stalk.1p [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 percona-toolkit-2.1.8]# make install Installing /usr/local/share/man/man1/pt-tcp-model.1p Installing /usr/local/share/man/man1/pt-mysql-summary.1p Installing /usr/local/share/man/man1/pt-slave-delay.1p Installing /usr/local/share/man/man1/pt-log-player.1p Installing /usr/local/share/man/man1/pt-online-schema-change.1p 省略... Installing /usr/local/bin/pt-ioprofile Installing /usr/local/bin/pt-tcp-model Installing /usr/local/bin/pt-table-sync Installing /usr/local/bin/pt-find Installing /usr/local/bin/pt-kill Appending installation info to /usr/lib/perl5/perllocal.pod [root@HOME001 percona-toolkit-2.1.8]# [root@HOME001 bin]# which pt-query-digest /usr/local/bin/pt-query-digest [root@HOME001 bin]#
検証用のSlowログを意図的に作成してみる。
動的設定はGLOBALで設定しているので既存セッションには有効になっていないが。
既存設定を即時変更する場合は、globalオプションを付けなければOK。
mysql> show variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> mysql> set global long_query_time=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
別セッションからは反映されている事を確認。
mysql> show variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.000000 | +-----------------+----------+ 1 row in set (0.01 sec) mysql>
幾つかQueryを実行したので、こちらをpt-query-digestで確認。
SET timestamp=1359610105; show variables like 'long%'; # Time: 130131 14:28:32 # User@Host: root[root] @ localhost [] # Query_time: 0.000441 Lock_time: 0.000108 Rows_sent: 39 Rows_examined: 39 SET timestamp=1359610112; select * from test.TABLE001; # Time: 130131 14:29:08 # User@Host: root[root] @ localhost [] # Query_time: 0.433720 Lock_time: 0.000131 Rows_sent: 0 Rows_examined: 1 SET timestamp=1359610148; delete from test.TABLE001 where id = 39; # Time: 130131 14:29:12 # User@Host: root[root] @ localhost [] # Query_time: 0.000011 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 1 SET timestamp=1359610152; # administrator command: Quit;
Slowログのサンプルが集まったのでlong_query_timeの設定を戻す。
mysql> set global long_query_time=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> exit
Slowログをpt-query-digestにて解析してみる。
[root@HOME001 data]# /usr/local/bin/pt-query-digest /usr/local/mysql/data/home001-mysql-slow.log # 380ms user time, 30ms system time, 13.71M rss, 19.34M vsz # Current date: Thu Jan 31 14:33:10 2013 # Hostname: HOME001.localdomain # Files: /usr/local/mysql/data/home001-mysql-slow.log # Overall: 11 total, 7 unique, 0.06 QPS, 0.00x concurrency _______________ # Time range: 2013-01-31 14:26:02 to 14:29:12 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 901ms 11us 461ms 82ms 433ms 171ms 865us # Lock time 1ms 0 166us 104us 152us 53us 125us # Rows sent 122 0 39 11.09 38.53 16.89 0.99 # Rows examine 161 0 39 14.64 38.53 18.20 0.99 # Query size 377 27 63 34.27 40.45 10.82 26.08 # Profile # Rank Query ID Response time Calls R/Call Apdx V/M Item # ==== ================== ============= ===== ====== ==== ===== ========== # 1 0x677853E0D6CB8C75 0.4610 51.2% 1 0.4610 1.00 0.00 UPDATE test.TABLE? # 2 0x16CB61E3D2F803A3 0.4337 48.1% 1 0.4337 1.00 0.00 DELETE test.TABLE? # MISC 0xMISC 0.0064 0.7% 9 0.0007 NS 0.0 <5 ITEMS> # Query 1: 0 QPS, 0x concurrency, ID 0x677853E0D6CB8C75 at byte 3035 _____ # This item is included in the report because it matches --limit. # Scores: Apdex = 1.00 [1.0]*, V/M = 0.00 # Query_time sparkline: | ^ | # Time range: all events occurred at 2013-01-31 14:28:19 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 9 1 # Exec time 51 461ms 461ms 461ms 461ms 461ms 0 461ms # Lock time 11 137us 137us 137us 137us 137us 0 137us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 24 39 39 39 39 39 0 39 # Query size 16 63 63 63 63 63 0 63 # String: # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `test` LIKE 'TABLE001'\G # SHOW CREATE TABLE `test`.`TABLE001`\G update test.TABLE001 set comment = 'test percona-toolkit-2.1.8'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select comment = 'test percona-toolkit-2.1.8' from test.TABLE001 \G # Query 2: 0 QPS, 0x concurrency, ID 0x16CB61E3D2F803A3 at byte 3653 _____ # This item is included in the report because it matches --limit. # Scores: Apdex = 1.00 [1.0]*, V/M = 0.00 # Query_time sparkline: | ^ | # Time range: all events occurred at 2013-01-31 14:29:08 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 9 1 # Exec time 48 434ms 434ms 434ms 434ms 434ms 0 434ms # Lock time 11 131us 131us 131us 131us 131us 0 131us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1 1 1 1 1 0 1 # Query size 10 39 39 39 39 39 0 39 # String: # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `test` LIKE 'TABLE001'\G # SHOW CREATE TABLE `test`.`TABLE001`\G delete from test.TABLE001 where id = 39\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from test.TABLE001 where id = 39\G [root@HOME001 data]#
参考サイト
pt-query-digest
tcpdumpとmk-query-digestでMySQLのクエリ解析
実録MySQLのチューニング 春の陣
Maatkitはtcpdumpを食べられる上，memcachedも咀嚼出来る
その他
mk-table-checksum – Perform an online replication consistency check,
maatkitでレプリケーションデータの整合性を確認
Tracking long running transactions in MySQL
関連情報
mysqldumpslow
[root@HOME001 data]# mysqldumpslow -t 3 -l /usr/local/mysql/data/home001-mysql-slow.log Reading mysql slow query log from /usr/local/mysql/data/home001-mysql-slow.log Count: 1 Time=0.46s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost update test.TABLE001 set comment = 'S' Count: 1 Time=0.43s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost delete from test.TABLE001 where id = N Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select @@version_comment limit N [root@HOME001 data]#