最近、単純な INSERT ステートメントを完了するのに必要な時間の差が大幅に増加していることに気づきました。これらのステートメントには平均で約 11 ミリ秒かかりますが、場合によっては 10 ～ 30 秒かかることもあり、実行に 5 分以上かかる場合もあります。

MySQL のバージョンは 8.0.24 で、Windows Server 2016 上で実行されます。私の知る限り、サーバーのリソースが過負荷になったことはありません。サーバーには十分な CPU オーバーヘッドがあり、32 GB の RAM が割り当てられています。

これは私が使用しているテーブルです:

CREATE TABLE `saved_segment` ( `recording_id` bigint unsigned NOT NULL、 `index` bigint unsigned NOT NULL、 `start_filetime` bigint unsigned NOT NULL、 `end_filetime` bigint unsigned NOT NULL、 `offset_and_size` bigint unsigned NOT NULL DEFAULT '18446744073709551615', `storage_id` tinyint unsigned NOT NULL、 主キー (`recording_id`,`index`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

このテーブルには他のインデックスや外部キーがなく、他のテーブルの外部キーへの参照として使用されません。テーブル全体のサイズは約20GB、行数は約281Mとそれほど大きくないと感じます。

テーブルはほぼ読み取り専用モードで使用され、1 秒あたり最大 1000 回の読み取りが行われます。これらの読み取りはすべて、複雑なトランザクションではなく単純な SELECT クエリで発生し、主キー インデックスを効果的に利用します。このテーブルへの同時書き込みは、たとえあったとしてもごくわずかです。これは、挿入が遅い場合に役立つかどうかを確認するために意図的に行われましたが、効果はありませんでした。それまでは、常に最大 10 個の同時挿入が進行します。 UPDATE または DELETE ステートメントは、このテーブルに対して実行されることはありません。

私が問題を抱えているクエリはすべてこのように構築されています。これらはトランザクションには決して現れません。クラスター化された主キーに基づく挿入は明らかに追加専用ではありませんが、クエリはほとんどの場合、1 ～ 20 の隣接する行をテーブルに挿入します。

saved_segment に IGNORE を挿入 (recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) 値 (19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10)、 (19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10)、 (19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10)、 (19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10)、 (19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10)、 (19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10)、 (19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10)、 (19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10)、 (19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10)、 (19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)
これは、上記のクエリの EXPLAIN ステートメントの出力です。
 <テーブルクラス="s-テーブル">
      <頭>
       idタイプを選択 テーブルパーティションタイプ可能なキーキーkey_len 参考OKフィルタリング済み エクストラ   <みんな> 1挿入 保存されたセグメント 空 すべて 空 空 空 空 空 空 空    
これらの問題は比較的新しいもので、テーブルが約 2 倍小さかったときには目立たなくなりました。 
 
テーブルへの同時挿入の数を約 10 から 1 に減らしてみました。また、挿入をさらに高速化するために、一部の列の外部キー (recording_id) も削除しました。 テーブル分析とスキーマ分析では、実用的な情報は得られませんでした。
 
私が考えた解決策の 1 つは、クラスター化主キーを削除し、自動インクリメント主キーと通常のインデックスを (recording_id,index) 列に追加することでした。私の意見では、これは挿入を「追加専用」にするのに役立ちます。あらゆる提案を歓迎します。よろしくお願いします。 
 
編集: コメントと回答で提起されたいくつかの点と質問について説明します。 
       

        

        autocommitが
        ONに設定されています。 
innodb_buffer_pool_size の値は 21474836480、innodb_buffer_pool_chunk_size の値は 134217728  
あるコメントでは、読み取りに使用される読み取りロックと書き込みに使用される排他ロックの間の競合について懸念が生じました。テーブルはキャッシュのように使用されます。パフォーマンスの向上を意味する場合、読み取りで常にテーブルの最新の状態を反映する必要はありません。ただし、テーブルはサーバーのクラッシュやハードウェア障害が発生した場合でも耐久性を維持する必要があります。これは、より緩和されたトランザクション分離レベルで実現できるでしょうか? 
 
アーキテクチャは確実に最適化できます。recording_id は 4 バイトの整数にすることができ、end_filetime は渡された値に変更でき、 start_filetime ; が小さくなる場合もあります。残念ながら、これらの変更は、節約されたスペースを補うためにテーブルのサイズが増加するまで、問題をしばらく延期するだけではないかと思います。 
 
テーブルへの挿入は常に連続的です テーブルに対して実行される SELECT は次のようになります: 
 
 
TRUE を選択 FROM 保存済みセグメント WHERE Recording_id = ? AND `index` = ?
 
SELECT インデックス、start_filetime、end_filetime、offset_and_size、storage_id FROM 保存済みセグメント WHERE レコーディング ID = ? AND start_filetime >= ? かつ start_filetime <= ? ORDER BY `index` ASC
 
2 番目のタイプのクエリはインデックスを使用して確かに改善できますが、これにより INSERT のパフォーマンスがさらに低下するのではないかと心配しています。 
 
もう 1 つ言い忘れていたのですが、これとよく似たテーブルが存在します。クエリと挿入はまったく同じですが、さらに IO 不足が発生する可能性があります。 
 
編集 2: SHOW TABLE STATUS テーブル saved_segment と非常によく似たテーブル saved_screenshot (bigint unsigned にあります) null 列の追加インデックス)。
 <テーブルクラス="s-テーブル">
        <頭>
          名前エンジンバージョン行フォーマットOK平均ライン長データ長最大データ長 Index_length データなし 自動インクリメント作成時間更新 時刻を確認 組織 チェックサム 作成オプション コメント   <みんな>  保存されたスクリーンショット InnoDB10ニュース 483430208 61297806069760213804646406291456 空「2021-10-21 01:03:21」「2022-11-07 16:51:45」空 utf8mb4_0900_ai_ci 空  保存されたセグメント InnoDB10ニュース 281861164 73 20802699264004194304 空「2022-11-02 09:03:05」「2022-11-07 16:51:22」空 utf8mb4_0900_ai_ci 空