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

今日友人にMySQLを最適化する方法を尋ねられたので、私の考えに従って整理したところ、大きく21の方向に分けることができました。 いくつかの詳細もあります (テーブルキャッシュ、テーブルデザイン、インデックスデザイン、ターミナルキャッシュなど)。ここではリストしません。システムの場合、以下を完了すると良いでしょう。初期段階のシステム。

1. 十分なメモリがあることを確認します

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

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

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

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

実際の運用ではSolarisが最も推奨されており、CentOSと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以降でサポートされています。 6. より高速に使用します。ストレージ デバイス SSD またはソリッド ステート カード

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

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

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

強く推奨されるファイルシステム: 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. 合理的にLOGを選択します。リフレッシュメカニズム

やり直しログ:

- 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で構築することを推奨します。

以上がMySQL を最適化するための 21 のヒントの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。