11 Bitrix24のMySQL「料理のレシピ」

新しい大きなプロジェクトであるBitrix24を設計、開発、および立ち上げる際に、チームワーク（および無料-最大12ユーザー）向けの非常にクールなサービスを作成するだけでなく、クラウドの運用経験を収集して蓄積したいと考えましたWebサービス。高負荷のフォールトトレラントプロジェクトの開発能力を「活用」し、最も重要なことは、この知識をパートナーおよび「高負荷」のトピックに近いすべてのWeb開発者と共有することです。 :)

もちろん、1つの記事（1つでもない）で、絶対にすべてのプロジェクトに適した普遍的な「レシピ」を説明することは不可能です。一部の人にとっては、生産性がより重要です（信頼性を損なう場合もあります）。とりわけ、どこかに多くの小さなテーブルがあり、どこかに大量のデータがある...

特定の実用的な問題を解決する上で、私たちの仕事に何度も役立ってきた「ハイライト」を説明しようとしました。 それらが役に立つことを願っています。 :)

順番に始めましょう。

Bitrix24が Amazonにデプロイされていることをすでに何度も述べ、 書いています。 そして、私たちはさまざまなクラウドサービスを愛し、積極的に使用しているため、最初の質問...

1.なぜRDSを使用しないのですか？

Amazon Relational Database Service （Amazon RDS）はクラウドベースのデータベースです。 MS SQL、Oracle、および-私たちにとって興味深いもの-MySQLのサポートがあります。

私たちは長い間じっと見つめていましたが、プロジェクトでそれを使いたいと思いました...その結果、私たちはこの考えを捨てました。 いくつかの主な理由：

• 第一に、システムは柔軟性と透過性が十分ではありません。 たとえば、データベースへの完全なルートアクセスを取得しません。 そしてこれは、特定の（特にプロジェクト用の）特定の設定が機能しない場合があることを意味します。
• 事故が発生すると、長時間のダウンタイムが発生するリスクがあります。 そして、実際には、このリスクは非常に現実的です。 はい、1つのゾーンのみにあるベースがより頻繁に影響を受け、いわゆるマルチAZ DBインスタンスを使用してフォールトトレランスを確保できます。 しかし-それらについてさらに...
• データベースにマルチAZ DBインスタンスを使用する場合、スタンバイバックアップは常に別のAZ（アベイラビリティゾーン）に書き込まれ、最初のゾーンでの事故の場合、自動的に別のゾーンに切り替わります。 ただし、この場合、ベースを持つ1つのインスタンスのコストは、ちょうど2倍高くなります。 同時に、ユーザーは1台のマシンのリソースのみを効果的に使用します（先ほど説明したマスターレプリケーションスキームとは異なります）。

これはすべて、RDSが不適切なサービスであることを意味するものではなく、使用しないでください。 そうではありません。 彼は私たちに特に合っていませんでした。 そして、おそらく、誰かがAmazonツールを使用して正確にスケーリングとフォールトトレランスを提供する方がはるかに簡単になるでしょう。

2.マスタースレーブですか？ いいえ、マスターマスター！

MySQLの標準のマスタースレーブレプリケーションスキームは、多くのプロジェクトで長い間使用され、いくつかの問題を解決しています。負荷スケーリング（読み取り専用）-スレーブへのクエリ（SELECT）の再配布、フォールトトレランス。

しかし、決定-完全ではありません。

1.スケーリングして記録したい。
2.信頼できるフェイルオーバーを実現し、事故が発生した場合に自動的に動作し続けたい（マスターでの事故の場合のマスター/スレーブでは、手動モードまたは半自動モードでスレーブの1つをマスターの役割に切り替える必要があります）

これらの問題を解決するために、「マスター-マスター」レプリケーションを使用します。 繰り返しますが、最近、Habréの別の投稿がこの手法に当てられています。

3. MySQL？ いいえ、Percona Server！

最初の数ヶ月（プロトタイプ、開発プロセス、サービスのクローズドベータテストの開始時）で、標準MySQLに取り組みました。 そして、彼らが長く働くほど、彼らはさまざまなフォークをよく見ました。 最も興味深いのは、私たちの意見では、 Percona ServerとMariaDBでした。

最終的に、Perkonを選択しました-もちろん、同様の「反転」ロゴがあるためです。 ;）



...そして、私たちにとって非常に重要であることが判明したいくつかの機能：

• Percona Serverは、低速ディスクでの作業に最適化されています。 これは「クラウド」についても非常に当てはまります。従来のドライブは、「アイロン」サーバーの通常のドライブよりも（残念ながら）ほとんど常に低速です。 ソフトウェア RAIDの編成は問題をうまく解決します（RAID-10を使用します）が、サーバー側の最適化はプラスにすぎません。
• （大量のデータを含む）データベースの再起動は、費用のかかる長時間の操作です。 Perconには、高速シャットダウン、バッファプールのプリロードなど、はるかに高速に再起動できる多数の機能があります。 後者を使用すると、再起動中にバッファプールの状態を保存できるため、開始直後に「ウォームアップ」ベースを取得できます（長時間かかることはありません）。
• 多くのカウンターと高度なレポート（ユーザー、テーブル、SHOW ENGINE INNODB STATUSの拡張出力など）。たとえば、システムをロードする最も「クライアント」を見つけるのに役立ちます。
• XtraDBストレージエンジン-標準のInnoDBとの後方互換性がありますが、高負荷プロジェクト用に最適化されています。

完全なリストは、 Perconaサーバー機能の比較セクションの Webサイトにあります 。

重要なポイント-標準のMySQLからPercona Serverへの移行では、コードやアプリケーションロジックを変更する必要はありませんでした。

そして、ここで、「移動」のプロセスは非常に興味深いものでした。 また、「マスター-マスター」レプリケーションでスキームを使用したおかげで、ユーザーにはまったく気付かれませんでした。 単にダウンタイムはありませんでした。

移動スキームは次のとおりです。

• 「ゼロ」ステップ-スナップショットマシンのテストベンチを、実際のデータをベースに上げました。 そして、安定した動作のために構成のデバッグを開始しました。 そして、もちろん、構成ファイルでPercona固有のオプションを選択します。
• 安定した構成を受け取ったので、すべてのトラフィックを1つのDCに切り替え、データベースは負荷なしでレプリケーションからオフに切り替えられました。
• これに基づいて、Percona Serverに移行しました。
• レプリケーションにベースを含めました（標準のMySQLおよびPercona Server互換）。
• データの同期を待機しました。
• その後、2番目のベースで同じ手順を実行しました。

4. MyISAM？ InnoDB？

ここではすべてが簡単です。

• MyISAMの長いクエリでは、テーブル全体がロックされます。 InnoDBでは、使用する行のみ。 したがって、InnoDBでは、長いリクエストが他のリクエストに影響を与える可能性は低く、ユーザーエクスペリエンスには影響しません。
• 大量のデータで高負荷の場合、MyISAMテーブルが破損します。 これは既知の事実です。 これは当社のサービスでは受け入れられません。
• もちろん、「改善された」InnoDB-XtraDBがあるため、このストレージエンジンを使用します。 :)

* * *

アーキテクチャの問題からより実用的な問題に移行します。 :)

5.すべてのデータを複製する必要がありますか？ いいえ、すべてではありません。

ほとんどすべてのプロジェクトには、損失が重要なデータまたは回復可能なデータがあります。 含む-およびデータベース内。

私たちの場合、そのようなデータはセッションでした。 すべてを複製することの何が問題になりましたか？

• セッションテーブルには、SELECTとともに、多くの書き込み操作（INSERT、UPDATE、DELETE）があります。 これは、スレーブベースに余分な負荷をかけることを意味します。 回避できる負荷。
• さらに、query_cache_sizeの値が十分に大きいと、これらのテーブルでのアクティブな作業とレプリケーションへの参加により、多くのスレッドが「 クエリキャッシュロックの待機」状態（SHOW PROCESSLISTで表示）で「フリーズ」するという事実に直面します 。 さらに、これにはCPUの負荷の増加とパフォーマンスの一般的な低下が伴います。

このデータをレプリケーションから除外することで、問題は完全に解決しました。

除外する方法は？ さまざまな方法があります。

1.アプリケーションレベルで、レプリケーションから除外するテーブルを操作している接続で、以下を実行します。

SET sql_log_bin = 0; 

2.より簡単で直感的な方法は、MySQL構成ファイルで例外を指定することです。

 replicate-wild-ignore-table = %.b_sec_session 

この設計では、すべてのデータベースのb_sec_sessionテーブルがレプリケーションから除外されます。

より複雑なロジックが必要な場合、すべてが少し複雑になります。 たとえば、 dbデータベースを除くすべてのデータベースでテーブルテーブルをレプリケートしないでください。

この場合、オプションの適切な組み合わせであるフィルターを作成するには、MySQLが提供するような回路図を作成する必要があります。

• データベースレベルのレプリケーションとバイナリログオプションの評価
• テーブルレベルの複製オプションの評価

6.レプリケーションのタイプ。

多くの論争により、STATEMENTベースのレプリケーションを使用するのか、ROWベースのレプリケーションを使用するのかという疑問が生じます。 それと他のオプションは両方ともプラスとマイナスの両方を持っています。

デフォルトでは、MySQL（Percona）5.5はSTATEMENTベースのレプリケーションを使用します。

この構成のアプリケーションでは、ログで次の行を定期的に確認しました。 「ステートメントはステートメント形式でログインしても安全ではない可能性があります」 。

さらに、マスター複製マスターの2つのデータベースを比較すると、データに矛盾がある可能性があります。 もちろん、これは受け入れられませんでした。

MySQLには私たちに完全に合った興味深いソリューションがあります-MIXED binlog形式を使用してください：

 binlog-format = mixed 

この場合、デフォルトでは、レプリケーションはSTATEMENTモードであり、そのような安全でない操作の場合にのみROWに切り替わります。

7.複製が壊れています。 どうする

複製がまだ中断する場合があります。 特に怖い（最初は:)）「マスターマスター」で作業するときに聞こえます。

実際、心配することはありません。 真実は。 :)

まず、 説明されているマスター-マスター複製スキームは、実際には2つの通常のマスター-スレーブ複製であることに注意してください。 はい、いくつかのニュアンスがありますが、標準スキームで使用されるほとんどのプラクティスはここで機能します。

最も単純な（最も頻繁に発生する）問題は、 「1062 Error 'Duplicate entry'」という形式のエラーです。

理由は異なる場合があります。 たとえば、基地で事故が発生した場合、トラフィックを別のDCに切り替えます。 要求がDC 1で既に実行されているが、DC 2でレプリケートされずに再度実行された場合-はい、まさにこのエラーが発生します。

スレーブで次のコマンドを実行することで処理されます。

 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; 

したがって、追加のリクエストはスキップします。 次に、レプリケーションステータスを確認します。

 SHOW SLAVE STATUS\G 

必要に応じて、手順を繰り返します。

* * *

はい、現在、最も簡単なオプションを詳細に検討しています。 ファイルシステムが壊れたり、ファイルが壊れたりなど、すべてがさらに悪化します。

「すべてを修正する方法」という普遍的なレシピはありません。 ただし、次のことに注意することが常に重要です。

• パニックにならないでください。
• SHOW SLAVE STATUS \ Gを使用して、スレーブのステータスを確認します。
• SHOW MASTER STATUSを使用してウィザードの状態を確認します。
• 常にログを保持します（ log-error = /var/log/mysqld.log）-たくさんの有用な情報が含まれています。 たとえば、スレーブがマスターbinlogを読み取る位置に関するデータ。 事故の場合に役立ちます。
• すべてが完全に壊れている場合-バックアップから上昇します。

8.サーバーの1つをバックアップからレプリケーションマスターに上げる方法は？

それで、2つのマスタースキームで何かがうまくいかなかった場合（たとえば、 数日前にAmazonで事故が発生し 、複数のサーバー上のファイルシステムが不可逆的に破損した場合）はどうすればよいでしょうか？

「額に」ソリューション-あるサーバーから別のサーバーにデータを転送し、ゼロから複製を開始する-長すぎる。

Amazonでは、 マシン全体にディスクスナップショットとイメージング（AMI）メカニズムを使用しています。 これにより、たとえば数時間前など、目的のサーバーの完全なコピーをすばやく展開できます。

バックアップからマシンを展開する場合、興味深い効果が得られます。「ライブ」マスターbinlogからデータの読み取りを開始します（バックアップが作成された瞬間から）が、デフォルトではレコードは同じサーバーのサーバーからであるため、それらの半分のみを読み取ります。 id（バックアップ時間に関する「未来」から）は複製されません。 これは、「マスターマスター」の「ループ」を回避するために行われます。

これを行います：

1.すべてのトラフィックは「ライブ」DCに送られます。 復元するサーバーに負荷はありません。
2.バックアップから生成されたサーバーで、mysqldをすぐに停止し、構成に入力します。

 skip-slave-start replicate-same-server-id #log-slave-updates = 1 ; ! 

3. mysqldを起動し、レプリケーションを開始します。
4.データが同期された後、設定を元の状態に戻します。

 #skip-slave-start #replicate-same-server-id log-slave-updates = 1 

5.「マスターマスター」があるため、逆方向にレプリケーションを開始する必要があります。 復元したサーバーでレプリケーションを停止し、次を実行します。

 SHOW MASTER STATUS; 

レプリケーションを停止しないと、データが変更されます。
6.目的の位置から最初の（ライブ）サーバーでレプリケーションを開始します。

 STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS = ...; START SLAVE; 

パラグラフ5で取得したデータを入力します。
7. 2番目のサーバーでレプリケーションを開始します。

9.レプリケーションのパフォーマンスと信頼性のバランスはどうですか？

MySQL / Perconaには、たとえば突然の再起動の場合にデータの信頼性と安全性を劇的に改善できる多くのオプションがあります。 それらのほとんどすべてが、システムの速度を劇的に低下させます。

このオプションの組み合わせで、私たちは自分自身のバランスを見つけました。

 sync_binlog = 1 sync_master_info = 0 sync_relay_log = 0 sync_relay_log_info = 0 innodb-flush-log-at-trx-commit = 2 

binlogは私たちにとって重要であるため、 sync_binlog = 1です。 ただし、同時に、binlogはシステム内の別のディスクに保存されるため、このディスクへの書き込みによってシステム全体のパフォーマンスが低下することはありません。

10.システムのパフォーマンスを評価するにはどうすればよいですか？

大きな「重い」リクエストがある場合は、もちろん、それらの実行の時間についてはささいな方向に向いています。

多くの場合（そして私たちの場合-その方法です）、システムは多くの多くの小さなリクエストを処理します。

もちろん、さまざまな模擬テストを使用して、システムのパフォーマンスを評価できます。 そして、彼らはいくらか感謝します。 しかし、「戦闘で」使用できる実際のインジケーター（できれば数字で表示することをお勧めします）が必要です。

Percona Serverには素晴らしいツールがあります：

 SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; 

 +----------------+-------+----------------+ | time | count | total | +----------------+-------+----------------+ | 0.000001 | 0 | 0.000000 | | 0.000010 | 6555 | 0.024024 | | 0.000100 | 56132 | 2.326873 | | 0.001000 | 23165 | 6.686421 | | 0.010000 | 9755 | 39.737027 | | 0.100000 | 1437 | 40.831493 | | 1.000000 | 141 | 31.785571 | | 10.000000 | 9 | 17.891514 | | 100.000000 | 0 | 0.000000 | | 1000.000000 | 0 | 0.000000 | | 10000.000000 | 0 | 0.000000 | | 100000.000000 | 0 | 0.000000 | | 1000000.000000 | 0 | 0.000000 | | TOO LONG | 0 | TOO LONG | +----------------+-------+----------------+ 14 rows in set (0.00 sec) 

このようなクエリ実行時間の分布のヒストグラムは、システムの一般的な状態を評価するのに非常に役立ちます。

たとえば、特定の重大なしきい値を決定しました-実行時間が0.01秒を超える（全体の）要求の5％以下です。

ダイナミクスでこの状態を追跡するために、この比率のグラフを描画するだけのMuninの簡単なプラグインを作成しました。 これは非常に便利であり、そして最も重要なことは、明確で明確な指標です。

11.メモリーバランス。

MySQLの設定は、メモリ消費のバランスが取れるようにする必要があります！

それは単純で理解可能なルールのようですが、しばしば忘れられます。 数回（最初はプロトタイプで）告白しましたが、OOM（Out of memory）を受け取り、その結果、オペレーティングシステムによってmysqldプロセスが「強制終了」されました。

理想的には、mysqldプロセスは完全にRAMに収まり、スワップでは動作しないような方法で動作するはずです。

必須 -すべてのシステムプロセスをメモリ+スワップに配置する必要があります。

多くの場合、mysqldが消費できるメモリ量の計算は、多くの人には明らかではありません。

式は次のようなものです。

• グローバルバッファサイズ：key_buffer_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size
• 1つの接続のバッファーサイズ：read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size
• 最大メモリ使用量：グローバルバッファ+接続バッファ*接続の最大数

本当に考慮したくない場合:)、mysqltuner.plスクリプトを使用できます。これは、この情報に加えて、システム、セキュリティ、パフォーマンスなどに関する他の多くのデータを表示します。

 # wget mysqltuner.pl # perl mysqltuner.pl 

* * *

この場所を読んでくれてありがとう！ :)



Bitrix24の作業で使用する実用的な問題と手法の一部のみを検討しました 。 彼らへの感謝を含め、サービスは成長し、発展しています。

私たちの経験が、プロジェクトの作成と開発に役立つことを願っています。

そして今、1つの記事のボリュームが完全に不十分であることは明らかです。 近い将来、大規模なプロジェクトでMySQLを使用するというトピックを継続し、新しいレシピを共有し、最も興味深く関連性のあるトピックをより詳細に説明しようとします。