引数渡しとログ出力関数化

今回も前回に引き続き、引数渡しとログ出力関数化について。

いつも応援ありがとう！


【参考】

達人プログラマー

関数化直後のコードはこちら。

まずは、シェル関数にパラメータを渡す方法についてテストしてみる。

関数に渡す引数は、関数呼び出しの後に並べて、関数内では$1や$2で参照できる。

このノリは、シェルスクリプトに引数を渡すのと変わらない。

試しに、output_log()のログファイル名を引数にしてみよう。

function main()
{
local result=0
local cannot_sendmail=0
local fail_backup=0

log_host
purge
backup
result=$?
if check_sendmail
then
output_mail
else
result=2
fi
output_log
return $result
}

これをリファクタリングすると、こんな感じ。

function main()
{
local result=0
local cannot_sendmail=0
local fail_backup=0

log_host
purge
backup
result=$?
if check_sendmail
then
output_mail
else
result=2
fi
output_log /usr/local/bin/backup.log
return $result
}

function output_log()
{
cat ${log} >> $1
}

テストしてみよう。

# rm backup.log
# ./xoopsbackup.sh
# less backup.log
[2005/10/14 20:16:34] Host: mysql
[2005/10/14 20:16:34] Backup: xoops20051014201634.dump(785206bytes)

よし、問題ないね。

ちなみに、リファクタリング後のテストをここまでの記事では省略しているけど、実は書き換えるたびにテストを必ず行っている。

リファクタリングは、少し変えるたびにテストをする、これが重要なコツなので、リファクタリングするたびにテストを忘れずに。

もっといいのは、テストの自動化。

bashなら、bashunitでテストの自動化ができるが、まぁこのネタについてはまた今度。

さて、この引数渡しを使って、やたらとechoとリダイレクトでウザい、ログ出力を関数化してスマートにしてみよう。

function check_sendmail()
{
local result=0
if ! [ -f $SENDMAIL ]
then
log "sendmail not found... path=$SENDMAIL"
result=2
fi
return $result
}

function log_host()
{
log "Host: `hostname`"
}

function purge()
{
for name in `find *.dump -mtime +6`
do
rm $name
log "Delete: $name"
done
}

function backup()
{
local result=0
dumpfile="xoops"`date +%Y%m%d%H%M%S`".dump"
mysqldump -F -l xoops > $dumpfile 2> ${errormessage}
if [ $? = 0 ]
then
log "Backup: $dumpfile(`wc -c $dumpfile|awk '{print $1}'`bytes)"
else
log "Backup failure..."
log "`cat ${errormessage}`"
result=2
fi
return $resutl
}

function log()
{
echo "[`date +%Y/%m/%d` `date +%H\:%M\:%S`] $1" >> ${log}
}

ログ出力がだいぶスッキリしたなぁ。

テストも問題ないね。

次回は、送信先メールアドレスやログファイル名などをパラメータ化してみよう。

変数のスコープ

今回は、前回に引き続き、変数のスコープについて。

いつも応援ありがとう！


【参考】

達人プログラマー

コード全体はこちら。

まず基本的な知識として、変数宣言の前にlocalを付けると、関数内だけで有効な変数になる。

たとえば、start()の変数resultは、start()の中だけで有効な変数となり、他の関数で宣言されているローカルのresultとは別物で扱われる。

function start
{
local result=0

make_temporary

main
result=$?

remove_temporary

exit $result
}

今回のリファクタリングで追加した変数は、全てローカル変数にしている。

さて、ここで問題なのは、logやerrormessageのような、リファクタリング前から宣言していた変数群。

これらは、関数の外側でも有効な変数になったままなので、関数間で簡単に使いまわすことができる反面、取り扱いを気をつけなければいけない。

次回は、これらグローバル変数（って言い方でいいのかなぁ？）を安全に取り扱うためのリファクタリングをしてみよう。

関数の出口と戻り値の扱い

今回は、前回に引き続き、関数化の説明で、関数の出口と戻り値の扱いについて。

いつも応援ありがとう！


【参考】

達人プログラマー

コード全体はこちら。

どの関数も、必ず関数の最後でreturn（exit）をするようにしていて、戻り値も最初に初期化した後は、変更が必要なところで変更した後、やはり最後で返すようにしている。

たとえば、こんな感じ。

function check_sendmail()
{
local result
result=0
if ! [ -f $SENDMAIL ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "sendmail not found... path="$SENDMAIL >> ${log}
result=2
fi
return $result
}

main()やstart()、backup()なんかも、軒並み同じような構造になっている。

こうして関数の出口を最後の一箇所にすることで、途中で戻ってしまうことによる弊害をなくすことができて、戻り値の扱いもシンプルになる。

まぁ、詳しくは、今回紹介している達人プログラマーを読んでもらえればよいかと(^_^)

次回は、シェルの変数のスコープについて。

リファクタリングの目玉

今回は、前回の関数化の内容の説明を。

トップ１０に返り咲き！いつも応援ありがとう！


【参考】

入門bash

今回のリファクタリングの目玉は、main()に機能単位を並べること。

「機能単位を並べる」って何？って思われるかも知れないが、まず関数化したそれぞれが機能単位。

で、その機能単位がどの順番に処理されるのかをすっきりさせることが、処理をわかりやすくさせるコツ。

main()は大雑把に言えば、パージして、バックアップして、メールが投げられるなら投げて、ログを書く．．．そういう流れが見えるような作りになっている。

function main()
{
local result=0
local cannot_sendmail=0
local fail_backup=0

log_host
purge
backup
result=$?
if check_sendmail
then
output_mail
else
result=2
fi
output_log
return $result
}

また、main()の前後にある、テンポラリファイルの作成と後始末、それとexitを返すところは、メインの処理ではないので、start()にまとめた。

function start
{
local result=0

make_temporary

main
result=$?

remove_temporary

exit $result
}

残りの関数は、機能単位を関数として分割しただけ。

これで、枝葉と重要な部分は分離できた。

次回は、変数の扱いについてやろうかな。

関数ですっきり

今回は、複雑になってきたxoopsバックアップスクリプトを関数ですっきりさせる。

いつも応援ありがとう！


【参考】

入門bash

シェルで関数を使うときは、function hogehoge()という感じで関数定義できる。

functionは省略可能だが、分かりにくくなるので、あえて付けておこう。

細かいことはすっ飛ばして、関数化を行ったスクリプトはこんな感じ。

#!/bin/bash

SENDMAIL="/usr/sbin/sendmail"

function start
{
local result=0

make_temporary

main
result=$?

remove_temporary

exit $result
}

function main()
{
local result=0
local cannot_sendmail=0
local fail_backup=0

log_host
purge
backup
result=$?
if check_sendmail
then
output_mail
else
result=2
fi
output_log
return $result
}

function make_temporary()
{
log="`mktemp`"
errormessage="`mktemp`"
}

function remove_temporary()
{
rm -f ${log}
rm -f ${errormessage}
}

function check_sendmail()
{
local result
result=0
if ! [ -f $SENDMAIL ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "sendmail not found... path="$SENDMAIL >> ${log}
result=2
fi
return $result
}

function log_host()
{
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Host: "`hostname` >> ${log}
}

function purge()
{
for name in `find *.dump -mtime +6`
do
rm $name
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Delete: "$name >> ${log}
done
}

function backup()
{
local result
result=0
dumpfile="xoops"`date +%Y%m%d%H%M%S`".dump"
mysqldump -F -l xoops > $dumpfile 2> ${errormessage}
if [ $? = 0 ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup: "$dumpfile"("`wc -c $dumpfile|awk '{print $1}'`"bytes)" >> ${log}
else
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup failure..." >> ${log}
cat ${errormessage} >> ${log}
result=2
fi
return $resutl
}

function output_log()
{
cat ${log} >> /usr/local/bin/backup.log
}

function output_mail()
{
cat <<END | $SENDMAIL -t
Return-Path: <[e-mail]>
From: xoopsbackup <[e-mail]>
To: administrator <【ここにテストメール送信先メールアドレス】>
Subject: Backup xoops database
X-Mailer: xoopsbackup 0.3.05

`cat ${log}`
END
}

start

割と派手にリファクタリング（振る舞いを変えずにコードの内容をよくすること）したので、今回はスクリプトを読んでもらっておしまい。

次回、リファクタリングの内容について、解説する予定。

後処理をまとめる

今回も前回に引き続き、xoopsバックアップスクリプトの改善。

いつも応援ありがとう！


【参考】

入門bash

後処理をまとめるために、変数cannot_sendmailを追加。

この変数にスクリプトの終了時のステータスを入れておいて、最後にexitを返すようにすれば、途中で後処理をしたり、exitをする必要はなくなる。

また、sendmailコマンドがないときは、最後のメール送信をやらないようにするためにも、cannot_sendmailをチェックすればいい。

sendmailだけじゃなく、ついでにmysqldumpのエラーもチェックしておこう。

変更後のスクリプトはこんな感じ。

#!/bin/bash

SENDMAIL="/usr/sbin/sendmail"
cannot_sendmail=0
fail_backup=0

# Create temporary files.
log="`mktemp`"
errormessage="`mktemp`"

# Write hostname.
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Host: "`hostname` >> ${log}

# Check command exist.
if ! [ -f $SENDMAIL ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "sendmail not found... path="$SENDMAIL >> ${log}
# cat ${log} >> /usr/local/bin/backup.log
# rm -f ${log}
# rm -f ${errormessage}
# exit 2
cannot_sendmail=2
fi

# Purge old dump files.
for name in `find /usr/local/backup/*.dump -mtime +6`
do
rm $name
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Delete: "$name >> ${log}
done

# Backup current xoops database.
dumpfile="/usr/local/backup/xoops"`date +%Y%m%d%H%M%S`".dump"
$mysqldump -F -l xoops %gt; $dumpfile 2> ${errormessage}
if [ $? = 0 ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup: "$dumpfile"("`wc -c $dumpfile|awk '{print $1}'`"bytes)" >> ${log}
else
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup failure..." >> ${log}
cat ${errormessage} >> ${log}
fail_backup=2
fi

# Logging.
cat ${log} >> /usr/local/bin/backup.log

if [ $cannot_sendmail -eq 0 ]
then
# Send mail to administrator.
cat <<END | <font color="red">$SENDMAIL -t
Return-Path: <[e-mail]>
From: xoopsbackup <[e-mail]>
To: administrator <【ここにテストメール送信先メールアドレス】>
Subject: Backup xoops database
X-Mailer: xoopsbackup 0.3.04

`cat ${log}`
END
fi

# Remove temporary file.
rm -f ${log}
rm -f ${errormessage}

if [ $cannot_sendmail -eq 0 ] && [ $fail_backup -eq 0 ]
then
exit 0
else
exit 2
fi

まずは、正常系をチェック。

# rm backup.log
# ./xoopsbackup.sh
# echo $?
0
# less backup.log
[2005/10/05 17:28:45] Host: mysql
[2005/10/05 17:28:46] Backup: xoops20051005172845.dump(762706bytes)

次に、sendmailのパスをおかしなものにしてチェック。

# rm backup.log
# ./xoopsbackup.sh
# echo $?
2
# less backup.log
[2005/10/05 17:30:39] Host: mysql
[2005/10/05 17:30:39] sendmail not found... path=/usr/sbin/sendmaillll
[2005/10/05 17:30:39] Backup: xoops20051005173039.dump(762706bytes)

それから、mysqldumpで存在しないデータベースをバックアップした場合もチェック。

# rm backup.log
# ./xoopsbackup.sh
# echo $?
2
# less backup.log
[2005/10/05 17:53:57] Host: mysql
[2005/10/05 17:53:57] Backup failure...

よし、OKだね。

それにしても、最初はシンプルだったスクリプトがえらい複雑になった感じだなぁ．．．

そろそろ、処理を関数化して、まとめてみようかな。

コマンドの存在チェック

今回は、先週のMySQLのトレーニングで中断してしまったxoopsのバックアップスクリプト改善の続き、スクリプト中で実行するコマンドやファイルが存在するかどうかのチェック。

シェルスクリプトでエラーハンドリングするときの参考にどうぞ。

いつも応援ありがとう！


【参考】

入門bash

さて、今回の変更を行う前に、変数tmpfileの変数名が変なので、実態に合わせて変えておく。その後、コマンドの存在確認をしよう。

エディタの置換でtmpfileをlogに置換すると、スクリプトはこんな感じになる。

#!/bin/bash

# Create temporary files.
log="`mktemp`"
errormessage="`mktemp`"

# Write hostname.
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Host: "`hostname` >> ${log}

# Purge old dump files.
for name in `find /usr/local/backup/*.dump -mtime +6`
do
rm $name
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Delete: "$name >> ${log}
done

# Backup current xoops database.
dumpfile="/usr/local/backup/xoops"`date +%Y%m%d%H%M%S`".dump"
mysqldump -F -l xoops %gt; $dumpfile 2> ${errormessage}
if [ $? = 0 ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup: "$dumpfile"("`wc -c $dumpfile|awk '{print $1}'`"bytes)" >> ${log}
else
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup failure..." >> ${log}
cat ${errormessage} >> ${log}
fi

# logging.
cat ${log} >> /usr/local/bin/backup.log

# Send mail to administrator.
cat <<END | /usr/sbin/sendmail -t
Return-Path: <[e-mail]>
From: xoopsbackup <[e-mail]>
To: administrator <【ここにテストメール送信先メールアドレス】>
Subject: Backup xoops database
X-Mailer: xoopsbackup 0.3.02

`cat ${log}`
END

# Remove temporary file.
rm -f ${log}
rm -f ${errormessage}

念のため、テストしてみよう。

# rm backup.log
# ./xoopsbackup.sh
# less backup.log
[2005/10/03 16:26:29] Host: mysql
[2005/10/03 16:26:30] Backup: xoops20051003162629.dump(753028bytes)

よし、大丈夫そうだ。

では、コマンドの存在確認をしよう。

このxoopsバックアップスクリプトは、sendmailコマンド（実際はpostfixのもの）が存在することを前提に置いているので、このコマンドが存在しない場合、まともにメールが投げられない。

ということで、sendmailのパスを調べて、このパスにsendmailがない場合は、エラーになるようにしてみよう。

$ which sendmail
/usr/sbin/sendmail

次に、ファイルの存在確認は、以前にエラー処理で紹介したtestコマンドもしくは[コマンドにオプションとして-fを指定するとできる。

$ if test -f /usr/sbin/sendmail
> then
> echo 'exist'
> else
> echo 'not exist'
> fi
exist

今度は、存在しないファイルをチェックした場合。testの代わりに[を使ってみよう。

$ if [ -f /not_exist_file ]
> then
> echo 'exist'
> else
> echo 'not exist'
> fi
not exist

あとは、このチェックをスクリプトに入れてやるだけ。

分かりやすくするために、sendmailコマンドをSENDMAIL変数に入れておこう。

#!/bin/bash

SENDMAIL="/usr/sbin/sendmail"

# Create temporary files.
log="`mktemp`"
errormessage="`mktemp`"

# Write hostname.
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Host: "`hostname` >> ${log}

# Check command exist.
if ! [ -f $SENDMAIL ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "sendmail not found... path="$SENDMAIL >> ${log}
cat ${log} >> /usr/local/bin/backup.log
rm -f ${log}
rm -f ${errormessage}
exit 2
fi

# Purge old dump files.
for name in `find /usr/local/backup/*.dump -mtime +6`
do
rm $name
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Delete: "$name >> ${log}
done

# Backup current xoops database.
dumpfile="/usr/local/backup/xoops"`date +%Y%m%d%H%M%S`".dump"
$mysqldump -F -l xoops %gt; $dumpfile 2> ${errormessage}
if [ $? = 0 ]
then
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup: "$dumpfile"("`wc -c $dumpfile|awk '{print $1}'`"bytes)" >> ${log}
else
echo -n "["`date +%Y/%m/%d`" "`date +%H\:%M\:%S`"] " >> ${log}
echo "Backup failure..." >> ${log}
cat ${errormessage} >> ${log}
fi

# Logging.
cat ${log} >> /usr/local/bin/backup.log

# Send mail to administrator.
cat <<END | $SENDMAIL -t
Return-Path: <[e-mail]>
From: xoopsbackup <[e-mail]>
To: administrator <【ここにテストメール送信先メールアドレス】>
Subject: Backup xoops database
X-Mailer: xoopsbackup 0.3.03

`cat ${log}`
END

# Remove temporary file.
rm -f ${log}
rm -f ${errormessage}

まずは、正常系をチェック。

# rm backup.log
# ./xoopsbackup.sh
# echo $?
0
# less backup.log
[2005/10/03 16:56:21] Host: mysql
[2005/10/03 16:56:21] Backup: xoops20051003162629.dump(753028bytes)

次に、SENDMAIL変数のパスをでたらめなパスにしてから異常系をチェック。

# rm backup.log
# ./xoopsbackup.sh
# echo $?
2
# less backup.log
[2005/10/03 16:59:32] Host: mysql
[2005/10/03 16:59:32] sendmail not found... path=/usr/sbin/sendmaillll

よし、うまくいったみたいだ。

うーん、でもエラー発生後の後処理とかが、スマートじゃないなぁ．．．

．．．という訳で、次回は、後処理をうまくまとめるための方法について考えてみよう。

MySQLチューニング

今回も前回に引き続き、ただいま受講中のMySQLトレーニングの実況中継（5日目最終日）。

いつも応援ありがとう！


【参考】

実践ハイパフォーマンスMySQL

今日のトレーニングは、MySQLのチューニング。

個人的には今回のトレーニング最大の目玉で、この日が来るのが待ち遠しかった！

今回も、実践に役立ちそうな技から、試験ぐらいでしか使わなそうな技まで、気になったものをトレーニングを受けながらリアルタイムに追記していくので、もし今日の分をまとめて見たいあなたは、18時以降にまたどうぞ。

1. MySQL付属のsql-benchは、単純なselectとinsertしかテストしてくれない。しかも実行するクエリーは、コード中にハードコーディングされているので、カスタマイズも面倒。
   
   
2. プリペアドステートメントは、セッション内だけで有効なので、セッション毎に登録してあげる必要がある。
   
   
   mysql> prepare country_name from 'select name from country where code=?';
   Query OK, 0 rows affected (0.00 sec)
   Statement prepared
   
   mysql> set @a='FIN';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> execute country_name using @a;
   +---------+
   | name |
   +---------+
   | Finland |
   +---------+
   1 row in set (0.00 sec)
   
   
3. explainでselect文の解析ができる。
   
   
   mysql> explain select co.name, ci.name from city as ci, country as co where ci.id=co.capital and co.region like 'Nordic%'\G
   *************************** 1. row ***************************
   id: 1
   select_type: SIMPLE
   table: co
   type: ALL
   possible_keys: NULL
   key: NULL
   key_len: NULL
   ref: NULL
   rows: 239
   Extra: Using where
   *************************** 2. row ***************************
   id: 1
   select_type: SIMPLE
   table: ci
   type: eq_ref
   possible_keys: PRIMARY
   key: PRIMARY
   key_len: 4
   ref: world.co.Capital
   rows: 1
   Extra:
   2 rows in set (0.00 sec)
   
   
4. expainは、type（結合の種類）、key（使用するインデックス）、extra（結合方法の追加情報）をまずチェックする。
   
   
5. expainのtypeにALLが出ているときは、インデックスを全く使っていないので、インデックスを追加したり、検索キーを他の列を変更する等、チューニングすること。
   
   
6. expainのextraに"Using filesort"、"Using temporary"、"Range checked for each record"が出ている場合は、パフォーマンス低下している可能性が高いので注意。
   
   
7. MySQLでインデックスを使用するパターンは、以下の通り。
   
   定数との比較
   
   mysql> select * from city where name="Japan";
   
   インデックスを使って結合する場合
   
   select * from city, country where city.country = country.code;
   
   先頭列のインデックスに対して、>、<、IS NULL、BETWEENで定数比較や結合をする場合
   
   mysql> select * from city where id %lt; 10;
   
   先頭がワイルドカードでないlikeを使用する場合
   
   mysql> select * from city where country like 'AU%';
   
   インデックス列でmax()、min()を検索する場合
   
   mysql> select min(country) from city;
   
   先頭列のインデックスに対してorder byやgroup byをする場合
   
   mysql> select * from city order by id desc;
   
   使用されている列がすべてインデックスの一部である場合
   
   mysql> select name from city where id < 10;
   
   
8. MySQLでインデックスを使用しないパターンは、以下の通り。
   
   先頭がワイルドカードのlikeを使用する場合
   
   mysql> select * from city where country like '%AB';
   
   先頭列のインデックスを使っていない場合
   
   mysql> select * from city where name = "Tokyo";
   
   MySQLがテーブルスキャンした方が高速だと判断した場合（たとえば、テーブル全体の80%以上を対象にした場合など）
   
   whereで使用するインデックスとorder byで使用するインデックスが異なる場合
   
   mysql> select * from city where name = "Tokyo" order by id;
   
   MEMORYテーブルでインデックスに対して=を使用しないで検索する場合やorder byを使用した場合
   
   
9. proedure analyseで列の分析ができる。最も長い（短い）文字列のチェックや文字列長の平均値、nullの数、列をenumにした方がいいか等がわかる。
   
   mysql> select * from user procedure analyse(5)\G
   *************************** 1. row ***************************
   Field_name: test.user.id
   Min_value: 1
   Max_value: 4
   Min_length: 1
   Max_length: 1
   Empties_or_zeros: 0
   Nulls: 0
   Avg_value_or_avg_length: 2.5000
   Std: 1.1180
   Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
   *************************** 2. row ***************************
   Field_name: test.user.name
   Min_value: abe
   Max_value: utoyama
   Min_length: 3
   Max_length: 8
   Empties_or_zeros: 0
   Nulls: 0
   Avg_value_or_avg_length: 5.5000
   Std: NULL
   Optimal_fieldtype: ENUM('abe','fujiwara','masq','utoyama') NOT NULL
   *************************** 3. row ***************************
   Field_name: test.user.department
   Min_value: Inteligence
   Max_value: Sales
   Min_length: 5
   Max_length: 13
   Empties_or_zeros: 0
   Nulls: 0
   Avg_value_or_avg_length: 10.0000
   Std: NULL
   Optimal_fieldtype: ENUM('Inteligence','MySQL Project','Sales') NOT NULL
   3 rows in set (0.00 sec)
   
   
10. サマリーテーブル（分析結果を保持しておくテーブル）を作って参照するようにすると、いちいちjoinしなくてもよくなる（ただし、定期的にサマリーテーブルを更新する必要あり）
    
    
11. サマリーテーブルにMEMORYストレージエンジンを使うと、かなりスピードアップする。
    
    
12. InnoDBは、トランザクションが必要だったり、参照と更新が同じくらい発生するテーブルにのみ使うこと。
    
    
13. 長いvarchar列は、先頭部分にだけインデックスを張ることでインデックスを最適化できる。
    
    mysql> create table longname ( id int primary key, name char( 255 ), index( name( 5 ) ) );
    Query OK, 0 rows affected (0.48 sec)
    
    mysql> show index from longname\G
    *************************** 1. row ***************************
    Table: longname
    Non_unique: 0
    Key_name: PRIMARY
    Seq_in_index: 1
    Column_name: id
    Collation: A
    Cardinality: 0
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 2. row ***************************
    Table: longname
    Non_unique: 1
    Key_name: name
    Seq_in_index: 1
    Column_name: name
    Collation: A
    Cardinality: 0
    Sub_part: 5
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    2 rows in set (0.00 sec)
    
    
14. show variablesでMySQL全体の設定を見れる。これらの値をsetやmy.cnfでいじってチューニングする。
    
    
15. show statusでMySQL全体の現在の状態を見れる。MySQL自体をチューニングする際にここの値を元にする。
    
    
16. show engine innodb statusでInnoDBの状態を見れる。InnoDBのチューニングする際にここの値を元にする。
    
    
17. MySQLは、メモリ周りのチューニング余地がかなりあるので、できるだけメモリをたくさん積んだ方がチューニングしやすくなる。
    

MySQLの管理など

今回も前回に引き続き、ただいま受講中のMySQLトレーニングの実況中継（4日目）。

いつも応援ありがとう！


【参考】

MySQL Certification Study Guide

今日のトレーニングは、MySQLのログ、トランザクション、バックアップ、レプリケーションについて。

実践に役立ちそうな技から、試験ぐらいでしか使わなそうな技まで、気になったものをトレーニングを受けながらリアルタイムに追記していくので、もし今日の分をまとめて見たいあなたは、18時以降にまたどうぞ。

1. 一般クエリーログをオン（デフォルトはオフのまま）にすると、発行されたクエリーを記録できるが、パフォーマンスが10%ほど低下する。
   
   
2. スロークエリーログを使うと、実行の遅いクエリーやインデックスを使用していないクエリーを記録できる。
   
   
3. 基本的にMyISAMの方がselect、insertなどの動作がInnoDBよりも速い。
   
   
4. MyISAMはテーブルロック、InnoDBは行ロックがサポートされている。
   
   
5. InnoDBのトランザクションは、マルチバージョニングで実装されているため、更新中の読み取り待ちがない。
   
   
6. InnoDBのトランザクション分離レベルは、REPEATABLE READがデフォルト（Oracleと同じ）。
   
   
7. InnoDBの行ロックは、基本的にプライマリキーが張られていないと機能しない。
   
   
8. select 〜 lock in share modeで共有ロックがかけられる。ロック対象は、参照された行のみ。
   
   
9. select 〜 for updateで排他ロックがかけられる。ロック対象は、参照された行のみ。
   
   
10. select 〜 for updateによる排他ロックは、現在存在しないがロック対象の行に該当するであろうキー行に対しても自動的にロックをかける（ネクストキーロックという機能）。
    
    クライアントA
    
    mysql> select id from city;
    +----+
    | id |
    +----+
    | 1 |
    | 5 |
    | 10 |
    ...
    mysql> begin;
    mysql> select * from city where id < 1 and id > 10 for update;
    
    
    クライアントB
    
    mysql> begin;
    mysql> insert city (id) values( 7 );
    ↑このinsertクエリーはクライアントAがコミットもしくはロールバックするまでブロックされる
    
    
    詳しくは、ネクストキーロック: ファントムの問題の回避を参照。
    
    
11. MyISAMテーブルは、最悪.MYDファイルさえあれば、何とか復旧できる。
    
    
12. check tableやrepair tableを定期的に実行するといい（MySQL ABでもcronで週一チェックしている）。
    
    
    mysql> check table city, country, countrylanguage;
    +-----------------------+-------+----------+----------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+-------+----------+----------+
    | world.city | check | status | OK |
    | world.country | check | status | OK |
    | world.countrylanguage | check | status | OK |
    +-----------------------+-------+----------+----------+
    3 rows in set (0.04 sec)
    
    mysql> repair table city, country, countrylanguage;
    +-----------------------+--------+----------+---------------------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+--------+----------+---------------------------------------------------------+
    | world.city | repair | note | The storage engine for the table doesn't support repair |
    | world.country | repair | status | OK |
    | world.countrylanguage | repair | status | OK |
    +-----------------------+--------+----------+---------------------------------------------------------+
    3 rows in set (0.02 sec)
    
    ※check tableはMyISAM、InnoDB共に有効だが、repair tableはMyISAMだけが有効。
    
    
13. mysqld起動時に--myisam-recoverオプションを指定することで、MyISAMもInnoDBと同じように自動テーブルチェックが有効になる。
    
    
14. optimize tableでテーブルを最適化するだけでMyISAMはパフォーマンスが格段にアップするらしい（特にデータ更新が頻繁なテーブルの場合）。
    
    
    mysql> optimize table city, country, countrylanguage;
    +-----------------------+----------+----------+----------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+----------+----------+----------+
    | world.city | optimize | status | OK |
    | world.country | optimize | status | OK |
    | world.countrylanguage | optimize | status | OK |
    +-----------------------+----------+----------+----------+
    3 rows in set (0.65 sec)
    
    
15. InnoDBに対してoptimize tableをかけた場合、かけた直後はパフォーマンスが低下することがある（内部的には、alter tableでテーブルを作り直している）。
    
    
16. インデックスだけを最適化したいときは、optimize tableの代わりにanalyze tableを使うといい。
    
    mysql> analyze table city, country, countrylanguage;
    +-----------------------+---------+----------+-----------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-----------------------+---------+----------+-----------------------------+
    | world.city | analyze | status | OK |
    | world.country | analyze | status | Table is already up to date |
    | world.countrylanguage | analyze | status | Table is already up to date |
    +-----------------------+---------+----------+-----------------------------+
    3 rows in set (0.01 sec)
    
    
    
17. バックアップの手段はmysqlhotcopyやファイルの直接コピーなどいろいろあるが、運用中のホットコピーをMyISAM、InnoDBの区別なしに行う場合は、mysqldumpが最も適している。
    
    
18. mysql_multiを使うと、1台のサーバーで複数のMySQLを起動するのが簡単になる。
    
    
19. MySQLのレプリケーションは、insertのラッシュ時にマスターが落ちると、スレーブにちゃんとデータが渡らないケースがある（負荷分散、バックアップ目的にはOKだが、冗長性確保目的には微妙）。
    
    

MySQLアーキテクチャなど

今回も前回に引き続き、ただいま受講中のMySQLトレーニングの実況中継（3日目）。

そうそう、昨日MySQL 5.0系がとうとうベータ版からリリース候補版に移行したらしい。

．．．って、今までのパターンだと、ベータの後はガンマのはずなんだが．．．

MySQL ABの商業的理由だろうか？（本当に大丈夫？）

いつも応援ありがとう！


【参考】

MySQL Certification Study Guide

今日のトレーニングは、MySQLのアーキテクチャ周りとセキュリティ、環境設定について。

実践に役立ちそうな技から、試験ぐらいでしか使わなそうな技まで、気になったものをトレーニングを受けながらリアルタイムに追記していくので、もし今日の分をまとめて見たいあなたは、18時以降にまたどうぞ。

1. Install SheildベースだったWindows版MySQL インストーラは、MySQL 4.1からWindowsインストーラベースに変わった。設定もオートマチックでラクチン。
   
   
2. 新機能を使ったり、MySQLに改造を入れたりするのでなければ、ソースコード版は使わず、バイナリ版やRPM版を使った方がいい。
   
   
3. Linuxでは、mysqld_multiを使うと、複数のMySQLサーバーを同時に起動できるらしい（未検証）。
   
   
4. MySQLのパフォーマンスチューニングは、ストレージエンジン（MyISAM、InnoDBなど）を各テーブルの目的に合わせてまず切り替えるところから。
   
   
5. MyISAMテーブルは、【テーブル名】.frm（テーブル情報などが格納される）、【テーブル名】.MYD（データが格納される）、【テーブル名】.MYI（インデックスが格納される）の3種類のファイルで構成される。
   
   
6. 複数のMyISAMテーブルをつなげて、MERGEテーブルという大きなストレージを作ることができる。
   
   mysql> create table sales200508 ( id int auto_increment primary key, item_name char(20), price int ) engine=myisam;
   Query OK, 0 rows affected (0.10 sec)
   
   mysql> create table sales200509 ( id int auto_increment primary key, item_name char(20), price int ) engine=myisam;
   Query OK, 0 rows affected (0.06 sec)
   
   mysql> insert sales200508 ( item_name, price ) values ( "pen", 100 ), ( "card", 400 ), ( "photo album", 4000 );
   Query OK, 3 rows affected (0.00 sec)
   Records: 3 Duplicates: 0 Warnings: 0
   
   mysql> select * from sales200508;
   +----+-------------+-------+
   | id | item_name | price |
   +----+-------------+-------+
   | 1 | pen | 100 |
   | 2 | card | 400 |
   | 3 | photo album | 4000 |
   +----+-------------+-------+
   3 rows in set (0.00 sec)
   
   mysql> insert sales200509 ( item_name, price ) values ( "gum", 200 ), ( "ink", 600 ), ( "eraser", 100 );
   Query OK, 3 rows affected (0.00 sec)
   Records: 3 Duplicates: 0 Warnings: 0
   
   mysql> select * from sales200509;
   +----+-----------+-------+
   | id | item_name | price |
   +----+-----------+-------+
   | 1 | gum | 200 |
   | 2 | ink | 600 |
   | 3 | eraser | 100 |
   +----+-----------+-------+
   3 rows in set (0.00 sec)
   
   mysql> create table sales ( id int not null, item_name char(20), price int, key( id ) ) type=merge union=( sales200508, sales200509 );
   Query OK, 0 rows affected, 1 warning (0.11 sec)
   
   mysql> select * from sales;
   +----+-------------+-------+
   | id | item_name | price |
   +----+-------------+-------+
   | 1 | pen | 100 |
   | 2 | card | 400 |
   | 3 | photo album | 4000 |
   | 1 | gum | 200 |
   | 2 | ink | 600 |
   | 3 | eraser | 100 |
   +----+-------------+-------+
   6 rows in set (0.00 sec)
   
   
7. MERGEテーブルは、insert_methodオプションを指定しないとinsertができない（update、deleteはできる）。insert_method=firstでマージ元テーブルの最初のテーブルにinsert、insert_method=firstでマージ元テーブルの最後のテーブルにinsert。
   
   mysql> insert sales values( 4, 'brain', 100000000 );
   ERROR 1031 (HY000): Table storage engine for 'sales' doesn't have this option
   
   mysql> drop table sales;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> create table sales ( id int not null, item_name char(20), price int, key( id ) ) type=merge union=( sales200508,
   sales200509 ) insert_method=last;
   Query OK, 0 rows affected, 1 warning (0.06 sec)
   
   mysql> insert sales values( 4, 'brain', 100000000 );
   Query OK, 1 row affected (0.00 sec)
   
   mysql> select * from sales;
   +----+-------------+-----------+
   | id | item_name | price |
   +----+-------------+-----------+
   | 1 | pen | 100 |
   | 2 | card | 400 |
   | 3 | photo album | 4000 |
   | 1 | gum | 200 |
   | 2 | ink | 600 |
   | 3 | eraser | 100 |
   | 4 | brain | 100000000 |
   +----+-------------+-----------+
   7 rows in set (0.00 sec)
   
   mysql> select * from sales200508;
   +----+-------------+-------+
   | id | item_name | price |
   +----+-------------+-------+
   | 1 | pen | 100 |
   | 2 | card | 400 |
   | 3 | photo album | 4000 |
   +----+-------------+-------+
   3 rows in set (0.00 sec)
   
   mysql> select * from sales200509;
   +----+-----------+-----------+
   | id | item_name | price |
   +----+-----------+-----------+
   | 1 | gum | 200 |
   | 2 | ink | 600 |
   | 3 | eraser | 100 |
   | 4 | brain | 100000000 |
   +----+-----------+-----------+
   4 rows in set (0.00 sec)
   
   
8. MERGEテーブルは、【テーブル名】.frm（マージ先のテーブル情報が格納される）、【テーブル名】.MRG（マージ元のテーブル情報が格納される）の2種類のファイルで構成される。
   
   
9. InnoDBテーブルは、テーブル毎の【テーブル名】.frmファイル（テーブル情報などが格納される）とibdata【数字】ファイル（InnoDBテーブル全体のデータ）、ib_logfile【数字】ファイル（InnoDBテーブル全体のログ）の3種類のファイルで構成される。
   
   
10. alter tableでテーブルのストレージエンジンを変更できる。
    
    mysql> show create table city\G
    *************************** 1. row ***************************
    Table: city
    Create Table: CREATE TABLE `city` (
    `Id` int(11) NOT NULL auto_increment,
    `Name` char(35) NOT NULL default '',
    `Country` char(3) NOT NULL default '',
    `District` char(20) NOT NULL default '',
    `Population` int(11) NOT NULL default '0',
    PRIMARY KEY (`Id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    mysql> alter table city engine=innodb;
    Query OK, 4079 rows affected (0.60 sec)
    Records: 4079 Duplicates: 0 Warnings: 0
    
    mysql> show create table city\G
    *************************** 1. row ***************************
    Table: city
    Create Table: CREATE TABLE `city` (
    `Id` int(11) NOT NULL auto_increment,
    `Name` char(35) NOT NULL default '',
    `Country` char(3) NOT NULL default '',
    `District` char(20) NOT NULL default '',
    `Population` int(11) NOT NULL default '0',
    PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    
11. データディレクトリの位置は、datadir環境変数に入っている（例はWindowsの場合）。
    
    mysql> show variables like 'data%';
    +---------------+-----------------------------------------------+
    | Variable_name | Value |
    +---------------+-----------------------------------------------+
    | datadir | C:\Program Files\MySQL\MySQL Server 4.1\Data\ |
    +---------------+-----------------------------------------------+
    1 row in set (0.00 sec)
    
    
12. grantで作成したユーザーは、revokeだけでは削除できず、drop userして削除になる。
    
    mysql> grant all on *.* to user1@localhost;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for user1@localhost;
    +----------------------------------------------------+
    | Grants for user1@localhost |
    +----------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' |
    +----------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> revoke all on *.* from user1@localhost;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for user1@localhost;
    +-------------------------------------------+
    | Grants for user1@localhost |
    +-------------------------------------------+
    | GRANT USAGE ON *.* TO 'user1'@'localhost' |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> drop user user1@localhost;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for user1@localhost;
    ERROR 1141 (42000): There is no such grant defined for user 'user1' on host 'localhost'
    
    
13. Windows版でインストールディレクトリにあるmy.iniは、サービスの起動オプション（--defaults-file=〜）として指定されている。