MySQLシリーズ15 MySQL共通設定とパフォーマンスストレステスト

1. 一般的なMySQLの設定

以下のすべての構成パラメータは、32G のメモリを搭載したサーバーに基づいています。

1. 独立した表領域を開く

innodb_file_per_table = 1

2. MySQL サービスで許可される同時セッション数の上限は、デフォルトでは 151 です。「接続数が多すぎます」というエラーメッセージが頻繁に表示される場合は、この値を増やす必要があります。

最大接続数 = 8000

3. オペレーティングシステムがリスニングキューに保持できる接続数

バックログ = 300

4. 各クライアント接続に許容されるエラーの最大数。この数を超えると、MYSQL サーバーが再起動されるか、flush hosts コマンドによってこのホストの関連情報がクリアされるまで、MYSQL サーバーはこのホストの接続要求を禁止します。

最大接続エラー数 = 1000

5. すべてのスレッドによって開かれたテーブルの数

オープンファイル制限 = 10240

6. 各接続で送信される最大データサイズは 1G で、1024 の倍数である必要があります。通常は、最大の BLOB の値に設定されます。

最大許容パケット = 32M

7. リクエストの最大接続時間

待機タイムアウト = 10

8. ソートバッファはORDER BYおよびGROUP BYキューによるソートを処理するために使用されます。

ソートバッファサイズ = 16M

9. インデックスなしのテーブル全体のスキャンの最小バッファサイズ

結合バッファサイズ = 16M

10. クエリバッファサイズ

クエリキャッシュサイズ = 128M

11. 1回のクエリで使用できるバッファサイズを指定します。デフォルトは1Mです。

クエリキャッシュ制限 = 4M

12. デフォルトのトランザクション分離レベルを設定する

transaction_isolation = 繰り返し読み取り

13. スレッドが使用するヒープサイズ。この値は、ストアドプロシージャの再帰深度と、メモリ内で処理できる SQL ステートメントの複雑さを制限します。このメモリ量は、接続ごとに予約されます。

スレッドスタック = 512K

14. バイナリログを有効にする

ログビン

15. バイナリログ形式: 行ベース

binlog_format = 行

16. InnoDB は、インデックスと生データを格納するためにバッファプールを使用します。この変数は、サーバーの物理メモリサイズの 80% に設定できます。

innodb_buffer_pool_size = 6G

17. IO操作を同期するために使用されるIOスレッドの数

innodb_file_io_threads = 4

18. Innodb コア内で許可されるスレッド数の推奨設定は、CPU の数とディスクの数の 2 倍です。

innodb_thread_concurrency = 16

19. ログデータをバッファリングするために使用されるバッファのサイズ

innodb_log_buffer_size = 16M

20. ロググループ内の各ログファイルのサイズ

innodb_log_file_size = 512M

21. ロググループ内のファイル総数

innodb_log_files_in_group = 3

22. SQL文がロールバックされる前にInnoDBトランザクションがInnoDB行ロックを待機する時間

innodb_lock_wait_timeout = 120

23. 遅いクエリレコードのしきい値期間はデフォルトで10秒です

長いクエリ時間 = 2

24. インデックスを使用しないクエリを記録する

インデックスを使用しないログクエリ

my.cnf の例:

[mysqld]
データディレクトリ=/var/lib/mysql
ソケット=/var/lib/mysql/mysql.sock
シンボリックリンク=0
innodb_file_per_table = 1
innodb_buffer_pool_size = 6442450944 #メモリが不足している場合はエラーが報告されます innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = 行
遅いクエリログ
長いクエリ時間 = 2
インデックスを使用しないログクエリ
transaction_isolation = 繰り返し読み取り
クエリキャッシュサイズ = 128M
クエリキャッシュ制限 = 4M
最大接続数 = 8000
バックログ = 300
最大接続エラー数 = 1000
オープンファイル制限 = 10240
最大許容パケット = 32M
待機タイムアウト = 10
ソートバッファサイズ = 16M
結合バッファサイズ = 16M
スレッドスタック = 512K

2. MySQLパフォーマンスストレステスト

一般的なテストツール:

mysqlslap
システムベンチ
tpcc-mysql
MySQL ベンチマークスイート
MySQL スーパースマック
マイベンチ

mysqlslap ツールの紹介

mysqlslap は mariadb パッケージから提供されます。テスト中、デフォルトで mysqlslap スキーマが生成され、テストテーブル t1 が生成され、テストデータがクエリされて挿入され、mysqlslap ライブラリが自動的に生成されます。すでに存在する場合は、まず削除されます。 --only-print を使用して実際のテストプロセスを印刷すると、テスト全体が完了した後、データベースにトレースが残されません。

一般的なオプション:

--auto-generate-sql, -a テストテーブルとデータを自動的に生成し、mysqlslapツールによって生成されたSQLスクリプトが同時圧力のテストに使用されることを示します。
--auto-generate-sql-load-type=type テストステートメントのタイプ。テスト対象の環境が読み取り操作、書き込み操作、またはその 2 つの混合であるかどうかを示します。値には、読み取り、キー、書き込み、更新、混合（デフォルト）が含まれます。
--auto-generate-sql-add-auto-increment は、生成されたテーブルに auto_increment 列を自動的に追加することを意味します。この機能はバージョン 5.1.18 以降でサポートされています。
--number-char-cols=N, -x N 自動生成されたテストテーブル内の文字列の数、デフォルトは 1
--number-int-cols=N, -y N 自動生成されたテストテーブル内の数値列の数、デフォルトは 1
--number-of-queries=N テストクエリの合計数（同時クライアント数 × クライアントあたりのクエリ数）
--query=name,-q カスタムスクリプトを使用してテストを実行します。たとえば、カスタムストアドプロシージャまたは SQL ステートメントを呼び出してテストを実行できます。
--create-schemaはカスタムテストライブラリ名、テストスキーマを表し、MySQLのスキーマはデータベースです
--commint=N 何回のDML後にコミットするか
--compress, -C サーバーとクライアントの両方が圧縮をサポートしている場合はメッセージを圧縮する
--concurrency=N, -c N は同時実行数、つまり同時に select を実行するためにシミュレートされるクライアントの数を示します。カンマまたは --delimiter パラメータで指定された値を区切り文字として、複数の値を指定できます。
--engine=engine_name、-e engine_name はテストするエンジンを表します。区切り文字で区切られた複数のエンジンを指定できます。
--iterations=N, -i N テスト実行の反復回数。これは、異なる同時環境でテストを実行する回数を表します。
--only-print テスト文を実際に実行せずに印刷するだけです
--detach=N N 文を実行した後に切断し、再接続する
--debug-info, -T メモリとCPU関連の情報を出力します

テスト例:

1) シングルスレッドテスト

[root@centos7 ~]# mysqlslap -a -uroot -p
パスワードを入力してください: 
ベンチマーク
        すべてのクエリを実行するのにかかった平均秒数: 0.004 秒
        すべてのクエリを実行するのに要する最小秒数: 0.004 秒
        すべてのクエリを実行する最大秒数: 0.004 秒
        クエリを実行しているクライアントの数: 1
        クライアントあたりの平均クエリ数: 0

2) マルチスレッドテストでは、同時接続をシミュレートするために –concurrency を使用します。

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
パスワードを入力してください: 
ベンチマーク
        すべてのクエリを実行するのにかかった平均秒数: 3.384 秒
        すべてのクエリを実行するための最小秒数: 3.384 秒
        すべてのクエリを実行する最大秒数: 3.384 秒
        クエリを実行しているクライアントの数: 500
        クライアントあたりの平均クエリ数: 0

3) 異なるストレージエンジンのパフォーマンスを同時にテストして比較する

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
パスワードを入力してください: 
ベンチマーク
        エンジンmyisamのために実行
        すべてのクエリを実行するのにかかった平均秒数: 0.192 秒
        すべてのクエリを実行するのに要する最小秒数: 0.187 秒
        すべてのクエリを実行する最大秒数: 0.202 秒
        クエリを実行しているクライアントの数: 500
        クライアントあたりの平均クエリ数: 2

ベンチマーク
        エンジン innodb の実行
        すべてのクエリを実行するのにかかった平均秒数: 0.355 秒
        すべてのクエリを実行するための最小秒数: 0.350 秒
        すべてのクエリを実行する最大秒数: 0.364 秒
        クエリを実行しているクライアントの数: 500
        クライアントあたりの平均クエリ数: 2


ユーザー時間 0.33、システム時間 0.58
最大常駐セットサイズ 22892、積分常駐セットサイズ 0
非物理ページフォールト 46012、物理ページフォールト 0、スワップ 0
ブロック入力 0 出力 0、メッセージ入力 0 出力 0、シグナル 0
自発的なコンテキストスイッチ 31896、非自発的なコンテキストスイッチ 0

4) 同時クエリ数500と1000、合計クエリ数5000でテストを実行する

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --クエリ数 5000 --debug-info
パスワードを入力してください: 
ベンチマーク
        すべてのクエリを実行するのにかかった平均秒数: 3.378 秒
        すべてのクエリを実行するための最小秒数: 3.378 秒
        すべてのクエリを実行する最大秒数: 3.378 秒
        クエリを実行しているクライアントの数: 500
        顧客あたりの平均クエリ数: 10

ベンチマーク
        すべてのクエリを実行するのにかかった平均秒数: 3.101 秒
        すべてのクエリを実行するための最小秒数: 3.101 秒
        すべてのクエリを実行する最大秒数: 3.101 秒
        クエリを実行しているクライアントの数: 1000
        クライアントあたりの平均クエリ数: 5


ユーザー時間 0.84、システム時間 0.64
最大常駐セットサイズ 83068、積分常駐セットサイズ 0
非物理ページフォールト 139977、物理ページフォールト 0、スワップ 0
ブロック入力 0 出力 0、メッセージ入力 0 出力 0、シグナル 0
自発的なコンテキストスイッチ 31524、非自発的なコンテキストスイッチ 3

5) 反復テスト

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
パスワードを入力してください: 
ベンチマーク
        すべてのクエリを実行するのにかかった平均秒数: 3.307 秒
        すべてのクエリを実行するのに要する最小秒数: 3.184 秒
        すべてのクエリを実行する最大秒数: 3.421 秒
        クエリを実行しているクライアントの数: 500
        顧客あたりの平均クエリ数: 10


ユーザー時間 2.18、システム時間 1.58
最大常駐セットサイズ 74872、積分常駐セットサイズ 0
非物理ページフォールト 327732、物理ページフォールト 0、スワップ 0
ブロック入力 0 出力 0、メッセージ入力 0 出力 0、シグナル 0
自発的なコンテキストスイッチ 73904、非自発的なコンテキストスイッチ 3

上記は、MySQL シリーズ 15: 一般的な MySQL 構成とパフォーマンスストレステストの詳細な内容です。一般的な MySQL 構成とパフォーマンスストレステストの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: