MySql構成最適化の概要
5076 ワード
MySQLのパフォーマンスを監視するために雇われたとき、MySQLの構成を確認し、改善策を提示することを望んでいます.多くの人が驚いたのは、ここに何百もの構成項目があっても、いくつかの設定を変更することをお勧めしたからです.この文章の目的は、非常に重要な構成項目リストをあげることです.
私たちは数年前にブログでこのようなアドバイスをしましたが、MySQLの世界の変化は本当に速いです!
始まる前に...
ベテランでもミスを犯したり、トラブルを起こしたりします.これらの推奨を盲目的に運用する前に、次のことを覚えておいてください.
1.一度に1つだけ設定を変更!これは変更が有益かどうかをテストする唯一の方法です.
2.ほとんどの構成は、実行時にSET GLOBALを使用して変更できます.これは非常に便利な方法です.問題が発生した後、変更を迅速に取り消すことができます.しかし、永続的に有効にするには、プロファイルを変更する必要があります.
3.変更はMySQLを再起動しても機能しませんか?正しいプロファイルを使用していることを確認してください.構成を正しい領域に配置していることを確認してください(この記事で述べた構成はすべて[mysqld])
4.サーバーは構成を変更した後に起動できません:正しい単位を使用していることを確認してください.たとえばinnodb_buffer_pool_sizeの単位はMBでmax_接続には単位がありません.
5.1つのプロファイルに重複する構成項目を表示しないでください.変更を追跡したい場合は、バージョン管理を使用します.
6.無邪気な計算方法を使わないでください.例えば、「今、私のサーバーのメモリは前の2倍なので、すべての数値を前の2倍に変更しなければなりません」.
基本構成
以下の3つの構成項目を常に確認する必要があります.さもないと、すぐに問題が発生するかもしれません.
innodb_buffer_pool_size:InnoDBをインストールした後、最初に設定すべきオプションです.バッファ・プールはデータとインデックス・キャッシュの場所です.この値が大きいほど、ほとんどの読み取り操作でハード・ディスクではなくメモリを使用することができます.典型的な値は5-6 GB(8 GBメモリ)、20-25 GB(32 GBメモリ)、100-120 GB(128 GBメモリ)です.
innodb_log_file_size:これはredoログのサイズです.redoログは、書き込み操作が迅速で信頼性が高く、クラッシュ時にリカバリされることを保証するために使用されます.MySQL 5.1までは、パフォーマンスを向上させるためにより大きくしたい一方で、クラッシュ後のリカバリをより速くしたいため、調整が困難です.幸いなことに、MySQL 5.5以降、クラッシュ・リカバリのパフォーマンスは大幅に向上し、書き込みとクラッシュ・リカバリのパフォーマンスを同時に向上させることができます.MySQL 5.5まで、redoログの総サイズは4 GBに限定されています(デフォルトでは2つのlogファイルがあります).これはMySQL 5.6でアップされています.
最初からinnodb_をlog_file_sizeを512 M(1 GBのredoログがある)に設定すると、書き込み操作スペースに余裕があります.アプリケーションが頻繁にデータを書き込む必要があることを知っていて、MySQL 5.6を使用している場合は、最初から4 Gにすることができます.
max_connections:「Too many connections」のエラーがよく見られるのはmaxのためです.connectionsの値が低すぎます.これは、アプリケーションがデータベース接続を正しく閉じていないため、デフォルトの151接続数よりも大きな値が必要です.max_connection値が高く設定された(例えば1000以上)後の主な欠点は、サーバが1000以上のアクティブなトランザクションを実行すると応答しないことです.アプリケーションで接続プールを使用するか、MySQLでプロセスプールを使用すると、この問題を解決できます.
InnoDB構成
MySQL 5.5のバージョンから、InnoDBはデフォルトのストレージエンジンであり、他のストレージエンジンよりもずっと多く使用されています.それも、構成に注意する必要がある理由です.
innodb_file_per_テーブル:この設定は、InnoDBが共有テーブル空間にすべてのテーブルのデータとインデックスを格納必要があるか(innodb_file_per_table=OFF)または各テーブルのデータを個別に1つに置く必要があるかどうかを示す.ibdファイル(innodb_file_per_table=ON).各テーブルのファイルを使用すると、drop、truncate、またはrebuildテーブルでディスク領域を回収できます.これは、データ圧縮などの高度な特性にも必要です.しかし、パフォーマンスの収益は得られません.各テーブルにファイルを1つ持たせたくない主なシーンは、10 k+などのテーブルが非常に多いことです.MySQL InnoDB独立表領域モードの利点と欠点の紹介
MySQL 5.6では、この属性のデフォルト値はONなので、ほとんどの場合、何もする必要はありません.以前のバージョンでは、新しく作成したテーブルにのみ影響を与えるため、データをロードする前にこのプロパティをONに設定する必要があります.
innodb_flush_log_at_trx_commit:デフォルト値は1で、InnoDBがACID特性を完全にサポートしていることを示します.主な注目点がデータセキュリティである場合、この値はプライマリノードなどに最適です.しかし、ディスク(読み書き)の速度が遅いシステムでは、flushをredoログに変更するたびに追加のfsyncsが必要になるため、コストがかかります.
0に設定すると、このモードは最も速くなりますが、安全ではありません.mysqldプロセスのクラッシュにより、前の秒にすべてのトランザクションデータが失われます.
1に設定すると、このモードは最も安全ですが、最も遅い方法です.mysqldサービスがクラッシュしたり、サーバホストcrashがクラッシュしたりした場合、binary logは最大1つの文またはトランザクションを失う可能性があります.
2に設定すると、モードの速度が速く、0よりも安全で、OSがクラッシュしたり、システムが電源が切れたりした場合にのみ、前の秒にすべてのトランザクションデータが失われる可能性があります.
ほとんどの場合、2を取ればいいです.
innodb_flush_method:この構成により、データとログがハードディスクに書き込まれる方法が決まります.一般的には、ハードウェアRAIDコントローラがあり、独立したキャッシュがwrite-backメカニズムを採用し、バッテリーの断電保護がある場合は、O_に設定する必要があります.DIRECT;そうでなければ、ほとんどの場合、fdatasync(デフォルト)に設定します.Sysbenchは、このオプションを決定するのに役立つ良いツールです.
innodb_log_buffer_size:この構成により、実行されていないトランザクションに割り当てられたキャッシュが決定されます.デフォルト値(1 MB)は一般的に十分ですが、トランザクションにバイナリオブジェクトまたは大きなテキストフィールドが含まれている場合、このキャッシュはすぐに満たされ、追加のI/O操作がトリガーされます.Innodbを見てlog_waits状態変数、0でない場合はinnodb_を増やしますlog_buffer_size.
write/read thread
非同期IOスレッド数
innodb_write_io_threads=16innodb_read_io_threads=16
マルチコアcpuはこの2つのパラメータによってcpu性能をより効果的に利用することができる.
その他の設定
query_cache_size:query cache(クエリーキャッシュ)はよく知られているボトルネックであり、同時多発が少ない場合でもそうである.最適なオプションは、最初から無効にしてquery_を設定することです.cache_size=0(現在MySQL 5.6のデフォルト値)であり、他の方法でクエリーを高速化します.インデックスの最適化、コピー分散負荷の増加、memcacheやredisなどの追加キャッシュの有効化などです.アプリケーションにquery cacheを有効にして問題が見つからない場合は、query cacheが役に立つかもしれません.これはあなたがそれを止めたいなら、気をつけなければなりません.
log_bin:データベース・サーバをプライマリ・ノードのバックアップ・ノードとして機能させるには、バイナリ・ログを開く必要があります.そうしたらserverを設定するのを忘れないでください.idは一意の値です.サーバが1つしかない場合でも、ポイント・ベースのデータ・リカバリを行う場合は、最近のバックアップからリカバリ(フル・バックアップ)、バイナリ・ログの変更(インクリメンタル・バックアップ)を適用することが役立ちます.バイナリ・ログは作成されると永続的に保存されます.ディスク容量を消費したくない場合は、PURGE BINARY LOGSを使用して古いファイルを消去したり、expire_を設定したりすることができます.logs_daysは、ログが自動的に消去される日を指定します.
バイナリ・ログを記録するのはオーバーヘッドがないわけではありませんので、プライマリ・ノード以外のレプリケーション・ノードで不要な場合は、このオプションをオフにすることをお勧めします.
skip_name_resolve:クライアントがデータベース・サーバに接続すると、サーバはホスト名の解析を行い、DNSが遅い場合、接続の確立も遅くなります.したがって、サーバの起動時にskip_を閉じることを推奨します.name_DNS検索ではなくresolveオプション.唯一の限界は、その後GRANT文ではIPアドレスしか使用できないため、この設定を既存のシステムに追加するには特に注意しなければならない.
まとめ
もちろん、負荷やハードウェアに応じて、他の設定も機能します.遅いメモリと高速ディスク、高い同時および書き込みの負荷の場合、特別な調整が必要です.しかし、ここでの目標は、重要ではないMySQL設定を調整したり、ドキュメントを読んだりして、どの設定があなたにとって重要なのかを見つけるのにあまり時間を費やさずに、安定したMySQL構成を迅速に得ることです.
転載先:https://www.cnblogs.com/shamo89/p/8658644.html
私たちは数年前にブログでこのようなアドバイスをしましたが、MySQLの世界の変化は本当に速いです!
始まる前に...
ベテランでもミスを犯したり、トラブルを起こしたりします.これらの推奨を盲目的に運用する前に、次のことを覚えておいてください.
1.一度に1つだけ設定を変更!これは変更が有益かどうかをテストする唯一の方法です.
2.ほとんどの構成は、実行時にSET GLOBALを使用して変更できます.これは非常に便利な方法です.問題が発生した後、変更を迅速に取り消すことができます.しかし、永続的に有効にするには、プロファイルを変更する必要があります.
3.変更はMySQLを再起動しても機能しませんか?正しいプロファイルを使用していることを確認してください.構成を正しい領域に配置していることを確認してください(この記事で述べた構成はすべて[mysqld])
4.サーバーは構成を変更した後に起動できません:正しい単位を使用していることを確認してください.たとえばinnodb_buffer_pool_sizeの単位はMBでmax_接続には単位がありません.
5.1つのプロファイルに重複する構成項目を表示しないでください.変更を追跡したい場合は、バージョン管理を使用します.
6.無邪気な計算方法を使わないでください.例えば、「今、私のサーバーのメモリは前の2倍なので、すべての数値を前の2倍に変更しなければなりません」.
基本構成
以下の3つの構成項目を常に確認する必要があります.さもないと、すぐに問題が発生するかもしれません.
innodb_buffer_pool_size:InnoDBをインストールした後、最初に設定すべきオプションです.バッファ・プールはデータとインデックス・キャッシュの場所です.この値が大きいほど、ほとんどの読み取り操作でハード・ディスクではなくメモリを使用することができます.典型的な値は5-6 GB(8 GBメモリ)、20-25 GB(32 GBメモリ)、100-120 GB(128 GBメモリ)です.
innodb_log_file_size:これはredoログのサイズです.redoログは、書き込み操作が迅速で信頼性が高く、クラッシュ時にリカバリされることを保証するために使用されます.MySQL 5.1までは、パフォーマンスを向上させるためにより大きくしたい一方で、クラッシュ後のリカバリをより速くしたいため、調整が困難です.幸いなことに、MySQL 5.5以降、クラッシュ・リカバリのパフォーマンスは大幅に向上し、書き込みとクラッシュ・リカバリのパフォーマンスを同時に向上させることができます.MySQL 5.5まで、redoログの総サイズは4 GBに限定されています(デフォルトでは2つのlogファイルがあります).これはMySQL 5.6でアップされています.
最初からinnodb_をlog_file_sizeを512 M(1 GBのredoログがある)に設定すると、書き込み操作スペースに余裕があります.アプリケーションが頻繁にデータを書き込む必要があることを知っていて、MySQL 5.6を使用している場合は、最初から4 Gにすることができます.
max_connections:「Too many connections」のエラーがよく見られるのはmaxのためです.connectionsの値が低すぎます.これは、アプリケーションがデータベース接続を正しく閉じていないため、デフォルトの151接続数よりも大きな値が必要です.max_connection値が高く設定された(例えば1000以上)後の主な欠点は、サーバが1000以上のアクティブなトランザクションを実行すると応答しないことです.アプリケーションで接続プールを使用するか、MySQLでプロセスプールを使用すると、この問題を解決できます.
InnoDB構成
MySQL 5.5のバージョンから、InnoDBはデフォルトのストレージエンジンであり、他のストレージエンジンよりもずっと多く使用されています.それも、構成に注意する必要がある理由です.
innodb_file_per_テーブル:この設定は、InnoDBが共有テーブル空間にすべてのテーブルのデータとインデックスを格納必要があるか(innodb_file_per_table=OFF)または各テーブルのデータを個別に1つに置く必要があるかどうかを示す.ibdファイル(innodb_file_per_table=ON).各テーブルのファイルを使用すると、drop、truncate、またはrebuildテーブルでディスク領域を回収できます.これは、データ圧縮などの高度な特性にも必要です.しかし、パフォーマンスの収益は得られません.各テーブルにファイルを1つ持たせたくない主なシーンは、10 k+などのテーブルが非常に多いことです.MySQL InnoDB独立表領域モードの利点と欠点の紹介
MySQL 5.6では、この属性のデフォルト値はONなので、ほとんどの場合、何もする必要はありません.以前のバージョンでは、新しく作成したテーブルにのみ影響を与えるため、データをロードする前にこのプロパティをONに設定する必要があります.
innodb_flush_log_at_trx_commit:デフォルト値は1で、InnoDBがACID特性を完全にサポートしていることを示します.主な注目点がデータセキュリティである場合、この値はプライマリノードなどに最適です.しかし、ディスク(読み書き)の速度が遅いシステムでは、flushをredoログに変更するたびに追加のfsyncsが必要になるため、コストがかかります.
0に設定すると、このモードは最も速くなりますが、安全ではありません.mysqldプロセスのクラッシュにより、前の秒にすべてのトランザクションデータが失われます.
1に設定すると、このモードは最も安全ですが、最も遅い方法です.mysqldサービスがクラッシュしたり、サーバホストcrashがクラッシュしたりした場合、binary logは最大1つの文またはトランザクションを失う可能性があります.
2に設定すると、モードの速度が速く、0よりも安全で、OSがクラッシュしたり、システムが電源が切れたりした場合にのみ、前の秒にすべてのトランザクションデータが失われる可能性があります.
ほとんどの場合、2を取ればいいです.
innodb_flush_method:この構成により、データとログがハードディスクに書き込まれる方法が決まります.一般的には、ハードウェアRAIDコントローラがあり、独立したキャッシュがwrite-backメカニズムを採用し、バッテリーの断電保護がある場合は、O_に設定する必要があります.DIRECT;そうでなければ、ほとんどの場合、fdatasync(デフォルト)に設定します.Sysbenchは、このオプションを決定するのに役立つ良いツールです.
innodb_log_buffer_size:この構成により、実行されていないトランザクションに割り当てられたキャッシュが決定されます.デフォルト値(1 MB)は一般的に十分ですが、トランザクションにバイナリオブジェクトまたは大きなテキストフィールドが含まれている場合、このキャッシュはすぐに満たされ、追加のI/O操作がトリガーされます.Innodbを見てlog_waits状態変数、0でない場合はinnodb_を増やしますlog_buffer_size.
write/read thread
非同期IOスレッド数
innodb_write_io_threads=16innodb_read_io_threads=16
( ， mysql )ダーティページに書かれたスレッド数を増やし、このパラメータを大きくすると書き込み性能が向上します.マルチコアcpuはこの2つのパラメータによってcpu性能をより効果的に利用することができる.
その他の設定
query_cache_size:query cache(クエリーキャッシュ)はよく知られているボトルネックであり、同時多発が少ない場合でもそうである.最適なオプションは、最初から無効にしてquery_を設定することです.cache_size=0(現在MySQL 5.6のデフォルト値)であり、他の方法でクエリーを高速化します.インデックスの最適化、コピー分散負荷の増加、memcacheやredisなどの追加キャッシュの有効化などです.アプリケーションにquery cacheを有効にして問題が見つからない場合は、query cacheが役に立つかもしれません.これはあなたがそれを止めたいなら、気をつけなければなりません.
log_bin:データベース・サーバをプライマリ・ノードのバックアップ・ノードとして機能させるには、バイナリ・ログを開く必要があります.そうしたらserverを設定するのを忘れないでください.idは一意の値です.サーバが1つしかない場合でも、ポイント・ベースのデータ・リカバリを行う場合は、最近のバックアップからリカバリ(フル・バックアップ)、バイナリ・ログの変更(インクリメンタル・バックアップ)を適用することが役立ちます.バイナリ・ログは作成されると永続的に保存されます.ディスク容量を消費したくない場合は、PURGE BINARY LOGSを使用して古いファイルを消去したり、expire_を設定したりすることができます.logs_daysは、ログが自動的に消去される日を指定します.
バイナリ・ログを記録するのはオーバーヘッドがないわけではありませんので、プライマリ・ノード以外のレプリケーション・ノードで不要な場合は、このオプションをオフにすることをお勧めします.
skip_name_resolve:クライアントがデータベース・サーバに接続すると、サーバはホスト名の解析を行い、DNSが遅い場合、接続の確立も遅くなります.したがって、サーバの起動時にskip_を閉じることを推奨します.name_DNS検索ではなくresolveオプション.唯一の限界は、その後GRANT文ではIPアドレスしか使用できないため、この設定を既存のシステムに追加するには特に注意しなければならない.
まとめ
もちろん、負荷やハードウェアに応じて、他の設定も機能します.遅いメモリと高速ディスク、高い同時および書き込みの負荷の場合、特別な調整が必要です.しかし、ここでの目標は、重要ではないMySQL設定を調整したり、ドキュメントを読んだりして、どの設定があなたにとって重要なのかを見つけるのにあまり時間を費やさずに、安定したMySQL構成を迅速に得ることです.
転載先:https://www.cnblogs.com/shamo89/p/8658644.html