MYSQLの運用・管理ツール検証

Percona Toolkit

最新版の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]#

percona

参考サイト
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]#