日々の覚書: 7月 2012

2012/07/31

pagerの無理矢理な使い方

mysql> pager <コマンド>

と指定してやると、出力結果が標準出力ではなくて<コマンド>に切り替えられる。
よく使うのはlessだけど、たまに変なこともしてみたくなる。

mysql> pager cat > /dev/null

SELECTの結果を全てnull deviceに棄てるという暴挙。

前に一度だけ、
`SELECTの結果を全部フェッチさせたいのでLIMITは使えない'
`けど100万行もターミナルに転送されたらウザい'
というシチュエーションがあったので使ってみた。
戻し忘れた時のorz感が大きい。

mysql> pager cat > /tmp/result

SELECTの結果を`mysqlクライアントを起動しているホストの/tmp/result'に書き込む。
LOAD DATA LOCAL INFILEと同じ様な感じ。
おとなしくtee使えって気もするけど、全部ターミナルに表示されるのが嫌な時用。
gzipでもいい感じ。

戻し忘れのorz感はピカイチ。set -o noclobberでも上書き止められなかった。。
リダイレクトを>>にすれば良いのか。

mysql> pager grep --color -i <xxx>

grepの--colorってあまり使う人いない気がする。
マッチした文字列に色つけてくれます。
WHERE col1 LIKE '%xxx%' OR col2 LIKE '%xxx%' OR .. の代わりにざっと確認するのにオススメ。
ただし、Resultの行数と実際に表示される行数が食い違うので、
細かいことよりサクサクやりたい人向けかも。

戻し忘れるとorzというよりイラっとするのが難点。

mysql> pager logger

syslogdにSELECTの結果を送り付けるという意味の判らない暴挙。

mysql> pager md5sum

こっちのサーバと向こうのサーバで、出力結果(=データの中身)が
同じかどうかをざっくり調べるのに使えるかも知れない。

mysql> pager ssh cent02 cat > /dev/pts/0

あろうことか他のノードの仮想コンソールに転送してみたり。

お手製Perlとかにつなげば、夢は無限大。

結構便利なmysql --skip-column-nameとbash read

mysqlの--skip-column-nameオプション。
mysqlの出力結果から先頭の1行(column nameとか表示される行)を剥ぎ取ってくれます。
--executeと一緒に使うのが便利。
（CUIでログインしても、column nameを剥ぎ取ってくれるけど役に立った記憶は無い。。）

$ mysql -e "SHOW DATABASES"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+

$ mysql --skip-column-name -e "SHOW DATABASES"
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+

ついでに、この枠線はどういう仕組みかよく判らないけどパイプに渡すと消える。

$ mysql --skip-column-name -e "SHOW DATABASES" | cat
information_schema
mysql
performance_schema
test

ので、これをread(bashのビルトインコマンド)に渡すと美味しい。
while read <変数名> [<変数2> ..]の形で渡すと、
パイプから1行受け取るごとに\sをセパレータにして指定した引数に渡してくれる。

たとえばこんな。
world_myisamデータベースの中の全テーブルをCSVファイルにエクスポートして、
それをworldデータベースにインポートさせる。
（テーブルスキーマは移植済みって前提だけれども）

$ mysql --skip-column-name -e "SHOW TABLES FROM world_myisam" | while read tbl ; do
> mysql -e "SELECT * FROM world_myisam.$tbl INTO OUTFILE '/tmp/$tbl.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
> mysql -e "LOAD DATA INFILE '/tmp/$tbl.csv' INTO TABLE world.$tbl FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
> rm /tmp/$tbl.csv
> done

INSERTの使えないInfobrightのGPL版でworldデータベースを食わせたときのやつ。

1テーブルずつmysqldumpを取って圧縮するとか（テーブル間の整合性取れないけど）

$ mysql --skip-column-name -e "SHOW DATABASES" | egrep -v "information_schema|mysql|performance_schema" | while read db ; do
> mysql --skip-column-name -e "SHOW TABLES FROM $db" | while read tbl ; do
> mysqldump $db $tbl | gzip -1 > /tmp/$db.$tbl.sql.gz
> done
> done

他にもテーブル片っ端からPRIMARY KEYをDROPするとか（しない）
片っ端からTRUNCATEするとか（しない）
テーブルをさらうようなやり方なら、使い途は色々。

2012/07/30

Infobrightにworldデータベースを食わせてみる

前に書いたけれども、InfobrightはCommunity Edition(GPLで使える方)はINSERT,UPDATE,DELETEが利かない。
でもSELECT（というかGROUP BY）性能を試す為には当然データが必要。

なのでごり押しでworldデータベース(MySQL公式のサンプルDB）を食わせる方法のメモ。

1) まずは落としてくる

http://dev.mysql.com/doc/index-other.html
`world database (MyISAM version, used in MySQL certifications and training)'をチョイス。
InfobrightのMyISAMに食わせてからBRIGHTHOUSEに食わせ直すことにした。

2) MyISAMテーブルにロード

$ mysqladmin create world_myisam
$ mysql world_myisam < world.sql

さっくりと。

3) INDEXをひっぺがしたりデータ型を変えたり

world.sqlファイルを直接いじっても良いかも知れない。

mysql> ALTER TABLE `City` MODIFY `ID` INT NOT NULL,DROP PRIMARY KEY;
mysql> ALTER TABLE `Country` MODIFY `Continent` VARCHAR(13) NOT NULL DEFAULT 'Asia',DROP PRIMARY KEY;
mysql> ALTER TABLE `CountryLanguage` MODIFY `IsOfficial` VARCHAR(1) NOT NULL DEFAULT 'F',DROP PRIMARY KEY;

4) スキーマをBRIGHTHOUSEエンジンに食わせる

$ mysqladmin create world
$ mysqldump --no-data world_myisam | sed 's/ENGINE=MyISAM/ENGINE=BRIGHTHOUSE/i' | mysql world

ENGINEをInfobrightのエンジンに変更してそのまま食わせる。

5) データをCSVに吐き出してからBRIGHTHOUSEに食わせる

$ mysql --skip-column-name -e "SHOW TABLES FROM world_myisam" | while read tbl ; do
> mysql -e "SELECT * FROM world_myisam.$tbl INTO OUTFILE '/tmp/$tbl.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
> mysql -e "LOAD DATA INFILE '/tmp/$tbl.csv' INTO TABLE world.$tbl FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
> rm /tmp/$tbl.csv
> done

rmがアレなら最後にまとめて消す。

取り敢えずこれで使える形に落とし込めるけど、
worldデータベースだと件数が少なくてちょっとさみしいかも。

employeesだともうちょっと件数があるので、集約関数の速さは実感できた。

2012/07/27

Infobrightとやらに触ってみる

なんか色々ハマったのでメモ。。

入れたのはGPL版。
http://sourceforge.jp/projects/sfnet_infobright/releases/

・勝手にinfobrightビルドのMySQL(5.1.40 InnoDB使えなくしてある)がインストールされる。
プラグイン形式にしてくれれば良いのに。。

・nscdが浮いていないと怒られてインストール失敗する。
yumでnscdを入れて起動させておく。

・nscdが浮いていないとmysqldを浮かす時の--userオプションにことごとく失敗するので、
ちゃんと自動起動設定しておかないとあとで泣きたくなる。

・GPL版はBRIGHTHOUSEエンジンに対するINSERT,DELETE,UPDATE,ALTERが利かない。
既存のテーブルからSELECT .. INTO OUTFILE ..してLOAD DATA INFILE ..してやらないとダメ。
⇒という訳でmysqldumpを食わせられない。

・BRIGHTHOUSERエンジンのデータ型はMyISAMやInnoDBから見るとかなり制限がある。

・LOCK TABLESステートメントが利かない。
ロック粒度とかどうなってんだろ？（どのみち更新系DML使えないけど）

日本語ドキュメントにも書いてあるけど気になったところ。
http://www.infobright-partner.jp/download/jadocument/

- UNSIGNED,ZEROFILL属性はサポートしない。
- インデックスはPrimary Keyですらサポートしない。
- よって当然AUTO_INCREMENT属性もサポートされない。
- 日本語ユーザーガイド(2012/7/27現在)に
- `INTEGER 型は BINGHT に変換することができます'って書いてあるけどBIGINTの誤植。
- たまたまBIGINT UNSIGNEDを作ろうとして怒られて、UNSIGNEDが悪いと思わずに
- BIGINT型はサポートしてなくてBINGHT型なのか！とか思ってしまった罠orz

http://www.infobright.org/wiki/
- DEFAULTは無視されるらしい。INSERTできないから本当かどうか試してないけど。

また出てきたらまた書こう。忘れる。

【2012/07/30】
worldデータベースを食わせてみたメモ。

2012/07/20

ターミナルをリサイズするとmysqlクライアントが落ちるバグ

http://bugs.mysql.com/bug.php?id=65956

作業中とかにやっちゃうと笑えないかも知れません。

クエリを投げてる間に仮想ターミナルをリサイズすると、mysqlクライアントが落ちるというバグ。

mysql> SELECT SLEEP(100);
ここでターミナルのサイズを変える
ERROR 2013 (HY000): Lost connection to MySQL server during query

read()が走ってる間にやると落ちるっぽいので、
ALTER TABLEとかの間にやると落ちるでしょう。

（普通の重いSELECTなぞはread()自体はすぐ終わっているのでまるっと落ちたりはしないっぽい）

最近のパーティション周りのバグ

http://bugs.mysql.com/bug.php?id=65905

1) RANGEパーティションに使っているカラムにインデックスを張る
2) LESS THAN MAXVALUEのパーティションを指定しない
3) SELECT .. FROM .. WHERE partition_key > PARTITION句で指定した最大値

↓コア吐きます。

mysql> CREATE TABLE t1 ( num INT,KEY(num) ) PARTITION BY RANGE (num) (PARTITION
p1 VALUES LESS THAN (10));

mysql> SELECT * FROM t1 WHERE num > 11;


http://bugs.mysql.com/bug.php?id=65935

1) 3つ以上のKEYパーティションを作る
2) 検索する（再現条件不明。。）

結果が正しく返ってきません。

mysql> CREATE TABLE t2 (vID INT,pID INT) PARTITION BY KEY(pID,vID) PARTITIONS 5;
mysql> INSERT INTO t2 VALUES (1,6843),(2,6843);
mysql> SELECT * FROM t2 WHERE pID = 6843;

+------+------+
| vID  | pID  |
+------+------+
|    1 | 6843 |
|    2 | 6843 |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE pID = 6843 AND vID != 6843;

+------+------+
| vID  | pID  |
+------+------+
|    2 | 6843 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE pID = 6843 AND vID > 0;

Empty set (0.00 sec)

ゑー。。。

どんな条件の時にHITするのか判らないのは嫌だなぁ。。

MySQLのWHERE句サブクエリ（相関サブクエリ）の順番

FROM句にサブクエリを使う時の順番を書いたので、
今度はWHERE句に使う時（相関サブクエリ）の順番も考察してみる。

毎度の投げ遣りサンプルテーブルはFROM句サブクエリの時と一緒。
あと、今回はt1テーブルをまるっとtt1テーブルにもコピーして2つにしておく。

取り敢えずこんなサブクエリは如何だろうか。

mysql> SELECT * FROM t1 WHERE val IN (SELECT val FROM tt1 WHERE num BETWEEN 1 AND 10);

+-----+----------------------------------+
| num | val |
+-----+----------------------------------+
| 1 | c4ca4238a0b923820dcc509a6f75849b |
| 2 | c81e728d9d4c2f636f067f89cc14862c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | d3d9446802a44259755d38e6d163e820 |
+-----+----------------------------------+
10 rows in set (21.69 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE val IN (SELECT val FROM tt1 WHERE num BETWEEN 1 AND 10);

+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+

+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+

+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+

2 rows in set (0.00 sec)

想像通り、相関サブクエリ素敵に重い。

mysql> SHOW PROFILE;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000023 |

| Waiting for query cache lock | 0.000007 |

| checking query cache for query | 0.000092 |

| checking permissions | 0.000007 |

| checking permissions | 0.000008 |

| Opening tables | 0.000024 |

| System lock | 0.000018 |

| init | 0.000048 |

| optimizing | 0.000015 |

| statistics | 0.000014 |

| preparing | 0.000013 |

| executing | 0.000006 |

| Sending data | 0.000038 |

| optimizing | 0.000011 |

| statistics | 0.000039 |

| preparing | 0.000026 |

| executing | 0.000006 |

| Sending data | 0.000025 |

| executing | 0.000006 |

| Sending data | 0.000006 |

| executing | 0.000005 |

| Sending data | 0.000017 |

| executing | 0.000005 |

| Sending data | 0.000014 |

| executing | 0.000005 |

| Sending data | 0.000047 |

| end | 0.000010 |

| query end | 0.000008 |

| closing tables | 0.000011 |

| freeing items | 0.000050 |

| logging slow query | 0.000006 |

| logging slow query | 0.000063 |

| Opening table | 0.000075 |

| System lock | 0.000046 |

| cleaning up | 0.000008 |

+--------------------------------+----------+

2000025 rows in set (4.78 sec)

executingが100万回くらい発生している。

これというのも相関サブクエリは`外側のクエリを先に評価して'から`内側のクエリを毎回評価する'のが原因らしい。

（FROM句のサブクエリの時は`内側が先'だった）

100万行のt1をフェッチする。

num = 1の行を評価する為に、tt1をさらうクエリを実行(executing)

tt1をさらった結果を使って(Sending data) t1のnum = 1の行がマッチするかどうか判定。

num = 2の行を評価する為に ..

と、外側クエリの行数だけ内側クエリが実行される。

いくら内側クエリがPRIMARY KEYのレンジスキャンで10行しか返さないといっても、

100万回も反復問い合わせしてればそれは遅い罠。。ってことで。

FROM句サブクエリはDERIVEDだから一度にどかんで済んだけれど、

DEPENDENT SUBQUERYは反復になるので外側クエリが大きくなればなるほど地獄が見える。

こういうのはパズルだと思ってJOINに書き換えるしかない。

mysql> SELECT t1.* FROM t1 LEFT JOIN tt1 ON t1.val = tt1.val WHERE tt1.num BETWEEN 1 AND 10;

+-----+----------------------------------+

| num | val |

+-----+----------------------------------+

| 1 | c4ca4238a0b923820dcc509a6f75849b |

| 2 | c81e728d9d4c2f636f067f89cc14862c |

| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |

| 4 | a87ff679a2f3e71d9181a67b7542122c |

| 5 | e4da3b7fbbce2345d7772b0674a318d5 |

| 6 | 1679091c5a880faf6fb5e6087eb1b2dc |

| 7 | 8f14e45fceea167a5a36dedd4bea2543 |

| 8 | c9f0f895fb98ab9159f51fd0297e236d |

| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |

| 10 | d3d9446802a44259755d38e6d163e820 |

+-----+----------------------------------+

10 rows in set (1.49 sec)

mysql> show profile;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000025 |

| Waiting for query cache lock | 0.000008 |

| checking query cache for query | 0.000053 |

| checking permissions | 0.000006 |

| checking permissions | 0.000008 |

| Opening tables | 0.000022 |

| System lock | 0.000016 |

| init | 0.000022 |

| optimizing | 0.000015 |

| statistics | 0.000047 |

| preparing | 0.000047 |

| executing | 0.000010 |

| Sending data | 1.489763 |

| end | 0.000130 |

| query end | 0.000125 |

| closing tables | 0.000018 |

| freeing items | 0.000064 |

| logging slow query | 0.000006 |

| logging slow query | 0.000057 |

| Opening table | 0.000020 |

| System lock | 0.000046 |

| cleaning up | 0.000009 |

+--------------------------------+----------+

22 rows in set (0.08 sec)

mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN tt1 ON t1.val = tt1.val WHERE tt1.num BETWEEN 1 AND 10;

+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------+

2 rows in set (0.00 sec)

valにINDEX作ってないからjoin bufferになってるけど、Sending dataが1回だけになってるしずっと高速化している。

valにINDEX作ってやったら結果が返ってくるまでが0.00secまで落ちたので、多分これが正解。

相関サブクエリの方が直感的に意図が理解しやすいんだけどね。。

2012/07/17

coreファイルはスパースファイル

別にmysqldに限った話でもなく。

バグを内包してcoreをガスガス吐く様な環境でも、
意外とDISK容量が保った記憶がある。

# ps auxww | grep [/]usr/sbin/mysqld
mysql 1876 0.1 7.1 3242980 279436 ? Sl 15:15 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/error.log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306

VSZが3GBちょい、RSSが250MBちょい。

# df -k /var/lib/mysql

Filesystem 1K-ブロック使用使用可使用% マウント位置

/dev/mapper/VolGroup-lv_root

48964432 15636688 30840448 34% /

core吐かせる前の容量。

# ll core*

-rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core.1876

VSZと似たり寄ったりのcoreサイズ。

# ll -s core*

276020 -rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core.1876

# df -k /var/lib/mysql

Filesystem 1K-ブロック使用使用可使用% マウント位置

/dev/mapper/VolGroup-lv_root

48964432 15913660 30563476 35% /

使用ブロックは270MB弱、RSSと似たり寄ったりのサイズしか使用しない。

これはゼロが連続している未割り当て領域(スパース領域)を

かっ飛ばして書き込んでいるから。らしい。

# cp -ip --sparse=never core.1876 core

# ll -s core*

3079452 -rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core

276020 -rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core.1876

スパースさせずにきっちりコピーする(--sparse=never)と、ちゃんと3GBくらい容量を食う。

# df -k /var/lib/mysql

Filesystem 1K-ブロック使用使用可使用% マウント位置

/dev/mapper/VolGroup-lv_root

48964432 18992164 27484972 41% /

うむ。

# ps auxww | grep [/]usr/sbin/mysqld

mysql 2053 2.2 26.3 3841768 1032488 ? Sl 15:23 0:14 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/error.log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306

中でごにょごにょやって1GBくらいRSSを食わせる。

# ll -s core*

3079452 -rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core

276020 -rw------- 1 mysql mysql 3153354752 7月 17 15:23 2012 core.1876

1027692 -rw------- 1 mysql mysql 3753398272 7月 17 15:35 2012 core.2053

だよね。

innodb_flush_method=O_DIRECTはどうなのか

VirtualBox(4CPU + 4096MB Memory + ホストI/Oキャッシュ使用)でCent6.2で検証。

tpcc-mysqlでWH=1、ramp-up 0、measuring time 120というお手軽っぷり。

-- innodb_flush_method指定なし
2156.0 TpmC
2175.5 TpmC
2116.5 TpmC
2172.0 TpmC

-- innodb_flush_method=O_DIRECT指定
2063.0 TpmC
2051.5 TpmC
2074.0 TpmC
2110.0 TpmC

HDDの時は

`誤差か？と言われればそうかも知れない様な

比率で考えると誤差にしては大きい様な

…というレベルで劣化する'

という体感は正しかったっぽい。

http://d.hatena.ne.jp/sh2/20101205

↑こちらの方の方がはるかに丁寧です。

追伸。
ext4での結果なのです。ext3でやると差が縮まるかも。

2012/07/14

MySQLのFROM句サブクエリの順番

mysql> SELECT sql_no_cache * FROM t1 WHERE num = 100;

+-----+----------------------------------+
| num | val |
+-----+----------------------------------+
| 100 | f899139df5e1059396431415e770c6dd |
+-----+----------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE;

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000091 |
| Opening table | 0.000023 |
| System lock | 0.000351 |
| checking permissions | 0.000011 |
| Opening tables | 0.000019 |
| System lock | 0.000017 |
| init | 0.000107 |
| optimizing | 0.000019 |
| statistics | 0.000280 |
| preparing | 0.000025 |
| executing | 0.000007 |
| Sending data | 0.000016 |
| end | 0.000010 |
| query end | 0.000008 |
| closing tables | 0.000009 |
| freeing items | 0.000046 |
| logging slow query | 0.000006 |
| cleaning up | 0.000008 |
+----------------------+----------+
18 rows in set (0.00 sec)

まずはサブクエリなし。Primary Keyの定数検索なので当然速い。

PROFILEの結果貼るまでもない感じだけど比較用。

mysql> SELECT sql_no_cache * FROM (SELECT * FROM t1) AS dummy WHERE num = 100;

次いでFROMの中身をサブクエリに書き換える。

とても遅い。

ここで最初の順番の話にに立ち返る。

まずはサブクエリ .. SELECT * FROM t1が展開される。100万行に対する振るフェッチ。

その100万行に対してPRIMARYのクエリ、SELECT .. FROM .. WHERE ..が実行される。

サブクエリの結果にはINDEXが無いので、これもまるまるフェッチ。

合計200万行フェッチしている算段。

EXPLAINで確認できる。

mysql> EXPLAIN SELECT sql_no_cache * FROM (SELECT * FROM t1) AS dummy WHERE num = 100;

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000521 | |

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

2 rows in set (0.74 sec)

EXPLAINじゃない方のPROFILIE。

mysql> SHOW PROFILE;

+---------------------------+----------+
| Status | Duration |
+---------------------------+----------+
| starting | 0.000079 |
| Opening table | 0.000021 |
| System lock | 0.000100 |
| checking permissions | 0.000009 |
| Opening tables | 0.000017 |
| System lock | 0.000059 |
| optimizing | 0.000008 |
| statistics | 0.000011 |
| preparing | 0.000010 |
| executing | 0.000006 |
| Sending data | 0.079637 |
| converting HEAP to MyISAM | 0.036832 |
| Sending data | 0.484636 |
| init | 0.000032 |
| optimizing | 0.000012 |
| statistics | 0.000012 |
| preparing | 0.000018 |
| executing | 0.000006 |
| Sending data | 0.098403 |
| end | 0.000095 |
| query end | 0.000057 |
| closing tables | 0.000034 |
| removing tmp table | 0.023328 |
| closing tables | 0.000029 |
| freeing items | 0.000050 |
| logging slow query | 0.000006 |
| cleaning up | 0.000007 |
+---------------------------+----------+
27 rows in set (0.01 sec)

Sending data = 行のフェッチが2回発生していることと、
テンポラリテーブルがメモリ(この環境の設定では16M)に乗り切らずにMyISAM変換(DISKへの書込み)までかかってる。

泣きっ面に蜂状態。。

気を取り直してクエリを書き換える。

mysql> SELECT sql_no_cache * FROM (SELECT * FROM t1 WHERE num = 100) AS dummy;

+-----+----------------------------------+

| num | val |

+-----+----------------------------------+

| 100 | f899139df5e1059396431415e770c6dd |

+-----+----------------------------------+

1 row in set (0.00 sec)

サブクエリに書いてあったWHERE句を外側のクエリに移す。

サブクエリが遅い時に試す有名な手段。。だと思う。劇的に速い。

まずはSELECT * FROM t1 WHERE ..が展開される。

Primary Keyへの定数アクセスで1行が返る。

この1行に対してSELECT .. FROM ..が適用されて、1行をフェッチする。

合計2行のフェッチで済む様になった。

mysql> EXPLAIN SELECT sql_no_cache * FROM (SELECT * FROM t1 WHERE num = 100) AS dummy;

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

| 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | | 1 | |

+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

2 rows in set (0.00 sec)

EXPLAINじゃない方のPROFILE。
フェッチ動作は2回あるけど、十分速い。

mysql> SHOW PROFILE;

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000085 |
| Opening table | 0.000022 |
| System lock | 0.000082 |
| checking permissions | 0.000010 |
| Opening tables | 0.000019 |
| System lock | 0.000058 |
| optimizing | 0.000012 |
| statistics | 0.000045 |
| preparing | 0.000012 |
| executing | 0.000007 |
| Sending data | 0.000031 |
| init | 0.000011 |
| optimizing | 0.000006 |
| statistics | 0.000008 |
| preparing | 0.000008 |
| executing | 0.000006 |
| Sending data | 0.000044 |
| end | 0.000007 |
| query end | 0.000008 |
| closing tables | 0.000006 |
| removing tmp table | 0.000010 |
| closing tables | 0.000010 |
| freeing items | 0.000099 |
| logging slow query | 0.000011 |
| cleaning up | 0.000007 |
+----------------------+----------+
25 rows in set (0.00 sec)

テンポラリテーブルは作ってるけどオンメモリだし1行だけなので全然痛くない。

というかFROM句サブクエリは結構JOINに書き換えられるのでJOINでやった方が往々にして速い。。

今回はFROM句サブクエリだけれど、

相関サブクエリはrowsの値を足し算じゃなくて掛け算になるから、

気を遣わないと加速度的に評価行数が増えて大変なことになる。

それはまた時間があれば。。

【2012/07/20】
相関サブクエリ（WHERE句のサブクエリ）の解析はこちら。

2012/07/09

thread_cacheの性能

前にstraceでmmap覗いた時からちょこちょこ気になってたので、

threadの生成時間にどれくらいかかるのよってのを見てみた。

投げ遣りな負荷プログラムを書く。

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

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use Time::HiRes;

for (my $n = 1; $n < 8; $n++) { fork() };

my $tStTime = Time::HiRes::time;

my $oConn = DBI->connect("DBI:mysql:d1:localhost","root") or die print $!;

my $tEdTime = Time::HiRes::time;

print STDOUT ($tEdTime - $tStTime),"\n";

sleep();

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

これで128連続でMySQLにConnectだけしてくれるので、平均値を計算。

見ての通りコネクトしかしていないので云々。

thread_cache = 0 .. 平均0.49sくらい。

thread_cache = 250 .. 1回目(キャッシュなし)は0.54sくらい、2回目以降(キャッシュが作られてる)は0.45sくらい。

ついでに、mixiではskip-grant-tablesを使って少しでも速度を稼いでるという話を目にしたのでやってみる。

thread_cache = 0 .. 平均0.43sくらい。

thread_cache = 250 .. 1回目は0.43sくらい、2回目以降は0.38sくらい。

やっぱり数十ミリ秒の戦いで利く、くらいなのだね、どっちも。

2012/07/07

身に憶えのないDeadlock found when trying to get lock

アプリのコードが`必ず1トランザクションで1つのテーブルしか触ってない'コードなのに

Deadlock found when trying to get lockが検出されることがある。

InnoDBのデッドロック検出にはいくつかパターンがあるけれども、

たとえ1つのテーブルしか存在していなくてもデッドロックとして検出されるケースがあって、

1) あるトランザクションがある行にロックをかける

2) 後続のトランザクションが同じ行にロックをかけようとしてロック待ちになる

2') 2)のようなトランザクションが複数ロック待ち行列に入る

3) 最初の行ロックをリリースする

4) 複数のロック待ちトランザクションのうち、1つだけが有効に処理され、

　その他のトランザクションはデッドロックとして検出される

デッドロックというより、単純なロック競合な訳だけれども、

Deadlock found when trying to get lockで検出されるというお話。

【2013/07/30 19:32】
このレポート(http://bugs.mysql.com/bug.php?id=69835)のコメントで知ったんですが、昔からずっと知られていた(?)んですねこの挙動。

http://bugs.mysql.com/bug.php?id=43210

http://bugs.mysql.com/bug.php?id=43591

2012/07/04

tpcc-mysqlのコンパイル～計測手順メモ

必要なパッケージ(yumで依存関係を追ってインストールされる奴は除く）

・MySQL-server（CentOSのリポジトリ使うなら、mysql-server）
・MySQL-devel（CentOSのリポジトリ使うなら、mysql-devel）
・MySQL-shred（CentOSのリポジトリ使うなら、mysql-libs）
・bzr
・make
・gcc

MySQL-serverが入ってない状態だと、
コンパイルは出来たけど実行しようとするとSegmentation Faultした。

ソースの入手＆コンパイル

$ bzr branch lp:~percona-dev/perconatools/tpcc-mysql
カレントディレクトリのにtpcc-mysqlディレクトリを持ってきてくれる。

$ cd tpcc-mysql/src
$ make
パッケージが全部揃っていればmake一発で済むはず。

$ cd ../ ; ls -l
tpcc_loadがデータのロード用、tpcc_startが計測用のバイナリ。
makeがエラーになってなければあるはず。

データのロード

$ mysqladmin create [DB]
別に名前は何でも良いのでデータ用のDBを作成する。
tpcc_startのデフォルトDB名がtpccなので、それにしておくと後で指定しなくて良い。

$ mysql [DB] < create_table.sql
テーブル定義を読み込む。

$ ./tpcc_load [server] [DB] [user] [pass] [warehouse]
./tpcc_load localhost tpcc root "" 100 みたいな。

大体、warehouse * 500,000行、warehouse * 80メガバイトくらいの容量。
　⇒http://yoku0825.blogspot.jp/2012/06/tpcc-mysqltpcemysql.html

$ mysql [DB] < add_fkey_idx.sql
どっちかというとtpcc_loadよりこっちの処理のが重い。
tpcc_loadより先にこっちをやっちゃうのも可能。
後でINDEX作った方がまだ速いのかなと思うけれども、
DBT-3でやった時に先にINDEX作った方が速いという記事もあった。
いつか試そうと思ってるけどまだ試してない。。

ここでコールドバックアップを取っておく。

tpcc_startをかけるとレコードがINSERT/UPDATE/DELETEされるので、

同じ（ような）条件で計るにはまたtpcc_loadするかリストアするしかない。

mysqldumpだとリストアに時間がかかってしょうがいないのでコールドバックアップ推奨。

　⇒gzip -1だと速いよとかいう話。

$ ./tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file
色々オプションが付けられるけれどなんとなく心で感じて下さい。

あとは計測が終わるのを待つのみ。
複数回計るならリストアしてtpcc_start。
お疲れ様でした。

【2013/05/09 12:06】
CentOSリポジトリのパッケージ名間違えていたぽい。。orz
ところで、MySQL-Serverが入ってないとSegfaultするのはmroongaでもあったこの辺りが関係あるのかなぁ？
http://sourceforge.jp/projects/groonga/lists/archive/dev/2013-May/001358.html

調べる気はない。。

2012/07/03

mysqldにcoredumpを吐かせる

coreを吐かせてもback trace眺めるくらいしかできないけれど、一応。

/etc/my.cnf（あるいはそれに準ずる）のmysqldセクションに
core-file
を追加。

rootで/etc/init.d/mysqldを叩いている環境の場合これだけでは吐かないので、
echo 1 > /proc/sys/fs/suid_dumpable
でsuidしててもcoreを吐くように設定。

/procの下はパワーサイクルで揮発するので、永続化したければ
/etc/sysctl.confに
fs.suid_dumpable=1
を設定しておく。

ここでmysqldを再起動。これでcore吐く様になる。

ただし、このままの設定だとdatadirにメモリ使用量と同じだけのファイルサイズを吐き出すので注意。
（パースファイルだろうから、そのまままるっとストレージを食う訳ではないけれども。一応）

追伸。
ulimit -c unlimited 忘れてた。。
永続化する場合は/etc/security/limits.confに記載。

2012/07/02

MySQL5.5.25の超邪悪なバグ（5.5.25aでFIX）

MySQL5.5.25に巨大なバグが発見された為、5.5.25は差し止められました。

http://dev.mysql.com/doc/refman/5.5/en/news-5-5-25a.html

現状のGA最新版は5.5.24に差し戻されています。

5.5.25を使っている環境の場合、UPDATEのindex_merge関連の処理に不具合があり、
UPDATE文が無限ループしてUNDOセグメントを食いまくり、
InnoDBテーブルスペースが無限に肥大化します（そして、Disk Fullでクラッシュする）

バージョンダウンできない環境の方は、必ずワークアラウンド
(SET GLOBAL optimizer_switch = 'index_merge=off';）しておいた方が良いです。
ほとんどデメリットないと思いますので。万一に備えて。

http://bugs.mysql.com/bug.php?id=65745

【2012/07/12】
2012/7/5リリースの5.5.25aでバグはFIXされてます。

登録: 投稿 ( Atom )