Comments
Description
Transcript
SQL - MySQL Community Downloads
Apr. 3 2012 OTN MySQL User Forum Tokyo MySQL Cluster/MySQL Enterpriseを 活用したフルマネージドホスティング サービス事例紹介 株式会社アルティネット 櫻田 剛史 [email protected] 弊社紹介 株式会社アルティネット http://www.ultinet.co.jp/ 1999年、埼玉・大宮市にて創 業、現在は東京・湯島にござ います SIerです！ インフラ構築 アプリケーション開発 システム運用 LAMP環境を利用した、フルマ ネージドホスティングサービス を得意としています。 地図データ©2012 Google,ZENRIN 2 UltinetとMySQL（の歴史？） MySQL Version 3.22(1999年)からのお付き合いです。 その後～MySQL AB⇒Sun⇒Oracleといろいろ変遷ありま したが、一貫して使い続けています。 実は、前回のMySQLユーザーコンファレンス（2008年10 月31日）でも事例紹介をさせて頂きまして、本日はそれ の続編です。 3 フルマネージドホスティングサービス—ULTiDC 全く新規のシステムを構築する案件だけではなく、既存 のシステム（他のDCにあったり、オンプレミスであったり する）を丸ごと移設（引っ越し）、システムのリニューアル （リプレース）開発、インフラからアプリ部分まで全部の面 倒を見るのが得意。 老朽化したシステムをアプリケーション部分も含めて全 部リニューアルする提案をしています。 移行後環境をLAMPで統一することによって、コストを抑 え、高い品質のプロダクト・サービスとして提供。 MySQL DBはこのビジネスモデルの核となる重要な組 込みソフトウェアコンポーネントです。 4 フルマネージドホスティングサービス—ULTiDC ① まず、既存システムを丸ごと引っ越します アプリケーション ASP.NET (C#) IIS 丸ごと引っ越し SQL-Server Windows Server 既存システム ※他iDCもしくはオンプレミスで存在 5 ULTiDC（弊社のデータセンター） フルマネージドホスティングサービス—ULTiDC ② LAMP環境上でリニューアルします 既存システム （例：Windowsで構築されたシステム） アプリケーション リニューアル ASP.NET (C#) IIS SQL-Server Windows Server ULTiDC（弊社のデータセンター） 6 新システム アプリケーション （リニューアル版） PHP Apache MySQL RedHat Enterprise Linux(RHEL) フルマネージドホスティングサービス—ULTiDC 顧客側メリット 長年の増築開発でつぎはぎ だらけになって保守、運用コ ストが増大したシステムを最 適化できる。 自社でシステム運用担当を 抱えなくて済むことによるコス ト削減。 オンプレミスの場合、災害対 策(DR, BCP)対応のDCで安 定運用できる。 新システム アプリケーション （リニューアル版） PHP Apache MySQL RedHat Enterprise Linux(RHEL) ULTiDC（弊社のデータセンター） 7 フルマネージドホスティングサービス—ULTiDC 移行事例パターン OS Windows UNIX(Solaris, AIX, HP-UX) Linux DB SQL Server Oracle DB PostgreSQL MW Java(Weblogic) ASP.NET(C#,VB) Perl PHP リニューアル アプリケーション （リニューアル版） PHP Apache MySQL RedHat Enterprise Linux(RHEL) LAMPで統合 ※MySQL DBはこのビジネスモデルの核と なる重要な組込みソフトウェアコンポーネント 8 アジャイルソフトウェア開発 スクラム（SCRUM）開発手法 認定スクラムマスターが在籍 価値のあるソフトウェアを短いサイクル（スプリント）で、顧客に届け る仕組みを実践。 公開勉強会も開催してます アジャイルサムライ湯島道場 一緒に開発できる仲間を募集中です！ 9 （ここから本題）本日ご紹介したい事 MySQL Clsuterに興味を持っている人向け MySQL Clusterの具体的な活用事例についてはあまりオープ ンになっていないので、できるだけ具体的な話（当初の期待、 実際の導入時の検討、実運用での苦労、思ったように行かな かった点、今にして思えば考慮すべき点、最新のv7.2検証結 果、今後への期待）をしたい。 商用版のMySQLってどういう意味があるの？って疑問 に感じている人向け 10 一般のユーザ、エンジニアのレベルでは、既にGPLでフリーに 公開されているMySQLに対してお金を払うことに疑問を感じて いる人も多い（僕自身がそうだった）。その中でユーザ視点で の率直なメリット、デメリットを例示して、今まさにモヤモヤ感を 抱いている皆様の判断の一助になれば幸い。 UltinetでのMySQL Cluster導入事例 V7.0.xで２年超の運用継続中 データベースシステムとしての信頼性は非常に高いもの と考えています。 データの喪失のような重大事象は発生なし 個別の事象を解説するのはいろいろと問題があるので、 そこから判明した隠れた仕様、限界と、教訓にフォーカス してご紹介致します。 11 MySQL Cluster導入のための 検討ポイント 12 MySQL Cluster導入検討のポイント（結論） （NDBが適したアプリケーション） NDBを高性能かつ、高可用性(HA)を実現する部品とし て組み込むための、設計、作りこみを前提とする業務シ ステム。 MySQL Enterprise（普通のMySQL）ほど、入れたらぱっと使え るという種類のものではないです（後述します）。 インメモリDBでかつ、トランザクションを使えるというメ リットを生かした、オンライン処理(OLTP)が主体となる システム 13 大量のデータをまとめて処理するバッチ処理は苦手（V7.2の AQLで改善が見られたようではあります） インメモリDBとしてのメリットは、現在は、SSDをベースとしたも のとのコストベネフィット評価をする必要があると思います。 普通のMySQLとの違い 普通のMySQLであれば1個DBを置けばOK 冗長構成のためにはMaster/Salveの構成が一般的 マスタで障害が発生した場合にはSlaveをMasterに昇格させるなどの処置が必要（もしくは、 Heartbeat/DRBDなどでActive/Standby構成を作っておくなどの方法も一般的） 規模が大きくなれば更新系だけであればSlaveをたくさん設置してスケールアウトできるが、 更新系はボトルネックとして残る 最小構成 冗長構成 MySQL DB MySQL DB (Master) 【よくあるActive/Standby構成】 ・READインテンシブなアプリであればSlaveをいっぱい つなげてスケールアウト ・Masterに障害が発生した場合には、自動引き継ぎの 仕組みが必要(MHAとか、ディスクレベルの冗長化(DRBD)とか） アクセス多 MySQL DB (Master) MySQL DB (Slave1) 14 MySQL DB (Slave) MySQL DB (Slave2) MySQL DB (Slave3) MySQL DB (Slave4) MySQL DB (Slave5) MySQL Cluster導入のメリット 高可用性 HA機能が、もともとシステム自体に備わっている （Slave⇒Master昇格操作などを作りこむ必要が無い） 切替が高速（障害検知～収束のための時間を含めても10秒 程度） 高性能 15 インメモリデータベースであるため、ディスクI/Oによる、 READ/WRITEボトルネックが原則発生しない（例外もあり、後 述します） SQLノードを追加することによって、比較的リニアな性能向上 が期待できる 各SQLノードはActive/Active構成のため、更新系も分散させる ことができる（考慮ポイントを後述します） MySQL Clusterを構成するコンポーネント ３種類あります MGMノード ・NDBの管理をするマネジメントノード ・HA構成のF/Oを制御している ・負荷は小さいデーモンなので、SQL ノードなどに同居させることが多い ・オンラインバックアップなど作業を行 うためのコマンドが提供されている MGM 16 DATAノード ・NDBのデータベース本体 ・インメモリDB、トランザクション有効 （分離レベルはREAD COMMITED） ・SQLは直接は解釈できない、専用の NDBAPIを使う DATA SQLノード ・MySQLのインターフェースを提供し、 SQLを解釈してNDBAPIを介してNDB とやり取りする ・要はストレージエンジンとしてNDBを 使えるMySQLと理解できる ・特性上、複数のSQLノードを起動して も更新できる(Active/Active構成、マル チマスタ) SQL MySQL Cluster構成検討 基本的な構成 Web server Web server Web server Web server Web server Web server L2 SW SQL アプリケーションレイヤー SQL/MGM 2号機 SQL/MGM 1号機 SQL MGM Web server データベースレイヤー MGM L2 SW 17 データー 1号機 データー 2号機 データー 3号機 データー 4号機 DATA DATA DATA DATA ノード構成のパターン検討 冗長構成 最小構成 SQL MGM DATA ミニマム構成だが、冗長性も 無いので商用環境には向かない。 SQL SQL DATA DATA MGM MGM 単一障害点（SPOF）を排除した 冗長構成の基本形（NW部分の検討は必要） データ量大 アクセス多 MGM MGM DATA SQL SQL DATA DATA DATA SQL SQL DATA DATA MGM MGM SQL SQL 保存するデータ量（件数）が大きくなった場合にはデータノードを アクセスピークに応じて性能を向上（スケールアウト）させるため 増やして対応できる。ただし、Secondary Index使用時の制約あり には、SQLノードを増やして対応する。 （後述します） 今となっては、この構成が最適なケースが多いかもしれない。 18 ノード構成のパターン検討 構成 タイプ MGM ノード SQL ノード DATA 説明 合計 ノード ノード数 弊社 実績 I 1 1 1 3 最小構成（冗長化無し） 試験・開発環境ならば使えるがそ れ以上の意味が無い II 2 2 2 6 単一障害点(SPOF)無しの冗長構成 （基本形） ○ III 2 2 4 8 保存するデータ量（件数）が大きく なった場合 ○ IV 2 4 2 8 アクセスピークに応じて処理性能を 向上させたい場合 ○ V 2 8 4 14 データ量と処理性能の両方に要求 レベルが高い大規模システム - VI 2 2 3 7 データノードの冗長化を2重ではな く、3重にした場合（レプリカ数3） - VII 2 2 6 10 データ量が多く、データノードを増 やして対応した場合（レプリカ数2） - ○ ※完全にPK（主キー）に帰着できるアプリにできないのであれば、データノードの数は最大でも4程度に止めた方が良い。 19 メリット１：高可用性(HA) データノードの障害を検知後、自動的にF/O 所要時間は最短で約2.0秒（以下実験のログ参照） 16時25分37秒 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 2012-03-23 20 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:37 16:25:38 16:25:38 16:25:38 16:25:38 16:25:38 16:25:38 16:25:38 16:25:38 16:25:38 16:25:41 16:25:41 16:25:41 16:26:23 16:26:44 [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] [MgmtSrvr] ALERT ALERT INFO ALERT INFO INFO INFO ALERT ALERT INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO INFO WARNING ----------------------------------- Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Node Mgmt Node Node2のNDB 3: Node 2 Disconnected 5: Node 2 Disconnected デーモンをkill 5: Communication to Node 2 closed 101: Node 2 Disconnected 5: Communication to Node 2 closed 3: Communication to Node 2 closed 4: Communication to Node 2 closed 4: Node 2 Disconnected 5: Arbitration check won - node group majority 5: President restarts arbitration thread [state=6] 4: Communication to Node 2 closed 5: GCP Take over started 5: Node 5 taking over as DICT master 5: LCP Take over started 5: ParticipatingDIH = 0000000000000000 5: ParticipatingLQH = 0000000000000000 5: m_LCP_COMPLETE_REP_Counter_DIH = [SignalCounter: m_count=0 0000000000000000] 5: m_LCP_COMPLETE_REP_Counter_LQH = [SignalCounter: m_count=0 0000000000000000] 5: m_LAST_LCP_FRAG_ORD = [SignalCounter: m_count=0 0000000000000000] 5: m_LCP_COMPLETE_REP_From_Master_Received = 1 5: LCP Take over completed (state = 4) 5: ParticipatingDIH = 0000000000000000 5: ParticipatingLQH = 0000000000000000 5: m_LCP_COMPLETE_REP_Counter_DIH = [SignalCounter: m_count=0 0000000000000000] 5: m_LCP_COMPLETE_REP_Counter_LQH = [SignalCounter: m_count=0 0000000000000000] 5: m_LAST_LCP_FRAG_ORD = [SignalCounter: m_count=0 0000000000000000] 5: m_LCP_COMPLETE_REP_From_Master_Received = 1 16時25分38秒 5: GCP Take over completed F/O完了 5: kk: 31025252/3 0 0 5: Communication to Node 2 opened 4: Communication to Node 2 opened 3: Communication to Node 2 opened server state: nodeid 2 reserved for ip 172.16.11.151, m_reserved_nodes 2, 21, 22, 23, 24, 25 and 101. 5: Releasing node id allocation for node 2 HA構築時の考慮事項 MGMノード障害 SQLノード障害 MGMデーモンを複数台起動して、DATAノード側から両方参照(connect-strings で2個設定)しておけば、自動的にF/Oされます（但し、configファイルの自動同期 はうまく動かないです）。 MGMノードはNDBのメンテナンス、DATAノード、SQLノード障害発生時の自動 F/Oを行うために必要なものなので、Active/Standby構成も有りです（他のノード で障害が発生した時に存在していれば問題がないため）が、多重障害が発生 すれば当然システムが全断します。 切り離しはNDB側で制御されますが、アプリケーション側でF/Oの切り替えを作 り込んでおく必要があります。 高負荷対策としてautoincremt-prefechを有効にしている場合、一時的にばらつ きが発生するので、その場合でも問題が出ないようにアプリを作っておく必要 があります。 DATAノード障害 21 自動的にNDBのF/O機能が動きます。 障害発生時のクエリ・トランザクションはSQLノードより、エラーとして返却されま すので、適切なエラーハンドリング、リトライ（リカバリ）処理はアプリ側で作り込 みましょう メリット２：高性能インメモリDB 主キー(PK)でのランダムREAD(SELECT)性能試験を実施 該当のテーブルには数千万件のデータが存在する状態 60000 50000 40000 30000 1 Instance Throughput(TPS) 3 Instances Throughput(TPS) 20000 10000 0 0 50 100 150 並列数 22 200 250 300 高性能DBとして扱うためのポイント スキーマの全面的な見直しは必須 InnoDBの時と同じスキーマのままMySQL Clusterにマイグ レーションしても高い性能は享受できません。 （スキーマ・アプリ側のポイント） 23 可能な限り主キー(PK)に帰着させること 主キー(PK)に帰着が難しい場合も、ユニークインデックスを使 える場合はある程度性能が出るが、並列限界値、長時間ロッ ク多発の原因になるので注意が必要。 セカンダリインデックスはできるだけ使わないようにすること。 （並列処理は限界値が非常に低い、V7.2で改善あり） その他、利用上の要考慮ポイント GCP Stopを回避 GCP Stopが発生すると、データノードがダウン（再起動にデー タ件数・インデックス個数が多いと長時間（１時間～２時間）か かる）してしまうので、なるべく発生しないようにアプリ側での 作り込みが必要。 GCP Stopが発生する原因の一つとして、一度に大量のデータ 行数をCOMMITする際に設定閾値を超過すると発生してしま う。更新系(UPDATE/DELETE)クエリにおいてWHERE節での 指定には注意が必要（あらかじめ、最大に対象となる行数が 決まることが必須）。 JOINをなるべく避ける 24 JOINが苦手なので、バッチ処理等で大量の行を結合すること は避けるべき（V7.2 新機能AQLで改善あり）。 ちょっとだけ Version 7.2性能検証 25 AQLによるJOIN性能の改善 MySQL Cluster(NDB)では、これまでJOINで性能が全然 出ませんでした… V7.2のAQLによる性能改善を検証してみました。 検証条件 結合テーブル数： ２ 結果セット想定： 36,000件 結果 26 V7.0.x 12秒 V7.5.2 0.14秒 約85倍高速に!! ※社内のテストラボで試した結果 です。条件によっては速くならない こともあります。 SQLノード単体の性能向上 これまでは、同時並列的に大量のアクセスをかけると比 較的すぐに性能が劣化していました。 MySQL ClusterではSQLノードを増やすことで比較的リニ アにスケールアウトできていましたが、V7.2のSQLノード が最新のMySQL 5.5ベースになったことにより、SQLノー ド単体の同時並列処理数の限界値が伸びています。 ※社内のテストラボで試した結果 です。条件によっては速くならない こともあります。 27 SQLノード単体の性能向上（結果） 60000 スループット(TPS) 50000 40000 並列数を上げても性 能が劣化しない(V7.2) 30000 V7.2.5 V7.0.x 20000 10000 0 0 50 100 150 並列数 28 200 250 MySQL Cluster導入検討のポイント（まとめ） （NDBが適したアプリケーション） NDBを高性能かつ、高可用性(HA)を実現する部品とし て組み込むための、設計、作りこみを前提とする業務シ ステム。 インメモリDBでかつ、トランザクションを使えるというメ リットを生かした、オンライン処理(OLTP)が主体となる システム 29 大量のデータをまとめて処理するバッチ処理は苦手なので、 InnoDBエンジンと併用するなどの考慮が必要。 ユーザ視点から見た商用版MySQL ライセンスの優位性 30 ユーザ視点から見たMySQL商用版へのギモン （疑問１）Community(GPL)版がMySQL公式サイトからバ イナリを無料で入手できるのに、商用版を購入する必要 があるのか？ （疑問２）RedHat Enterprise Linuxの公式パッケージにも MySQLがあるけれどもどちらを使う方が良い？ （疑問３）MySQL Enterprise(InnoDB)は、もう相当枯れて いるんじゃないの？商用サポートが必要なケースってそ そうそう無いんじゃない？ ※本疑問と回答は、Ultinetが非公式に調査し た結果であり、各メーカーの公式見解ではあ りません。 31 （回答１）商用版バイナリ利用の意義 GPLの適用を避けたい（組込み系のプロダクトなどで多 いニーズ）ということであれば、商用ライセンスは必須で す。 組込み目的であれば、年間サブスクリプション契約とは 別メニューであるOEMライセンス(ESL)契約が割安です。 Community版には一部、Enterprise版から落とされてい る機能(Thread pooling等）がありますので、それを使用し たい場合にはEnterprise版を使用しましょう。 （商用版のアドバンテージ） 32 24h7daysのテクニカルサポートが使えます（無制限、日本語は weekday daytimeのみ、それ以外の時間帯は英語）。 MySQL Enterprise Monitor（とQuery Analyzer）やEnterprise Backupなどの便利なツール群が提供されます。 （回答２）RHEL環境でのバイナリ選択検討 Oracle （標準提供されるバイナリ） Community(GPL)版 商用(Enterprise)版 最新版(5.5 GA)が提供中 RedHat 3/21リリース! Ver. 5.5.22 （標準提供されるバイナリ） RHEL5 MySQL V5.0.x（22～95) RHEL6 MySQL V5.1.x（47～61） いずれも(Community)GPL版がベース ※RHEL6向けのバイナリパッケージは現在提 供されていない（Generic Linux版を使用するこ とは可能）。 ※V5.0.xはOracle側では2011/11でEOL ※現在V5.5のパッケージは提供なし （サポート体制） 商用ライセンス（サブスクリプションなど）を購 入していれば、独自ビルドであってもサポート はしてくれます（但し、Oracle側で再現性が取 れない場合は限界あり）。 （サポート体制） 標準パッケージ群にはRedHatのメンテナー チームがRHEL OSのEOLまで確保される。 RedHatの標準パッケージもユーザの独自ビル ドとしての扱いでサポートしてくれます。 33 ※Oracle提供のパッケージ (Community/Enterprise版問わず)を使った場 合のサポート体制は無い。 （回答３）商用テクニカルサポートの意義 重大度（１～４）に応じた、サービスレベルが提供されます。 その場ですぐの解決が保障されるわけではないのですが、暫定回 避策(Workaround)の提示など、サポートの動きをしてくれます。 問題事象の原因究明と、再発防止策をサポートしてくれます 弊社の実績では、２年超で１０数件くらいの問題事象が発生してい ます。 納得・解決するまで、とことん対応してくれるのもありがたい（やり取 りが100往復を超えているものあります。 InnoDBがらみの障害事象は尐ないですが、ゼロではないの で、やはりサポートがあるのは心強いです。 MySQL Clusterの方は、テクニカルサポート無しで運用をする のは不可能だったと思います。 34 MySQL商用サポートへの今後の期待（１） 安心・安全なバージョンアップへ 35 商用環境でのDBのバージョンアップは現状、マイナーバー ジョンであってもうっかり上げられないです（それが故の問題 を絶対に発生させたくないので）。 できれば、新、旧システムで全く同じ処理を実行させ、でしば らく並行稼働させて検証できるような仕組みがあるとありがた いです（レプリケーション（RBR)だと必ずしも検証にならないの で）。 MySQL商用サポートへの今後の期待（２） EOLをもっと長期に 36 業務システムでは、ソフトウェアのライフサイクルがどんどん 長くなっていますが、商用サポートの期限(EOL)が短いことが ネックになりそうです。 SIerとしてはEOLを迎える前にリプレース、マイグレーションの 提案を進めていますが、お客様によっては費用対効果により 提案しても受け入れられない事例もあります。 特に、組込みシステムでは一旦、完成して納入すると極力手 を触れない傾向もあるため、たとえばOSと同程度の期間メン テナンス、サポートを継続してもらえるとありがたいです。 UltinetによるMySQL Cluster導入支援サービス MySQL コンサルティング MySQLデータベース設計・開発・導入支援 MySQLデータベースチューニング URL http://www.ultinet.co.jp/mysql/ お問い合わせ・ご相談はこちらまで [email protected] 37