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

推薦する

CocosCreator で http と WebSocket を使用する方法

目次1. HTTPGET 2. HTTP POSTウェブソケット4. Egretのhttpとwebs...

ウェブ標準学習リソースの素晴らしいコレクション

これらの仕様は、下位互換性のあるドキュメントを Web 上で公開し、できるだけ幅広いユーザーがアクセ...

Vue パッケージサイズの最適化の実装 (1.72M から 94K)

1. 背景最近、独立した開発者がUIデザインを行うのを支援するために、uideaというWebサイト...

Windows Server 2016 に Docker をインストールするプロセスと発生した問題

前提条件Windows Server でコンテナーを実行するには、Windows Server (半...

MySQLデータベースのスケジュールバックアップを実装する方法

1. シェルスクリプトを作成する vim バックアップdb.sh 次のようにスクリプトを作成します。...

Dockerコンテナを更新、パッケージ化、Alibaba Cloudにアップロードする方法

今回は、実行中のコンテナをイメージにパッケージ化して Alibaba Cloud にアップロードし、...

フロントエンドとバックエンド分離プロジェクトのDockerデプロイメントの実装例

目次1. 環境整備2. イメージを実行する問題を解決するRedis のインストールNginx のイン...

トランジションコンポーネントのアニメーション効果を使用した Vue サンプルコード

トランジションドキュメントアドレスは、フェードインとフェードアウト効果を実現するための背景ポップアッ...

MySQLの3つの用途と違いは同等ではない

MySQLでは判定記号がよく使われますが、等しくない記号はもっと一般的に使われます。次の3つの等しく...

使用場所によって混乱しやすいXHTMLタグ

<br />jb51.net では、常に記事のセマンティクスを重視してきましたが、HTM...

nginxとバックエンドポート間の競合の解決策

質問: Alice 管理システムを開発しているときに、すべてのバックエンド インターフェイスが最初の...

2019 年に最も役立ち重要なオープンソース ツール トップ 10

Black Duck の 2017 年のオープンソース調査では、回答者の 77% がオープンソース...

MySQL サブクエリ (ネストされたクエリ)、結合テーブル、複合クエリの詳細な説明

1. サブクエリMySQL 4.1以降はサブクエリをサポートしていますサブクエリ:別のクエリ内にネス...

Vue 画像切り抜きコンポーネントのサンプルコード

例:ヒント:このコンポーネントはvue-cropperの二次パッケージに基づいていますプラグインをイ...

ズームインとズームアウトの閉じるボタンを実現する CSS (サンプル コード)

この効果はブラウザ ページで最もよく見られます。まずは効果の画像をご覧ください。 上の図に示すように...