MySQL運用が長くなってきたときに気をつけたい2つのポイント

この記事はCyberAgent エンジニア Advent Calendar 2015の18日目の記事です。 記事のアップが大幅に遅延してしまったことをお詫び致します。。。

www.adventar.org

今回はMySQLの運用が長くなってきたときによく起こりがちな問題と対策を2つ記します。

増加するデータ容量

運用が長くなればなるほど、おおよその場合データ容量は増え続けます。 さらにInnoDBの場合、レコードをDeleteしてもデータ容量自体は減りません。 Deleteしたあとはテーブルをオプティマイズしましょう。

> DELETE from sample_table WHERE update_datetime < '2014-12-24 00:00:00' LIMIT 100000;
> ALTER TABLE sample_table ENGINE=InnoDB;

削除件数が少なかったりレコード件数が少ないテーブルであればLIMITはいらないかもしれないですが、多いテーブルの場合は件数を絞り、間にsleepを入れるなどして削除していくと負荷に優しいです。 ALTER文も必要に応じてpt-online-schema-changeを用いましょう。

strsk.hatenablog.com

重複したインデックス

最初は必要がなかったけど、ソートする要件が加わったためORDER BY狙いのインデックスを作成したり、カラム追加に伴ってインデックスを作成したりといったことはよくあると思います。 それが重なっていくと、テーブル内に重複したインデックスが存在する可能性が高くなり、メモリの利用効率が悪くなります。 起こりうる変化は予め対策すべきですが、すべてを予期することは困難ですし、ハズレた場合は無駄も多くなります。

そんなときは、pt-duplicate-key-checkerの出番です。 https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html

$ pt-duplicate-key-checker  u=xxx,p=xxx --database=sample_db

# A software update is available:
#   * The current version for MySQL Community Server (GPL) is 5.6.24.

# ########################################################################
# sample_db.friend
# ########################################################################

# user_id is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `user_id` (`user_id`,`request_user_id`),
#   PRIMARY KEY (`user_id`,`request_user_id`),
# Column types:
#     `user_id` int(11) not null
#     `request_user_id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `sample_db`.`friend` DROP INDEX `user_id`;

# ########################################################################
# sample_db.gift
# ########################################################################

# idx_userId_status is a left-prefix of idx_useridStatusUpddatetime
# Key definitions:
#   KEY `idx_userId_status` (`user_id`,`status`) USING BTREE,
#   KEY `idx_useridStatusUpddatetime` (`user_id`,`status`,`upd_datetime`),
# Column types:
#     `user_id` int(11) not null
#     `status` mediumint(8) unsigned default null
#     `upd_datetime` datetime not null
# To remove this duplicate index, execute:
ALTER TABLE `sample_db`.`gift` DROP INDEX `idx_userId_status`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes   18895972132
# Total Duplicate Indexes  48
# Total Indexes            1286

実行すると、重複したインデックスと削除するためのALTER文が出力されます。 このサンプルでは重複したインデックスが48件も検出されています。これはすぐにでも削除したいところです。 削除については同じく、pt-online-schema-changeを利用するなり、計画的に削除しましょう。

最後に

他にも運用していると起こりうる問題はいろいろありますが、とりあえず今回はこの2点だけお伝えしました。

2014年を振り返る

あけましておめでとうございます！

2014年が過ぎ去ってますが、酒を飲みつつ去年を振り返ります。

2014年この記事を書いたことで、ちょっとずつ変わってきていた意識がより濃くなってきました。

以前は、技術に興味がないわけじゃないんだけど、すごい人達のレベルになるのは無理だろう。それなら技術もやるけどコミュニケーション（笑）や技術的な調整など、強みと評価されている部分でがんばろう、と割と職業エンジニアに近い精神でした。

それが自分のチーム（に近いもの）を持ってからは、すごいとかそもそも主観だからどうでも良いし、自分にしかできない伝え方がもっとできるんじゃないか、と思うようになりました。チーム開発でも、自分ひとりでは限られたことしかできないし、もっと大きく仕事をするためにはどうしたら良いか考えた結果、情報共有や教育といった部分が足りないと感じるようになって、動いた年でもあります。

そんな中で内部向けに書いていた情報を、1番最大化できるのはどこかと考えてエンジニアブログに寄稿したんですが、割と評価してもらえたこともあって、内部向けに何かを残すなら外にも出せば役立つ人もいるかもしれないし、クソならそれなりのフィードバックがもらえるので良いのではないか、と思って活動した結果がこれです。

12月に詰め込みすぎて、毎週自分に追い込まれるという悪夢を体験しましたが後悔はしていません。

それから、こっそり立ていた2014年の目標であるQiitaのContribution100は、自分用メモを残していたら達成出来ました。Qiitaすごい。

このように細々ではあるけれど2014年は変化の年だったので、2015年も実行力を鍛えつつ自分を追い込みながら成長していきたい。

今年もよろしくお願いします！

シンプルなVPC設計を考える

この記事はAWS Advent Calendar 2014の24日目の記事です。メリークリスマス！

今日は、最近イチから構成を考える機会があったVPCの設計について、その一部を晒してみたいと思います。

前提

以前からAWSは運用していましたが、今後複数アカウントが乱立したり、利用する人がより増えることが想定されたため、以下のような要件を前提に見直しました。

• Web/Appサーバとデータベースで構成されるWebサービスがメイン
• 複数アカウントで汎用的に利用できること
• なるべくNWレベルの意識をしなくても良いこと
• 運用の発生する部分を減らすこと
• シンプルでわかりやすいこと

基本構成

Functional Firewallパターンをベースにしました。Functional Firewallパターンは、各レイヤーのセキュリティルールをグループ化して適用することで煩雑になりにくく、シンプルに管理することができます。

CDP:Functional Firewallパターン - AWS-CloudDesignPattern

※bastionはsshの踏み台マシンです

VPC/サブネット

サブネットは、パブリックサブネットのみ作成します。プライベートサブネットは、NATインスタンスの冗長化や運用が面倒なのでセキュリティグループでカバーします。最近ではプライベートサブネットを用意せず、全台にPublic IPを振る構成は割と多いとも聞きます。

ただ外部との連携でこちらのGIPが制限されることもあるため、その場合は改めてNATインスタンスかProxyを立てます。

セキュリティグループ

Functional Firewallパターンを元に以下のルールを決めています。

• セキュリティの担保はセキュリティグループで行う
• Network ACLはつかわない
• 基本的にはInboundで制御しOutboundは変更しない
• インスタンスはdefault+機能ごとのセキュリティグループを付与する
• Sourceには必ずセキュリティグループを指定する
• development,staging,productionごとに作り、互いの干渉を防ぐ

Network ACLはVPCメニュー内にありわかりづらいこと、セキュリティグループと二重管理になりやすいため避けました。Outboundを利用しないのもセキュリティグループ間の重複を避けるためです。また、グループを抽象化することによって、ある程度の構成の違い（ミドルウェアやポート）は吸収でき、設定箇所が多くならないため管理が煩雑になりにくくなります。

下記はSecurity Groupの利用イメージです。

default

Type	Protocol	Port Range	Source
SSH	TCP	22	sg-bastion(踏み台)
All ICMP	All	N/A	default

prd-front(elb)

Type	Protocol	Port Range	Source
HTTP	TCP	80	0.0.0.0/0
HTTPS	TCP	443	0.0.0.0/0

prd-middle(web/app)

Type	Protocol	Port Range	Source
HTTP	TCP	80	sg-prd-front

prd-back(db/cache)

Type	Protocol	Port Range	Source
MYSQL	TCP	3306	sg-prd-middle
Custom TCP Rule	TCP	11211	sg-prd-middle

まとめ

このような初期設定を行うことで、利用者はVPCをあまり意識しなくても良くなり、セキュリティグループでの制御だけ設定する環境ができます。ただし自由度は下げたくないので、あくまでベースとして設定し、利用者に合わせて変更するところは変更しています。

※この構成は、所属している会社全体の方針ではありませんm(＿ ＿)m

pt-online-schema-changeを安全につかう

この記事はMySQL Casual Advent Calendar 2014とCyberAgent エンジニア Advent Calendar 2014の19日目の記事です。

この間のMySQL Casual Talks vol.7でも少し触れていますが、pt-online-schema-change（以下、pt-osc）には安全に実行するための便利なオプションが用意されています。これらのオプションを利用すれば、オンラインでの変更に抵抗がある方でも石橋を叩きながら実行することができます。5.6でOnline DDLが使えますが、セーフティに変更したい場合は有効です。

pt-online-schema-change — Percona Toolkit Documentation

スレッド数を監視する

--max-load

pt-oscはスレッド数をチェックしながら動作を制御できます。--max-loadのデフォルトは25で、超えた場合は下記のようなログを出力して一時停止します。

（略）
Copying `db_name`.`tbl_name`:  17% 35:45 remain
Pausing because Threads_running=44. <-この行
Copying `db_name`.`tbl_name`:  17% 40:58 remain
（略）

負荷状態に余裕があれば--max-load Threads_running=100のように値を指定することで一時停止せずに続行することができます。

--critical-load

--max-loadと書式が一緒で、こちらはデフォルト値が50です。これを超えた場合はpt-oscが終了します。

（略）
2014-06-10T17:11:10 Dropping triggers...
2014-06-10T17:11:10 Dropped triggers OK.
2014-06-10T17:11:10 Dropping new table...
2014-06-10T17:11:13 Dropped new table OK.
`db_name`.`tbl_name` was not altered.
2014-06-10T17:11:10 Error copying rows from `db_name`.`tbl_name` to `db_name`.`_tbl_name_new`: Threads_running=50 exceeds its critical threshold 50

早さを求めないときは--max-loadで実行負荷を制御しつつ、--critical-loadを多めに設定する、などといったやり方もできます。

レプリケーション遅延を監視する

pt-oscは、デフォルトでスレーブのレプリケーション遅延の状態をチェックしながら実行できます。ただ、マスターとスレーブで同じ権限を持っていない場合があるため、例えばrootユーザで実行すると以下のようなメッセージが出るかもしれません。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=root
Cannot connect to D=db_name,h=192.168.0.1,u=root
Cannot connect to D=db_name,h=192.168.0.2,u=root
No slaves found.  See --recursion-method if host db_master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
（略）

この場合はマスターからスレーブ郡へ同じユーザ権限を与えることでチェックできます。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=osc_user,p=osc_pass
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
  db_backup
（略）

--check-slave-lag

上記のようにユーザ権限を与えれば、processlistを見てレプリケーションしているスレーブ全台の遅延状況を確認しながら実行できます。ただし、例えばバックアップ用のDBがスレーブよりスペックが劣っていて、遅延は許容したい場合があったと思います。そのときは--check-slave-lagでチェック対象を1台のみに絞ることができます。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=osc_user,p=osc_pass --check-slave-lag h=192.168.0.1,u=osc_user,p=osc_pass
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
（略）

また、これを利用することでマスターとスレーブで同じユーザ権限を持っていない場合でも、レプリ遅延をチェックすることができます。適切な権限があれば。

--max-lag

レプリ遅延のしきい値を設定でき、デフォルトは1です。これを超えると一時停止し、それ以上の遅延を防止することができます。Seconds_Behind_Masterを見ているので、許容できる秒数を指定します。

--check-interval

--max-lagをチェックをする間隔で、デフォルトは1秒です。

その他

--set-vars

MySQLの変数を渡すオプションですが、主にレプリケーションさせたくない場合に--set-vars="sql_log_bin=0"を指定することが多いです。

--dry-run

pt-oscは--dry-runか--executeのどちらかを必ずオプションに指定します。 まずは--dry-runで確認しましょう。

$ pt-online-schema-change --dry-run --alter="engine=InnoDB" h=localhost,D=db_name,t=tbl_name,u=root
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `db_name`.`tbl_name` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db_name._tbl_name_new OK.
Altering new table...
Altered `db_name`.`_tbl_name_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2014-12-18T17:01:26 Dropping new table...
2014-12-18T17:01:26 Dropped new table OK.
Dry run complete.  `db_name`.`tbl_name` was not altered.

--nodrop-old-table

pt-oscは、新しいテーブルに変更内容を適用し、リネームしてから既存のテーブルを削除する仕組みになっていますが、このオプションで既存のテーブルが_tbl_name_oldとして残ります。万が一のためだったり、テストする場合などに利用できるでしょう。

通常時

（略）
2014-12-18T16:53:18 Dropping old table...
2014-12-18T16:53:18 Dropped old table `db_name`.`_tbl_name_old` OK.
（略）

オプション指定時

（略）
Not dropping old table because --no-drop-old-table was specified.
（略）

--progress

デフォルトは30秒ごとの進捗（time,30）が表示されますがこれを変更できます。値はtime,percentageなどが利用できます。

$ pt-online-schema-change --execute --progress=time,1 --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=ptosc,p=ptosc
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
  db_backup
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `db_name`.`tbl_name`...
Creating new table...
Created new table db_name._tbl_name_new OK.
Waiting forever for new table `db_name`.`_tbl_name_new` to replicate to db_master...
Altering new table...
Altered `db_name`.`_tbl_name_new` OK.
2014-12-18T23:43:43 Creating triggers...
2014-12-18T23:43:43 Created triggers OK.
2014-12-18T23:43:43 Copying approximately 1591002 rows...
Copying `db_name`.`tbl_name`:   1% 01:20 remain
Copying `db_name`.`tbl_name`:   7% 00:24 remain
Copying `db_name`.`tbl_name`:  12% 00:20 remain
Copying `db_name`.`tbl_name`:  18% 00:17 remain
Copying `db_name`.`tbl_name`:  23% 00:16 remain
Copying `db_name`.`tbl_name`:  29% 00:14 remain
Copying `db_name`.`tbl_name`:  34% 00:13 remain
Copying `db_name`.`tbl_name`:  39% 00:12 remain
Copying `db_name`.`tbl_name`:  45% 00:10 remain
Copying `db_name`.`tbl_name`:  48% 00:10 remain
Copying `db_name`.`tbl_name`:  53% 00:09 remain
Copying `db_name`.`tbl_name`:  58% 00:08 remain
Copying `db_name`.`tbl_name`:  64% 00:07 remain
Copying `db_name`.`tbl_name`:  69% 00:06 remain
Copying `db_name`.`tbl_name`:  75% 00:04 remain
Copying `db_name`.`tbl_name`:  80% 00:03 remain
Copying `db_name`.`tbl_name`:  85% 00:02 remain
Copying `db_name`.`tbl_name`:  91% 00:01 remain
Copying `db_name`.`tbl_name`:  96% 00:00 remain
2014-12-18T23:44:03 Copied rows OK.
2014-12-18T23:44:03 Swapping tables...
2014-12-18T23:44:03 Swapped original and new tables OK.
2014-12-18T23:44:03 Dropping old table...
2014-12-18T23:44:04 Dropped old table `db_name`.`_tbl_name_old` OK.
2014-12-18T23:44:04 Dropping triggers...
2014-12-18T23:44:04 Dropped triggers OK.
Successfully altered `db_name`.`tbl_name`.

まとめ

オンラインでの実行が前提だけあってセーフティなオプションがいろいろあります。 これらを利用すれば、本番環境のDBで実行する恐怖が少しは薄れるかなと思います。 ただし、ピーク帯の実行は極力控えましょう！

CyberAgent エンジニア Advent Calendar 2014の20日目は関西弁でお馴染みの@kakerukaeruです！お楽しみに！

MySQL Casual Talks vol.7で登壇してきました

他の登壇者がいつも勉強させてもらっている方ばかりなので何を話せばいいか迷ったけど、「自分なりのカジュアルとはなんぞや」と考えて、普段運用してて困ったことについて話してきました。

内容的には問題が起きてから解決までの流れをもうちょっと詳しく伝えられたらなと反省しています…。こういう場で話すのは初めてでしたが、準備段階から登壇までとても学び（反省点）が多く嬉しかったです！(☝ ՞ਊ ՞)＝☞)՞ਊ ՞)

打ち上げでもちょうどお店が一緒でとある方たちに合流させてもらったんですが、それも個人的にはすごく刺激を受けました…。

今回登壇の推薦をしてくれた、Cassandraを放棄した人 a.k.a. @oranieさん、運営の皆さんありがとうございました！！！