MySQL を最適化するための 21 のヒント

今日友人からMySQLの最適化方法を聞かれたので自分なりの考え方で整理してみたところ、大きく21方向に分けることができました。 ここに記載されていない詳細 (テーブル キャッシュ、テーブル設計、インデックス設計、端末キャッシュなど) がいくつかあります。システムとしては、初期段階で以下を完了できるのが良いシステムです。

1. 十分なメモリがあることを確認してください

データベースを効率的に実行できる最も重要な要素は、データをキャッシュするのに十分な大きさのメモリが必要であり、更新が最初にメモリ内で完了できることです。ただし、ビジネスによってメモリ要件は異なります。特にホットなデータの場合、メモリは基本的にデータベース サイズの 80% に達する必要があります。

2. より多くのより高速な CPU が必要です

MySQL 5.6 は 64 個のコアを利用できますが、各 MySQL クエリは 1 つの CPU でしか実行できないため、より多くの CPU が必要となり、より高速な CPU が同時実行により有利になります。

3. 適切なオペレーティングシステムを選択するには

公式の推奨では、Solaris が最も推奨されており、実際の運用では CentOS と REHL が推奨されており、もちろん REHL バージョンは 6 以降です。 Oracle Linux も良い選択です。 Windows は MySQL 5.5 以降最適化されていますが、同時実行性の高い環境で Windows を使用することはお勧めできません。

4. システムパラメータを適切に最適化する

ファイルハンドルを変更する ulimit -n デフォルトの 1024 は小さすぎます

の数プロセス制限 ulimit -u バージョンが異なると異なります

NUMA を無効にする numctl -interleave=all

5. 適切なメモリ割り当てアルゴリズムを選択します

デフォルトのメモリ割り当てアルゴリズムは c の malloc です 現在、最適化されたメモリ割り当てアルゴリズムが多数あります: jemalloc とtcmalloc

宣言型内部ストレージ方式はMySQL 5.5以降でサポートされています。

[mysqld_safe]

malloc-lib = tcmalloc

またはsoファイルを直接指します

[mysqld_safe]

malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

6.より高速なストレージデバイスを使用しますSSD またはソリッド ステート カード

ストレージ メディアは、MySQL のランダムな読み取り、書き込み、および更新の速度に大きく影響します。新世代のストレージ デバイス、ソリッド ステート SSD およびソリッド ステート カードの登場も MySQL を輝かせ、Taobao は IOE で善戦しました。

7. 適切なファイルシステムを選択してください

XFS、Ext4を推奨します。まだext2、ext3を使用している場合は、できるだけ早くアップグレードしてください。 XFS が推奨されます。これは、Linux が将来サポートするファイル システムでもあります。

強く推奨されるファイルシステム:

ext4 (rw,noatime,nodiratime,nobarrier,data=owned)

SSD またはソリッドステート ディスクを使用する場合は、以下を考慮する必要があります:

• innodb_page_size = 4K

• Innodb_flush_neighbors = 0

9. 適切なものを選択してくださいIOスケジューリング

通常の動作には期限を使用してください

デフォルトはnoopです

echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

10. 適切なRaidカードキャッシュ戦略を選択してください

を使用してください。パワード Raid を使用し、 WriteBack を有効にします。これは、REDO ログ、バイナリ ログ、およびデータ ファイルの高速化に適しています。

11. クエリ キャッシュを無効にする

Innodb ではクエリ キャッシュは少し役に立ちません。クエリ キャッシュを有効にすると、Innodb バッファ プールにキャッシュされ、更新と書き込みが可能になります。代わりにクエリ キャッシュをチェックするため、書き込みオーバーヘッドが増加します。

MySQL 5.6ではクエリキャッシュが無効になっています。

12. スレッドプールを使用する

現在、1つのデータは5つ以上のアプリシナリオに対応していますが、MySQLは接続数が増えるとパフォーマンスが低下する特性があるため、将来の200を超えるシナリオではスレッドプールの使用を検討してくださいこれは素晴らしい発明です。

13. メモリを適切に調整する

13.1 接続のメモリ割り当てを減らす

接続は、スレッド プールほど強力ではない thread_cache_size を使用してキャッシュできます。接続時にデータベースによって割り当てられるメモリは次のとおりです。

max_used_connections * (

read_buffer_size +

read_rnd_buffer_size +

join_buffer_size +

sort_buffer_size +

binlog_cache_size +

thread_stack +

2 * net_buffer_length …

)

13.2 より大きなバッファプール

を作成するには、メモリの60〜80%をinnodb_buffer_pool_sizeに割り当てます。これはデータサイズを超えてはならず、80%を超えて割り当てないでください。そうでない場合は、スワップが使用されます。

14合理的なログ更新メカニズムを選択します

やり直しログ:

- innodb_flush_log_at_trx_commit = 1 // 最も安全です

- innodb_flush_log_at_trx_commit = 2 // パフォーマンスが向上します

- innodb_flush_log_at_trx_commit = 0 // 最高の感情

binlog:

binlog_sync = 1 にはグループ コミットが必要です。 サポートされています。この機能が使用できない場合は、binlog_sync=0 を検討してパフォーマンスを向上させることができます。

データファイル:

innodb_flush_method = O_DIRECT

🎜 15. Innodbテーブルを使用してください🎜

より多くのリソースを利用できるようになり、オンライン変更操作が改善されました。 現在、中国語以外の全文もサポートされており、Memcache API アクセスもサポートされています。これは現在、MySQL に最適なエンジンです。

まだMyISAMを使用している場合は、早急に切り替えることを検討してください。

16. より大きな Redo ログを設定する

過去に Percona 5.5 と公式 MySQL 5.5 がパフォーマンスを競ったとき、勝利のヒントの 1 つは 4G 以上の Redo ログを割り当てることでしたが、公式 MySQL5.5 の REDO ログは割り当てることができませんでした。 MySQL 5.6 以降は、通常、REDO ログの合計サイズが 500M を超える可能性があります。 生成される REDO ログの量を観察し、1 時間を超える REDO ログの量を割り当てることができます。

17. ディスク IO を最適化します

Innodb_io_capactiy は、sas 15000 rpm では 800、ssd では 2000 以上に設定できます。

MySQL 5.6の場合:

innodb_lru_scan_ Depth = innodb_io_capacity / innodb_buffer_pool_instances

innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

18. 独立したテーブルスペースを使用する

現在、新しい機能は独立したテーブルスペースのサポートです:

Truncate table テーブルスペースのリサイクル

テーブルスペースの転送

断片化などの管理パフォーマンスの向上を最適化した方が良いです

全体として、独立したテーブルスペースを使用するのは無駄です。

19. 適切な同時実行性を設定する

innodb_thread_concurrency = Concurrency このパラメータは、Innodb で最も頻繁に変更されるパラメータでもあります。バージョンが異なると、マイナー バージョンが異なる場合があります。一般的な推奨事項:

スレッドプールを使用する場合:

innodb_thread_concurrency = 0 で問題ありません。

スレッドプールがない場合:

5.5 推奨: innodb_thread_concurrency = 16 – 32

5.6 推奨 innodb_thread_concurrency = 36

20. トランザクション分離レベルを最適化する

デフォルトはRepeatable read

ですRead commit を使用することをお勧めしますbinlog 形式 Mixed または Row を使用します

分離レベルが低い = パフォーマンスが向上します

21. モニタリングに注意を払う

モニタリングがなければ、どんな環境もモニタリングと切り離せないものになります。 監視機能はzabbix+mpmで構築することを推奨します。