MySQLデータベースの高利用可能なアーキテクチャ案と高合併最適化構成
12877 ワード
本記事は「琴を弾いて酒をわかす」のブログです。 http://yuhongchun.blog.51cto.com/1604432/509635
本文は全部二つの部分に分けられています。 MySQLの最適化 MySQLの最適化高利用アーキテクチャ
MySQLの最適化は三つの部分に分けられています。一つはサーバーの物理ハードウェアの最適化、二つはMySQLのインストール時のコンパイル最適化、三つはMySQL自身（my.cnf）の最適化です。
一、サーバーのハードウェアがMySQLの性能に与える影響 ①ディスク探索能力（ディスクI/O）は、今私たちが持っているのは全部SAS 15000回転のハードディスクです。MySQLは、一秒ごとに大量の、複雑なクエリ操作を行っています。ディスクの読み書き量を考えられます。したがって、ディスクI/Oは常にMySQLの性能を制約する最大の要因の一つと考えられています。一日当たりのアクセス量は100万PV以上のDiscuzです。フォーラムでは、ディスクI/Oの制約により、MySQLの性能が非常に低下します。この制約を解決するためには、RAID 1＋0ディスクアレイを使用して、RAID-5を使用しないように注意してください。MySQLのRAID-5ディスクアレイ上の効率は、あなたが期待するほど速くないです。 ②CPUはMySQLアプリケーションに対して、DELL R 710、E [email protected] GHz*2を推奨しています。私は今DELL R 710が好きです。Linuxakg仮想化アプリケーションも使っています。 ③物理メモリはMySQLを使用するDatabase Serverにとって、サーバメモリは2 GB以下ではなく、4 GB以上の物理メモリを推奨していますが、メモリは現在のサーバーにとっては無視できる問題であり、作業中にハイエンドのサービス装置に遭遇すると基本的にメモリは32 Gを超えています。 私達の仕事の中で多く使うデータベースサーバーはHP DL 580 G 5とDELL R 710で、安定性と性能はすべて悪くないです。特にDELL R 710は、多くの同行者がデータベースとしてのサーバーを採用していることに気づきました。
二、MySQLのオンラインインストールはコンパイルインストールの方法を提案します。このように性能が大幅に向上します。
サーバーシステムは64 bitのCentos 5.5を使って、ソースパケットのコンパイルパラメータはデフォルトでDebggモードでバイナリコードを生成しますが、DebugモードはMySQLにもたらす性能損失が大きいので、インストールする製品コードをコンパイルする時は必ず「—without-debug」パラメータでDebugモードを無効にしないでください。With-mysqld-ldflagsと─with-client-ldflagsの二つのコンパイルパラメータを—all-staticに設定すれば、コンパイラに静的なコンパイルとコンパイルの結果コードが最高の性能を得るように教えてくれます。静的コンパイルを使って，動的コンパイルを使ったコードと比較して，性能の差は5%から10%に達するかもしれない。簡単な朝陽さんのコンパイルパラメータを参考にしました。以下のように列を作ります。
上記サーバーのハードウェア制約を解決したら、MySQL自身の最適化はどのように操作されているかを確認してみましょう。
MySQL自体の最適化は主に、そのプロファイルmy.cnfの各パラメータを最適化して調整することである。性能に大きな影響を与えるいくつかのパラメータを紹介します。
以上のハードウェア構成に基づいて、最適化されたmy.cnfを組み合わせて説明します。
以下はmy.cnfファイルの「mysqld」段落の内容だけを列記します。他の段落の内容はMySQLの運行性能に影響が少ないので、とりあえず無視します。
MyISAMはデフォルトでキーを押すことができます。ブザーsize設定で動作可能ですが、Innodbはデフォルトのinnodb_にあります。ブザーpool_サイゼが設置されていますが、カタツムリのようです。Innodbはデータとインデックスを全部キャッシュしていますので、オペレーティングシステムに多くのメモリを残す必要はありません。ですから、Innodbだけを使うなら、70-80%までの利用可能なメモリを設定できます。いくつかはキーに適用されますブザーのルールはあります。もしあなたのデータ量が大きくなく、乱暴に増加しないなら、innodbを使う必要がありません。ブザーpool_sizeの設定が大きすぎます。
innodb_ロゴfile_sizeは、高い書き込み負荷、特に大きなデータセットの場合に重要である。この値が大きいほど性能は相対的に高いが，回復時間が増加する可能性があることに注意しなければならない。私はいつも64-512 MBに設定していますが、サーバーのサイズによって違います。
innodb_ロゴブザーsizeデフォルトの設定は中程度の強度で負荷を書き込み、短い事務の場合はサーバー性能も大丈夫です。更新動作のピークや負荷が大きい場合は、その値を大きくすることを考慮する必要があります。その値の設定が高すぎると、メモリを無駄にしてしまうかもしれません。毎秒1回更新されますので、1秒を超えるメモリ空間を設定する必要はありません。通常8-16 MBで十分です。小さいシステムほどその値は小さいです。
innodb_flaushロゴス.at唵trx_comitはInnodbがMyISAMより1000倍遅くて頭が大きいですか？このパラメータを変更するのを忘れたかもしれません。デフォルトの値は1です。これは、提出された更新トランザクション（またはトランザクション以外のステートメント）ごとにディスクに更新されることを意味します。これはかなりのリソースがかかります。特にバッテリのバックアップキャッシュがない場合です。多くのアプリケーション、特にMyISAMから変えたものは、その値を2に設定すればいいです。つまりログをディスクに更新せずにオペレーティングシステムのキャッシュに更新します。ログは毎秒ディスクに更新されますので、通常は毎秒1～2回の更新が失われません。0に設定するとかなり速くなりますが、比較的安全ではありません。MySQLサーバーが崩壊すると、いくつかのトランザクションが失われます。2は、オペレーティングシステムのキャッシュに更新された部分を失ったイベントを指揮するように設定します。
テーブルcache—一つの表を開くと出費が大きいかもしれません。例えば、MyISAMはMYIファイルヘッダをこの表で使用しています。このような操作はあまり頻繁ではないので、通常はキャッシュの数を増やして、開いているテーブルを最大限にキャッシュするようにします。オペレーティングシステムのリソースとメモリを使う必要があります。現在のハードウェア構成にとってはもちろん問題ではありません。200以上のテーブルがあると1024に設定したほうがいいかもしれません。接続数が大きいとその値を大きくします。100,000に設定されていることを見たことがあります。
query_cache—もしあなたのアプリケーションが大量に読んでいて、アプリケーションレベルのキャッシュがないなら、これはとても役に立ちます。それをあまり大きくしないでください。維持するためにも多くの出費が必要です。MySQLが遅くなります。通常は32-512 Mに設定されています。設定が終わったら、しばらく追跡して、運行が良好かどうかを確認したほうがいいです。一定の負荷圧力でキャッシュの命中率が低い場合に有効にします。
MySQL高利用可能なアーキテクチャ（MySQLクラスタ）
もし単一MySQLの最適化がいつまでも圧力に耐えられない場合、この時はMySQLの高い利用可能なアーキテクチャを考慮しなければならない（多くの学生がMySQLクラスタとも言う）。
一、MySQL Clausterの優勢：利用性が非常に高く、性能が非常に良いです。各データは少なくとも異なるホストにコピーを1つ保存し、冗長データのコピーはリアルタイムで同期することができる。しかし、そのメンテナンスは非常に複雑で、一部のBugがあります。まだ核心のオンラインシステムには向いていませんので、これはオススメしません。二、DRBDディスクネットワークミラー方式の利点：ソフトウェア機能が強く、データは底の高速デバイスレベルで物理ホストにまたがりミラーリングでき、性能と信頼性の要求に応じて、異なるレベルの同期を設定することができる。IO操作は順序を保持し、データベースのデータ整合性に対する厳しい要求を満たすことができます。しかし、分散されていないファイルシステム環境では、ミラーデータをサポートできません。性能と信頼性の両方が矛盾しています。性能と信頼性の要求が厳しい環境には適用できません。メンテナンスコストはMySQL Replicationより高いです。また、DRBDも公式推奨のMySQLに利用可能なソリューションの一つですので、これは実際の環境に合わせて配備するかどうか考えられます。三、MySQL Replicationは実際のアプリケーションシーンで、MySQL Replicationは最も広範なシステム拡張性を高める設計手段を使用しています。多くのMySQL利用者がReplication機能によってシステムの拡張性を向上させた後、単純な増加によって価格の安いハードウェア設備が倍になり、さらには数桁にもなり、元のシステムの性能を向上させました。広範なMySQLの中でローエンドユーザーが非常に好きな機能の一つであり、多くのMySQL使用者がMySQLを選ぶ最も重要な原因です。
従来のMySQL Replicationアーキテクチャもいくつかありますが、ここではそれぞれ簡単にMySQL Replicationアーキテクチャを説明します。一：従来のレプリカ・アーキテクチャであるMaster-slavesは、Masterから一つ以上のSalveのアーキテクチャモードにコピーされています。MySQL Replicationアーキテクチャ2：カスケードコピーアーキテクチャ、すなわちMaster-Selaves-laves、これもSlavesの読む圧力が大きすぎることを防止するために、1階の2級のSlavesを配置して、Master端が付属slavesが多すぎて、瓶力のリスクになりやすいです。MySQL Replicationアーキテクチャ3：Dual Masterとカスケードコピー結合アーキテクチャ、すなわちMaster-Master-aster-Slaves、最大の利点はマスターの書き込み操作がSlaveクラスタのコピーによってもたらされる影響を回避することができ、マスターのシングルポイントの故障を保証することである。以上は比較的にありふれたMySQL replicationアーキテクチャの方案です。皆さんは自分の会社の具体的な環境によって設計できます。Mysql負荷バランスはLVSまたはHaproxyで行うことができます。高利用可能なHAソフトはHeartbeatをオススメします。MySQL Replicationの不足：Masterホストのハードウェアの不具合が回復できない場合、slaave端末に一部未送信のデータが失われる可能性があります。だから、皆さんは自分の現在のネットワーク計画に基づいて、自分の合理的なMysqlアーキテクチャ方案を選択して、自分のMySQL DBAとプログラマとの溝が湧いてきて、多くのバックアップ（バックアップは少なくとも地元と異郷の両方のバックアップができます）を取って、多くのテストをして、データのことは最大のことです。本人自身の能力が限られていますので、上記の認識には間違いや疎外があるかもしれません。皆さんはどんな考えや意見がありますか？
会社がお金があれば、私もソリッドステートハードウェアを使って、MySQLサーバーに使うことを勧めます。
もう一つオススメします。仕事の中でInnoDBエンジンデータベースは主にコピー同期の心得です。。
本文は全部二つの部分に分けられています。
MySQLの最適化は三つの部分に分けられています。一つはサーバーの物理ハードウェアの最適化、二つはMySQLのインストール時のコンパイル最適化、三つはMySQL自身（my.cnf）の最適化です。
一、サーバーのハードウェアがMySQLの性能に与える影響 ①ディスク探索能力（ディスクI/O）は、今私たちが持っているのは全部SAS 15000回転のハードディスクです。MySQLは、一秒ごとに大量の、複雑なクエリ操作を行っています。ディスクの読み書き量を考えられます。したがって、ディスクI/Oは常にMySQLの性能を制約する最大の要因の一つと考えられています。一日当たりのアクセス量は100万PV以上のDiscuzです。フォーラムでは、ディスクI/Oの制約により、MySQLの性能が非常に低下します。この制約を解決するためには、RAID 1＋0ディスクアレイを使用して、RAID-5を使用しないように注意してください。MySQLのRAID-5ディスクアレイ上の効率は、あなたが期待するほど速くないです。 ②CPUはMySQLアプリケーションに対して、DELL R 710、E [email protected] GHz*2を推奨しています。私は今DELL R 710が好きです。Linuxakg仮想化アプリケーションも使っています。 ③物理メモリはMySQLを使用するDatabase Serverにとって、サーバメモリは2 GB以下ではなく、4 GB以上の物理メモリを推奨していますが、メモリは現在のサーバーにとっては無視できる問題であり、作業中にハイエンドのサービス装置に遭遇すると基本的にメモリは32 Gを超えています。 私達の仕事の中で多く使うデータベースサーバーはHP DL 580 G 5とDELL R 710で、安定性と性能はすべて悪くないです。特にDELL R 710は、多くの同行者がデータベースとしてのサーバーを採用していることに気づきました。
二、MySQLのオンラインインストールはコンパイルインストールの方法を提案します。このように性能が大幅に向上します。
サーバーシステムは64 bitのCentos 5.5を使って、ソースパケットのコンパイルパラメータはデフォルトでDebggモードでバイナリコードを生成しますが、DebugモードはMySQLにもたらす性能損失が大きいので、インストールする製品コードをコンパイルする時は必ず「—without-debug」パラメータでDebugモードを無効にしないでください。With-mysqld-ldflagsと─with-client-ldflagsの二つのコンパイルパラメータを—all-staticに設定すれば、コンパイラに静的なコンパイルとコンパイルの結果コードが最高の性能を得るように教えてくれます。静的コンパイルを使って，動的コンパイルを使ったコードと比較して，性能の差は5%から10%に達するかもしれない。簡単な朝陽さんのコンパイルパラメータを参考にしました。以下のように列を作ります。
./configure --prefix=/usr/local/mysql --without-debug --without-bench --enable-thread-safe-client --enable-assembler --enable-profiling --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-charset=latin1 --with-extra-charset=utf8,gbk --with-innodb --with-csv-storage-engine --with-federated-storage-engine --with-mysqld-user=mysql --without-embedded-server --with-server-suffix=-community --with-unix-socket-path=/usr/local/mysql/sock/mysql.sock上記サーバーのハードウェア制約を解決したら、MySQL自身の最適化はどのように操作されているかを確認してみましょう。
MySQL自体の最適化は主に、そのプロファイルmy.cnfの各パラメータを最適化して調整することである。性能に大きな影響を与えるいくつかのパラメータを紹介します。
以上のハードウェア構成に基づいて、最適化されたmy.cnfを組み合わせて説明します。
以下はmy.cnfファイルの「mysqld」段落の内容だけを列記します。他の段落の内容はMySQLの運行性能に影響が少ないので、とりあえず無視します。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
# MySQL ， 。
skip-name-resolve
# MySQL DNS ， MySQL DNS 。 ， ， IP ， MySQL ！
back_log = 384
#back_log MySQL 。 ， ， TCP/IP 。 。 back_log 。 50。 Linux 512 。
key_buffer_size = 256M
#key_buffer_size ， 。 4GB 256M 384M。 ： ！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
# 。 ： ， 100 ， 100 × 6 ＝ 600MB。 ， 4GB 6-8M。
read_buffer_size = 4M
# 。 sort_buffer_size ， 。
join_buffer_size = 8M
# ， sort_buffer_size ， 。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
# MySQL 。 MySQL ， Qcache_lowmem_prunes ， ； Qcache_hits ， ， ， ；Qcache_free_blocks， ， 。
tmp_table_size = 256M
max_connections = 768
# MySQL 。 Too Many Connections ， 。
max_connect_errors = 10000000
wait_timeout = 10
# ， 4GB 5-10。
thread_concurrency = 8
# CPU *2， ， 2 CPU， CPU H.T ， 4*2=8
skip-networking
# MySQL TCP/IP ， WEB MySQL ！ ！
table_cache=1024
# , . 2402, 512-1024
innodb_additional_mem_pool_size=4M
# 2M
innodb_flush_log_at_trx_commit=1
# 0 innodb_log_buffer_size , 1
innodb_log_buffer_size=2M
# 1M
innodb_thread_concurrency=8
# CPU , 8
key_buffer_size=256M
# 218， 128
tmp_table_size=64M
# 16M， 64-256
read_buffer_size=4M
# 64K
read_rnd_buffer_size=16M
# 256K
sort_buffer_size=32M
# 256K
thread_cache_size=120
# 60
query_cache_size=32MMyISAMはデフォルトでキーを押すことができます。ブザーsize設定で動作可能ですが、Innodbはデフォルトのinnodb_にあります。ブザーpool_サイゼが設置されていますが、カタツムリのようです。Innodbはデータとインデックスを全部キャッシュしていますので、オペレーティングシステムに多くのメモリを残す必要はありません。ですから、Innodbだけを使うなら、70-80%までの利用可能なメモリを設定できます。いくつかはキーに適用されますブザーのルールはあります。もしあなたのデータ量が大きくなく、乱暴に増加しないなら、innodbを使う必要がありません。ブザーpool_sizeの設定が大きすぎます。
innodb_ロゴfile_sizeは、高い書き込み負荷、特に大きなデータセットの場合に重要である。この値が大きいほど性能は相対的に高いが，回復時間が増加する可能性があることに注意しなければならない。私はいつも64-512 MBに設定していますが、サーバーのサイズによって違います。
innodb_ロゴブザーsizeデフォルトの設定は中程度の強度で負荷を書き込み、短い事務の場合はサーバー性能も大丈夫です。更新動作のピークや負荷が大きい場合は、その値を大きくすることを考慮する必要があります。その値の設定が高すぎると、メモリを無駄にしてしまうかもしれません。毎秒1回更新されますので、1秒を超えるメモリ空間を設定する必要はありません。通常8-16 MBで十分です。小さいシステムほどその値は小さいです。
innodb_flaushロゴス.at唵trx_comitはInnodbがMyISAMより1000倍遅くて頭が大きいですか？このパラメータを変更するのを忘れたかもしれません。デフォルトの値は1です。これは、提出された更新トランザクション（またはトランザクション以外のステートメント）ごとにディスクに更新されることを意味します。これはかなりのリソースがかかります。特にバッテリのバックアップキャッシュがない場合です。多くのアプリケーション、特にMyISAMから変えたものは、その値を2に設定すればいいです。つまりログをディスクに更新せずにオペレーティングシステムのキャッシュに更新します。ログは毎秒ディスクに更新されますので、通常は毎秒1～2回の更新が失われません。0に設定するとかなり速くなりますが、比較的安全ではありません。MySQLサーバーが崩壊すると、いくつかのトランザクションが失われます。2は、オペレーティングシステムのキャッシュに更新された部分を失ったイベントを指揮するように設定します。
テーブルcache—一つの表を開くと出費が大きいかもしれません。例えば、MyISAMはMYIファイルヘッダをこの表で使用しています。このような操作はあまり頻繁ではないので、通常はキャッシュの数を増やして、開いているテーブルを最大限にキャッシュするようにします。オペレーティングシステムのリソースとメモリを使う必要があります。現在のハードウェア構成にとってはもちろん問題ではありません。200以上のテーブルがあると1024に設定したほうがいいかもしれません。接続数が大きいとその値を大きくします。100,000に設定されていることを見たことがあります。
query_cache—もしあなたのアプリケーションが大量に読んでいて、アプリケーションレベルのキャッシュがないなら、これはとても役に立ちます。それをあまり大きくしないでください。維持するためにも多くの出費が必要です。MySQLが遅くなります。通常は32-512 Mに設定されています。設定が終わったら、しばらく追跡して、運行が良好かどうかを確認したほうがいいです。一定の負荷圧力でキャッシュの命中率が低い場合に有効にします。
MySQL高利用可能なアーキテクチャ（MySQLクラスタ）
もし単一MySQLの最適化がいつまでも圧力に耐えられない場合、この時はMySQLの高い利用可能なアーキテクチャを考慮しなければならない（多くの学生がMySQLクラスタとも言う）。
一、MySQL Clausterの優勢：利用性が非常に高く、性能が非常に良いです。各データは少なくとも異なるホストにコピーを1つ保存し、冗長データのコピーはリアルタイムで同期することができる。しかし、そのメンテナンスは非常に複雑で、一部のBugがあります。まだ核心のオンラインシステムには向いていませんので、これはオススメしません。二、DRBDディスクネットワークミラー方式の利点：ソフトウェア機能が強く、データは底の高速デバイスレベルで物理ホストにまたがりミラーリングでき、性能と信頼性の要求に応じて、異なるレベルの同期を設定することができる。IO操作は順序を保持し、データベースのデータ整合性に対する厳しい要求を満たすことができます。しかし、分散されていないファイルシステム環境では、ミラーデータをサポートできません。性能と信頼性の両方が矛盾しています。性能と信頼性の要求が厳しい環境には適用できません。メンテナンスコストはMySQL Replicationより高いです。また、DRBDも公式推奨のMySQLに利用可能なソリューションの一つですので、これは実際の環境に合わせて配備するかどうか考えられます。三、MySQL Replicationは実際のアプリケーションシーンで、MySQL Replicationは最も広範なシステム拡張性を高める設計手段を使用しています。多くのMySQL利用者がReplication機能によってシステムの拡張性を向上させた後、単純な増加によって価格の安いハードウェア設備が倍になり、さらには数桁にもなり、元のシステムの性能を向上させました。広範なMySQLの中でローエンドユーザーが非常に好きな機能の一つであり、多くのMySQL使用者がMySQLを選ぶ最も重要な原因です。
従来のMySQL Replicationアーキテクチャもいくつかありますが、ここではそれぞれ簡単にMySQL Replicationアーキテクチャを説明します。一：従来のレプリカ・アーキテクチャであるMaster-slavesは、Masterから一つ以上のSalveのアーキテクチャモードにコピーされています。MySQL Replicationアーキテクチャ2：カスケードコピーアーキテクチャ、すなわちMaster-Selaves-laves、これもSlavesの読む圧力が大きすぎることを防止するために、1階の2級のSlavesを配置して、Master端が付属slavesが多すぎて、瓶力のリスクになりやすいです。MySQL Replicationアーキテクチャ3：Dual Masterとカスケードコピー結合アーキテクチャ、すなわちMaster-Master-aster-Slaves、最大の利点はマスターの書き込み操作がSlaveクラスタのコピーによってもたらされる影響を回避することができ、マスターのシングルポイントの故障を保証することである。以上は比較的にありふれたMySQL replicationアーキテクチャの方案です。皆さんは自分の会社の具体的な環境によって設計できます。Mysql負荷バランスはLVSまたはHaproxyで行うことができます。高利用可能なHAソフトはHeartbeatをオススメします。MySQL Replicationの不足：Masterホストのハードウェアの不具合が回復できない場合、slaave端末に一部未送信のデータが失われる可能性があります。だから、皆さんは自分の現在のネットワーク計画に基づいて、自分の合理的なMysqlアーキテクチャ方案を選択して、自分のMySQL DBAとプログラマとの溝が湧いてきて、多くのバックアップ（バックアップは少なくとも地元と異郷の両方のバックアップができます）を取って、多くのテストをして、データのことは最大のことです。本人自身の能力が限られていますので、上記の認識には間違いや疎外があるかもしれません。皆さんはどんな考えや意見がありますか？
会社がお金があれば、私もソリッドステートハードウェアを使って、MySQLサーバーに使うことを勧めます。
もう一つオススメします。仕事の中でInnoDBエンジンデータベースは主にコピー同期の心得です。。