SHOW PROCESSLISTするときに気を付けること
気づいたら9月でs(ry（段々ここで言うネタが切れてきた）
今さら言うことではないのですが、MySQLでSHOW (FULL) PROCESSLISTをしたいときに気を付けたいポイントをば。
さて、MySQLサーバーの監視や調査などでSHOW PROCESSLISTを叩くことは多いのではないのでしょうか。
今回は、今さら言うことではないSHOW PROCESSLISTをするときに気を付けたいことを書いていきます。
PROCESS権限がないとすべてのスレッドを確認できない
社内DBAなどであれば、このあたりを特に意識する必要はないと思います。 しかし、例えばお客様先のDBの性能調査や障害時の原因調査をしたいなどの場合は権限を意識する必要があります。
PROCESS権限がない場合、SHOW PROCESSLISTの結果はそのユーザーに関連したスレッド情報しか参照できません。
これは下記の結果を見ていただければ一目瞭然でしょう。
- PROCESS権限無し
mysql-monitor@localhost[(none)]>show grants; +--------------------------------------+ | Grants for monitor@% | +--------------------------------------+ | GRANT SELECT ON *.* TO `monitor`@`%` | +--------------------------------------+ 1 row in set (0.00 sec) mysql-monitor@localhost[(none)]>show processlist; +-----+---------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+---------+-----------+------+---------+------+----------+------------------+ | 707 | monitor | localhost | NULL | Query | 0 | starting | show processlist | +-----+---------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
- PROCESS権限あり
mysql-monitor@localhost[(none)]>show grants; +-----------------------------------------------+ | Grants for monitor@% | +-----------------------------------------------+ | GRANT SELECT, PROCESS ON *.* TO `monitor`@`%` | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql-monitor@localhost[(none)]>show processlist; +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 10511 | Waiting on empty queue | NULL | | 10 | root | localhost | sample | Sleep | 3323 | | NULL | | 217 | admin | 192.168.100.128:61850 | NULL | Sleep | 77 | | NULL | | 218 | admin | 192.168.100.128:61851 | NULL | Sleep | 77 | | NULL | | 432 | root | localhost | NULL | Sleep | 6 | | NULL | | 610 | app_user | localhost | NULL | Sleep | 1482 | | NULL | | 711 | monitor | localhost | NULL | Query | 0 | starting | show processlist | +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+ 7 rows in set (0.00 sec)
これのいやらしいところは、権限が無くてもエラーにならないことだと思います。 テストをしてみてエラーにならないからオッケーと思ったら、本番で全然ほしい情報が取れていないなんて可能性がありますから。
ちなみに調査時に有用な情報を提供してくれるSHOW ENGINE INNODB STATUSにもPROCESS権限が要求されます。
mysql-monitor@localhost[(none)]>show engine innodb status\G ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
調査時には予めPROCESS権限を与えてもらいましょう。
余談ですが「このコマンド打つのに必要な権限ってなんだろう？」ってときは検証環境とかに何も権限がないユーザーを作っておいて そのユーザーで実行したいコマンドを打つとエラーメッセージに足りない権限が表示されるのでおすすめです。 （マニュアル読めばいいだろという気もしますが・・・・）
performance_schema.threadsテーブルを参照するほうが負荷は低い
このあたりも予め念頭に置いておきましょう。
MySQL 8.0.21まではSHOW PROCESSLISTはinformation_schema.processlistを参照しています。
一方、performance_schema.threadsからも同等の情報を取得できます。
さらに、performance_schema(以降はp_s)はinformation_schema(以降はi_s)よりも参照時の負荷が低いです。
(一般的にi_sは情報取得時のオーバーヘッド小、情報参照時のオーバーヘッド大で、p_sは情報取得時のオーバーヘッド大、情報参照時のオーバーヘッド小となる)
僕はまだ本番環境で経験したことはありませんが、極めて高負荷なサーバーではSHOW PROCESSLISTすら返ってこないことがあるようですよ。
参考：https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0038
というわけで、基本的にはp_s.threadsから情報を取得するのがいいんじゃないかと思います。
p_s.threasからshow processlistと同じ情報を取得する
SHOW PROCESSLISTとほとんど同等の情報は次のSQLで取得ができます。
SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_ID IS NOT NULL -- バックグラウンドプロセスは表示しない AND THREAD_ID <> PS_CURRENT_THREAD_ID() -- 自身の情報は表示しない ORDER BY PROCESSLIST_ID; -- デフォルトではTHREAD_IDでソートされてしまうため
結果は次のようになります。
+----------------+------------------+------------------+----------------+---------------------+------------------+------------------------+-----------------------------------------+ | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +----------------+------------------+------------------+----------------+---------------------+------------------+------------------------+-----------------------------------------+ | 5 | NULL | NULL | NULL | Sleep | NULL | Waiting on empty queue | NULL | | 7 | NULL | NULL | NULL | Daemon | 11907 | Suspending | NULL | | 10 | root | localhost | sample | Sleep | 360 | NULL | select sys.ps_thread_id(NULL) | | 217 | admin | 192.168.100.128 | NULL | Sleep | 273 | NULL | SHOW FUNCTION STATUS WHERE Db='sample' | | 218 | admin | 192.168.100.128 | NULL | Sleep | 273 | NULL | SHOW FULL COLUMNS FROM `sample`.`users` | | 432 | root | localhost | NULL | Sleep | 684 | NULL | GRANT PROCESS ON *.* TO 'monitor'@'%' | | 724 | monitor | localhost | NULL | Sleep | 261 | NULL | show processlist | +----------------+------------------+------------------+----------------+---------------------+------------------+------------------------+-----------------------------------------+ 7 rows in set (0.00 sec) mysql-monitor@localhost[p_s]>SHOW PROCESSLIST; +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 11973 | Waiting on empty queue | NULL | | 10 | root | localhost | sample | Sleep | 426 | | NULL | | 217 | admin | 192.168.100.128:61850 | NULL | Sleep | 339 | | NULL | | 218 | admin | 192.168.100.128:61851 | NULL | Sleep | 339 | | NULL | | 432 | root | localhost | NULL | Sleep | 750 | | NULL | | 724 | monitor | localhost | NULL | Sleep | 327 | | NULL | | 761 | monitor | localhost | p_s | Query | 0 | starting | SHOW PROCESSLIST | +-----+-----------------+-----------------------+--------+---------+-------+------------------------+------------------+
多少異なる情報ですが、欲しい情報はおよそとれるのではないのでしょうか。 欠点は、タイプが面倒くさいことですかね。 あとはTHREAD_IDとPROCESSLIST_IDは異なる情報であることにも注意した方がよいでしょう。
他にもp_s.threadsテーブルにしかない情報としてバックグラウンドプロセスの情報が参照できたりします。
まとめ
というわけで、今回はSHOW PROCESSLISTするときに気を付けたいことについて書きました。
SHOW PROCESSLISTの負荷って普段は考えないと思いますが、p_s.threadsを覚えておけば万一の時でも調査を進められるのでないでしょうか。
おまけ
MySQL 8.0.22からshow processlistはp_s.threadsを見に行くらしい
MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.29 SHOW PROCESSLIST Statementから引用
Note As of MySQL 8.0.22, an alternative implementation for SHOW PROCESSLIST is available based on the Performance Schema processlist table, which, unlike the default SHOW PROCESSLIST implementation, does not require a mutex and has better performance characteristics.
やったね！ 通常通りであれば来月中に次のリリースがありそうです:)
