Mysqlパラメーターinnodb_thread_concurrency

Mysql Parameter Innodb_thread_concurrency

0ロングシーク合計

innodb_thread_concurrency -innodb_thread_concurrency is a dynamic parameter that can be modified at any time -Directly allocate 0 within 64 active connections -High-pressure scenarios need to be tested from high to low to find the optimal value -A lower value in high-voltage scenarios can significantly increase the percentage of write QPS (high-frequency reads are limited) innodb_thread_sleep_delay (microseconds) -Define how long to wait to join the queue before starting to queue innodb_adaptive_max_sleep_delay (microseconds) -Configure the maximum allowed value of innodb_thread_sleep_delay. After configuration, innodb will automatically adjust the value of innodb_thread_sleep_delay to an appropriate range (adaptive algorithm) innodb_concurrency_tickets (default 5000) -When using a small value, small transactions can compete with large transactions. The disadvantage is that large transactions can only be run many times -Large transactions have advantages when using large values, but the disadvantage is that small transactions may not run all the time -Adjust this value by referring to the queue length. The length can be viewed from SHOW ENGINE INNODB STATUS (`ROW OPERATIONS` section of `SHOW ENGINE INNODB STATUS` output), or from TRX_CONCURRENCY_TICKETS of INFORMATION_SCHEMA.INNODB_TRX.

1公式説明

InnoDBは、オペレーティングシステムスレッドを使用してユーザートランザクション要求を処理します。 （トランザクションがコミットまたはロールバックされる前に、InnoDBエンジンに多くのリクエストをもたらす可能性があります）。最新のオペレーティングシステムとマルチコアプロセッサを搭載したサーバーでは、コンテキストスイッチングは非常に効率的であり、ほとんどのワークロードは同時スレッドの数に制限なく実行されます。 MySQL 5.5以降では、MySQLによってスケーラビリティが改善され、InnoDB内の同時実行スレッドの数を制限する必要が少なくなりました。

スレッド間のコンテキスト切り替えを最小限に抑えるのに役立ちます。 InnoDBは、さまざまな手法を使用して、オペレーティングシステムの同時実行スレッドの数を制限できます（したがって、大量の要求を一度に処理できます）。 InnoDBがユーザーセッションから新しいリクエストを受信したときに、スレッドの同時実行数が事前定義された制限に達すると、新しいリクエストは一定期間スリープしてから再試行します。スリープ後に計画どおりに実行できない要求は、先入れ先出しキューに入れられ、最終的に処理されます。ただし、ロックの取得を待機しているスレッドは、同時実行スレッドの数にはカウントされません。構成パラメーターinnodb_thread_concurrencyを設定することにより、並行スレッドの数を制限できます。実行スレッドの数がこの制限に達すると、追加のスレッドはペアキューに配置される前に数マイクロ秒スリープします。スリープは、パラメーターinnodb_thread_sleep_delaytimeを設定することで構成できます。

MySQL 5.6.3以降では、パラメータinnodb_adaptive_max_sleep_delayをinnodb_thread_sleep_delayに設定することで、最大許容値を設定できます。 InnoDBは、現在のスレッドスケジューリングアクティビティに従って、innodb_thread_sleep_delayの値を自動的に調整します。この動的調整メカニズムは、スレッドの作業に役立ちます。システム負荷が低いとき、またはシステムが全負荷に近いとき、スムーズにディスパッチできます。

MySQLとInnoDBの以前のバージョンシリーズでは、innodb_thread_concurrencyのデフォルト値と、同時スレッド数の暗黙的な制限が調整されました。 MySQLの現在の最新バージョンでは、innodb_thread_concurrencyのデフォルト値は0です。これは、スレッドの同時実行の数がデフォルトで制限されていないことを意味します。

さらに、InnoDBは、同時スレッドの数が制限されている場合にのみスリープします。スレッド数に制限がない場合、これらはすべて均等にスケジュールされます。つまり、innodb_thread_concurrencyが0の場合、値innodb_thread_sleep_delayは無視されます。

スレッド数が制限されている場合（innodb_thread_concurrency> 0の場合）、InnoDBは、単一のSQLステートメントの実行中に行われた複数の要求が、設定された制限に準拠せずにInnoDBに入るのを許可します。これにより、コンテキスト切り替えのオーバーヘッドinnodb_thread_concurrencyが削減されます。 SQLステートメント（結合など）には複数の行操作が含まれる場合があるため、InnoDBは指定された数の「チケット」を割り当て、最小限のオーバーヘッドでスレッドを繰り返すことができます。

新しいSQLステートメントが開始され、現在のスレッドに「チケット」がない場合は、innodb_thread_concurrencyパラメーター設定に準拠する必要があります。このスレッドがInnoDBに入る権利を取得すると、「チケット」が割り当てられます。これは、この「チケット」で使用できます。次に、InnoDBに入ると、行操作を実行します。 'tickets'が使い果たされると、スレッドは削除され、innodb_thread_concurrencyパラメーターが先入れ先出しキューの待機中のスレッドに返され、別の監視を待機します。このスレッドがInnoDBに再び入る権利を取得すると、「チケット」が再割り当てされます。グローバルパラメータinnodb_concurrency_tickets（デフォルトでは5000）を設定することで、「チケット」の数を指定できます。ロックの取得を待機しているスレッドには、ロックが使用可能になるとすぐに「チケット」が割り当てられます。

これらのパラメータの正しい値は、現在のシステム環境と負荷条件によって異なります。さまざまな異なる値を試して、現在のアプリケーションに適切な値を決定してください。マルチコアおよびマルチプロセッサコンピューターで、同時実行スレッドの数を制限する前に、innodb_adaptive_hash_indexなどのInnoDB構成パラメーターがパフォーマンスを向上させることができるかどうかを確認してください。

2履歴なし

innodb_thread_concurrency

innodb_thread_sleep_delay

innodb_concurrency_tickets

これらの3つのパラメーターを組み合わせて使用​​することは、そのような話です（オンラインの仲間が書いた抜粋を参照してください）

家の中にInnodbという名前の主要な売春婦がいて、誰もが彼女に近づきたいと思っています。

古いノガン（MySQL）では、これほど多くの人が同時に家に入ることができないため、一度に数人に制限されています。この制限の名前は（innodb_thread_concurrency）です。

他の人はどうですか、彼らは外に大きな列でしか入ることができません。同時に、古いノガンは言った、先生、あなたはしばらく眠ることができるので、あなたはそれほど一生懸命待つ必要はありません。

ここで、古いノガンは、眠らないように、一定期間（innodb_thread_sleep_delay）おじを起こします。

古いノガンはまた、叔父を起こすのが難しいのではないかと恐れていたので、彼はすぐにまた電話をしました。古いノガン自身が、ウェイクアップの数を最小限に抑えるための適応型ウェイクアップアルゴリズムを発明しました。しかし、叔父は最大ウェイクアップ時間を設定します。つまり、私はそのような時間にウェイクアップする必要があります（innodb_adaptive_max_sleep_delay）。

ですから、中から誰かが出てくると、待っているおじさん（最初のおじさん）が入って魚と水の喜びを楽しむことができます。

ただし、おじさんがサポートできる時間は異なり、1分（速い）もあれば、数時間かかるおじさんもいます。このように、外で待っているおじさんが意見を持っています。ああ、なぜ彼はまだ出てこないのですか。

古いバスタードは別の方法を考え、誰もが女の子の部屋に10分以上滞在してはならないと規定し（innodb_concurrency_tickets）、特に粘り強い人は10分に出て、列を作り続ける必要があります（最後に）キューの）。

次回彼が楽しむ番になるまで待ってください。

対応する文字：古いバスタード（MySQL）、叔父（スレッド）、女の子（innodb）

innodb_concurrency_ticketsを最適化する方法は、どの叔父が重要かによって異なります。たとえば、首相の息子がここにいます。首相の息子はとても丈夫です。より多くの時間を費やすのが最善です（innodb_concurrency_tickets）

首相の息子が長続きしない場合は、少し時間をかけて首相をすばやく列に並べます。

3公式の推奨事項

公式ドキュメントでは、次のように、innodb_thread_concurrencyの使用に関するいくつかの提案も示されています。

ワークロード内の同時ユーザースレッドの数が64未満の場合は、innodb_thread_concurrency = 0を設定することをお勧めします。

ワークロードが深刻であるか、ときどきピークに達する場合は、innodb_thread_concurrency = 128を設定し、最高のパフォーマンスを提供できるスレッドの数が見つかるまで、このパラメーター（96、80、64など）を継続的に減らすことをお勧めします。たとえば、システムが通常40〜50人のユーザーであるが、その数は定期的に60、70、さらには200に増加するとします。80人の同時ユーザーに設定すると、パフォーマンスが安定していることがわかります。この数値よりも大きい場合、代わりにパフォーマンスが低下します。この場合、パフォーマンスへの影響を避けるために、innodb_thread_concurrencyパラメーターを80に設定することをお勧めします。

InnoDBでユーザースレッドよりも多くの仮想CPU（20個の仮想CPUなど）を使用したくない場合は、innodb_thread_concurrencyパラメーターをこの値に設定することをお勧めします（パフォーマンスによっては低くなる場合があります）。 MySQLを他のアプリケーションから分離します。 mysqldプロセスを専用の仮想CPUにバインドすることを検討できます。ただし、myslqdプロセスがそれほどビジーでない場合、この種のバインディングにより、ハードウェアの使用が最適化されない可能性があることに注意してください。この場合、mysqldプロセスにバインドされた仮想CPUを設定して、他のアプリケーションが仮想CPUの一部またはすべてを使用できるようにすることができます。

場合によっては、最適なinnodb_thread_concurrencyパラメーター設定が仮想CPUの数よりも少なくなることがあります。システムを定期的に検出して分析します。負荷、ユーザー数、または作業環境の変更には、innodb_thread_concurrencyパラメーター設定の調整が必要になる場合があります。

4ノート

チケットは、MySQLレイヤーとInnodbレイヤーの間の相互作用の数として理解できます。たとえば、データを選択するには、Innodbレイヤーがデータを返す必要があり、MySQLレイヤーはwhere条件をフィルター処理してクライアントに返します。条件フィルタリングの場所に関係なく、条件フィルタリングがある場合ステートメントは100個のデータをクエリする必要があるため、実際にはInnodbレイヤーに100回入る必要があるため、実際に消費されるチケットは100です。もちろん、挿入選択操作の場合、クエリはInnodbレイヤーに1回入る必要があり、挿入はInnodbレイヤーに再度入る必要があるため、必要なチケットは通常の選択の2倍です。

このようにして、innodb_concurrency_ticketsが（長時間の処理スレッド）長期のブロック（短時間の処理スレッド）を回避できる理由も理解できます。 innodb_concurrency_ticketsが5000（デフォルト値）であると仮定すると、100W行のデータをクエリする必要がある大きなselect操作と、100行のデータをクエリする必要がある小さなselect操作があります。大選択操作が最初に実行されますが、5000行のデータが照会されると、CPU使用率が失われます。右、小選択操作が実行され、すぐに完了します。

5テストと要約

5.1まとめ

innodb_thread_concurrency

• innodb_thread_concurrencyは、いつでも変更できる動的パラメーターです。
• 64のアクティブな接続内で0を直接割り当てます
• 最適な値を見つけるには、高電圧シナリオを高から低までテストする必要があります
• 高電圧シナリオで値を低くすると、書き込みQPSの割合が大幅に増加する可能性があります（高周波読み取りは制限されています）

innodb_thread_sleep_delay（マイクロ秒）

• キューに入る前にキューに参加するのを待つ時間を定義します

innodb_adaptive_max_sleep_delay（マイクロ秒）

• innodb_thread_sleep_delayの最大許容値を構成します。構成後、innodbはinnodb_thread_sleep_delayの値を適切な範囲に自動的に調整します（適応アルゴリズム）

innodb_concurrency_tickets（デフォルトは5000）

• 小さな値を使用する場合、小さなトランザクションが大きなトランザクションと競合する可能性があります。欠点は、大きなトランザクションを何度も実行できることです。

• 大きな値を使用する場合、大きなトランザクションには利点がありますが、小さなトランザクションが常に実行されるとは限らないという欠点があります。

• この値を調整すると、キューの長さを参照できます。長さはSHOW ENGINE INNODB STATUS（ROW OPERATIONS出力のSHOW ENGINE INNODB STATUSセクション）から確認でき、INFORMATION_SCHEMA.INNODB_TRXのTRX_CONCURRENCY_TICKETSからも確認できます。

• innodb_thread_concurrencyが制限された後、アクティブな接続ステータスは変更されません。トランザクションがキューに入れられているかどうかはinnodb_trxから確認でき、show engine innodbstatusの行部分も確認できます。

• これは、長いトランザクションと短いトランザクションのシナリオに非常に役立つはずです。チケットを適切に減らすと、短いトランザクションを実行しやすくなります。次のテストシナリオによると、SQLの読み取りが5回のトランザクション、読み取りが5回と書き込みが3回のトランザクション、および2回のトランザクションの長さが基本的に同じです。キューイングがオンになっていて、各トランザクションが同じチケットでInnoDBに入ると、実行回数はより公平になります。したがって、書き込みの数が大幅に増加していることがわかります。

• sysbenchテストシナリオに関しては、読み取りの影響は大きくありません

横軸はinnodb_thread_concurrencyで、縦軸はQPSです。

5.3 90c読み取りおよび書き込み9：1テスト

（チケット= 5000は変更されず、ccは変更されました）

sysbench読み取り専用モデル

sysbench oltp_read_only --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 run BEGIN SELECT c FROM sbtest%u WHERE id=? SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ? SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c COMMIT

sysbench読み取りおよび書き込みモデル

sysbench oltp_read_write --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 run BEGIN SELECT c FROM sbtest%u WHERE id=? SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ? SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c UPDATE sbtest%u SET k=k+1 WHERE id=? UPDATE sbtest%u SET c=? WHERE id=? DELETE FROM sbtest%u WHERE id=? COMMIT

innodb_thread_sleep_delay = 10000

innodb_adaptive_max_sleep_delay = 150000

innodb_concurrency_tickets = 5000

2つのクライアントに対してそれぞれ1024、cc = innodb_thread_concurrency

DC	はい	アクティブ	cpu | qps	cs
0	3.6	1850年	51/38 | 19.6w	50w
500	3.6	1700	50/40 | 19.7w	52w
100	3.43.4	1800	42/48 | 18.1w	53w
50	2.8	1900年	35/53 | 15.0w	51w
10	0.5	2000年	20 / 6.6 | 11w	74w

qpsの読み取りと書き込み

DC	選択する	更新	削除	インサート
0	16.6w	3500	1700	1700
500	16.5w	4000	2000年	2000年
100	14.8w	6800	3400	3400
50	11.7w	7100	3600	3600
10	8.7w	5400	2700	2700

5.4トランザクションテストなし

（チケット= 5000は変更されず、ccは変更されました）

sysbench読み取り専用モデル（トランザクションなし、範囲クエリなし）

sysbench oltp_read_only --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 --range_selects=off --skip_trx=on run SELECT c FROM sbtest%u WHERE id=?

sysbench読み取り/書き込みモデル（トランザクションなし、範囲クエリなし）

sysbench oltp_read_write --mysql-host=rm-wz9w4eq0md96sc89x.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql --tables=64 --table-size=10000000 --rand-type=uniform --report-interval=1 --threads=1024 --time=12000 --skip_trx=on --range_selects=off run UPDATE sbtest%u SET k=k+1 WHERE id=? UPDATE sbtest%u SET c=? WHERE id=? DELETE FROM sbtest%u WHERE id=?

2つのクライアントに対してそれぞれ1024、cc = innodb_thread_concurrency

DC	はい	アクティブ	cpu | qps	cs
0	3.6	1850年	51/38 | 19.6w	50w
500	3.6	1700	50/40 | 19.7w	52w
100	3.43.4	1800	42/48 | 18.1w	53w
50	2.8	1900年	35/53 | 15.0w	51w
10	0.5	2000年	20 / 6.6 | 11w	74w
0トランザクションなし	2.7	1750	24/67 | 15.9w	45w
500トランザクションなし	2.7	1800	23/68 | 15w	45w
100トランザクションなし	2.5	1900年	21/70 | 14w	47w
50トランザクションなし	2.5	1800	20/69 | 13w	50w
10トランザクションなし	1.9	1900年	19/60 | 11w	58w

qpsの読み取りと書き込み

DC	選択する	更新	削除	インサート
0	16.6w	3500	1700	1700
500	16.5w	4000	2000年	2000年
100	14.8w	6800	3400	3400
50	11.7w	7100	3600	3600
10	8.7w	5400	2700	2700
0トランザクションなし	15.0w	4000	2000年	2000年
500トランザクションなし	15w	4300	2700	2100
100トランザクションなし	13w	5500	2700	2700
50トランザクションなし	12w	5900	2900	2900
10トランザクションなし	10w	7800	3900	3900

innodb_concurrency_tickets = 10

innodb_thread_concurrency = 10

mysql> select trx_operation_state,count(*) from information_schema.innodb_trx group by trx_operation_state +---------------------------------+----------+ | trx_operation_state | count(*) | +---------------------------------+----------+ | NULL | 25 | | sleeping before entering InnoDB | 1932 | | starting index read | 10 | +---------------------------------+----------+