mysqlについて質問させてください。


バージョン:mysql5
データ型 :InnoDB

カラムを追加する前と後で、
InnoDBの空き容量を比較したのですが、
ほぼ変化がありませんでした。

追加したテーブルは800万レコード程度あります。
SQLは下記のような形です。

alter table hogehoge add col varchar(255)

InnoDBの空き領域に関しては、
「show table status」で調べました。

単純にカラム追加で新しく領域を確保して、
InnoDBの空き容量は減ると想定したのですが、
こちらの現象はどういった理由によるものでしょうか。

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2009/04/24 16:27:29
  • 終了:2009/05/01 16:30:02

回答(3件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332009/04/24 17:03:37

ポイント27pt

SHOW TABLE STATUS のどのデータを見ていますか?


もし Data_free だとすると、これは使用されていないエクステントのサイズを示しています。実際には、ページ単位の空き領域の合算値を示すこととなり、フラグメンテーションを起こしているかどうかの指標になります。

つまり、alter add したばかりであれば、エクステントは増えませんから、Data_free の値も変わりません。

id:the_yakisoba

Commentの項目の「InnoDB free」の値を見ています。

Data_freeではないです。

よろしくお願いします。

2009/04/24 22:13:59
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012009/04/25 01:59:09

ポイント27pt

13.5.13.4. 物理的なレコード構造

InnoDB は内部的に固定長フォーマットの CHAR(10) のような固定長文字カラムを格納します。

InnoDB は VARCHAR カラムから後続領域を切り捨てます。

何もデータが入っていないVARCHAR形のフィールドはデータ量ゼロ。


ただし、データ量はゼロでも管理するために

SQL NULL 値はレコード ディレクトリ内で1か2バイトを蓄えておきます。

それ以外に、SQL NULL 値は可変長カラム内に格納されるとレコードのデータ部分にゼロ バイトを蓄えます。

それは固定長カラム内でレコードのデータ部分内にカラムの固定長を蓄えます。

NULL 値に固定領域を蓄える目的は、そうする事でインデックス ページの崩壊を起こさずに

NULL からのカラムを非 NULL 値に、更新する事ができるという事です。

レコードには、そのレコードの各フィールドへのポインタも含まれます。

レコード内のフィールド長の合計が128バイト未満の場合はポインタが1バイト、

128バイト以上の場合はポインタが2バイトになります。

これらのポインタの配列はレコード ディレクトリと呼ばれます。

これらのポインタが指し示すエリアはレコードのデータ部分と呼ばれます。

などがありますので

800万レコードもあれば数十から数百MBの増加があるはずです。

id:pahoo No.3

pahoo回答回数5960ベストアンサー獲得回数6332009/04/25 13:14:45

ポイント26pt

InnoDB の場合、DBの実体は、ある一定の初期サイズのファイルになっており、データ容量がこれを超えると自動的に一定の容量だけファイルサイズを拡張していく(エクステント)仕組みになっています。詳しくは「InnoDB 起動オプション」(MySQL公式マニュアル)を参考にしてください。

Comment に表示される "InnoDB free" は、確保されたファイルサイズの残容量を示します。


varchar カラムは可変長であるため、定義時に確保されるのは、データ長を格納する領域(255以下なら1バイト、256以上なら2バイト)だけです。

もし800万レコードの varchar(255) を add したとしても、それによって消費される容量は、8,000,000×1=7.6Mバイト程度に過ぎません。

この程度の増分では、過去にパージされなかったエクステントに吸収されてしまい、free area として表れなかったのではないかと推測します。

  • id:kn1967
    >定義時に確保されるのは、データ長を格納する領域(255以下なら1バイト、256以上なら2バイト)だけです。

    "レコードディレクトリ"の増加分をどうして無視されるのでしょう?

    (1)フィールド追加前からレコード長が128バイト以上の場合
    (データ部1バイト + レコードディレクトリ2バイト)× 800万レコード

    (2)フィールド追加前も後もレコード長が128バイト未満の場合
    (データ部1バイト + レコードディレクトリ1バイト)× 800万レコード

    (3)フィールド追加でレコード長が128バイト以上になった場合
    (データ部1バイト + レコードディレクトリ2バイト + 既存フィールド数分のバイト数)× 800万レコード

    以上3パターンを考慮して数十MBから数百MBという幅を持たせて回答したのですが・・・。


    >Data_free だとすると、これは使用されていないエクステントのサイズ

    Data_freeはテーブルスペースとして確保してあるなかでデータ削除によって空いた空間の事です。
    テーブルスペースの中には拡張領域(エクステンド)も当然ながら含まれますが
    説明不足のため誤解を招くような文章になってますね。


    >データ容量がこれを超えると自動的に一定の容量だけファイルサイズを拡張していく

    確かにそのとおりで、この際に初めてエクステンドという言葉も出てきます。
    800万レコードという状況からエクステンドによる自動拡張も当然ながら考えられる事ですし
    再利用に関しても考えられる事なので補足として記述しておくほうが良いことでしょう。
    補足としては良いのですが、本回答の部分を間違っていては・・・。


    はてなの回答に対する信頼確保のためにも、
    多くの回答を行っている者が間違いを放置しておく訳にもいきますまい。
    私の回答/コメントに間違いがあれば
    回答者ご自身、そして閲覧していただいている皆様方から
    コメントいただければと願います。
    (有意義な回答と理解した場合、適宜ポイント送信させていただきます。)

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません