MySql 最適化のための my.ini 中国語構成スキームの詳細な説明: InnoDB、4GB メモリ、および複数のクエリ

この記事は、4G メモリ システム用の MySQL 構成ファイル ソリューションです (主に InnoDB テーブルのみで MySQL を実行し、複数の接続を使用して複雑なクエリを実行します)。

#設定情報の開始
#説明: 4GB のメモリ、InnoDB のみ、ACID、少数の接続、大量のクエリ
# タイプ: システム
#設定情報の終了

# このファイルを /etc/my.cnf にコピーしてグローバル オプションを設定するか、mysql-data-dir/my.cnf にコピーしてサーバー固有のオプションを設定するか (このインストールでは、ディレクトリは C:mysqldata)、~/.my.cnf にコピーしてユーザー固有のオプションを設定できます。
#
# このファイルでは、プログラムがサポートするすべての永続的なオプションを使用できます。
# プログラムがサポートするオプションを知りたい場合は、プログラムの実行時に「--help」オプションを使用します。
#
# 個々のオプションに関する詳細情報もマニュアルに記載されています。

# 次のオプションは MySQL クライアント アプリケーションによって読み取られます。
# このセクションを読み取ることができるのは MySQL 標準クライアント アプリケーションのみであることに注意してください。
# 独自の MySQL クライアント プログラムでこれらの値を尊重する場合は、MySQL クライアント ライブラリの初期化時にオプションとして指定する必要があります。
#
[クライアント]
#password = [あなたのパスワード]
ポート = 3306
ソケット = /tmp/mysql.sock

# *** アプリケーション固有のオプションは以下に記載 ***

# MySQL サーバー。
[mysqld]

# 一般的な設定オプション
ポート = 3306
ソケット = /tmp/mysql.sock

# back_log は、MySQL 接続マネージャー スレッドが処理する前に、オペレーティング システムの listen キューに保持されている接続の数を示します。
# 接続率が非常に高く、「接続拒否」エラーが表示される場合は、この値を増やすことを検討してください。
# このパラメータの最大値については、システムのドキュメントを確認してください。
# back_log を OS の制限より高く設定しても効果はありません。
バックログ = 50

# TCP/IP ポートをリッスンする必要はまったくありません。
# 同じホスト上で実行されているすべてのプロセスが mysqld に接続する必要がある場合、これはセキュリティ強化となる可能性があります。
# mysqld とのすべてのやり取りは、Unix ソケットまたは名前付きパイプを介して行う必要があります。
# Windows で名前付きパイプを有効にせずに (「enable-named-pipe」オプションを使用して) このオプションを使用すると、mysqld が役に立たなくなることに注意してください。
#
#ネットワークをスキップ

# MySQL が許可する同時セッションの最大数。
# 接続の 1 つは SUPER 権限を持つユーザー用に予約され、接続制限に達した場合でも管理者がログインできるようになります。
最大接続数 = 100

# ホストごとに許可されるエラーの最大数。
# この制限に達すると、ホストは「FLUSH HOSTS」が実行されるかサーバーが再起動されるまで、MySQL サーバーへの接続をブロックします。
# 接続フェーズ中に無効なパスワードやその他のエラーが発生すると、この値が増加します。
# グローバル カウンターの "Aborted_connects" ステータス変数を参照してください。
最大接続エラー数 = 10

# すべてのスレッドによって開かれているテーブルの数。
# この値を増やすと、mysqld に必要なファイル記述子の数が増えます。
# したがって、「mysqld security」セクションの「open-file-limit」変数で、開いているファイルの数を少なくとも 4096 に設定する必要があります。
テーブルオープンキャッシュ = 2048

# 外部ファイルレベルのロックを有効にします。
# ファイル ロックを有効にするとパフォーマンスに悪影響が出るため、同じファイルで複数のデータベース インスタンスを実行している場合 (一部の制限が適用されることに注意してください)、またはファイル レベルで MyISAM テーブルをロックする必要がある他のソフトウェアを使用する場合にのみ使用してください。
#外部ロック

# サーバーが処理できるクエリ パケットの最大サイズと、サーバーが処理できるクエリの最大サイズ (大きな BLOB フィールドを操作する場合に重要)。
# 接続ごとに動的に拡張します。
最大許容パケット = 16M

# トランザクション内のバイナリ ログ SQL ステートメント用に維持できるキャッシュのサイズ。
# 大規模な複数ステートメントのトランザクションを頻繁に使用する場合は、この値を増やすとパフォーマンスが向上します。
# トランザクションからのすべてのステートメントはバイナリ ログ キャッシュにバッファリングされ、COMMIT の直後にバイナリ ログに書き込まれます。
# トランザクションがこの値より大きい場合は、代わりにディスク上の一時ファイルが使用されます。
# このバッファは、トランザクションの最初の更新ステートメントで各接続に割り当てられます。
binlog_cache_size = 1M

# 単一の HEAP (メモリ内) テーブルの最大許容サイズ。
# このオプションは、すべてのメモリ リソースを使い果たしてしまうような非常に大きな HEAP テーブルが誤って作成されるのを防ぎます。
最大ヒープテーブルサイズ = 64M

# ソート バッファは、一部の ORDER BY および GROUP BY クエリのソートを実行するために使用されます。
# ソートされたデータがソート バッファーに収まらない場合は、代わりにディスクベースのマージ ソートが使用されます - 「Sort_merge_passes」ステータス変数を参照してください。
# ソートが必要な場合は、各スレッドに割り当てられます。
ソートバッファサイズ = 8M

# このバッファは、FULL JOIN (インデックスなしの JOIN) を最適化するために使用されます。
# いずれにしても、この JOIN はほとんどの場合パフォーマンスに非常に悪影響を及ぼしますが、この変数を大きな値に設定するとパフォーマンスへの影響が軽減されます。
# FULL JOIN の特定の数については、「Select_full_join」ステータス変数を参照してください。
# FULL JOIN が見つかった場合は、各スレッドに割り当てられます。
結合バッファサイズ = 8M

# 再利用可能なスレッドを多数キャッシュに保存します。
# クライアントが切断すると、これより前のスレッド数が thread_cache_size を超えていない場合、クライアントのスレッドはキャッシュに配置されます。
# 新しい接続が多数ある場合、これにより作成する必要があるスレッドの数が大幅に削減されます。
# (適切なスレッド実装がある場合、通常、これによって目立ったパフォーマンスの向上は得られません。)
スレッドキャッシュサイズ = 8

# これにより、アプリケーションは、同時に実行するスレッドの希望数に関するヒントをスレッド システムに提供できるようになります。
# この値は、thread_concurrency() 関数呼び出しをサポートするシステム (Sun Solaris など) でのみ意味を持ちます。
# thread_concurrency には CPU 数の 2/4/6/... 倍を試してください。
スレッド同時実行性 = 8

# クエリ キャッシュは、SELECT 結果をキャッシュし、実際に同じクエリを再実行せずに後で結果を返すために使用されます。
# 同じクエリが多数あり、テーブルをほとんど変更しない場合は、クエリ キャッシュを有効にすると速度が大幅に向上します。
# 「Qcache_lowmem_prunes」ステータス変数を調べて、現在の値が負荷に対して十分に高いかどうかを確認してください。
# 注意: テーブルが頻繁に変更される場合、またはクエリの性質が毎回異なる場合、クエリ キャッシュによってパフォーマンスの向上は失われ、速度が低下します。
クエリキャッシュサイズ = 64M

# キャッシュされた結果セットのみがこの制限より小さくなります。
# これにより、非常に大きな結果セットを持つクエリ キャッシュが他のすべてのクエリ結果を上書きするのを防ぐことができます。
クエリキャッシュ制限 = 2M

# 全文検索インデックスの最小単語長。
# より短い単語を取得する必要がある場合は、これを減らすことをお勧めします。
# この値を変更した後は、FULLINDEX インデックスを再構築する必要があることに注意してください。
ft_min_word_len = 4

# システムが memlock() 関数呼び出しをサポートしている場合は、そのオプションを有効にすることができます (MySQL を実行してメモリにロックされた状態を維持し、メモリ負荷が高い場合にスワップアウトが発生するのを防ぐため)。
# これはパフォーマンスに非常に良いです。
#メモリロック

# CREATE TABLE ステートメントで別のテーブル タイプが指定されていない場合に、新しいテーブルを作成するときに使用するデフォルトのテーブル タイプ。
デフォルトのストレージエンジン = MYISAM

# 使用するスレッドスタックのサイズ。
# この量のメモリは接続時に常に予約されます。
# MySQL 自体は通常 64K を超えるメモリを必要としませんが、独自のスタック UDF 関数を使用している場合や、システムが特定の操作にさらに多くのスタックを必要とする場合は、この値をより高い値に設定することをお勧めします。
スレッドスタック = 192K

# デフォルトのトランザクション分離レベルを設定します。
# 使用可能なレベルは、READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE です。
transaction_isolation = 繰り返し読み取り

# 内部 (メモリ内) 一時テーブルの最大サイズ。
# テーブルがこの値を超えて大きくなると、自動的にディスクベースのテーブルに変換されます。
# この制限は単一のテーブルに適用されますが、このようなテーブルは多数存在する可能性があります。
tmp_table_size = 64M

# バイナリログを有効にします。
# これは、レプリケーション構成で MASTER として機能するために必要です。
# 最後のバックアップ時点から復元できるようにする必要がある場合は、バイナリ ログも必要です。
ログ bin = mysql bin

# 推奨されるバイナリ ログ形式 - 混合。
binlog_format = 混合

# レプリケーションに連鎖スレーブ サーバー (A->B->C) を使用している場合は、サーバー B でこのオプションを有効にする必要があります。
# スレーブ サーバ スレッドを介してスレーブ サーバのバイナリ ログにログを記録することで、ログの更新が可能になります。
#ログスレーブ更新

# 完全なクエリ ログを有効にします。サーバーが受信したすべてのクエリ (構文的に間違っているものも含む) がログに記録されます。
# これはデバッグには便利ですが、通常、本番環境では無効になっています。
＃ログ

# 警告をエラー ログ ファイルに出力します。
# MySQL で問題が発生した場合は、警告ログを有効にして、エラー ログで考えられる説明を確認してください。
#ログ警告

# 遅いクエリを記録します。
# 遅いクエリとは、「long_query_time」で定義された合計時間よりも長い時間がかかるクエリ、または log_short_format が有効になっていない場合にインデックスを使用しないクエリです。
# システムに新しいクエリを頻繁に追加する場合は、これをオンにすることをお勧めします。
遅いクエリログ

# この合計時間よりも長くかかるすべてのクエリは遅いとみなされます。
# ここで「1」の値を使用しないでください。これにより、非常に高速なクエリであっても時々ログに記録されるようになります (MySQL は現在、最も近い秒単位でのみ時間を測定します)。
長いクエリ時間 = 2

# MySQL が一時ファイルを保存するために使用するディレクトリ。
# たとえば、大規模なディスクベースのソートや、内部および明示的な一時テーブルを実行するために使用されます。
# 非常に大きな一時ファイルを作成しない場合は、swapfs/tmpfs ファイルシステムに配置すると便利です。
# あるいは、専用のディスクに置くこともできます。
# 「;」で区切って複数のパスを指定できます。これらは後でループ内で使用されます。
#tmpdir = /tmp

# *** レプリケーションに関する設定

# 1 から 2^32-1 までの一意のサーバー識別番号。
# この値はマスター サーバーとスレーブ サーバーの両方に必要です。
# 「master-host」が設定されていない場合はデフォルトで 1 になりますが、省略すると MySQL はマスター サーバーとして機能しません。
サーバーID = 1

# レプリケーションスレーブ (これを使用するにはマスターセクションをコメントアウトします)。
#
# このホストをレプリケーション スレーブとして構成するには、次の 2 つの方法から選択できます。
#
# 1) CHANGE MASTER TO コマンドを使用します (詳細はマニュアルに記載されています) - 構文は次のとおりです。
#
# MASTER を MASTER_HOST = 〈ホスト〉、MASTER_PORT = 〈ポート〉、MASTER_USER = 〈ユーザー〉、MASTER_PASSWORD = 〈パスワード〉 に変更します。
#
# 〈host〉、〈user〉、〈password〉を引用符で囲んだ文字列に置き換えます。また、〈port〉はマスターサーバーのポート番号です（デフォルトは3306）。
#
＃ 例：
#
# マスターを MASTER_HOST = '125.564.12.1'、MASTER_PORT = 3306、MASTER_USER = 'joe'、MASTER_PASSWORD = 'secret' に変更します。
#
＃ または
#
# 2) 次の変数を設定します。ただし、この方法を選択した場合、初めてレプリケーションを開始すると（MASTER_PASSWORD にパスワードを入力せずスレーブ接続が失敗した場合など、失敗した場合でも）、スレーブは master.info ファイルを作成します。スレーブをシャットダウンし、master.info を削除してスレーブを再起動しない限り、そのファイル内の次の変数の値に対するその後の変更は無視され、master.info ファイル内の接続によって上書きされます。
# このため、次の行は変更せず (コメント化)、代わりに CHANGE MASTER TO (上記参照) を使用することをお勧めします。
#
# 2 から 2^32-1 までの一意の ID が必要です (プライマリ サーバーとは異なります)。
# 「master-host」が設定されている場合、デフォルトは 2 になります。
# ただし省略するとスレーブサーバーとして機能しません。
#サーバーID = 2
#
# このスレーブのレプリケーション マスター - 必須。
#master-host = 〈ホスト名〉
#
# スレーブがマスターに接続するときに認証に使用するユーザー名 - 必須。
#master-user = 〈ユーザー名〉
#
# スレーブがマスターに接続するときに認証に使用するパスワード - 必須。
#master-password = 〈パスワード〉
#
# マスター サーバーがリッスンしているポート。
# オプション - デフォルトは 3306 です。
#master-port = 〈ポート〉

# スレーブ サーバーを読み取り専用にします。
# SUPER 権限とレプリケーション スレーブ サーバー スレッドを持つユーザーのみがデータを変更できます。
# これを使用すると、アプリケーションがマスターではなくスレーブ上のデータを誤って変更することがないようにすることができます。
#読み取り専用

#*** MyISAM 固有のオプション

# MyISAM テーブルのインデックス ブロックをキャッシュするために使用されるキー バッファーのサイズ。
# OS もラインをキャッシュするためにメモリを必要とするため、使用可能なメモリの 30% を超える値を設定しないでください。
# MyISAM テーブルを使用しない場合でも、内部の一時ディスク テーブルにも使用されるため、8-64M に設定する必要があります。
キーバッファサイズ = 32M

# MyISAM テーブルの完全なテーブルスキャンに使用されるバッファのサイズ。
# テーブル全体のスキャンが必要な場合は、各スレッドに割り当てられます。
読み取りバッファサイズ = 2M

# 順序付けられたソートで行を読み取る場合、ディスクシークを回避するために、このバッファを介して行を読み取ることができます。
# この値を非常に高い値に設定すると、ORDER BY のパフォーマンスが大幅に向上します。
# 必要に応じて各スレッドに割り当てます。
読み取りバッファサイズ = 16M

# MyISAM は、特別なツリーのようなキャッシュを使用して、一括挿入 (INSERT ... SELECT、INSERT ... VALUES(...)、および LOAD DATA INFILE) 操作を高速化します。
# この変数は、各スレッドのキャッシュ ツリーのサイズをバイト単位で制限します。
# 0 に設定すると、この最適化は無効になります。
# パフォーマンスを最適化するには、「key_buffer_size」より大きく設定しないでください。
# このバッファは、多数の挿入が検出された際に割り当てられます。
バルク挿入バッファサイズ = 64M

# このバッファは、MySQL が REPAIR、OPTIMIZE、ALTER テーブル ステートメント、および LOAD DATA INFILE を使用して空のテーブルにインデックスを再構築する必要があるときに割り当てられます。
# スレッドごとに割り当てられるので、大きな設定には注意してください。
myisam_sort_buffer_size = 128M

# インデックスの再構築時（REPAIR、ALTER TABLE、またはLOAD DATA INFILE中）にMySQLが使用できる一時ファイルの最大サイズ。
# 「file-size」がこの値より大きい場合、インデックスはキーキャッシュを介して作成されます（速度が遅くなります）。
myisam_max_sort_file_size = 10G

# テーブルに複数のインデックスがある場合、MyISAM はソート時に複数のスレッドを使用してそれらを並列に修復できます。
# 複数の CPU と十分なメモリがある場合、これは意味があります。
myisam_repair_threads = 1

# 適切に閉じられなかった MyISAM テーブルを自動的にチェックして修復します。
マイサム回復

# *** INNODB 固有のオプション ***

# InnoDB が有効になっている MySQL サーバーがあり、それを使用する予定がない場合は、このオプションを使用します。
# これにより、メモリとディスク領域を節約し、速度を向上させることができます。
#スキップ-innodb

# InnoDB がメタデータ情報を格納するために使用する追加のメモリ プール。
# InnoDB がこの目的のためにさらに多くのメモリを必要とする場合、オペレーティング システムからメモリの割り当てを開始します。
# 最近のほとんどのオペレーティング システムでは十分な速度なので、通常はこの値を変更する必要はありません。
# SHOW INNODB STATUS は現在の合計使用量を表示します。
innodb_追加メモリプールサイズ = 16M

# InnoDB は MyISAM とは異なり、バッファ プールを使用してインデックスと行データをキャッシュします。
# この値を大きく設定するほど、テーブル内の必要なデータにアクセスするときに必要なディスク I/O が少なくなります。
# 専用データベース サーバーでは、このパラメーターをマシンの物理メモリ サイズの 80% に設定できます。
# 物理メモリの競合により OS でページングが発生する可能性があるため、この値をあまり大きく設定しないでください。
# 32 ビット システムでは、プロセッサあたりのユーザー レベル メモリが 2 ～ 3.5 G に制限される可能性があるため、この値を高く設定しすぎないようにしてください。
innodb_buffer_pool_size = 2G

# InnoDB は 1 つ以上のデータ ファイルにデータを保存して、表領域を形成します。
# データ用の物理デバイスが 1 つしかない場合は、自動拡張ファイル 1 つで十分です。
# その他の場合には、デバイスごとに 1 つのファイルを選択するのが適切です。
# また、InnoDB を raw ディスク パーティションを使用するように構成することもできます。詳細については、マニュアルを参照してください。
innodb_data_file_path = ibdata1:10M:自動拡張

# InnoDB テーブルスペース ファイルを別の場所に保存する場合は、このオプションを設定します。
# デフォルトは MySQL データ ディレクトリです。
#innodb_data_home_dir = 〈ディレクトリ〉

# 非同期 IO 操作に使用される IO スレッドの数。
# この値は Unix システムでは 4 にハードコードされていますが、Windows では、より大きな数値の方がディスク I/O にメリットがある場合があります。
innodb_file_io_threads = 4

# InnoDB テーブルスペースの破損が発生した場合、この値をゼロ以外の値に設定すると、テーブルを簡単にエクスポートできるようになります。
# 値 1 から始めて、テーブルを正常にエクスポートできるようになるまで値を増やします。
#innodb_force_recovery=1

# InnoDB カーネルで許可されるスレッドの数。
# 最適な値は、アプリケーション、ハードウェア、およびオペレーティング システムのスケジュール プロパティによって大きく異なります。
# 値が高すぎるとスレッドのスラッシングが発生する可能性があります。
innodb_thread_concurrency = 16

# 1 に設定すると、InnoDB はコミットごとにトランザクション ログをディスクにフラッシュします (完全な ACID 動作を提供します)。
# 安全性を犠牲にして、小さなトランザクションを実行している場合は、0 または 2 を使用してログのディスク I/O を削減できます。
# 値が 0 の場合、ログはログ ファイルにのみ書き込まれ、ログ ファイルは約 1 秒に 1 回ディスクにフラッシュされます。
# 値が 2 の場合、コミットごとにログがログ ファイルに書き込まれますが、ログ ファイルは 1 秒に 1 回程度しかディスクにフラッシュされません。
innodb_flush_log_at_trx_commit = 1

# InnoDBのシャットダウンを高速化します。
# これにより、オフにすると InnoDB による完全なパージと挿入バッファのマージが無効になります。
# シャットダウン時間はかなり長くなる可能性がありますが、代わりに InnoDB が次回の起動時にこれを実行します。
#innodb_fast_shutdown

# InnoDB がログ データをバッファリングするために使用するバッファのサイズ。
# いっぱいになると、InnoDB はそれをディスクにフラッシュします。
# いずれにせよ毎秒更新されるため、非常に大きくしたり、非常に長いトランザクションにしたりする必要はありません。
innodb_log_buffer_size = 8M

# ログ グループ内の各ログ ファイルのサイズ。
# 不要なバッファプールの動的フラッシュとログファイルの書き換えを回避するために、ログファイルの合計サイズをバッファプールサイズの 25% ～ 100% に設定できます。
# ただし、ログ ファイルのサイズが大きいほど、回復プロセスに必要な時間が長くなることに注意してください。
innodb_log_file_size = 256M

# ログ グループ内のファイルの総数。
# 通常は 2 ～ 3 の値で十分です。
innodb_log_files_in_group = 3

# InnoDB ログ ファイルの場所。
# デフォルトは MySQL のデータ ディレクトリです。
# パフォーマンスを向上させるために、これを専用のハードドライブまたは RAID1 ボリュームに割り当てることをお勧めします。
#innodb_log_group_home_dir

# InnoDB バッファ プールで許可されるダーティ ページの最大パーセンテージ。
# 到着すると、InnoDB はクリーンなページをすべて消費しないように積極的にクリーンアップを開始します。
# これはソフト制限であり、維持されることは保証されません。
innodb_max_dirty_pages_pct = 90

# InnoDB がログに使用するフラッシュ方法。
# テーブルスペースは常に二重書き込みフラッシュロジックを使用します。
# デフォルト値は「fdatasync」、その他のオプションは「O_DSYNC」です。
#innodb_flush_method = O_DSYNC

# InnoDB トランザクションがロールバックする前にロックが許可されるまで待機する時間。
# InnoDB は、独自のロック テーブルでトランザクションのデッドロックを自動的に検出し、トランザクションをロールバックします。
# 同じトランザクションで LOCK TABLES コマンドを使用したり、InnoDB よりもトランザクションセーフな他のストレージ エンジンを使用したりすると、後で InnoDB がプロンプトできないデッドロックが発生する可能性があります。
# このような場合、タイムアウトはトラブルシューティングに役立ちます。
innodb_lock_wait_timeout = 120

[mysqlダンプ]
# ファイルに書き込む前に結果セット全体をバッファリングしないでください。
# 非常に大きなテーブルをエクスポートするときに必要です。
素早い

最大許容パケット = 16M

[mysql]
自動再ハッシュなし

# キーを使用した UPDATE と DELETE のみを許可します。
#安全なアップデート

[マイサムチク]
キーバッファサイズ = 512M
ソートバッファサイズ = 512M
読み取りバッファ = 8M
書き込みバッファ = 8M

[mysqlホットコピー]
対話タイムアウト

[mysqld_safe]
# プロセスごとに開くことができるファイルの数を増やします。
# 警告: グローバル システム制限が十分に高く設定されていることを確認してください。
# 開いているテーブルの数が多い場合は、高い値が必要です。
オープンファイル制限 = 8192

上記のパラメータ設定は、ご自身の実装状況に応じて参照してください。