MySQL パーティションテーブルに関するパフォーマンスバグ

1. 問題の説明

最近、問題が発生しました。パーティションテーブルをデータクエリ/ロードに使用すると、通常のテーブルと比較してパフォーマンスが約 50% 低下しました。主なボトルネックは CPU に発生しました。CPU のボトルネックであったため、 perf top -a -gとpstackを収集してパフォーマンスのボトルネックを見つけることができます。同時に、通常のテーブルと比較して、次の図に示すように、CPU は主に関数build_template_fieldで消費されていることがわかりました。

2. pt-pmapを使用したスタック分析

perf top -g -aで検証するために、その時点でのpstackも取得しました。スレッド数が多いため、次のように pt-pmap でフォーマットして、有用な情報を取得しやすくしました。

フォーマット後、アイドル待機スタックを削除したところ、上記のように大量の待機スタックが見つかりました。これは、perf top -a -g のパフォーマンスでも確認されました。

3. このコラムのボトルネックポイントの分析

ここではcpuが大量に消費されていることがわかります

ha_innobase::build_template
 ->テンプレートフィールドの構築
   ->dict_col_get_clust_pos

templateはほとんどの場合特定のクエリにバインドされるため、通常のステートメントには少なくとも 1 つのtemplateが必要です。その構造はrow_prebuilt_tで、クエリタプル、クエリテーブル、クエリに使用されるインデックス、トランザクション関連情報、永続カーソル、MySQL レイヤークエリ行の長さ、自動インクリメント情報、ICP 関連情報、 mysql_row_templ_t構造などの情報が含まれています。 mysql_row_templ_t情報は、フィールドごとに 1 つあります。その主な機能は、MySQL レイヤーのフィールド情報と Innodb レイヤーの列情報の関連属性を記録することです。これは、MySQL レイヤーと Innodb レイヤーの間でレコードの行をすばやく変換するために使用されます。 mysql_row_templ_tを初期化するために、上記のロジックが登場します。

ロジックはおおよそ次のようになります。

テーブル内の各フィールドをループします (ループの 1 レベル) ha_innobase::build_template
アクセスする必要があるフィールドですか? build_template_needs_field
これには、クエリおよび書き込みが行われるすべてのフィールドが含まれます。アクセスする必要があるフィールドが増えるほど、速度は遅くなります。
そうでない場合はループを継続しないでください
アクセスが必要な場合
build_template_field (mysql_row_templ_t 構造体で埋められる)
主キーの各フィールドをループする（第2レベルループ）
疑似列を含め、主キーはテーブル内のすべてのフィールドです。テーブル内のフィールドが多いほど遅くなります) dict_col_get_clust_pos主キーにおけるこのフィールドの位置を確認してください
pos0 主キーpos1 DB_TRX_ID pos2 DB_ROLL_PTR pos3ユーザー用のその他のフィールド
インデックスの各フィールドをループします (2 レベルのループですが、通常はインデックスフィールドが多すぎることはないので、ここでは遅くなりません) dict_index_t::get_col_posインデックス内のこのフィールドの位置を確認し、そうでない場合はNULLを返します。
posを返します。たとえば、主キーがid1で、セカンダリインデックスがid2 id3の場合、セカンダリインデックスはpos0 id2 pos1 id3 pos2 id1です。 pos0 id2 pos1 id3 pos2 id1引き続き、mysql null ビットマップ、mysql 表示長、mysql 文字セットなどの他のプロパティを入力します。

ここでは、実際には 2 つのループ層、つまりループ内のループ (時間計算量 O(M×N))があり、ループが 2 つの場所に最も大きな影響を与えていることがわかります。

第1レベル、テーブル内のフィールドの数
2番目のレイヤーでは、アクセスする必要があるフィールド（読み取りと書き込みの両方）が主キー（つまり、すべてのフィールド）を介してループされます。

ここが遅いのはそのためです。ただし、テンプレートは通常、クエリに対して複数回作成されることはありません。たとえば、共通テーブルの大規模なクエリは、ステートメントが初めてデータを検索する前にのみ作成されます。これは、パーティションテーブルと共通テーブルの比較における特別な点です。以下に説明させていただきます。

4. パーティションテーブル内のテンプレートの複数作成

次のようなパーティションテーブルがあるとします。

テーブルtを作成する(
    id1 int,
    id2 整数、
    主キー(id1)、
    キー(id2)
)エンジン=innodb
範囲(id1)でパーティション分割(
    パーティションp0の値が（100）未満の場合、
    パーティションp1の値が（200）未満である、
    パーティションp2の値が（300）未満である    

t値(1,1)に挿入します。
t値(101,1)に挿入します。
t値(201,1)に挿入します。
t値(2,2)に挿入します。
t値(3,2)に挿入します。
t値(4,2)に挿入します。
t値(7,2)に挿入します。
t値(8,2)に挿入します。
t値(9,2)に挿入します。
t値(10,2)に挿入します。

「 select * from t where id2=1 」というステートメントを使用します。明らかに、id2はセカンダリインデックスです。すべてのMySQLセカンダリインデックスはローカルパーティションであるため、ここでの値は3つのパーティションに分散されます。このようなステートメントでは、通常のテーブルが最後の位置決め後の位置（ next_same ）に引き続きアクセスする必要がある場合、パーティションテーブルをカプセル化することで、 index read変更して再度位置決めします。これは次のパーティションをスキャンしていること、およびそのpart = 1が2番目のパーティション、つまりp1（最初は0）であることが明確にわかります。

この方法では、パーティションごとにtemplateを再構築する必要がありscan next partition )、上記の問題が発生します。これは理解できます。新しいパーティションは新しい InnoDB ファイルなので、最後に見つかった永続カーソルは実際には役に立たず、これは新しいテーブルアクセスに相当します。 templateを作成するかどうかの別の判断は次のとおりです。

  （m_prebuilt->sql_stat_start）の場合{
    テンプレートをビルドします(false);
  }

m_prebuilt->sql_stat_start は、ステートメントの先頭で true に設定されるだけでなく、次のようにパーティションが変更されるたびに true に設定されます。

ha_innopart::set_partition:
ビルド済みの sql_stat_start を m_sql_stat_start_parts.test(part_id);

5. 特別なプロセスについて

次のような、障害pstackのスタックもあります。

このスタックは実際には完全ではありませんが、 Partition_helper::handle_ordered_index_scanその中に登場します。この関数は実際にはパーティションテーブルのソートに関係しています。このような状況を考えると、セカンダリインデックスの select max(id2) from t の場合、最初に各パーティションにアクセスして最大値を取得し、次に各パーティションの最大値を比較して最終結果を取得する必要があります。MySQL は処理に優先キューを使用しますが、これはこの関数によって完了する関数の一部であるはずです (注意深く見ていません)。次に、範囲クエリに使用されるQUICK_RANGE_SELECTがあるので、次のように構築します。

id2<2 の場合、t から * を選択し、id2 で並べ替えます。

スタック：

これは、id2 フィールドはパーティション内でデータがサイズ順にソートされることを保証するだけですが、テーブル全体では順序が乱れ、追加の処理が必要になるためです。

6. 問題のシミュレーション

これらの準備により、300 個のフィールドと 25 個のパーティションを持つパーティションテーブルを構築できます。最新のテストバージョンは8.0.26です

テーブルtpar300col(を作成
    id1 int,
    id2 整数、
    id3 int、
    id4 整数、
...
    id299 varchar(20),
    id300 varchar(20)、
    主キー(id1)、
    キー(id2)
)エンジン=innodb
範囲(id1)でパーティション分割(
    パーティションp0の値が（100）未満の場合、
    パーティションp1の値が（200）未満である、
    パーティションp3の値が（300）未満の場合、
 ...
    パーティションp25の値が（2500）未満である  

tpar300col に値を挿入します (1,1,1,
.... パーティションごとに1つのデータをtpar300col値（2401,1,1）に挿入します。

次に、他のデータ id2 を 1 以外に構築し、ストアドプロシージャを作成します。

区切り文字 //

プロシージャ test300col() を作成します。
始める 
  num int を宣言します。
  数値を 1 に設定します。 
num <= 1000000 の場合
  id2=1 の場合、tpar300col から * を選択します。
  num = num+1 を設定します。
終了しながら;
終わり //
埋め込む：
 /opt/mysql/mysql3340/install/mysql8/bin/mysql -S--socket=/opt/mysql/mgr3315/data/mgr3315.sock -e'use test;call test300col();' > log.log

次に、perf top は次のことを観察します。

これにより問題が確認されました。

VII. 結論

この問題は、実際にはパーティションキーに対するセカンダリインデックスのデータ分散に関係していますが、セカンダリインデックスのデータを制御することはできず、インデックスを使用する必要があります。いくつかの方法で回避することしかできません。もちろん、次のようにバグも提出しました。

参考:

この問題を解決する方法があるかどうかはわかりません。たとえば、パーティション化されたテーブルの場合、各パーティションのフィールドは実際には同じです。毎回mysql_row_templ_t.clust_rec_field_noを再構築する必要がありますか?必要がなければ、問題は自然に解決されます。当局は、この問題が存在することを確認しました。これを回避するには、次の方法があります。

パーティションテーブルにはフィールドが多すぎないようにする
アクセスするフィールドは必ずしもselect *を使用する必要はありません
この問題が悪化する可能性があるハッシュパーティションの使用は避けてください。

これで、MySQL パーティションテーブルのパフォーマンスバグに関するこの記事は終了です。MySQL パーティションテーブルのパフォーマンスバグの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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