MySQL表の空間破片の概念と関連問題の解決
4477 ワード
背景
MySQLを頻繁に使用すると、MySQLデータファイルのディスク空間は一般的に増加し続けており、データを削除したり、データを挿入したりすると、ディスク空間は変化しない場合があります。この妙な現象を引き起こしたのは、MySQLの表空間の破片である。
表空間の破片は何ですか？
表空間の破片とは、表空間に破片があることを意味し、イメージの点でいえば、A 4紙のように「表空間のかけら」というのは、このA 4紙をちぎって新たに綴るように、それぞれの破片の間に隙間があります。再結合された破片は、実際にはA 4紙より一回り大きくなります。これは、表空間が引き起こしやすい問題です。スペースの浪費です。
背景に記述された現象については、一枚の図で説明することができる。
図中の数字は実際のデータ行を表し、角の長方形は表の空間を表します。左から右へ、最初の操作はデータを削除します。MySQLは設計上自発的に空間を解放しないので、テーブルのデータ行が削除された時、データは削除されましたが、実際にはこの部分の空間は解放されておらず、依然としてTable Aに占有されます。しかし、MySQLのディスク空間は低下していません。
PS：このような解放されない空間の設計は大半が不活性性削除に関連しています。初期の設計データベースでは、IO装置を使用していますが、SSDに比べて読み書きの性能がかなり悪いので、削除操作はディスク上のデータ削除を直接触発することはほとんどありません。
データが削除された後、元々連続していた空間の中に二つの空白が生じている領域を見ることができます。このような一般的なものは表空間空洞といい、空洞が多すぎることを表空間の破片化といいます。この部分の空間は解放されませんが、繰り返し利用可能としてマークされます。一番右の表空間図（三つ目の角の長方形）を参考にして、データを新たに挿入すると、新しいデータが表空間空洞に書き換えられます。これはまた、大規模なデータ削除テーブルにデータを書き込むと、テーブル空間が著しく成長しないか、または伸びないかもしれません。
実際に表空間の空洞を作る操作はdeleteだけではなく、udateもこの問題を引き起こしています。例えば、varrharのような長くなる文字列でデータを修正したり、短くすると非常に小さい空洞ができます。長くすると、スペースが足りなくなり、データ行の一部のデータを他のところに移すことができます。
表空間の破片はどうやって調べますか？
MySQLのシステムテーブルは表空間の使用状況を記録しています。次のように調べられます。
表空間のかけらの問題をどう解決しますか？
現在、表空間を回収できる方法は一つしかないです。つまり、表を再建する方法はoptimize、alter tableなどを含みますが、これに限らないです。alter tableの一部の操作はrebuild表でしかできないので、大時計をいくつかメンテナンス操作した後、ディスクの空間使用率が下がっているのが見えます。これはテーブル空間の破片が出てくる部分の空間を回収しました。
一般的な経験から見ると、テーブル空間の破片の回収操作は頻繁に行われることを推奨しません。毎月一回で十分です。このテーブルはサーバの資源に影響が大きいので、この表の書き込み操作に影響します。デブリ率free_20%以下の場合も特に気にする必要はありません。ディスク空間が非常に緊張していない限り、ログはほぼクリアされます。
回収スペースについての問題
いくつかのログテーブル、または地域的な特徴があるテーブルに対して、MySQLのパーティションテーブルを使って管理することを提案しています。データを整理する必要がある場合、パーティションtruncateで整理してもいいです。ディスク空間も直接に解放できます。
以上がMySQLテーブルの空間デブリの概念と関連問題解決の詳細です。MySQLテーブルの空間デブリに関する詳細については、他の関連記事に注目してください。
MySQLを頻繁に使用すると、MySQLデータファイルのディスク空間は一般的に増加し続けており、データを削除したり、データを挿入したりすると、ディスク空間は変化しない場合があります。この妙な現象を引き起こしたのは、MySQLの表空間の破片である。
表空間の破片は何ですか？
表空間の破片とは、表空間に破片があることを意味し、イメージの点でいえば、A 4紙のように「表空間のかけら」というのは、このA 4紙をちぎって新たに綴るように、それぞれの破片の間に隙間があります。再結合された破片は、実際にはA 4紙より一回り大きくなります。これは、表空間が引き起こしやすい問題です。スペースの浪費です。
背景に記述された現象については、一枚の図で説明することができる。
図中の数字は実際のデータ行を表し、角の長方形は表の空間を表します。左から右へ、最初の操作はデータを削除します。MySQLは設計上自発的に空間を解放しないので、テーブルのデータ行が削除された時、データは削除されましたが、実際にはこの部分の空間は解放されておらず、依然としてTable Aに占有されます。しかし、MySQLのディスク空間は低下していません。
PS：このような解放されない空間の設計は大半が不活性性削除に関連しています。初期の設計データベースでは、IO装置を使用していますが、SSDに比べて読み書きの性能がかなり悪いので、削除操作はディスク上のデータ削除を直接触発することはほとんどありません。
データが削除された後、元々連続していた空間の中に二つの空白が生じている領域を見ることができます。このような一般的なものは表空間空洞といい、空洞が多すぎることを表空間の破片化といいます。この部分の空間は解放されませんが、繰り返し利用可能としてマークされます。一番右の表空間図（三つ目の角の長方形）を参考にして、データを新たに挿入すると、新しいデータが表空間空洞に書き換えられます。これはまた、大規模なデータ削除テーブルにデータを書き込むと、テーブル空間が著しく成長しないか、または伸びないかもしれません。
実際に表空間の空洞を作る操作はdeleteだけではなく、udateもこの問題を引き起こしています。例えば、varrharのような長くなる文字列でデータを修正したり、短くすると非常に小さい空洞ができます。長くすると、スペースが足りなくなり、データ行の一部のデータを他のところに移すことができます。
表空間の破片はどうやって調べますか？
MySQLのシステムテーブルは表空間の使用状況を記録しています。次のように調べられます。
SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
ENGINE as 'engine'
FROM information_schema.TABLES
WHERE table_schema = 'tablename'
ORDER by data_free desc;
mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
-> table_rows AS 'Number of Rows',
-> CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
-> CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
-> CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
-> CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
-> ENGINE as 'engine'
-> FROM information_schema.TABLES
-> WHERE table_schema = 'sbtest'
-> ORDER by data_free desc;
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| table_name | Number of Rows | data_size | index_size | data_free | data_free_pct | engine |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| sbtest.sbtest5 | 0 | 0.02 M | 0.00 M | 44.00 M | 2816.00 % | InnoDB |
| sbtest.sbtest4 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest3 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest2 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest1 | 987400 | 199.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
5 rows in set (0.00 sec)表空間のかけらの問題をどう解決しますか？
現在、表空間を回収できる方法は一つしかないです。つまり、表を再建する方法はoptimize、alter tableなどを含みますが、これに限らないです。alter tableの一部の操作はrebuild表でしかできないので、大時計をいくつかメンテナンス操作した後、ディスクの空間使用率が下がっているのが見えます。これはテーブル空間の破片が出てくる部分の空間を回収しました。
一般的な経験から見ると、テーブル空間の破片の回収操作は頻繁に行われることを推奨しません。毎月一回で十分です。このテーブルはサーバの資源に影響が大きいので、この表の書き込み操作に影響します。デブリ率free_20%以下の場合も特に気にする必要はありません。ディスク空間が非常に緊張していない限り、ログはほぼクリアされます。
回収スペースについての問題
いくつかのログテーブル、または地域的な特徴があるテーブルに対して、MySQLのパーティションテーブルを使って管理することを提案しています。データを整理する必要がある場合、パーティションtruncateで整理してもいいです。ディスク空間も直接に解放できます。
以上がMySQLテーブルの空間デブリの概念と関連問題解決の詳細です。MySQLテーブルの空間デブリに関する詳細については、他の関連記事に注目してください。