Movatterモバイル変換

MySQL 運用虎の巻！@ 徳島オープンセミナー 2013奥野幹也@nippondanjimikiya (dot) okuno (at) gmail (dot) com

免責事項●本プレゼンテーションにおいて示されている見解は、私自身の見解であって、オラクル・コーポレーションの見解を必ずしも反映したものではありません。ご了承ください。

自己紹介●MySQL サポートエンジニア–日々のしごと●●●●ライフワーク–自由なソフトウェアの普及●●トラブルシューティング全般Q&A 回答パフォーマンスチューニングなどオープンソースではないブログ––漢のコンピュータ道http://nippondanji.blogspot.com/今日は個人として参加しています。

アジェンダ●●●●●●MySQL の概要インストールと初期設定ユーザー管理開発関係の話バックアップパフォーマンスチューニング

MySQL とは●●●●●オープンソースのリレーショナル・データベース・マネージメント・システムMySQL AB => Sun Microsystems => Oracle によって開発。Web 開発においては圧倒的なシェア使い易く、安定して、高速に動作するのがウリホームページ––http://www-jp.mysql.com/http://dev.mysql.com/

MySQL の機能的特徴●●●●●●●●ANSI SQL 標準に準拠（一部の文法を除く。）種々のプラットフォームをサポート（ Windows 、 Linux 、 Mac 、各種 UNIX 系 OS ）ストレージエンジンによりデータを格納するレイヤーを仮想化ACID 準拠のトランザクションXA トランザクション非同期・準同期 - Master/Slave型レプリケーション水平パーティショニング（ Range 、 List 、 Hash 、 Key ）ストアドプロシージャ、ストアドファンクション、トリガ●●●●●●●●●●●UNIONビューサブクエリINFORMATION_SCHEMASSL による通信Unicode をはじめとした各種文字コードのサポート全文検索タイムゾーンのサポート多種多様な開発言語のサポート（ C 、 C++ 、 Java 、 Perl 、 PHP 、 Python 、Ruby など）ODBC による接続のサポートNoSQL インターフェイス

MySQL の構造的特徴●●●●●●●●●●シングルプロセス・マルチスレッド1 セッション＝ 1 スレッドストレージエンジン API によるデータストアの仮想化その他各種プラグイン APIGNU Bison による Lexical Scannerコストベースのオプティマイザ全ての更新を保存するバイナリログ2 種類のスレッドで実装された非同期型レプリケーション移植性の高い関数群（ MySQL システムライブラリ mysys ）を使って実装ソースコードは C/C++ 混在

MySQL の仕組み（イメージ図）クライアントJavaクライアントPHPコネクションスレッドコネクションスレッドクライアントODBCコネクションスレッドSQL の解析と最適化は共通パーサー・オプティマイザ・アクセス管理等ストレージエンジン APIMyISAMtable_name.MYItable_name.MYDInnoDBテーブルスペースログファイルMySQLClusterNDBAPIMySQL サーバデータノードデータの格納やアクセス方法はストレージエンジンごとに違う

ライセンス●MySQL はデュアルライセンスを採用–Community 版●●–GPLv2FOSS License Exception あり商用版●●●コマーシャルライセンスサブスクリプションOEMOracle がすべての著作権を保有しているから可能

GPL - GNU General Public License●リチャード・ストールマン氏により考案されたソフトウェアライセンス––––●●1989 年〜世界で最も用いられているオープンソースライセンスフリー（自由な）ソフトウェアを実現するために考えられたコピーレフトを具現化するためのライセンス現在の著作権法の上に立脚Copyleft – All rights reversed–Copyright – All rights reserved を文字ったもの●––意味は真逆ユーザーに完全な自由を認める。再配布物（派生物や 2 次利用した作品）のライセンスを同じものにすることを要求

GPL の適用範囲●ライセンスを GPL にする必要がある場合––GPL のソフトウェアを改造してまたはそのまま再配布GPL のソフトウェアを利用したソフトウェアを再配布●●●●GPL のライブラリをリンクGPL のソースコードを拝借プロセス間通信で GPL のプログラムと協調動作。（グレーゾーン中のグレーゾーン！）ライセンスを GPL にする必要がない場合––––––ソフトウェアを頒布しない場合ネットワーク経由で GPL のプログラムと協調動作単独で動作するプロセスとしてバンドルGPL の OS カーネル上で動作するGPL のシステムライブラリを利用するGPL ソフトウェア用の”インストーラ”

インストールは 15 分●3 ステップ––––●●●ダウンロードhttp://dev.mysql.com/downloadsパッケージのインストール起動！！慣れれば 5 分でインストール可能最新版を使うべし！Windows 版はインストールウィザードあり––サーバー本体だけでなく、ドライバや GUI ツールも一発インストールただし XP は非対応

MySQL Sandboxhttp://mysqlsandbox.net/●●●●Giuseppe Maxia 氏による検証用 MySQL イントールツール複数のバージョンのインスタンスを同時にインストールレプリケーションも一発構築可能対応プラットフォームは *NIX–tar.gz 版のパッケージを利用

mysql_secure_installation●セキュリティの脅威になりやすい初期設定を変更するツール––––root ユーザーのパスワード変更リモートからの root ユーザーによるログインを無効化匿名ユーザーの削除test データベース（誰でもアクセスできる）の削除

鉄板設定これだけはやっておけ！●スロークエリログの有効化––●バイナリログの有効化–––●character_set_server = utf8接続数––●log_bin=mysql-binexpired_logs_days = 30sync_binlog=1文字コード–●slow_query_log=1long_query_time=1max_connections = 1000limits.conf で FD を増やしておくことInnoDB の各種パラメータ調整–––––innodb_buffer_pool_size = 10Ginnodb_log_file_size = 1Ginnodb_log_buffer_size = 64Minnodb_file_format = Barracudainnodb_io_capacity = 2000 # SSD の場合

MySQL のユーザーアカウント●●●username@hostname という形式– username は任意のユーザー名（文字列）– hostname はクライアントのホスト名または IP アドレス– ユーザー名が同じでも接続元が違えば別アカウント！匿名ユーザー– ''@host1ワイルドカード = %– john@'%' … 任意のホスト名に一致– john@'%.hoge.com' … hoge.com というドメイン名に一致– john@'192.168.1.%' … 192.168.1.0 のネットワークに一致● john@'192.168.1.0/255.255.255.0' のほうがベター

ユーザーアカウントのマッチング●●●より具体的なアカウントから先にマッチするユーザー名よりもホスト名優先優先順位の例1.john@hoge2.''@hoge3.john@'%.hoge.com'4.john@'%'5.''@'%'●最初にマッチしたユーザーアカウントが選択される

MySQL 5.6 で利用可能な認証プラグインプラグイン名説明商用Nativeデフォルトの認証方式Old Nativeバージョン 4.1 以前の認証方式SHA-256SHA-256 をパスワードのハッシュに利用する方式Clear Text平文を用いた認証方式。外部認証を行う場合に必要PAMPAM を用いた認証方式。 MySQL サーバーにはパ Yスワードを平文で送る必要があるWindows NativeWindows 認証を用いて接続するSocket PeerCredentialUNIX ドメインソケットで接続するときに利用可能な特殊なプラグイン。Y

ユーザーの作成例SHA-256 プラグインの場合●ユーザーの作成–●CREATE USER 'sha256user'@'localhost'IDENTIFIED WITH sha256_password;パスワードの設定––SET old_passwords = 2;SET PASSWORD FOR 'sha256user'@'localhost' =PASSWORD('sha256P@ss');

利用可能なドライバの例言語名称配布元ライセンスClibmysqlclientOracleGPLv2C++Connector/C++OracleGPLv2JavaConnector/JOracleGPLv2.NETConnector/.NETOracleGPLv2ODBCConnector/ODBCOracleGPLv2PHPmysqlndPHPPHPPythonConnector/PythonOracleGPLv2PerlDBD::mysqlcpanGPLv2RubyRuby/MySQLgem(tmtm 氏 )Ruby

FOSS License Exception●平たく言うと・・・–●ドライバが対象–●自由あるいはオープンソースソフトウェアのためにMySQL を利用する場合は、 GPLv2 を適用しなくても良いという規定サーバーは Exception なし適用されるライセンスのリスト––http://www.mysql.com/about/legal/licensing/fossexception/例） AGPLv3 、 GPLv3 、 LGPLv2 〜、 MIT 、 BSD 、 Apache License 、 PHP License 、 Python License 、 ArtisticLicense

意外とハマりやすい文字コードMySQL の文字コードの実装はとても柔軟柔軟さが増せば複雑さも増す・・・

MySQL で利用可能な文字コード文字コード名対応文字ストレージサイズsjisJIS X 0208:19971 〜 2 バイトcp932JIS X 0208:1997 + NEC 特殊文字・ IBM拡張文字1 〜 2 バイトujisJIS X 0208:19971 〜 3 バイトeucjpmsJIS X 0208:1997 + NEC 特殊文字・ IBM拡張文字1 〜 3 バイトutf8JIS X 0208:1997 + NEC 特殊文字・ IBM拡張文字1 〜 3 バイトutf8mb4JIS X 0213:20041 〜 4 バイト

文字コードと照合順序●文字コード–●利用可能な文字の集合をコード化したもの照合順序–––文字コードに対する並び順を定義したもの大文字小文字の区別あり・なしなどSHOW COLLATIONS

文字コードが適用される箇所●カラム内のデータ–●●文字コードはカラムごとに異なるものを指定可能テーブル名などのメタデータクライアントとの通信

文字コードの指定はカラム単位CREATE TABLE t (a VARCHAR(100) CHARACTER SET cp932,b VARCHAR(100) CHARACTER SET eucjpms,c VARCHAR(100) CHARACTER SET utf8,:);必要に応じて自動変換

MySQL サーバーにおける文字コード自動変換④ データを蓄える際の文字コード② クエリの実行に利用する文字コード⑤ テーブル名やカラム名に対する文字コードテーブル① 送信するSQL 文に対する文字コードクライアントセッションMySQL サーバー⑥ ファイル名を解決する際の文字コード③ クエリの実行結果に対する文字コードファイルシステム出展：エキスパートのための MySQL［運用＋管理］トラブルシューティングガイド

カラムの文字コードのデフォルト値CREATE TABLE t (a VARCHAR(100) CHARACTER SET cp932,b VARCHAR(100) CHARACTER SET eucjpms,c VARCHAR(100),:指定がない) CHARACTER SET utf8;デフォルト値としてテーブルの文字コードがカラムに適用される

テーブルの文字コードのデフォルト値CREATE TABLE t (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100),:);カラム、テーブル双方に文字コードの指定がないCREATE DATABASE db CHARACTER SET utf8;データベースの文字コードが適用される

データベースの文字コードのデフォルト値CREATE DATABASE db1 CHARACTER SET utf8;CREATE DATABASE db2;データベース作成時に文字コードの指定がない指定がない場合にはcharacter_set_serverが適用される

文字コード適用のルールcharacer_set_serverデータベースの文字コード（ character_set_database ）テーブルのデフォルト文字コードカラムの文字コード優先度が最も高い

トランザクションが利用可能なストレージエンジン●InnoDB–––––標準の MySQL に添付多くの負荷パターンで良好な性能を発揮行レベルロックMVCCクラスタインデックス●ndbcluster––––––MySQL サーバーとは別パッケージで配布並列分散型のデータストアシェアードナッシングアーキテクチャインメモリデータベースピーキーな性能特性Join Pushdown

利用可能な分離レベルダーティリード反復不可能読み取りファントムOXOREAD-COMMITTEDXXOREPEATABLE-READXOXXOX分離レベルREADUNCOMMITTEDSERIALIZABLE分離性低高

ロックの種類●データへのアクセスはすべて行ロック––Non-locking readRecord Locks●●––●インデックスレコードをロックするX-lock / S-lockNext Key lockInsertion Intentionテーブルロックあり–––LOCK TABLESDDL 用IX 、 X 、 IS 、 S ロック

ネクストキーロック●●Next Key Lock = Record Lock + Gap LockGap Lock とは–––レコードとレコードの間の仮想的な空間をロックするS-Lock / X-Lockファントム防止●●X-Gap-Lock は Insertion Intention Lock と競合するInsertion Intention Lock––レコードを挿入する前に Gap をロックする共有ロック●同じ Gap に対して同時に INSERT 可能

エラー処理●●最低でもトランザクションのリトライは必須トランザクション理論では、トランザクションの終了ステータスは Commit もしくは Abort–Abort を皆無にすることは理論上不可能

リトライするべきエラーエラーの種類エラーの判別方法追加で行うべき対処デッドロックエラーコード 1213または SQLSTATE40001行へアクセスする順序の見直し。Lock WaitTimeoutエラーコード 1205SHOW PROCESSLIST の記録。長時間ロックを保持するトランザクションのチューニング。一時的なエラー（ NDB ）エラーコード 1297各種リソースの見直し。接続関係のエラーSQLSTATE 08S01COMMIT 中にエラーが発生した場合は COMMIT が成功したかどうかの確認。

レプリケーションの用途●●●●●高可用性用スタンバイバックアップレポーティング等の負荷の分離スケールアウトディザスタリカバリ

スタンバイとしてのレプリケーション●非同期または準同期–準同期はスレーブへログが到着するのを待つ●●●切り替えは高速！–––●スレーブへ切り替えてもデータの損失はないマスター側で COMMIT の応答が返ったかどうかは分からない論理的なコピーなのでクラッシュリカバリは不要MySQL 自身には切り替えのための機能はないクライアントの繋ぎ変え問題点––切り戻しの手順が複雑になりがちスレーブが複数ある場合は差分を解消する必要あり

準同期レプリケーションアプリケーション1. COMMIT7. send_ok6-1-2. ack接続スレッド3. ログ更新ストレージエンジン2.6-1-1. ackスレーブ接続スレッドバイ更ナリ新ログ4. バイナリログ送信バイナリログマスターI/OスレッドSQLスレッド5. リレーログ更新6-2. 更新の適用リレーログストレージエンジンスレーブ

クエリのチューニング

Explainmysql> EXPLAIN SELECT 'cond1' AS LABEL, COUNT(1) AS COUNT FROM Country WHERE Code LIKE 'J%' UNION SELECT 'cond2', COUNT(1)FROM Country WHERE IndepYear > 1900 UNION SELECT 'cond3', COUNT(1) FROM Country WHERE Continent = 'Africa' UNION SELECT'cond4', count(1) FROM Country WHERE Name = LocalName;+----+--------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+| id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra|+----+--------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+| 1 | PRIMARY| Country| range | PRIMARY| PRIMARY | 3| NULL |3 | Using where; Using index || 2 | UNION| Country| ALL| NULL| NULL| NULL| NULL | 239 | Using where|| 3 | UNION| Country| ALL| NULL| NULL| NULL| NULL | 239 | Using where|| 4 | UNION| Country| ALL| NULL| NULL| NULL| NULL | 239 | Using where|| NULL | UNION RESULT | <union1,2,3,4> | ALL| NULL| NULL| NULL| NULL | NULL | Using temporary|+----+--------------+----------------+-------+---------------+---------+---------+------+------+--------------------------+5 rows in set (0.00 sec)

クエリの構造を読み解く●select_type でクエリの構造が分かる！–JOIN●–UNION●–SIMPLEUNION, UNION RESULTサブクエリ●●●FROM 句のサブクエリ ... PRIMARY, DERIVEDその他 ... PRIMARY, SUBQUERY, DEPENDENTSUBQUERY, UNCACHEABLE SUBQUERY など。サブクエリが UNION になっている場合 ...DEPENDENT UNION, UNCACHEABLE UNION

JOIN の種類●MySQL にある JOIN の種類––––Nested Loop JoinBlock Nested Loop Join ← 変形 NLJBatched Key Access Join ← 変形 BNLJPushdown Join （ MySQL Cluster のみ。爆速）

Block Nested Look Joint2t1スキャン1. WHERE 句の条件に沿ってt1 からレコードをフェッチしバッファに格納2. バッファが満タンになったら t2 をスキャンして JOIN3. t1 のすべてのレコードについてJOIN BufferくりかえしUsing Join Buffer...

Batched Key Access Joint2t11. WHERE 句の条件に沿ってt1 からレコードをフェッチしバッファに格納2. バッファが満タンになったら MRR で t2 からレコードをフェッチしてJOINMRRScan4. t1 のすべてのレコードについてくりかえし3. ROWID の順でt2 からレコードをフェッチJOIN Buffer

Performance Schema●●●●各種統計情報を収集するストレージエンジンSELECT で情報にアクセスUPDATE で設定情報が豊富だが、豊富すぎて直接扱うのは難しい––ps_helper が便利！http://www.markleith.co.uk/ps_helper/

バックアップ戦略〜考慮するべきポイント〜●●●●●オンライン or オフライン論理 or 物理フル or 差分 / 増分どのストレージエンジンが対象かバックアップ / リストアにかかる時間––●スケジュール。––●●●復旧優先？データ保全優先？どの時間帯にとるか？どこまで巻戻っても OK か？手順は確立されているか？バックアップの保管先有償 or 無償

バックアップツールの種類バックアップ方法（ツール名）オンラインInnoDBMyISAM形式バックアップ速度リストア速度フル差分mysqldumpYesYesYesLogicalMidSlowYesNoコールドバックアップNoYesYesRawFastFastYesNoスナップショットYesYesYesRawInstantFastYesNoバイナリログYesYesYesLogical--SlowNoYesMySQL EnterpriseBackupYesYesYesInnoDB FastNativeFastYesYesレプリケーションYesYesYesLogicalContigu VeryousFastYesYes

バックアップのためのレプリケーション活用●フルバックアップの負荷分散––●スレーブはマスターの完全なコピーマスターへの影響がないのでどの方式でバックアップを取ることも可能遅延レプリケーション–スレーブ側で更新の適用を意図的に遅らせる●–例えば１時間などヒューマンエラー（間違ってテーブルを DROP した！）などが起きても直ぐに代わりとして利用可能

まとめ●●●MySQL はお手頃な RDBMSGPLv2+FOSS License Exception / 商用ライセンス運用のコツをおさえて安定稼働––––––––設定のツボを抑える文字コードは確実に指定トランザクションのエラー処理は忘れずに使い方広がるレプリケーション準同期レプリケーションでお手軽 HAMySQL Workbench で Visual Explainバックアップは万全に遅延レプリケーションでお手軽バックアップ

Q&A!!ご静聴ありがとうございました。

Movatterモバイル変換

Change Language

Mysql toranomaki

Embed presentation

Recommended

More Related Content

What's hot

Viewers also liked

Similar to Mysql toranomaki

More from Mikiya Okuno

Mysql toranomaki