こんにちは、サービス開発部の荒引 (@a_bicky) です。 突然ですが、RDBMS の既存のテーブルを見てみたら「何でこんなにインデックスだらけなの？」みたいな経験はありませんか？不要なインデックスは容量を圧迫したり、挿入が遅くなったりと良いことがありません。 そんなわけで、今回はレコードを検索するために必要なインデックスの基礎知識と、よく見かける不適切なインデックスについて解説します。クックパッドでは Rails のデータベースとして主に MySQL 5.6、MySQL のストレージエンジンとして主に InnoDB を使っているので、MySQL 5.6 の InnoDB について解説します。 InnoDB のインデックスに関する基礎知識 インデックスの構造 (B+ 木) InnoDB では B+ 木が使われています。B+ 木は次のような特徴を持った木構造です。 次数を b とすると、
最近，環境ごとのデータベーススキーマの差分をチェックする機会があった．プロダクション環境とステージング環境ならまだしも，開発環境だと検証のために追加したインデックスがそのままになっていたり，開発が途中で止まってしまって日の目を見ることがなかったテーブルが残っていたり，そういうことって比較的あるのではないかなと思う．特に今の環境だと，マイグレーションの仕組みが整っていないという課題もあり，より一層，データベーススキーマに差分が出やすくなってしまっている． 今回は MySQL から公式に提供されている mysqldiff というツールを使ってデータベーススキーマの差分をチェックした． mysqldiff をインストールする mysqldiff は MySQL Utilities という MySQL の管理ツールパッケージの中に同梱されている．現在だと v1.6 が最新になっている． MySQL
PHPMigrate - PHPでマイグレーションツールを作った というMigrationプログラムを自作したのですが、このプログラムでMySQLのトリガを作成するSQLを実行したら、 mysql server version for the right syntax to use near 'delimiter ... というエラーがでて動作しませんでした。PHPからPDOでMySQLに接続してSQLを実行しているだけなんですが。 で、ちょっと調べたら、トリガ作成の時に使用してるdelimiterというコマンドが問題で、これはSQLではなくて、mysqlのクライアント/usr/bin/mysqlが用意しているコマンドとのこと。は～。どうりで動かんわけだ。 とはいえdelimiterを使えないのはちょっと困るので、SQLの実行をPDOの代わりにmysqlコマンドで行うことができるようにPH
MySQLのトリガー便利です。PHPでトランザクションを張って同期…というのもありだけど、データベースのレイヤで関係が担保されているのは開発者にとっても安心です。 しかし、トリガーの開発方法についての情報は少ない感じがあります。手探りで自己流なところがあると思いますが、トリガーの開発方法を整理できるくらいに自分の中でルーティンが決まってきたので記事としてまとめておきます。 掲示板のデータベースを例に作ってみよう お題がないと説明しにくいので、掲示板アプリを例に話を進めます。YY-BBSのような単純な掲示板を思い浮かべてください。 2つのテーブルを用意します。 スレッドテーブル 投稿テーブル もう少しテーブルの関係がイメージしやすいようにもうちょい妄想しちゃいます。 スレッドテーブルと投稿テーブルは一対多の関係にあります。一つのスレッドに複数の投稿が記録できる仕様です。掲示板にはスレッド一覧
※RDSは使っていません。 負荷を見てみる DBサーバーの負荷状況を見てみます。 当時の監視ツールの画像がないのですが、以下の状況でした。 LA(Load Average)が突き抜けている リクエスト数は「常識的に考えて」それほどでもない メモリの使用量にあまり変化がない swapはしていない ストレージ容量を結構食っている WEBサーバーから見れば、処理待ちのままプロセスが処理されていない典型的なパターンだったと思います。 DBサーバーとしては、LAに対し、メモリの使用量があっていないように思われました。 仮説 上記の状態から、仮説を立てます。 スロークエリ が頻発しているのではないか メモリ が正しく割り当てられていないのではないか 各種ログ の設定が適切ではないのではないか 仮説を検証することで、対策をしていきます。 設定を見直す 上記の仮説の設定は、MySQLの設定ファイルである「
10. 文字集合文字集合 US-ASCII 数字、英字、32個の記号 JIS X 0201 US-ASCII(「」→「¥」/「~」→「‾」)+カタカ ナ JIS X 0208 数字、ひらがな、カタカナ、漢字、ラテン文字、 ギリシャ文字、記号等々 JIS X 0213 JIS X 0208 + 第三水準/第四水準、ローマ数字、 鼻濁音文字等々 11. 文字集合文字集合 Windows-31J JIS X 0201 + JIS X 0208 + NEC特殊文字 + IBM 拡張文字(「⑧」「Ⅷ」「㈱」「髙」「﨑」「彅」 等) Unicode 世界中の文字。絵文字(「�����������������」「�������������������」等)も含む。
MySQLにもバグはあるのです〜 mysql5.6で、error.logに以下のログが出力される事があります。 2014-08-25 12:52:43 7f1c8e330700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2014-08-25 12:52:43 7f1c8e330700 InnoDB: Recalculation of persistent statistics requested for table "test"."hoge" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.恐らく以下のバグであると思われます。 MySQL Bu
MySQLサーバーをダウンさせた夜は数知れず。 その度にmy.cnfの設定を見なおしてみてはトライし、治ったと思いきや突然のダウン。 サーバーがダウンしてしまう原因は何かと聞かれれば、「メモリです」と断言しましょう。 メモリ設定は諸刃の剣。 パフォーマンスを最大に引き出すこともできればそれと引き換えにサーバーをダウンさせてしまうこともできるんです。 今回はMySQLのメモリの設定の勘所というかたちで紹介しようと思います。 グローバルバッファとスレッドバッファ メモリの設定についてまず「グローバルバッファ」と「スレッドバッファ」について理解しておくことが大事です。バッファとは一時的な記憶領域・つまりはメモリの領域のことなのですが。 グローバルバッファ MySQLで使用する全体的なメモリ使用量を計算するには グローバルバッファ + (スレッドバッファ × コネクション数) = メモリ使用量 と
表記の件について。ちなみにほとんどはブログ主の推測です。 目次 目次 キーワード そもそもToo many connectionsとは？ 関連パラメータ で、結局どうすればいいのよ 設定変更（の前の注意事項） 設定変更 現状の動作を変更する 次回起動時の動作を変更する 参考文献 キーワード MySQL そもそもToo many connectionsとは？ 「古い接続を切断しない限り、これ以上新規の接続ができません」とMySQLが言っている ここでの「接続」の定義は「認証開始後～quit」と思われる Too many connectionsが認証処理の後に出力されたため quitは推測 関連パラメータ 接続から切断まで、全ての処理が正常に進む前提なら、以下が関係しそう どれだけの頻度で新規接続が行われるか どれだけの時間接続されているか 接続後に行われる処理はどれだけの計算量か 処理を行う
Photo via Visual hunt Backlog の一部のスペースにて Amazon Aurora へと移行しました。ここでは、その経緯と実際に実施した作業を簡単にご紹介させていただきます。 移行の経緯 昨年末データベース障害が発生しユーザー様には多大なご迷惑をお掛けしてしまいました。 Backlog には Terraform をどう使っているかを紹介したブログ にあるように複数の運用環境があります。 その各々の環境の構築時期によって EC2 上で自前運用していた MySQL もあれば、RDS for MySQL もある、といった統一されていない状況でした。また EC2 上ではまだ MySQL 5.1 も稼働していました。 移行を検討するにあたり、優先したのは障害時の復旧が素早く出来ることと、少しでも運用の管理コストを下げることでした。Backlog のサーバは 100 台以上で
MySQL Casual Advent Calendar 2016 - Qiitaの6日目の記事です。 AdventCalendar自体初参加でドキドキ、してたら、成り行きで2日連続。 コレ用のきれいなエビデンス取れるような環境要していなかったので、普段より荒っぽいですが、Casualな感じで失礼します。 大きなテーブルを繰り返しSELECTしてたら、挙動が変わったんですよ。 バッファに載っているなら載っているで早いだろうし、載っていいなら最初ガッツリ遅くて、次からグイっと速くなるだろうと思っていたんですよ。 バッファに載りきらないなら、何回やっても遅いだろうと思っていたんですよ。 で、ちょいと計測的なことをやってた関係で、同じSQLを何度か叩いて平均、中央を見ようと思っていたんです。 そしたら、 45.71秒、44.90秒、24.44秒、13.32秒、13.12秒・・・ と、段階的に応答
photo by byte MySQLといえば、巷ではInnoDBばかり注目され、MyISAMの地下アイドル化がにわかに語られる今日この頃、皆様いかがお過ごしでしょうか。 まあカジュアルにストレージエンジンを変換するだけで済むなら、簡単なのです。 -- legacy_my_tableをInnoDBストレージエンジンに変換する ALTER TABLE legacy_my_table ENGINE=InnoDB; よし終わった！さあランチタイムだ！ ・・・と片付けてしてしまうと、悲劇が起こるかもしれません。(>o<;) それでは本日、MyISAMからInnoDBへ移行するなら知っておきたい意外な落とし穴とTipsを紹介します。 AUTO INCREMENTの挙動が違う落とし穴 以下に該当するクエリを利用している場合には、注意が必要です。私はハマりました。 INSERT IGNORE INTO
MySQLを使い始めて間もない人がよく陥る罠の中に、気づくと使ってるストレージエンジンがMyISAMだった！ということがある。デフォルトのストレージエンジンはMyISAMなので、MySQLに詳しくない人たちが比較的陥りやすい罠なのだ。そもそもストレージエンジンという概念自体がMySQL独自のものなので仕方のない話である。MyISAMは素晴らしいストレージエンジン（たとえばこのYahoo!の中の人による投稿で言われているように）であるが、長所もあれば短所もある。例えば、 トランザクション対応ではない。 クラッシュセーフではない。 更新と参照が入り乱れた場合の同時実行性能がよくない。 テーブルが大きく（数億行とか）なるとINSERTの性能が劣化する。 などなど。特に前者の2つが問題で、アトミックな操作が必要なところでロジックを実装出来なかったり、サーバがクラッシュした時にデータがお亡くなりにな
どもです。InfiiDBの最終章は、色々とトラブルがあって検証中です。もすこしで解決しそうなので、その時になったらまた記載させていただきます。ごめんなさり・・。今回は、そのトラブルの一つであった、「異なるストレージエンジン間のレプリケーション」の部分の話をさせてください。 異なるストレージエンジン間のレプリケーションって何？ つまり以下のような構成のレプリケーションのことです。 ちょっと前回の「InfiniDB でリアルタイムDWHを作る PART3 ～驚速DWHの構築」のBLACKBOXの部分の話の種明かしをしてしまうと、リアルタイムのしくみは、このフェデレーションによる透過的なテーブル更新を利用してDWHに書き込んでしまえ！という話だったのです。実は今のところ、この試みはさまざまな問題があり、うまいこと話が進まない状態なので今現在は、ちゃんと説明できる状態にはなっておりません。すみませ
Percona Database Performance Blogの翻訳。既に運用を始めたデータベースで、インデックスが正しく使われているか、無駄や不足がないかを確認する方法のまとめ記事。クエリをひとつひとつ確認するのではなく、統計情報を元に判断する分かりやすい方法。 このブログ記事では、MySQLインデックスに手入れする基本的なステップについて見ていこうと思います。 データベースは、インデックス次第でハイパフォーマンスにも、役立たずで遅くて大変にもなりうることはご存知でしょう。インデックスは、時々手入れをする価値がある非常に重要なものです。それでは、何をチェックすればよいのでしょうか？順不同ですが、確認すべき点を挙げてみます。 1. 使われていないインデックス sysスキーマで、使われていないインデックスをとても簡単に見つけられます。 schema_unused_indexes ビューを
これは何 MySQLにリモートでコード実行される可能性のある脆弱性 CVE-2016-6662 が発覚。その概要と対応をまとめる。 目次 これは何 目次 資料 対象バージョン ざっくり言うと？ 設定ファイルが書き換えられるとなぜ攻撃になる？ 今回の脆弱性 my.cnfに追記する ファイルが無い状態から作る 対応方針案 資料 CVE-2016-6662 http://legalhackers.com/advisories/MySQL-Exploit-Remote-Root-Code-Execution-Privesc-CVE-2016-6662.html 対象バージョン 2016 09 13日現在の全てのバージョン。 具体的に言うと、現時点での最新のバージョンの以下を含む、それ以前のバージョンに脆弱性が存在。 5.7.15 5.6.33 5.5.52 要するにパッチがまだない。 MySQL
MySQL を UTF-8 で使おうと思ってハマりがちなのは charset utf8 を指定してしまうことです。 MySQL の UTF-8 には歴史的事情により utf8 と utf8mb4 の二つあります。 UTF-8 は1バイト〜4バイトで1文字が構成される文字コードですが、MySQL の utf8 は4バイト文字を扱うことができません。ハマりたくなければ utf8mb4 を使いましょう。 utf8 を使ってしまった場合に4バイト文字がどのように扱われるか、自分でもうろ覚えだったのでメモしておきます。 登録 接続が utf8mb4 でカラムが utf8mb4 あたりまえですが、そのまま登録されます。 mysql> insert into utf8mb4 (c) values ('美味しい🍣と🍺'); mysql> select * from utf8mb4; +--------
免責 取り敢えずわかっている範囲で書いただけなので、手元で再現やパッチの正当性は確認していません。 自己責任でどうぞ。 これ(2016/09/22 22:00)以降新しい情報が出てきても、おそらくもう更新しません。 CVE-2016-6662 についてはこちら MySQLに重大な脆弱性見つかる、パッチ存在せずデフォルトで影響 - ITmedia ニュース oss-sec: CVE-2016-6662 - MySQL Remote Root Code Execution / Privilege Escalation ( 0day ) この脆弱性を再現させるために必要なもの (未検証) 5.5.52, 5.6.33, 5.7.15は影響を受けないかも知れません。詳しくは図のさらに下に。 手元で再現させてはいませんが、 オリジナルの脆弱性報告 の影響を受けるバージョンがしゃらっと "5.7.14
この画像の例のように、muninでサーバーを軽く監視しておきたいので、インストールしようと思う。 1. muninはepelからインストールするので、入れてなければ EPELを設定。 http://mirrors.fedoraproject.org/publiclist/EPEL/ ここから適当なミラーを選ぶと良いが、どこかのミラーから epel-release-6-8.noarch.rpm を入れる。% su # rpm -ivh http://ftp.riken.jp/Linux/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm ... # vi /etc/yum.repos.d/epel.repo ... enabled=0 (デフォルトではepelを見に行かないようにしておく。この辺はお好みで。) ... 2. 次に、muninとmu
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く