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

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

1. 問題の説明

最近、問題が発生しました。パーティション テーブルをデータ クエリ/ロードに使用すると、通常のテーブルと比較してパフォーマンスが約 50% 低下しました。主なボトルネックは CPU に発生しました。CPU のボトルネックであったため、 perf top -a -gpstackを収集してパフォーマンスのボトルネックを見つけることができます。同時に、通常のテーブルと比較して、次の図に示すように、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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL パーティション テーブルのパーティション キーが主キーの一部である必要があるのはなぜですか?
  • MySQLパーティションテーブルは月別に分類されています
  • MySQLパーティションテーブルの詳細な説明
  • Mysql パーティションテーブルの管理とメンテナンス
  • MySQL 最適化パーティションテーブル
  • MySQL パーティションテーブル管理コマンドの概要

<<:  Nginx イントラネット スタンドアロン リバース プロキシの実装

>>:  私のCSSアーキテクチャのコンセプト - それは人によって異なり、ベストなものはなく、適切なものだけがある

推薦する

CSS background-blend-modeの仕組みを深く理解する

この記事は共有および集約することを歓迎します。全文を転載する必要はありません。著作権を尊重してくださ...

Dockerはポートを介してコンテナに接続します

Dockerコンテナ接続1. ネットワークポートマッピングPythonアプリケーション用のコンテナを...

html+cssレイアウトの3つの方法(ナチュラルレイアウト/フローレイアウト/ポジショニングレイアウト)

1. 自然なレイアウト<br />レイアウトは変更せずに自動的に左揃えになります。 2....

Linux でのマルチスレッドにおけるフォークの紹介

目次質問:ケース(1)子スレッドを作成する前にフォークするケース(2)子スレッドを作成した後にフォー...

大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション

MySQL では通常、limit を使用してページ上のページング機能を完了しますが、データ量が大きな...

MySQL がタイムスタンプを使用するときにタイムゾーンの問題を無視できるのはなぜですか?

私はいつも、なぜMySQLデータベースのtimestampタイムゾーンの問題を無視できるのか疑問に思...

MYSQLは継続サインイン機能を実装しており、サインイン後1日経過すると最初から開始します(SQL文)

1. テストテーブルを作成する テーブル `testsign` を作成します ( `userid`...

DockerHubを自分で構築する方法

先ほど使用したDocker HubはDockerによって提供されています。独自のDockerを構築す...

Vueはプルダウンとスクロールでデータを読み込む例を実装しています

目次ステップ1: インストールステップ2: 引用ステップ3: 使用Webプロジェクトでは、データを読...

Linux bzip2 コマンドの使用

1. コマンドの紹介bzip2 は、ファイルの圧縮と解凍に使用されます。これは、Linux システム...

jsを使用してシンプルな弾幕スクリーンシステムを実装する

この記事では、弾幕効果を実現するためのネイティブjsの具体的なコードを参考までに共有します。具体的な...

Maven プロジェクトのリモート デプロイメント && Tomcat を使用してデータベース接続を構成する方法

1つ。 tomcat を使用したリモート展開1.1 発生した問題:プロジェクトでは、サードパーティの...

JS Canvas インターフェースとアニメーション効果

目次概要Canvas API: グラフィックスの描画パス線種矩形アーク文章グラデーションと画像の塗り...

JavaScript の Set データ構造の詳細な説明

目次1. セットとは何か2. セットコンストラクタ2.1) 配列2.2) 文字列2.3) 議論2.4...

MySQL エラー「すべての派生テーブルには独自のエイリアスが必要です」の解決方法

MySQL は、マルチテーブルクエリを実行するときにエラーを報告します。 [SQL] SELECT ...