MySQL | さくさく TECH

稼働中のシステムにおいて発生するトラブルのひとつに、MySQLのコネクションに関するトラブルがあります。
“アプリケーションからDBコネクションを取得できない！””想定以上にアプリケーションがコネクションを取得している”といったとトラブルになります。

今回は、MySQLのコネクションに関するトラブルが発生した場合に調査するポイントと、調査のために実行するコマンドについて紹介していきます。

現在のコネクション数を確認

MySQLに、現在接続しているコネクション数を確認するには、show status コマンドを実行します。
MySQLにログインしてコマンドを実行します。

mysql> show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 29    |
+-------------------+-------+
1 row in set (0.00 sec)

上記コマンドで現在接続されているコネクション数を確認して、MySQLで許可している最大コネクション数を超えていないかの確認が可能です。

最大コネクション数を確認

MySQLが許可している最大コネクション数を確認します。
このコネクション数が接続される可能性がある数を超えてしまうので、MySQLに接続できない状態になってしまいます。

mysql> SHOW GLOBAL VARIABLES like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 512   |
+-----------------+-------+
1 row in set (0.01 sec)

接続元のIPを確認

現在のプロセスを一覧で表示して、発生しているコネクションが、どのサーバから接続されているかの確認も可能です。

mysql> SHOW processlist;
+------------+------------+------------------------------+
| Id | User | Host | db | Command | Time | State | Info  |
+------------+------------+---------------------+--------+
　　・
　　・
　　・

Hostカラムが接続元サーバのIPとPORTです。

MySQLを起動してからの最大接続数を確認

MySQLを起動してから接続された最大コネクション数も確認することができます。

トラブルが発生しても、調査を開始したタイミングではコネクション数は問題ない場合があります。

しかし、トラブル発生直後はコネクション数が規定値をオーバーしている可能性があるので、このコマンドを実行して過去の最大コネクション数を確認するとよいです。

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 513   |
+----------------------+-------+
1 row in set (0.00 sec)

DBサーバのKeepAliveを確認

TomcatなどのWEBアプリケーションがコネクションプールを取得している状態で、Tomcatが起動しているサーバが落ちてしまうと、MySQLのコネクションは残ってしまいます。
このコネクションは不要なコネクションとなるのですが、一定時間が過ぎると自動的にコネクションは解放されます。

この「一定時間」は、MySQLがインストールされているサーバのKeepAliveパケット送信の設定に依存します。

システム設定値となり、以下のコマンドで格納可能です。

以下の設定例では、６０秒で不要なコネクションは解放されます。

$>sysctl -a
　・
　・
　・
net.ipv4.tcp_keepalive_intvl=5
net.ipv4.tcp_keepalive_probes=6
net.ipv4.tcp_keepalive_time=30
　・
　・
　・

30秒間隔でKeepAliveパケットを6回送信する。
その6回は5秒間隔、という設定になります。

各項目の説明を一応記載しておきます。

net.ipv4.tcp_keepalive_intvl	KeepAliveパケットを送信する間隔
net.ipv4.tcp_keepalive_probes	KeepAliveパケットを送信する回数
net.ipv4.tcp_keepalive_time	KeepAliveパケットを送信するまでの時間

こんにちは。
さくさくTECHブロガーのさくです。

今回は、Linux + MySQL の環境において、シェルを使ってエクスポートとインポートをおこなう方法を紹介します。

どういった場面を想定しているかといいますと、一番は定期実行のバッチですね。
例えば、１日１回、特定テーブルのデータをエクスポートする、といった要件があった場合。

その場合、エクスポートするコマンドをシェル化してクーロンに設定すると思います。
そういった時に使える手法です。

インポートについては、例えば実データが壊れてしまった場合の復旧ですね。
エクスポートしたデータをインポートすれば、データは元通りになります。

環境情報

OS：Linux
DB：MySQL

MySQLには「saku」というＤＢを準備します。
そのＤＢには「person」というテーブルが存在します。
テーブル構成は以下。

mysql> desc person;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| personID | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32)  | NO   | MUL | NULL    |                |
| kana     | varchar(128) | NO   |     | NULL    |                |
| address  | varchar(512) | YES  |     | NULL    |                |
| gender   | char(1)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

また、あらかじめデータも格納しておきます。

mysql> select * from person;
+----------+--------+--------------+-----------+--------+
| personID | name   | kana         | address   | gender |
+----------+--------+--------------+-----------+--------+
|        4 | 山田   | ヤマダ       | 東京      | 0      |
|        5 | 高橋   | タカハシ     | 神奈川    | 0      |
|        6 | 田中   | タナカ       | 千葉      | 1      |
+----------+--------+--------------+-----------+--------+
3 rows in set (0.00 sec)

このテーブルに対して、エクスポート、および、インポートをおこないます。

エクスポート

まずはエクスポート。
エクスポートする条件も指定します。
「gender」が「1」のレコードのみをエクスポートします。
シェルは以下になります。

#!/bin/sh
# 
# エクスポートシェル
# 
mysqldump -h localhost \--single-transaction --skip-opt --extended-insert --quick --set-charset --no-create-info --no-autocommit \
  -u ※ユーザ名 -p※パスワード -h ※ホスト名 ※ＤＢ名 person \
  --where="gender = '0'" \
  | gzip > person.sql.gz 2>&1 | tee -a export.log

使用するコマンドは mysqldump コマンドです。
mysqldump コマンドで、「gender」が「1」のレコードのみをエクスポートします。
かつ、エクスポートするファイルはperson.sql.gzという圧縮ファイルとして出力し、エクスポート時のログをexport.logに出力します。

エクスポート結果を圧縮することで、仮に大量データをエクスポートすることになったとしても、ある程度はディスク使用率おさえることができます。

様々なオプションを指定していますが、各オプションの用途は以下になります。

オプション	説明
–single-transaction	これを設定しておくと、InnoDBについてエクスポートする際、他ＳＱＬがロックされないです。運用中システムについてエクスポートする際は、このオプションを指定した方が無難。
–skip-opt	「–opt」の設定をオフにします。「–opt」の設定の中に「–add-locks」がありますが、これが有効だとエクスポート中にテーブルがロックされてしまう可能性があります。それを防ぐために、–skip-optを設定して、「–opt」の設定をオフにします。
–extended-insert	出力されるダンプファイルについて、バルクインサートの形でＳＱＬを作成します。
–quick	MySQLのマニュアルをみる限りでは、大量データをエクスポートする際はつけた方がいいみたいです。意味を理解しきれませんでしたが、たぶん、メモリ上にエクスポートデータを展開するタイミングの話のようです。このオプションを指定した方が、より高速なやり方を実施するようです。
–set-charset	出力するエクスポートするファイルに SET NAMES default_character_set を出力します。
–no-create-info	エクスポートするファイルに CREATE文を出力しません。
–no-autocommit	エクスポートするファイルに AutoCommitをオフにします。具体的には、エクスポートするファイルのＳＱＬ先頭に「set autocommit=0;」を出力します。かつ、最後に「commit;」を出力します。

エクスポートはこんな感じです。
エクスポートが完了するとperson.sql.gzというファイルが作成されます。

インポート

次にインポート。
インポートは、エクスポートシェルで作成したperson.sql.gzを解凍して、解凍したファイルを参照してインポートします。

シェルは以下になります。

#!/bin/sh
# 
# インポートシェル
# 
gunzip -d person.sql.gz >> insert.sql
mysql -u ※ユーザ名 -h ※ホスト名 -D ※ＤＢ名 -p※パスワード < insert.sql 2>&1 \ | tee -a import.log

まずは guzip コマンドで解凍して、「insert.sql」を出力。
gunzipに-d オプションをつけているので、元ファイルの「person.sql.gz」を削除して「insert.sql」に解凍結果を出力。

インポート自体はmysqlコマンドで実行しています。
リダイレクト「<」を指定して、解凍したSQLファイルをそのままインポートします。

まとめ

いかがでしたでしょうか？

今回はたった３行しか入っていないテーブルに対してのエクスポートなので性能は意識する必要はないのですが、数億件のエクスポートする場合は性能が大事になります。
紹介したオプションを指定すれば、極力、サーバ性能に沿ったパフォーマンスを出せると思っています。
参考にしてください。

それではまた！

タグ: MySQL

MySQLのDBコネクション関連でトラブルが発生した際にやること

現在のコネクション数を確認

最大コネクション数を確認

接続元のIPを確認

MySQLを起動してからの最大接続数を確認

DBサーバのKeepAliveを確認

MySQLデータのインポートとエクスポート！圧縮と解凍する方法を紹介

環境情報

エクスポート

インポート

まとめ

現在のコネクション数を確認

最大コネクション数を確認

接続元のIPを確認

MySQLを起動してからの最大接続数を確認

DBサーバのKeepAliveを確認

関連記事

環境情報

エクスポート

インポート

まとめ

関連記事