普段はあまり気にすることはないのですが、運用してから数ヶ月、もしくは数年たった時に「あれ？テーブルスペース内にどれくらいのデータは入っているのだろう？」とか思ったことはありませんか？たとえばこの心配って、MySQLの設定でテーブルスペースを固定サイズに設定した場合なんかに戸惑うのではないかと思います。そうです、テーブルスペースの利用率を算出するコマンドは、MySQLにはないのです (T_T)

たぶんなのですが、MySQL InnoDBのテーブルスペースのデフォルトはオートエクステントです。設定を確認すると、こんな感じになっているのではないかと思います。

・オートエクステントの設定 [SQL] show global variables like '%innodb_data_file_path%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec) [/SQL] Valueの値に、10M:autoextendと記載されております。 > テーブルスペースのファイル名は：ibdata1 > 初回は、10M > 必要に応じて、自動的にサイズは増加する

・テーブルスペース固定値(fixed）の設定 [SQL] mysql> show global variables like '%innodb_data_file_path%'; +-----------------------+-------------+ | Variable_name | Value | +-----------------------+-------------+ | innodb_data_file_path | ibdata1:50G | +-----------------------+-------------+ 1 row in set (0.00 sec) [/SQL] Valueの値に、10M:autoextendと記載されております。 > テーブルスペースのファイル名は：ibdata1 > テーブルスペースのサイズは、50GB > 自動拡張はしない上記の設定の場合は、データが1MBであろうと、100MBであろうと、50GBがあらかじめ割り当てられております。

ここでは、後者の、固定値でテーブルスペースを作成した場合の、実データの利用率はどれくらいなの？ってのを中心に説明したいと思います。

■フリースペースの取得 フリースペースは、INFORMATION_SCHEMA.TABLESのDATA_FREE列に格納されております。MySQLのテーブルスペースは基本的には、共有テーブルスペース（テーブル毎にデータ領域を持たせることができるinnodb_file_per_tableオプションがありますが）なので、結果的には数あるテーブルの中の、DATA_FREE列を一行もってくるだけでいいです。以下のSQLで『DATA_FREEの大きさ（ここ大事）』を求めることができます。

[SQL] SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='INNODB' AND CREATE_OPTIONS != 'partitioned' GROUP BY DATA_FREE; [/SQL]

尚、出力された結果は、DATA_FREE つまり空いている容量なので、全体のテーブルスペース容量がいくつなのかわからない・・・。なので、データが入っている割合が分からない・・・。よって、OS上で定義しているテーブルスペースの容量を確認します。以下のコマンドで調べることができます。

ふむふむ、ibdata1は50GBの領域を割り振っているのねって感じですね。実際にOSのコマンド上でも確認してみましょう。

[SHELL] [root@svr1 mysql]# pwd /var/lib/mysql/ /var/lib/mysql [root@dsvr1 mysql]# du -m ibdata1 | cut -f1 51251 [/SHELL]

実際は、バイトで算出したいので、こんなコマンドでやってます。

[SHELL] [root@srv1 mysql]# du -b -c ibdata* | cut -f1 | tail -n 1 53687091200 [/SHELL]

では、上記のものをいろいろい合体させて、利用率を算出してみたいと思います。それが以下のコマンド・・・

[SHELL] [root@svr1 mysql]# total_size=du -b -c ibdata* | cut -f1 | tail -n 1; echo "SELECT X.TOTAL_SIZE , X.DATA_FREE, X.TOTAL_SIZE - X.DATA_FREE AS USED_SIZE, (X.TOTAL_SIZE - X.DATA_FREE) * 100 / X.TOTAL_SIZE AS PER FROM (SELECT ${total_size} as TOTAL_SIZE,DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='INNODB' AND CREATE_OPTIONS != 'partitioned' GROUP BY DATA_FREE ) AS X" | mysql -uroot -pmypassword [/SHELL]

結果は以下のとおりとなりました。

[SHELL] TOTAL_SIZE DATA_FREE USED_SIZE PER 53687091200 51028951040 2658140160 4.9512 [/SHELL]

・TOTAL_SIZE ：テーブルスペース全体の大きさ（Byte）・DATA_FREE ：テーブルスペースの空き容量（Byte）・USED_SIZE ：テーブルスペースの使用量（Byte）・PER ：テーブルスペースの利用率

という結果になりました。上記の結果ですと、50GB中、4.9%はデータ領域として使われていますねーといった感じです。ちょっと見やすく書き直したものが以下のとおりです。基本、上のものと同じです。

[SHELL] ------------ per.sh ---

!/bin/bash

cd /var/lib/mysql

_total_size=du -b -c ibdata* | cut -f1 | tail -n 1

mysql -u root -pmypassword << EOD SELECT X.TOTAL_SIZE , X.DATA_FREE, X.TOTAL_SIZE - X.DATA_FREE AS USED_SIZE, (X.TOTAL_SIZE - X.DATA_FREE) * 100 / X.TOTAL_SIZE AS PER FROM ( SELECT ${_total_size} as TOTAL_SIZE, DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='INNODB' AND CREATE_OPTIONS != 'partitioned' GROUP BY DATA_FREE ) AS X;

EOD

exit 0

./per.sh

TOTAL_SIZE DATA_FREE USED_SIZE PER 53687091200 51028951040 2658140160 4.9512

[/SHELL]

これで無事解決！！！・・と言いたいところですが。いままで説明したものは、あくまでも、「固定で割り割り当てた」テーブルスペースでの利用率のみに活用できるものであって・・・

たとえば

▼A.作成したテーブルがオートエクステントだった場合や、

[SQL] mysql> show global variables like '%innodb_data_file_path%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | +-----------------------+------------------------+ [/SQL] ▼B. innodb_file_per_tableが指定

[SQL] mysql> show variables like '%innodb_file_per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ [/SQL]

されていた場合などは、

SQLから何からいろいろと計算式が変わってきます。

- Aの場合の変更点 そもそも、テーブルスペースの上限はなくなってしまうので、割合を出すことはできません、この場合のトータルバイトは、ざっくり計算になってしまいますが、OS上のファイルシステムパーティションが、全体の容量としてよいかな？と思います。

・ディスク（パーティション）の総容量を求める [SHELL]

df / | awk '{ print $2 }' | tail -n -1 ;

[/SHELL]

・mysqlのデータ領域の容量を求める [SHELL]

du -b /var | tail -n 1 | cut -f1

[/SHELL]

- Bの場合の変更点 初めのほうで、チョロっと触れましたが、innodb_file_per_tableが有効になると、テーブル毎にテーブルスペースを生成することが可能になります。

[SQL] SELECT SUM(DATA_FREE) as SUM_DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${_SCHEMAS}' and ENGINE='innodb' GROUP BY TABLE_SCHEMA; [/SQL]

結局、ディスクの利用量しか見てない感じになってしまいましたが、まあこんな感じです。・・で

どのテーブルスペース形式でも、なんとなく、利用率が分かるシェルスクリプトを以下に掲載します。ただし、自分の環境に都合の良いように作っているいるので環境に合わせて修正する必要があると思いますが、ご参考までにどうぞ。

[SHELL]

cat check_tablespace.sh

!/bin/sh

MySQLのテーブルスペース監視に利用する

---------------------------------------------------------------------------------------------

Nagiosでの、テーブルスペース監視でも使えるんじゃないかと思い作ってみて、テーブルスペース利用率算出shell

---------------------------------------------------------------------------------------------

スキーマ名

_SCHEMAS=$1

DATADIR=/var/lib/mysql sqlfile=/tmp/tablespace.sql _logfile=/tmp/tablespaces.log

result code-----------------------------------

OK=0 WARNING=1 CRITICAL=2 UNKNOWN=3

----------------------------------------------

cd ${_DATADIR}

DBUSER=mysqluser DBPASSWD=mypassword

テーブルスペースのモードを判別する

mode1=$(echo "select VARIABLE_VALUE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_FILE_PER_TABLE';" | mysql -N -u ${DBUSER} -p${_DBPASSWD})

エラー判定

if [ $? -ne 0 ]; then echo "error code=$?" exit $UNKNOWN fi

mode2=$(echo "select count(*) as cnt from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_VALUE like '%autoextend%';" | mysql -N -u ${DBUSER} -p${_DBPASSWD})

エラー判定

if [ $? -ne 0 ]; then echo "error code=$?" exit $UNKNOWN fi

if [ "$mode2" = "0" ]; then TABLESPACE="tablespace:fixed" else _TABLESPACE="tablespace:autoextend" fi

if [ "$mode1" = "OFF" ]; then cat << EOF > ${sqlfile}

SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${_SCHEMAS}' and ENGINE='innodb' limit 1;

EOF else

cat << EOF > ${_sqlfile}

SELECT SUM(DATA_FREE) as SUM_DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${_SCHEMAS}' and ENGINE='innodb' GROUP BY TABLE_SCHEMA;

EOF

if [ "$mode2" = "0" ]; then datasize=du -b ibdata1 | cut -f1 else datasize=df /var/ | awk '{ print $2 }' | tail -n -1 datasize=$*1 fi

テーブルスペースの空き容量を出力

datafree=$(cat ${sqlfile} | mysql -N -u ${DBUSER} -p${DBPASSWD})

エラー判定

if [ $? -ne 0 ]; then echo "error code=$?" exit $UNKNOWN fi

if [ "$mode2" = "0" ]; then usesize=$*2 else _usesize=du -b /var | tail -n 1 | cut -f1 fi

テーブルスペース利用率の算出

per=$*3 per2=echo "scale=2; ${_usesize}*100/${_datasize}" | bc

_per2=printf "%.2f" ${_per2}

echo "${TABLESPACE} innodb_file_per_table : ${mode1} total_data_size : ${datasize} usesize : ${usesize} used_per : ${_per2}%"

echo "${datasize},${usesize},${per2}%" >> ${logfile}

echo ${_per2}

if [ "$_per" -ge 90 ] then exit $CRITICAL fi

if [ "$_per" -ge 80 ] then exit $WARNING else exit $OK fi

exit $UNKNOWN [/SHELL]

てな感じで長文でした

*1:_datasize * 1024

*2:datasize - datafree

*3:usesize * 100 / datasize

Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL　Innodb テーブルスペース利用率をさくっと算出する

!/bin/bash

exit 0

./per.sh

df / | awk '{ print $2 }' | tail -n -1 ;

du -b /var | tail -n 1 | cut -f1

cat check_tablespace.sh

!/bin/sh

MySQLのテーブルスペース監視に利用する

---------------------------------------------------------------------------------------------

Nagiosでの、テーブルスペース監視でも使えるんじゃないかと思い作ってみて、テーブルスペース利用率算出shell

---------------------------------------------------------------------------------------------

スキーマ名

result code-----------------------------------

----------------------------------------------

テーブルスペースのモードを判別する

エラー判定

エラー判定

テーブルスペースの空き容量を出力

エラー判定

テーブルスペース利用率の算出

echo "${TABLESPACE} innodb_file_per_table : ${mode1} total_data_size : ${datasize} usesize : ${usesize} used_per : ${_per2}%"