MySQL 圧縮の使用シナリオとソリューション

導入

圧縮トランスポート プロトコル、圧縮列ソリューション、圧縮テーブル ソリューションなど、MySQL 圧縮の使用例とソリューションについて説明します。

MySQL の圧縮に関しては、次のような圧縮関連のシナリオが考えられます。

1. クライアントとサーバー間で送信されるデータの量が大きすぎるため、帯域幅を節約するために圧縮する必要がある

2. MySQLの特定の列のデータ量が多く、特定の列のデータのみが圧縮されている

3. 1 つまたは複数の MySQL テーブルにデータが多すぎます。ディスク領域の使用量を減らすには、テーブル データを圧縮する必要があります。

これらの問題には、MySQL 側に適切な解決策があります。最初の問題については、MySQL 圧縮プロトコルを使用して解決できます。2 番目の問題については、MySQL の圧縮および解凍機能を使用して完璧に解決できます。最も複雑な 3 番目の問題については、エンジン レベルで解決できます。現在、myisam、innodb、tokudb、MyRocks などのエンジンはすべてテーブル圧縮をサポートしています。この記事では、MySQL の圧縮メカニズムに関連する問題について詳しく説明します。主な内容は次のとおりです。

1. MySQL 圧縮プロトコルの概要

1. 適用可能なシナリオ

MySQL 圧縮プロトコルは、MySQL サーバーとクライアント間で送信されるデータの量が多い場合や、利用可能な帯域幅が低い場合に適しています。一般的なシナリオは次のとおりです。

a. 大量のデータを照会する場合（たとえば、データをエクスポートする場合）の帯域幅が不十分です。

b. コピー時に、binlog の量が大きすぎます。slave_compressed_protocol パラメータを有効にして、ログ圧縮レプリケーションを実行します。

2. 圧縮プロトコルの概要

圧縮プロトコルは、MySQL 通信プロトコルの一部です。データ転送に圧縮プロトコルを有効にするには、MySQL サーバーとクライアントの両方が zlib アルゴリズムをサポートしている必要があります。圧縮プロトコルを有効にすると、CPU 負荷がわずかに増加します。圧縮プロトコルを有効にする クライアント圧縮機能を有効にするには、-C パラメータまたは --compress=true パラメータを使用します。 -C または Compress=true オプションが有効になっている場合、サーバー セグメントに接続するときにサーバー機能フラグ 0x0020 (CLIENT_COMPRESS) が送信され、サーバーとのネゴシエーション後 (3 回のハンドシェイク後)、圧縮プロトコルがサポートされます。圧縮により、データ パケットの形式が変わります。具体的な変更点は次のとおりです。

非圧縮パケット形式:

圧縮されたデータ パケットの形式は次のとおりです。

圧縮データグラム形式が圧縮形式と非圧縮形式に分かれていることに気付いたかもしれません。これは、CPU オーバーヘッドを削減するために MySQL によって行われた最適化です。コンテンツが 50 バイト未満の場合は圧縮されず、50 バイトを超える場合は圧縮が有効になります。具体的なルールは以下のとおりです。

3 番目のフィールドの値が 0x00 に等しい場合、現在のパケットは圧縮されていないことを意味し、n *バイトの内容は 1 *バイト、n *バイト、つまり要求タイプと要求内容になります。

3 番目のフィールドの値が 0x00 より大きい場合、現在のパケットは zlib を使用して圧縮されていることを意味します。したがって、使用時に n *バイトを解凍する必要があります。解凍されたコンテンツは 1 *バイト、n *バイト、つまり、リクエスト タイプとリクエスト コンテンツです。

3. 解答練習

クライアントが接続するときに -C または --compress=true パラメータを追加します。同期に圧縮プロトコルのサポートを追加する場合は、slave_compressed_protocol=1 を設定する必要があります。以下は、圧縮プロトコルを使用して MySQL サーバーに接続する例です。

MySQL -h hostip -uroot -p パスワード --compress

MySQLdump -h hostip -uroot -p password -default-character-set=utf8 --compress --single-transaction dbname tablename > tablename.sql

マスター-スレーブ レプリケーションで圧縮転送を有効にする必要がある場合は、スレーブで slave_compressed_protocol=1 パラメータをオンにするだけです。

4. 圧縮効果

MySQLdump で --compress オプションを使用するか、マスタースレーブレプリケーションで slave_compressed_protocol パラメータを使用すると、圧縮転送の効果を確認できます。効果は簡単に確認できるので、ここではスクリーンショットは示しません。

MySQL 列圧縮ソリューション

現在、MySQL 列を圧縮する直接的なソリューションはありません。Tencent の TMySQL は列を直接圧縮できます。ここでは主に、MySQL がビジネス レベルで提供する圧縮および解凍機能を使用して列を圧縮および解凍するという、遠回りの節約方法を紹介します。つまり、列を圧縮したい場合は、書き込み時に COMPRESS 関数を呼び出してその列の内容を圧縮し、対応する列に格納する必要があります。読み取り時には、UNCOMPRESSED 関数を使用して圧縮されたコンテンツを解凍します。

1. 適用可能なシナリオ

MySQL の 1 つまたは複数の列のデータ量は特に大きく、通常は varchar、text、char などのデータ型です。

2. 圧縮機能の紹介

MySQL 圧縮関数 COMPRESS は文字列を圧縮し、バイナリ文字列を返します。この関数を使用するには、MySQL サーバーが圧縮をサポートしている必要があります。サポートしていない場合は NULL が返されます。圧縮されたフィールドは、varbinary または blob フィールド タイプを使用して保存するのが最適です。圧縮されたデータを解凍するには、UNCOMPRESSED 関数を使用します。このアプローチでは、ビジネス側で若干の変更が必要になることに注意してください。圧縮されたコンテンツは次のように保存されます。

a. 空の文字列は空の文字列として保存されます

b. 空でない文字列は次のように保存されます。最初の 4 バイトには圧縮されていない文字列が保存され、その後に圧縮された文字列が続きます。

3. 解答練習

フィールド圧縮スキームに関係するいくつかの関連機能は次のとおりです。

圧縮機能

COMPRESS()

減圧機能

UNCOMPRESS()

文字列の長さ関数

LENGTH()

非圧縮文字列の長さ関数

UNCOMPRESSED_LENGTH()

実践的な手順:

a. テストテーブルを作成する

存在しない場合はテーブルを作成 `test`.`test_compress` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`content` BLOB NOT NULL COMMENT 'content column'、

主キー (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='テストテーブルを圧縮';

b. 圧縮データをネットリストに挿入する

`test`.`test_compress`(content) values(COMPRESS(REPEAT('a',1000))) に挿入します。

c. 圧縮されたデータを読み取る

`test`.`test_compress` から UNCOMPRESS(content) を選択します。

d. 対応する長さと内容を照会する

4. 圧縮効果

上記のスクリーンショットから、圧縮効果が比較的良好であることがわかります。text、char、varchr、blob などの場合、繰り返しデータが多いほど、圧縮効果は高くなります。

3. InnoDBテーブル圧縮ソリューション

1. 適用可能なシナリオ

圧縮テーブルは、一般的に、データ量が大きすぎる、ディスク容量が不足している、負荷が主に IO に反映される、サーバーの CPU に余裕があるなどのシナリオで使用されます。

2. テーブル圧縮の概要 a. なぜ圧縮が必要なのか?

現在、Myisam、InnoDB、TokuDB、MyRocks など、多くのテーブルが圧縮をサポートしています。 InnoDB の使用には変更は必要なく、オンライン環境に対して完全に透過的であり、非常に成熟した圧縮ソリューションを備えているため、ここでは InnoDB についてのみ詳しく説明します。 TokuDB と MyRocks の圧縮スキームについては、「MySQL 圧縮スキーム (パート 2)」で説明します。

SSD が普及していなかった頃、データベースはほぼすべて IO 負荷でした。CPU に余裕があった頃は、ディスク IO のボトルネックが顕著でした。大量のデータ、特にログ データや監視データを保存すると、ディスク領域が急速に増加します。多くの企業では、ハードディスクの容量不足も明らかになります。圧縮を使用してディスク領域の使用量を削減し、CPU リソースを少し犠牲にして IO と帯域幅を最適化するという、より優れた方法が生まれました。特に、読書量を増やしたり減らしたりするビジネスに最適です。

SSD の登場により、データベースの IO 負荷は軽減されましたが、ディスク容量の問題は依然として十分に解決されていませんでした。そのため、圧縮テーブルは今でも広く使用されています。これが、多くのエンジンが圧縮をサポートする理由です。 InnoDB は MySQL 5.5 以降で圧縮をサポートしていますが、圧縮率は比較的低く、通常は 50% 程度です。 tokuDB の圧縮率は約 80% に達し、MyRocks の圧縮率は約 70% に達します。

注: 圧縮率は保存するデータの構成に大きく関係します。すべてのデータが上記の圧縮率を達成できるわけではありません。データのほとんどが文字列で、重複するデータが多い場合、圧縮率は非常に高くなります。

b. InnoDB 圧縮の概要

Innodb 圧縮を使用するための前提条件は、innodb_file_per_table パラメータが有効になっており、innodb_file_format パラメータが Barracuda に設定されていることです。

ROW_FORMAT=COMPRESSED を使用してテーブルを作成または変更し、InnoDB の圧縮を有効にすることができます。KEY_BLOCK_SIZE が指定されていない場合、デフォルト値は innodb_page_size の半分になります。KEY_BLOCK_SIZE=n を指定して InnoDB の圧縮を有効にすることもできます。n は 1、2、4、8、または 16 (K 単位) です。 n の値が小さいほど、圧縮率が高くなり、消費される CPU リソースが多くなります。 32K または 64K ページでは圧縮はサポートされないことに注意してください。圧縮を有効にすると、インデックス データも圧縮されます。

innodb_compression_level を調整して圧縮レベルを 1 から 9 まで設定することもできます (デフォルトは 6)。レベルが低いほど圧縮率は高くなりますが、より多くの CPU リソースが必要になります。

c. 圧縮アルゴリズム

InnoDB 圧縮は有名な zlib ライブラリに依存しており、成熟しておりデータ サイズと CPU 使用率を削減する効率的な L777 圧縮アルゴリズムを採用しています。同時に、このアルゴリズムはロスレスであるため、圧縮されたファイルから元の非圧縮データをいつでも再構築できます。LZ777 の実装原理は、繰り返しデータのシリアル番号を見つけて圧縮することであるため、データ パターンによって圧縮効率が決まります。一般的に、ユーザー データは 50% 以上圧縮できます。

d. buffer_pool 内の圧縮テーブルを処理する方法

buffer_pool バッファプールには、KEY_BLOCK_SIZE サイズのページに圧縮データが保存されます。圧縮データを抽出したり、圧縮データに対応する列を更新したりする場合は、非圧縮ページを作成してデータを解凍します。データの更新が完了すると、圧縮ページのデータが圧縮ページに書き換えられます。メモリが不足している場合、MySQL は対応する非圧縮ページを削除します。したがって、圧縮を有効にすると、buffer_pool には圧縮されたページと圧縮されていないページが含まれるか、または圧縮されたページのみが含まれる可能性があります。ただし、圧縮されたページと圧縮されていないページの両方を保存できるように、buffer_pool バッファ プールを拡張する必要がある場合があります。

MySQL は、最近最も使用されていない (LRU) アルゴリズムを使用して、どのページをメモリに保持し、どのページを削除するかを決定するため、ホット データはより頻繁にメモリに保持されます。圧縮されたテーブルにアクセスすると、MySQL は適応型 LRU アルゴリズムを使用して、メモリ内の圧縮されたページと圧縮されていないページのバランスを維持します。システム IO 負荷が高い場合、このアルゴリズムは、圧縮されていないページを削除して、より多くの圧縮ページ用のスペースを確保する傾向があります。システムの CPU 負荷が高い場合、MySQL は圧縮されたページと圧縮されていないページの両方を削除する傾向があります。このとき、ホット データを保持するためにより多くのメモリが使用されるため、解凍操作が削減されます。

e. KEY_BLOCK_SIZEが適切かどうかを評価する方法

圧縮されたテーブルがパフォーマンスに与える影響をよりよく理解するために、メモリ使用量や圧縮率などの指標を評価するために使用できる対応するテーブルが情報スキーマ ライブラリにあります。 INNODB_CMP は、特定のタイプの KEY_BLOCK_SIZE 圧縮テーブルの全体的なステータスに関する情報を収集し、すべての KEY_BLOCK_SIZE 圧縮テーブルの統計を要約します。 INNODB_CMP_PER_INDEX テーブルは、各テーブルとインデックスの圧縮情報を収集します。この情報は、特定の時点でのテーブルの圧縮効率を評価したり、パフォーマンスの問題を診断したりするのに役立ちます。 INNODB_CMP_PER_INDEX テーブルの収集はシステム パフォーマンスに影響します。innodb_cmp_per_index_enabled オプションを使用して記録する必要があります。実稼働環境では有効にしないことをお勧めします。

INNODB_CMP テーブルの圧縮失敗を観察できます。失敗が多い場合は、KEY_BLOCK_SIZE を増やす必要があります。一般的に、KEY_BLOCK_SIZE は 8 に設定することをお勧めします。

3. 解答練習

a. innodb_file_per_tableとinnodb_file_formatパラメータを設定する

SET GLOBAL innodb_file_per_table=1;SET GLOBAL innodb_file_format=Barracuda;

b. 対応する圧縮テーブルを作成する

テーブルがすでに存在する場合は、alter を使用して変更します。SQL は次のとおりです。

ALTER TABLE のcompress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

4. 圧縮効果

圧縮効果は、オンライン監視テーブルを圧縮ファイル サイズに変更することで示されます。圧縮前と圧縮後の比較は次のとおりです。