【MySQL】Disk IOが大きいSQLの内容を確認する





Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 1384

Warning: preg_match_all(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 700

Warning: Invalid argument supplied for foreach() in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 707

Warning: preg_match_all(): Compilation failed: invalid range in character class at offset 4 in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 700

Warning: Invalid argument supplied for foreach() in /home/dotlab/webmake.info/public_html/wp-content/plugins/lightbox-plus/classes/shd.class.php on line 707




目次

サーバが重くなる原因

サーバが何やら重いぞ?と思い、調べると原因は大抵

  • CPU
  • メモリ
  • ディスク

のいずれかに当たるかと思います。

ディスクIOが高いパターンの調査方法

IO waitが多く発生している場合は、外部からのデータアクセスがスペックに対して過剰であることが多いと思います。

とはいえ、どこが問題なの?という判別はtopコマンドではなかなかわかりづらいため、「iotop」コマンドを使います。

iotopコマンドの使い方

# iotop

 これだけ。実行すると下記のような画面が出てきます。

Total DISK READ: 10.33 M/s | Total DISK WRITE: 227.73 K/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
   58 be/4 root        0.00 B/s    0.00 B/s  0.00 % 99.99 % [kswapd0]
 7921 be/4 mysql       7.60 M/s    0.00 B/s  0.00 % 92.43 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --use~8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
 8308 be/4 mysql     478.62 K/s    0.00 B/s  0.00 % 35.51 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --use~8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
 7913 be/4 mysql    1219.71 K/s    0.00 B/s  0.00 % 33.41 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --use~8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
 7925 be/4 mysql      61.76 K/s    0.00 B/s  0.00 % 23.85 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --use~8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
 7929 be/4 mysql    1065.32 K/s    0.00 B/s  0.00 % 22.79 % mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --use~8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
 8306 be/4 mysql      84.92 K/s    7.72 K/s  0.00 % 21.17 % mysqld --basedir=/usr --datadir=/var/lib/mysql --p

これを見ると、MySQL重すぎワロリンですね。
デッドロックが発生しているのか、クエリがマズイのか、サーバスペックが追いついていないのか。
というところですが、まずはクエリの中身を確認したい所ですね。

クエリ内容を確認する

MySQLにログインしましょう。

# mysql -u root -p

続いて、

mysql> SHOW FULL PROCESSLIST \G;

 でプロセスリストを確認できます。

mysql> SHOW FULL PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 1079
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
*************************** 2. row ***************************
     Id: 4173
   User: root
   Host: localhost:45440
     db: satei_chan
Command: Query
   Time: 77
  State: Copying to tmp table
   Info: SELECT employee_id FROM emp;
...

 みたいな。

ここで出てきたSQLでチューニングができる箇所があればチューニング。

もうやりようないよ。。。という際は移転やスケールアップを考えましょう。

スポンサーリンク
スポンサーリンク




スポンサーリンク




シェアする

  • このエントリーをはてなブックマークに追加

フォローする

スポンサーリンク
スポンサーリンク