MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.9.1.3 InnoDB テーブルの圧縮の調整

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / InnoDB テーブルの圧縮の調整

このページは機械翻訳したものです。

15.9.1.3 InnoDB テーブルの圧縮の調整

ほとんどの場合、InnoDB データストレージと圧縮で説明した内部的な最適化によって、圧縮済みデータを使用してもシステムは適切に動作します。ただし、圧縮の効率性はデータの特性によって異なるため、圧縮テーブルのパフォーマンスに影響を与える決定を行うことができます。

圧縮するテーブル。
使用する圧縮済みページサイズ。
実行時のパフォーマンス特性 (システムでデータの圧縮および圧縮解除に要する時間など) に基づいて、バッファープールのサイズを調整するかどうか。ワークロードがデータウェアハウス (主にクエリー) または OLTP システム (クエリーと DML の混在) に似ているかどうか。
システムの圧縮テーブル上で DML 操作が実行されているときに、データを配布する方法によって実行時に負荷の高い圧縮が失敗する場合は、追加の高度な構成オプションを調整することがあります。

このセクションのガイドラインを使用すると、このようなアーキテクチャー上および構成上の選択を行う際に役立ちます。長期間のテストを実施し、圧縮テーブルを本番環境に移行する準備ができたら、これらの選択を現実の状況で行なった場合の効率性を検証する方法について、セクション15.9.1.4「実行時の InnoDB テーブル圧縮の監視」を参照してください。

圧縮を使用するタイミング

一般に、圧縮は、適当な数の文字列カラムが含まれ、データの書き込みよりも読み取りの頻度の方がはるかに高いテーブルで最適に動作します。特定の状況で圧縮の利点が得られるかどうかを予測するための保証された方法はないため、必ず、代表的な構成で実行する特定のワークロードおよびデータセットをテストしてください。圧縮するテーブルを決定する際は、次の要素を検討してください。

データの特性と圧縮

データファイルのサイズを削減する際に圧縮の効率性の決定要因となるものは、データ自体の特性です。圧縮は、データのブロックで繰り返されるバイト文字列を識別することで動作していることを思い出してください。完全にランダム化されたデータは、最悪のケースです。多くの場合、一般的なデータには繰り返し値が含まれているため、効率的に圧縮されます。 CHAR、VARCHAR、TEXT、または BLOB のいずれのカラムに定義されているのかに関係なく、多くの場合、文字列は効率的に圧縮されます。その一方で、一般に、ほとんどがバイナリデータ (整数または浮動小数) や以前に圧縮されたデータ (JPEG または PNG イメージなど) を含むテーブルは、大幅にまたはまったく効率的に圧縮されない可能性があります。

InnoDB テーブルごとに圧縮を有効にするかどうかを選択します。テーブルおよびそのすべてのインデックスでは、同じ (圧縮済み) ページサイズが使用されます。すべてのテーブルカラムのデータを含む主キー (クラスタ化) インデックスは、セカンダリインデックスよりも効率的に圧縮される可能性があります。長い行が存在する場合に圧縮を使用すると、DYNAMIC 行フォーマットで説明したように、長いカラム値が「オフページ」に格納される可能性があります。このようなオーバーフローページは、効率的に圧縮される可能性があります。これらの検討事項を考慮すると、多くのアプリケーションでは、一部のテーブルがその他よりも効率的に圧縮され、圧縮されたテーブルのサブセットを含むワークロードのみが最適に動作する場合もあります。

特定のテーブルを圧縮するかどうかを決定するには、実験を行います。非圧縮テーブルの .ibd ファイルのコピー上に、LZ77 圧縮 (gzip や WinZip など) が実装されたユーティリティーを使用すると、データを圧縮する際の効率性の概算見積もりを取得できます。 MySQL ではページサイズ (デフォルトは 16K バイト) に基づいたチャンク単位でデータが圧縮されるため、MySQL で圧縮されたテーブルからは、ファイルベースの圧縮ツールよりも低い圧縮率が得られると予測できます。ページ形式には、ユーザーデータに加えて、圧縮されていない内部システムデータもいくつか含まれます。ファイルベースの圧縮ユーティリティーでは、さらに大きなデータチャンクを調査できるため、MySQL の各ページで見つかるよりも多くの繰り返し文字列が巨大なファイルで見つかる可能性があります。

特定のテーブルの圧縮をテストする別の方法は、圧縮されていないテーブルの一部のデータを file-per-table テーブルスペース内の類似した圧縮テーブル (すべて同じインデックスを持つ) にコピーし、結果の .ibd ファイルのサイズを確認することです。例:

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

この実験では、次のような数値が生成されました。当然、テーブル構造やデータによって、数値が大幅に異なる可能性があります。

-rw-rw----  1 cirrus  staff  310378496 Jan  9 13:44 data/test/big_table.ibd
-rw-rw----  1 cirrus  staff  83886080 Jan  9 15:10 data/test/key_block_size_4.ibd

特定のワークロードで圧縮が効率的かどうかを確認するには:

単純なテストでは、その他の圧縮テーブルが含まれない MySQL インスタンスを使用して、INFORMATION_SCHEMA.INNODB_CMP テーブルに対してクエリーを実行します。
複数の圧縮テーブルが含まれるワークロードが関与するより詳細なテストでは、INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX テーブルに対してクエリーを実行します。 INNODB_CMP_PER_INDEX テーブルの統計は収集にコストがかかるため、そのテーブルをクエリーする前に構成オプション innodb_cmp_per_index_enabled を有効にする必要があり、そのようなテストを開発サーバーまたはクリティカルでないレプリカサーバーに制限できます。
テスト中の圧縮テーブルに対して、一般的な SQL ステートメントをいくつか実行します。
INFORMATION_SCHEMA.INNODB_CMP または INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX テーブルのクエリーを実行し、COMPRESS_OPS と COMPRESS_OPS_OK を比較することで、圧縮操作全体に対する正常な圧縮操作の比率を調査します。
圧縮操作が正常に完了した比率が高い場合は、そのテーブルが圧縮対象の候補である可能性が高くなります。
圧縮が失敗する比率が高い場合は、セクション15.9.1.6「OLTP ワークロードの圧縮」で説明したように、innodb_compression_level、innodb_compression_failure_threshold_pct、および innodb_compression_pad_pct_max オプションを調整すれば、さらに詳細なテストを試すことができます。

データベースの圧縮とアプリケーションの圧縮

アプリケーション内とテーブル内のどちらでデータを圧縮するかどうかを決定します。同じデータで両方のタイプの圧縮を使用しないでください。アプリケーション内でデータを圧縮し、その結果を圧縮テーブルに格納すると、追加の領域が節約される可能性は大幅に低くなり、二重圧縮によって単に CPU サイクルが無駄になるだけです。

データベース内での圧縮

これを有効にすると、MySQL テーブルの圧縮は自動的になり、すべてのカラムおよびインデックス値に適用されます。 LIKE などの演算子を含むカラムも引き続きテストでき、インデックス値が圧縮されている場合でも、ソート操作でインデックスを引き続き使用できます。多くの場合、インデックスがデータベースの合計サイズの相当な割合を占めるため、圧縮を使用すると、ストレージ、I/O、またはプロセッサ時間が大幅に節約される可能性があります。圧縮および圧縮解除の操作は、予期される負荷を処理できるようにサイズ変更された強力なシステムとなる可能性が高いデータベースサーバー上で発生します。

アプリケーション内での圧縮

テキストなどのデータをアプリケーション内で圧縮してから、データベースに挿入する場合は、一部のカラムは圧縮されるが、その他は圧縮されないことで効率的に圧縮されないデータで、オーバーヘッドが節約される可能性があります。このアプローチでは、圧縮および圧縮解除用の CPU サイクルがデータベースサーバー上ではなく、クライアントマシン上で使用されるため、多数のクライアントが含まれる分散アプリケーションや、予備の CPU サイクルを備えたクライアントマシンに適している場合があります。

ハイブリッドアプローチ

当然、これらのアプローチは組み合わせることができます。一部のアプリケーションでは、いくつかの圧縮テーブルといくつかの非圧縮テーブルを使用することが適切である場合があります。一部のデータを外部で圧縮して (それを非圧縮テーブルに格納して)、アプリケーション内のその他のテーブル (の一部) を MySQL で圧縮できるようにすることが最適な方法である場合もあります。通常どおり、適切な決定に達するには、事前の設計および現実のテストが重要となります。

ワークロードの特性と圧縮

圧縮するテーブル (およびページサイズ) を選択することに加えて、ワークロードはもう 1 つのパフォーマンスの主要な決定要因でもあります。アプリケーションが更新ではなく、読み取りで占有されている場合は、圧縮済みデータ用に MySQL で保持されるページごとの「変更ログ」用の空き領域がインデックスページによって使い果たされたあとに、再編成および再圧縮する必要のあるページが少なくなります。更新によって、インデックスなしのカラムまたはそれらが含まれている BLOB や、偶然に「オフページ」に格納される大きな文字列が主に変更される場合は、圧縮のオーバーヘッドが許容可能になる可能性があります。単調に増加する主キーを使用する INSERT がテーブルへの唯一の変更であり、セカンダリインデックスがほとんどない場合は、インデックスページを再編成および再圧縮する必要もほとんどありません。 MySQL では、非圧縮データを変更することで、「適切に」、圧縮済みページ上のデータに「削除マークを付け」てから削除できるため、テーブル上の DELETE 操作は比較的効率的に行われます。

環境によっては、データのロードに要する時間がリアルタイム検索と同じくらいに重要である場合があります。特にデータウェアハウス環境では、数多くのテーブルが読み取り専用または読み取りが大半になっている可能性があります。このような場合、結果として少数のディスク読み取りとストレージコストの節約が重要である場合を除いて、ロード時間が長くなるという点で圧縮の犠牲を払うことが許容できる場合と、許容できない場合があります。

本来は、データを圧縮および圧縮解除する際に CPU 時間を使用できるときに、圧縮が最適に動作します。そのため、ワークロードが CPU バウンドではなく、I/O バウンドである場合に、圧縮を使用することで全体的なパフォーマンスを改善できることがわかるでしょう。さまざまな圧縮構成でアプリケーションのパフォーマンスをテストする際は、計画した本番システム構成と同様のプラットフォーム上でテストしてください。

構成の特性と圧縮

データベースページのディスクからの読み取りとディスクへの書き込みは、システムパフォーマンスのもっとも低速な側面です。圧縮では、CPU 時間を使用してデータを圧縮および圧縮解除することで I/O の削減が試みられるため、プロセッササイクルと比べて、I/O が比較的少ないリソースであるときに、もっとも効率性が高くなります。

多くの場合、これは特に、高速のマルチコア CPU が搭載された複数ユーザー環境で動作しているときに当てはまります。圧縮テーブルのページがメモリー内にあるときは、MySQL では多くの場合、ページの非圧縮コピー用のバッファープール内で追加のメモリー (一般に 16K バイト) が使用されます。適応型 LRU アルゴリズムでは、I/O バウンドと CPU バウンドのどちらの方式でワークロードが動作しているのかに関係なく、考慮される圧縮済みページと非圧縮ページ間でメモリー使用のバランスを調整しようと試みられます。メモリーが非常に制約されている構成よりも、バッファープール専用のメモリーがより多く搭載された構成の方が、圧縮テーブルを使用するときに適切に動作する傾向があります。

圧縮済みページサイズの選択

圧縮済みページサイズの最適な設定は、テーブルおよびそのインデックスに含まれるデータの型および分布によって異なります。圧縮済みページのサイズは、常に最大のレコードサイズよりも大きくするようにしてください。そうでなければ、B ツリーページの圧縮で注記したように、操作に失敗する可能性があります。

圧縮済みページサイズの設定が大きすぎると、一部の領域が無駄になりますが、頻繁にページを圧縮する必要はなくなります。圧縮されたページサイズが小さすぎる場合、挿入または更新に時間のかかる再圧縮が必要になることがあり、B-tree ノードをより頻繁に分割する必要があるため、データファイルが大きくなり、インデックス付けの効率が低下する可能性があります。

一般に、圧縮済みページサイズは 8K バイトまたは 4K バイトに設定されます。 InnoDB テーブルの最大行サイズが約 8K とすれば、通常、KEY_BLOCK_SIZE=8 は安全な選択です。

PREV HOME UP NEXT