MySQL設定ファイルmy.cnfの最適化の詳細説明

MySQL 5.5.13
パラメータの説明:
[client]
character-set-server = utf8
port = 3306
socket = /data/mysq l/3306 /mysql.靴下
[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = /data/mysql/3306/sock
ベースのir = / usr /local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306 /mysql.pid
# table_cache パラメータはテーブル キャッシュの数を設定します。接続が確立されるたびに、少なくとも 1 つのテーブル キャッシュが開かれます。 #したがって、table_cache のサイズは max_connections の設定に関連する必要があります。たとえば、200 # の接続を並行して実行する場合、少なくとも 200 × N のテーブルのキャッシュが必要です。N は、アプリケーションがクエリ # に対して実行できる結合内のテーブルの最大数です。さらに、いくつかの追加のファイル記述子を一時テーブルとファイル用に予約する必要があります。
# Mysql がテーブルにアクセスするとき、テーブルがキャッシュ内で開かれている場合は、キャッシュに直接アクセスできます。# キャッシュされていないが、Mysql テーブル バッファーにまだスペースがある場合、テーブルはテーブル キャッシュがいっぱいの場合、テーブル キャッシュをオープンしてテーブル バッファに置きます。テーブル キャッシュを使用する利点は、現在使用されていないテーブルが特定のルールに従って解放されるか、一時的に拡張されることです。テーブルのコンテンツにより迅速にアクセスできるようになります。フラッシュ テーブルを実行すると、キャッシュの内容が #clear されます。一般に、データベースのピーク実行時間中の Open_tables # および Opened_tables のステータス値を確認することで、table_cache の値を増やす必要があるかどうかを判断できます (open_tables は現在開いているテーブルの数、 Opened_tables は開かれているテーブルの数です)。つまり、open_tables が table_cache に近く、Opened_tables の値が徐々に増加している場合は、この # 値のサイズを増やすことを検討する必要があります。また、Table_locks_waited が比較的高い場合は、table_cache も増やす必要があります。
open_files_limit = 10240
table_cache = 512
#非動的変数、サービスを再起動する必要があります
# MySQL への接続可能な数を指定します。 MySQL メイン スレッドが短期間に大量の接続リクエストを受信すると、このパラメータが有効になり、メイン スレッドは接続の確認と新しいスレッドの開始に短時間かかります。 back_log パラメータの値は、MySQL が新しいリクエストへの応答を一時的に停止するまでに、短期間にスタックに保存できるリクエストの数を示します。システムに短期間に多数の接続がある場合は、着信 TCP/IP 接続のリスニング キューのサイズを指定するこのパラメーターの値を増やす必要があります。オペレーティング システムが異なると、このキュー サイズには独自の制限があります。 back_log をオペレーティング システムの制限よりも高く設定しようとしても、効果はありません。デフォルト値は 50 です。 Linux システムの場合は、512 未満の整数に設定することをお勧めします。
back_log = 600
#MySQL で許可される接続の最大数
max_connections = 5000
#許可されるエラー接続の数
max_connect_errors = 6000
#Use --スキップ- 外部-locking MySQL オプションで外部ロックを回避します。このオプションはデフォルトで有効になっています
external-locking = FALSE
# 最大パケット サイズを設定し、サーバーが受け入れるデータ パケットのサイズを制限し、長すぎる SQL の実行による問題を回避します。が 16M の場合、MySQL クライアントまたは mysqld サーバーが max_allowed_pa​​cket バイトを超えるパケットを受信すると、「パケットが大きすぎます」エラーが発行され、接続が閉じられます。一部のクライアントでは、通信パケットが大きすぎると、クエリの実行中に「MySQL サーバーへの接続が失われました」エラーが発生することがあります。デフォルト値は 16M です。
#dev-doc: //m.sbmmt.com/
max_allowed_pa​​cket = 32M
# Sort_Buffer_Size は、各接続で初めて使用する必要がある接続レベルのパラメーターです (セッション) 時に、設定されたメモリを一度に割り当てます。
#Sort_Buffer_Size 接続レベルのパラメータが大きすぎると、システムのメモリ リソースが枯渇する可能性があるため、大きいほど良いです。例: 500 の接続は 500*sort_buffer_size(8M)=4G のメモリを消費します
#Sort_Buffer_Size が 2KB を超えると、メモリ割り当てに malloc() の代わりに mmap() が使用されるため、効率が低下します。
#技術紹介 //m.sbmmt.com/
#dev-doc: //m.sbmmt.com/
#ファイルソートが表示されるテーブルから*選択してください
#主要な最適化パラメータ
sort_buffer_size = 8M
#テーブル間の関連付けキャッシュに使用されるサイズ
join_buffer_size = 1M
# サーバー スレッド キャッシュ この値は、接続が切断されたときにキャッシュにまだ空きがある場合、スレッドが再度リクエストされた場合に、キャッシュに再利用できるスレッドの数を示します。キャッシュが空であるか、新しいリクエストがある場合、この値を増やすとシステムのパフォーマンスが向上する可能性があります。 Connections 状態と Threads_created 状態の変数を見ると、この変数の役割がわかります
thread_cache_size = 300
# thread_concurrency の値が正しく設定されているかどうかは、mysql のパフォーマンスに大きな影響を与えます。 thread_concurrency の値により、MySQL は複数の CPU (またはマルチコア) を完全に利用できなくなり、同時に動作できるのは 1 つの CPU (またはコア) だけになります。たとえば、デュアルコア CPU がある場合、thread_concurrency は 4 に設定され、2 つのデュアルコア CPU の場合、thread_concurrency の値は 8 に設定される必要があります
#Itは重要な最適化パラメータです
thread_concurrency = 8
# MySQL を使用するユーザーにとっては、誰もがこの変数に精通しているでしょう。これまでの MyISAM エンジンの最適化では、このパラメータも重要な最適化パラメータでした。しかし、開発の過程で、このパラメータにはいくつかの問題も明らかになりました。マシンのメモリはますます大きくなり、人々は以前は便利だったパラメータにますます大きな値を割り当てることに慣れています。このパラメータを増やすと、一連の問題も発生しました。まず、query_cache_size の動作原理を分析してみましょう。DB で SELECT クエリが実行された後、同じ SQL が再度 DB に来て呼び出されると、DB はテーブルを変更せずにそのステートメントをキャッシュします。キャッシュからクライアントを取得します。ここで重要な点は、DB が Query_cache を使用して動作する場合、ステートメントに含まれるテーブルがこの期間中に変更されていないことが必要であるということです。では、テーブルが変更された場合、Query_cache 内のデータはどのように処理されるのでしょうか?まず、テーブルに関連するすべての Query_cache とステートメントを無効にしてから、更新を書き込みます。次に、Query_cache が非常に大きく、テーブルに多くのクエリ構造があり、クエリ ステートメントが無効である場合、更新または挿入が非常に遅くなり、更新または挿入がなぜ非常に遅いのかがわかります。したがって、データベースの書き込みまたは更新の量が比較的多いシステムでは、このパラメータは大きすぎる割り当てには適していません。また、同時実行性が高く、書き込み量が多いシステムでは、この機能を無効にすることをお勧めします。
#主要な最適化パラメータ（メインライブラリの追加、削除、変更 - MyISAM）
query_cache_size = 512M
#単一のクエリで使用できるバッファサイズを指定します。デフォルトは1Mです
query_cache_limit = 2M
#デフォルト 大きな設定値は大きなデータクエリには適していますが、クエリがすべて小さなデータクエリである場合、メモリの断片化と無駄が発生しやすくなります
#クエリキャッシュ断片化率 = Qcache_free_blocks / Qcache_total_blocks * 100%
# クエリ キャッシュの断片化率が 20% を超える場合は、FLUSH QUERY CACHE を使用してキャッシュを最適化するか、クエリがすべて小さいデータ ボリュームである場合は query_cache_min_res_unit を減らしてみてください。
#クエリキャッシュ使用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#クエリ キャッシュの使用率が 25% 未満の場合は、query_cache_size の設定が大きすぎるため、適切に減らすことができることを意味します。クエリ キャッシュの使用率が 80% を超え、Qcache_lowmem_prunes > 50 の場合は、query_cache_size が少し小さい可能性があることを意味します。または断片が多すぎます。
#クエリキャッシュヒット率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
#それぞれに使用されるスタックサイズを制限するデータベーススレッド。ほとんどのアプリケーションにはデフォルト設定で十分です
thread_stack = 192K
# デフォルトのトランザクション分離レベルを設定します。 使用可能なレベルは次のとおりです:
# READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE
# 1.READ UNCOMMITTED - コミットされていない読み取り 2.READ COMMITTE - コミットされた読み取り 3.REPEATABLE READ - 反復可能な読み取り 4.SERIALIZABLE - シリアル
transaction_isolation = READ-COMMITTED
# tmp_table_size のデフォルトのサイズは 32M です。一時テーブルがこのサイズを超えると、MySQL は「テーブル tbl_name がいっぱいです」という形式のエラーを生成します。高度な GROUP BY クエリを多数実行する場合は、tmp_table_size の値を増やしてください。
tmp_table_size = 246M
max_heap_table_size = 246M
#インデックスキャッシュサイズ: データベースインデックス処理の速度、特にインデックス読み取りの速度を決定します
key_buffer_size = 512M
# Sq lバッファへの読み込みサイズ。テーブルの順次スキャンのリクエストは読み取りバッファを割り当て、MySql はそれにメモリ バッファを割り当てます。 read_buffer_size 変数は、このバッファのサイズを制御します。テーブルに対する順次スキャン要求が非常に頻繁で、頻繁なスキャンの実行が遅すぎると感じる場合は、この変数の値とメモリ バッファ サイズを増やすことでパフォーマンスを向上させることができます。
read_buffer_size = 4M
# MySql のランダム読み取り (クエリ操作) バッファ サイズ。行が任意の順序 (ソート順など) で読み取られる場合、ランダムな読み取りバッファーが割り当てられます。ソートクエリを実行するとき、MySql はディスク検索を回避し、クエリ速度を向上させるために最初にバッファをスキャンします。大量のデータをソートする必要がある場合は、この値を適切に増やすことができます。ただし、MySql はこのバッファ スペースを顧客接続ごとに割り当てるため、過剰なメモリ オーバーヘッドを避けるためにこの値を適切に設定するようにしてください。 wread_rnd_buffer_size =16m
＃バッチ挿入データキャッシュサイズ、これにより挿​​入効率を効果的に改善できます。デフォルトは8m
bulk_insert_buffer_size=64m
＃myisamテーブルが変更されたときに必要なバッファーです
myisam_sort_buffer_size = 128M
# MySQL がインデックスを再構築するとき (REPAIR、ALTER TABLE、または LOAD DATA INFILE を実行するとき) に許可される最大一時ファイル サイズ
# ファイル サイズがこの値より大きい場合、インデックスはキー値 バッファーの作成 (遅い)
myisam_max_sort_file_size = 10G
# テーブルに複数のインデックスがある場合、MyISAM は複数のスレッドを使用して並列ソートによってそれらを修正できます
# これは、次の場合に便利です。複数の CPU と多くのメモリ状況のユーザーには、良い選択です
myisam_repair_threads = 1
#適切に閉じられていない MyISAM テーブルを自動的にチェックして修復します
myisam_recover
interactive_timeout = 120
wait_timeout = 12 0
innodb_data_home_dir = /data/mysql/3306/data
#テーブルスペースファイルの重要なデータ
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#このパラメータは設定するために使用されますInnoDB によって保存されるデータ ディレクトリ情報およびその他の情報 Oracle のライブラリ キャッシュと同様の、内部データ構造のメモリ プール サイズ。これは必須パラメータではないため、超過する可能性があります。
innodb_Additional_mem_pool_size = 16M
# これは Innodb テーブルにとって非常に重要です。 Innodb テーブルは、MyISAM テーブルよりもバッファリングの影響を受けやすくなります。 MyISAM はデフォルトの key_buffer_size 設定で問題なく動作しますが、Innodb はデフォルトの innodb_buffer_pool_size 設定でカタツムリのように動作します。 Innodb はデータとインデックスの両方をキャッシュするため、オペレーティング システムに大量のメモリを残す必要がなく、Innodb のみを使用する必要がある場合は、使用可能なメモリの最大 70 ～ 80% に設定できます。 key_buffer に適用されるいくつかのルールは次のとおりです - データ量が大きくなく、劇的に増加しない場合は、innodb_buffer_pool_size を大きく設定する必要はありません
innodb_buffer_pool_size = 512M
#ファイル IO スレッドの数、通常は 4 , ただし、Windows ではさらに大きく設定できます。
innodb_file_io_threads = 4
# InnoDb コアで許可されるスレッドの数
# 最適な値は、アプリケーション、ハードウェア、およびオペレーティング システムのスケジューリング方法によって異なります
# 値が高すぎると、次のような問題が発生する可能性があります。 Mutex バンプへのスレッド。
innodb_thread_concurrency = 8
# このパラメータが 1 に設定されている場合、各トランザクションがコミットされた後にログがディスクに書き込まれます。パフォーマンスを向上させるために、0 または 2 に設定できますが、障害が発生した場合にデータが失われる危険があります。 0 に設定すると、トランザクション ログがログ ファイルに書き込まれ、ログ ファイルが 1 秒に 1 回ディスクにフラッシュされます。 2 に設定すると、トランザクション ログはコミット時にログに書き込まれますが、ログ ファイルは一度に 1 回ずつディスクにフラッシュされます。
innodb_flush_log_at_trx_commit = 2
#このパラメータは、一部のログ ファイルで使用されるメモリ サイズ (M 単位) を決定します。バッファを大きくするとパフォーマンスが向上しますが、予期しない障害が発生するとデータ損失が発生する可能性があります。MySQL 開発者は、バッファを 1 ～ 8M に設定することを推奨します
innodb_log_buffer_size = 16M
#このパラメータは、M を使用してデータ ログ ファイルのサイズを決定します。単位を大きくするとパフォーマンスが向上しますが、障害が発生したデータベースの回復に必要な時間も長くなります
innodb_log_file_size = 128M
#パフォーマンスを向上させるために、MySQL はラウンドロビン方式でログ ファイルを複数のファイルに書き込むことができます。推奨設定は 3M
innodb_log_files_in_group = 3
#推奨読書 //m.sbmmt.com/
# Buffer_Pool 内の Dirty_Page の数は、InnoDB のシャットダウン時間に直接影響します。パラメータ innodb_max_dirty_pages_pct は Buffer_Pool 内の Dirty_Page の比率を直接制御でき、幸いなことに innodb_max_dirty_pages_pct は動的に変更できます。したがって、InnoDB を閉じる前に、innodb_max_dirty_pages_pct を減らし、一定期間データ ブロックを強制的にフラッシュします。これにより、MySQL のシャットダウン時間を大幅に短縮できます。
innodb_max_dirty_pages_pct = 90
# InnoDB にはデッドロック検出メカニズムが組み込まれており、これにより未完了のトランザクションがロールバックされる可能性があります。ただし、MyISAM のロック テーブル ステートメントまたはサードパーティのトランザクション エンジンを InnoDB と組み合わせて使用​​する場合、InnoDB はデッドロックを認識できません。この可能性を排除するには、innodb_lock_wait_timeout を、最終的にトランザクション ロールバックの対象となるデータの変更を他のトランザクションに許可するまでの待ち時間 (秒単位) を MySQL に指示する整数値に設定できます
innodb_lock_wait_timeout = 120
#Exclusive tableスペース (閉じた)
innodb_file_per_table = 0
#start mysqld with –slow-query-log-file=/data/mysql/3306/slow.log
slow_query_log
long_query_time = 1
複製する-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
#このスレーブの更新操作でバイナリ ファイルを書き込むかどうかを設定します。他のスレーブ ライブラリのメイン ライブラリである場合、スレーブ ライブラリのスレーブ ライブラリがログ同期を実行できるように、このパラメータを -logs-bin
と一緒に使用する必要があります。log-slave-updates
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
#STATEMENT,ROW,MIXED
# SQL ステートメントベースのレプリケーション (statement-ベース レプリケーション (SBR)、行ベース レプリケーション (RBR)、混合ベース レプリケーション (MBR)。これに対応して、binlog には STATEMENT、ROW、および MIXED の 3 つの形式があります。
binlog_format = MIXED
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay -ログ情報ファイル=/データ/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
skip-name-resolve
#master-connect-retry = 10
スレーブスキップエラー = 1032,1062,126,1114,1146,1048,1396
サーバーID = 1
[mysqldump]
クイック
max_ allowed_pa​​cket = 32M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

上記は MySQL の設定ですfile my. cnf 最適化の詳細な説明については、PHP 中国語 Web サイト (m.sbmmt.com) の関連コンテンツに注目してください。