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

Linux

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


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


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


現在、MySQLに接続しているコネクション数を確認する方法は以下になります。
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パケット送信の設定に依存します。
システム設定値となり、以下のコマンドで格納可能です。


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


$>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パケットを送信するまでの時間


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

Linux

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


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


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


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


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


環境情報


OS:Linux
DB:MySQL


MySQLには「saku」というDBを準備します。
そのDBには「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 ※ホスト名 ※DB名 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についてエクスポートする際、他SQLがロックされないです。運用中システムについてエクスポートする際は、このオプションを指定した方が無難。

–skip-opt

「–opt」の設定をオフにします。

「–opt」の設定の中に「–add-locks」がありますが、これが有効だとエクスポート中にテーブルがロックされてしまう可能性があります。

それを防ぐために、–skip-optを設定して、「–opt」の設定をオフにします。

–extended-insert

出力されるダンプファイルについて、バルクインサートの形でSQLを作成します。

–quick

MySQLのマニュアルをみる限りでは、大量データをエクスポートする際はつけた方がいいみたいです。意味を理解しきれませんでしたが、たぶん、メモリ上にエクスポートデータを展開するタイミングの話のようです。このオプションを指定した方が、より高速なやり方を実施するようです。

–set-charset

出力するエクスポートするファイルに SET NAMES default_character_set を出力します。

–no-create-info

エクスポートするファイルに CREATE文を出力しません。

–no-autocommit

エクスポートするファイルに AutoCommitをオフにします。

具体的には、エクスポートするファイルのSQL先頭に「set autocommit=0;」を出力します。

かつ、最後に「commit;」を出力します。


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


インポート


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


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

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

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


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


まとめ


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


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


それではまた!