MySQLがレプリケーション遅延がALTERで治った

パフォーマンス MySQL

ある特定のテーブルに対するレプリケーションの遅延時間が、ここ数ヶ月間どんどん長くなり、場合によっては10分以上（！？）という状態になっていて困っていました。


データ量や更新頻度は、テーブルを作成した時とほぼ同じなのにどうして！と思って調べていました。

OPTIMIZE TABLEコマンドが使えそう

該当のこのテーブルは、他のテーブルと比較して、以下のような特徴があります。

  • データ量は多い
  • INDEXデータサイズも大きい
  • 更新頻度もかなり多い


このあたりが、レプリケーション遅延に影響しているのだろうと思い、調べていると以下のような情報を見つけました。

optimize tableでテーブルを最適化するだけでMyISAMはパフォーマンスが格段にアップするらしい（特にデータ更新が頻繁なテーブルの場合）。

MySQLとオープンソースに捧げる毎日:MySQLの管理など - livedoor Blog（ブログ）

はい。更新はかなり頻繁にあります。

時間がたつにつれて、テーブルに多くの変更が加わると、インデックスの効率が悪くなることがある。

  • 最も良く発生しそうな問題は以下の２つ。
    • ディスク上をデータブロックが移動することによるフラグメント化(断片化)
    • インデックス統計情報が不正確になること
  • 対応方法
    • MyISAMテーブルの場合、インデックスデータを最適化するのは簡単
      • OPTIMIZE TABLEコマンドを使用して、テーブルにインデックスを付け直すことができる。
      • MySQLは、テーブル内のすべてのレコードを読み直し、すべてのインデックスをつくり直す。その結果、良好な統計情報を備え、緊密にパックされたインデックスができる。
MySQLパフォーマンスチューニングのためのインデックスの基礎知識

確かに、INDEXデータサイズが作成当時よりも（3割ほど）大きくなってます。


と思いきや、ALTER TABLEしたほうがよさそう

ってことで、OPTIMIZE TABLEコマンド使えば、解決か！と思って検証しようとしましたが、ある日突然、遅延時間がほぼ無くなりました！


確認したところ、該当テーブルにカラム追加したことが解決した原因でした。つまり、「ALTER TABLE」したら治ったということです。不思議に思って、調べてみると、

最適化したい場合にはOPTIMIZE TABLEコマンドを使うのだが、これらのコマンドはWRITEだけでなくREADもブロックしてしまう。従って、メンテナンス中にWRITEは出来なくてもREADだけは可能にしたい、というような場合には、まずはALTER TABLEを試して見るといいだろう。テーブル定義の変更をせずに、テーブルを再作成したい場合には、次のようにALTER TABLEコマンドを実行するといい。

漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。


おおなんと！ALTER TABLEにも同じ効果があるとは！！しかも、READをブロックしない分、適用できるシーンが多そうではないですか！！！

mysql> ALTER TABLE user_table ENGINE MyISAM;


とかって感じでやると、

このようにストレージエンジンを指定してやると、ALTER TABLEコマンドによってテーブルの再作成が行われるのである。もちろん、ストレージエンジンは元のテーブルと同じものを指定する必要がある。

漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。

ということで、テーブルの定義は変更せずに、ALTER文を実行可能とのこと。もし次回以降、同じ症状が現れたら、この方法を試してみようと思います。

外部CSSの中で画像パスを指定する時の注意

CSS

かなり初歩的なことですが、少しはまってしまったのでメモ。


以下のようにファイルが配置されているケースで、

/sample
  /img/iPad2.jpg
  /css/sample.css
  sample.html


外部CSS（sample.css）から、画像（iPad2.jpg）を参照する場合、以下の書き方では参照できません。

background: url(./img/iPad2.jpg)


上記の書き方では、参照先が「/sample/css/img/iPad2.jpg」になってしまうからです。外部CSS（sample.css）から見た相対パスなのでそうなります。


ですので、以下のように、「../」として、「1つ上の階層のimgディレクトリ」という指定にする必要があります。

background: url(../img/iPad2.jpg)


画像は（httpから始まる）絶対URLで指定したほうがいいかな。

ImageMagickを使って画像をリサイズ

ImageMagick

携帯で撮影された画像を投稿できる機能を実装しようと考えた場合、画像のファイルサイズに気をつけなければなりません。
最近の携帯のデジカメの画像サイズは、500KByteくらいのものが多く、そのまま画面表示すると、容量オーバーで画面自体が表示できない危険性があります。
そこで、ImageMagick*1を使ってサイズ調整することを検討します。


※ここでは、ImageMagick（Ver6.6.7）を利用しています。

基本的なリサイズ

方法はとても簡単で、アップロード画像を「横230px、縦180px」に変換したい場合、以下のコマンドを実行するだけです。
結果を見ると、ファイルサイズが大きく削減され、見た目についても極端に劣化していませんでした。

[root]# /usr/bin/convert Sunset.jpg -resize 230x180\> Sunset_230x180.jpg

[root]# ls -l
 -rw-r--r-- 1 root root 589115  1月 11 18:06 Sunset.jpg
 -rw-r--r-- 1 root root  12174  1月 11 18:08 Sunset_230x180.jpg


※「\>」の記述は、「もし指定サイズより画像が小さい場合はリサイズされない」という記述になります。画像が粗くなるのがいやであれば指定するといいかもしれません。*2

処理時間の改善

便利なImageMagickですが、処理は結構重いのが問題です。


上記のコマンド実行例では、（約600KByteの画像変換に）約1秒かかっています。リサイズパターンが数パターンあると、画像1枚で数秒かかってしまうということになり、単位時間あたりに処理可能な画像の枚数がとても少なくなってしまいます。。


下記のページに、とても参考になる情報が書かれていたので、ご紹介します。

いつもの convert に「-define jpeg:size=...」をつけるだけで10倍速くなる
「ある条件下で高速に」と書きましたが、その条件とは、以下の2つです。

条件1: 元ファイルが JPEG 画像であること
条件2: 元ファイルを開くより前に、変換後の縦横pxが分かっていること

本当は速いImageMagick: サムネイル画像生成を10倍速くする方法 - 昼メシ物語


上記の条件についてですが、

  • 元ファイルが JPEG 画像であること
    • デジカメの画像保存形式は、JPEGなので投稿機能の制限としてしまっても問題ないでしょう。
  • 変換後の縦横pxが分かっていること
    • これも画面仕様として決まっていることがほとんどなので問題ないと思います。


ということで、実際にやってみました。

[root]# /usr/bin/convert 500K.jpg -resize 230x180 500K_230x180.jpg
⇒処理時間：約1秒

[root]# /usr/bin/convert -define jpeg:size=230x180 -resize 230x180 500K.jpg 500K_230x180_fast.jpg
⇒処理時間：約0.1秒


確かに、約10倍速い！！！画質も特に問題なさそうです。素晴らしい。


ちなみに、「条件2: 元ファイルを・・・」についてですが、「縦横両方とも事前に分かっていること」が必要だと思っていましたが、「縦か横の片方だけ」の指定でもうまく動作します。

[root]# /usr/bin/convert -debug Coder -log %e -resize 230x 500K.jpg 500K_230x.jpg
Profile: exif, 5664 bytes
Profile: iptc, 14824 bytes
・・・
Geometry: 1509x2100
・・・

[root]# /usr/bin/convert -debug Coder -log %e -define jpeg:size=230x -resize 230x 500K.jpg 500K_230x_fast.jpg
Profile: exif, 5664 bytes
Profile: iptc, 14824 bytes
・・・
Geometry: 378x525
・・・


上記のようにデバック情報を出力してみると、Geometry という項目が、元画像のサイズよりもかなり小さな値で読み込まれているのがわかります。


若干、注意が必要なのは、このオプションは、1/2以下に圧縮する場合のみ効果があるということです。

  • 1200x900 ⇒ 800x600 : 効果なし（※Geometry: 1200x900）
  • 1200x900 ⇒ 600x450 : 効果有り（※Geometry: 600x450）
  • 1200x900 ⇒ 200x150 : 効果有り（※Geometry: 300x225）


その理由は、このオプションの仕組みにあります。

ImageMagick は、 -define jpeg:size={width}x{height} というオプションが与えられると、実際の画像サイズの 1/2、1/4、1/8 のサイズの中から、オプションで指定されたサイズに近いものを選び、そのサイズとして画像を開きます。どうしてそんなことが可能なのかというと、その仕組はJPEGのデータ構造に由来しています。JPEG画像は、8x8（場合によっては16x16）pxの小さな正方形の画像の集合からなっています。詳しい説明は他の資料に任せますが、この構造を活かして、ImageMagickで利用しているJPEGライブラリのlibjpegでは、1/2、1/4、1/8のサイズへの縮小は高速に計算できるのです。

本当は速いImageMagick: サムネイル画像生成を10倍速くする方法 - 昼メシ物語


つまり、1/2、1/4、1/8のどれかに高速に変換後*3、指定された任意の値に変換するという動作をするため、1/2以下でなければ、このオプションは動作できないということです。

補足

補足1
但し、下記にもある通り、画像処理ソフトは脆弱性がある可能性の高いソフトウェアなので、念のため注意が必要です。

画像処理プログラムには過去、さまざまな脆弱性が発見されてきました。ImageMagickもご多分に漏れず、CVEを検索するだけでも過去に37件の脆弱性が発見されていることが分かります。

画像処理ソフトウェア「ImageMagick」の脆弱性 (1/2)：CodeZine（コードジン）


補足2
リサイズすることを前提とするなら、HTMLでの画像表示の際は、「widthの指定」はなくてもよいと思います。昔のブラウザでは「width,height属性は指定すべき*4」という話もありましたが、今は逆に指定しないほうがよいかもしれません。

それは「画像の差し替え時にHTMLファイルの修正が不要となる」ことです。
サイト上のプロモーション、ユーザーへのアプローチを変えるためにキャッチとなる画像を変更したりということが多々あるのですが、見せ方によって画像のサイズが変わってしまう場合もあります。
レイアウトはそのままで画像のサイズだけが少し変わるというケースがあったとして、その画像（img要素）に対してwidth,height属性を指定してる場合には、画像ファイルの差し替えだけでなくHTMLファイルの方も修正しないといけません。

http://designblog.ecstudio.jp/guideline/img-width-height.html

*1:参考：コマンドラインで画像を縮小する：ImageMagick convert mogrify

*2:ImageMagickの詳しいオプションはコチラを参照⇒ImageMagickオプション（日本語）

*3:面積としては、1/2 X 1/2 = 1/4 , 1/8 X 1/8 = 1/64 のように圧倒的に小さくなり、CPUがメモリを確保する仕事量が大きく削減されることになり、これほどの効果があると考えられます。

*4:レンダリングエラーになるとか、指定しているとレンダリングが速いとかいう話ですね。でも最近のブラウザを考えるとこのあたりはもう考えなくてもよいと思います。

SQLでの日付条件に注意

DB

以下のエントリーを読んでいて、過去に自分もハマッたなあと思ったのでメモ。

ヒビコレショウジン - フレームワーク開発時代のSQLチューニング基礎（２）SQLのやっちゃだめ前編 について確認してみた


※ほとんど引用のみになっちゃいました。。

SELECT empno, ename, hiredate FROM emp
WHERE TO_CHAR(hiredate, 'YYYY/MM/DD') = '2011/02/16';


よくありがちですが、これはHIREDATE列にINDEXがあっても使えません。
列値が関数などで加工されると、INDEX内の列値を使ってマッチングができないからですね。
日付での絞り込みが遅いシステムの原因としてよく見かけます。

これは常識ですね。いつも気をつけています。

でも、この書き方じゃINDEXが使えないのはわかったので、改良してみます。


SELECT empno, ename, hiredate FROM emp
WHERE hiredate = TO_DATE('2011/02/16', 'YYYY/MM/DD');


で、これを実行すると、データが1件もヒットしなかったりします。
原因は、DATE型は年月日 時分秒を持っているため、「年月日」だけの文字列を変換すると、時刻部分は00:00:00扱いになるためです。

ココ！！注意。不具合としてよくありがち。

なので、時刻部分は無視できるようにします。


SELECT empno, ename, hiredate FROM emp
WHERE hiredate >= TO_DATE('2011/02/16 00:00:00', 'YYYY/MM/DD HH:MI:SS')
AND hiredate <= TO_DATE('2011/02/16 23:59:59', 'YYYY/MM/DD HH:MI:SS');


betweenのほうがすっきりするかもしれませんが。
同様に、データ型の暗黙変化でもINDEXは使えなくなるので、文字と数値の比較などを行わないように注意が必要です。

  • 条件指定する列自体を関数で加工するとINDEXが利かないよね。
  • だから、条件指定する値のほうを加工しちゃえ！
  • あれ、正しい条件のはずが、一件もヒットしない（時刻部分は00:00:00扱いだから）


という感じで間違えちゃうんですよね。以前、ほぼ同じ間違いをやって、同じような修正をして記憶が。。。みなさんもお気をつけください。

Pgpool接続を強制切断すると

Pgpool

pgpool.logに下記のようなログがいっぱい出力された〜

ERROR: ・・・ read_startup_packet: incorrect packet length (-465498)
ERROR: ・・・ read_startup_packet: out of memory


これは、接続が不正に接続された場合に出力されるエラーのようで、今回は、TELNETで接続していた接続を、Ctrl+Cで強制切断したから出たみたい。


少しだけソースを調べてみると、Pgpoolの「read_startup_packet」という関数の中で、このエラーは発生しているようでした。
http://www.jonahharris.com/osdb/pgsql/pgpool2/pgpool-II-1.0.1/child.c

static StartupPacket *read_startup_packet(POOL_CONNECTION *cp)
{
	StartupPacket *sp;
	StartupPacket_v2 *sp2;
	int protov;
	int len;
	char *p;

	sp = (StartupPacket *)calloc(sizeof(*sp), 1);
	if (!sp)
	{
		pool_error("read_startup_packet: out of memory");
		return NULL;
	}

	/* read startup packet length */
	if (pool_read(cp, &len, sizeof(len)))
	{
		return NULL;
	}
	len = ntohl(len);
	len -= sizeof(len);

	if (len <= 0)
	{
		pool_error("read_startup_packet: incorrect packet length (%d)", len);
	}

	sp->startup_packet = calloc(len, 1);
	if (!sp->startup_packet)
	{
		pool_error("read_startup_packet: out of memory");
		pool_free_startup_packet(sp);
		return NULL;
	}

	/* read startup packet */
	if (pool_read(cp, sp->startup_packet, len))
	{
		pool_free_startup_packet(sp);
		return NULL;
	}

	sp->len = len;
	memcpy(&protov, sp->startup_packet, sizeof(protov));
	sp->major = ntohl(protov)>>16;
	sp->minor = ntohl(protov) & 0x0000ffff;
	p = sp->startup_packet;

	switch(sp->major)
	{
		case PROTO_MAJOR_V2: /* V2 */
			sp2 = (StartupPacket_v2 *)(sp->startup_packet);

			sp->database = calloc(SM_DATABASE+1, 1);
			if (!sp->database)
			{
				pool_error("read_startup_packet: out of memory");
				pool_free_startup_packet(sp);
				return NULL;
			}
			strncpy(sp->database, sp2->database, SM_DATABASE);

			sp->user = calloc(SM_USER+1, 1);
			if (!sp->user)
			{
				pool_error("read_startup_packet: out of memory");
				pool_free_startup_packet(sp);
				return NULL;
			}
			strncpy(sp->user, sp2->user, SM_USER);

			break;

		case PROTO_MAJOR_V3: /* V3 */
			p += sizeof(int);	/* skip protocol version info */

			while(*p)
			{
				if (!strcmp("user", p))
				{
					p += (strlen(p) + 1);
					sp->user = strdup(p);
					if (!sp->user)
					{
						pool_error("read_startup_packet: out of memory");
						pool_free_startup_packet(sp);
						return NULL;
					}
				}
				else if (!strcmp("database", p))
				{
					p += (strlen(p) + 1);
					sp->database = strdup(p);
					if (!sp->database)
					{
						pool_error("read_startup_packet: out of memory");
						pool_free_startup_packet(sp);
						return NULL;
					}
				}
				p += (strlen(p) + 1);
			}
			break;

		case 1234:		/* cancel or SSL request */
			/* set dummy database, user info */
			sp->database = calloc(1, 1);
			if (!sp->database)
			{
				pool_error("read_startup_packet: out of memory");
				pool_free_startup_packet(sp);
				return NULL;
			}
			sp->user = calloc(1, 1);
			if (!sp->user)
			{
				pool_error("read_startup_packet: out of memory");
				pool_free_startup_packet(sp);
				return NULL;
			}
			break;

		default:
			pool_error("read_startup_packet: invalid major no: %d", sp->major);
			pool_free_startup_packet(sp);
			return NULL;
	}

	pool_debug("Protocol Major: %d Minor: %d database: %s user: %s", 
			   sp->major, sp->minor, sp->database, sp->user);

	return sp;
}

Pgpoolのプロセスについて

Pgpool

　

PostgreSQLのプロセスを確認しようとしてpsコマンド打ったら、以下のようなプロセスが大量に出てビビッてしまった。。でもよく見ると、これは「pgpool」のプロセスじゃないか。。起動ユーザが「postgres」というOSユーザなだけｗ

[root]# ps -aef | grep postgres
postgres 32228 13092・・・        00:00:00 pgpool: wait for connection request
・・・
postgres 32404 13092・・・        00:00:00 pgpool: wait for connection request


以上、終わり。

では、あれなので、この機会にpgpoolのプロセス*1について、少し整理しておきたいと思います。

PostgreSQL側から見た最大同時接続数はnum_init_children * max_poolとなるが、クライアント側から見た最大同時接続数はnum_init_childrenとなる。

http://www.mono-space.net/doc/pgpool.html

num_init_childrenについて

重要なことは、上記引用にも挙げましたが、DBとしての同時接続数は、「num_init_children」で設定した値によって決まるということです。以下、ポイントの整理です。

  • pgpoolを起動すると、num_init_children分の子プロセスが作成される。*2
  • 生成された各子プロセスは、(PHPアプリケーションなどの)クライアントからの接続を待つ。
  • クライアントから接続要求があると、子プロセスはPostgreSQLと接続する。
  • １つの子プロセスでは１クライアント分の接続のみ可能で、要求が同時に複数来た場合は、複数の子プロセスで対応する。

max_poolについて

前回の接続要求と、ユーザ名、データベース名が同じならば、各子プロセスが保持している接続を使いまわします。

  • クライアントから接続が切断される際、子プロセスからPostgreSQLへの接続は保持される。
  • ユーザ名、データベース名が同じならば、接続が使いまわされる。
  • もし、異なるユーザ名、データベース名ならば、新たにPostgreSQLとの接続を行う。

*1:このページの図解がとても参考になります。http://www.mono-space.net/doc/pgpool.html

*2:この動作により、冒頭のように「pgpool: wait for connection request」が数百個というレベルで出現します。

PHPでUnixタイムスタンプをミリ秒で取得

PHP

PHPで、ミリ秒の取得する関数って無いのね。。。microtime()でやるしかないか。

  • time() 秒単位で取得
  • microtime() マイクロ秒単位で取得


これだと扱いにくい。*1

echo microtime();
0.74204900 1297317486　←　[マイクロ秒](スペース)[秒]　という形式


とりあえず、浮動小数点形式に。（使えるのはPHP5.0以降）

echo microtime(true);
1297317505.0932


1000かけて

echo microtime(true)*1000;
1297317527957.5


（切り上げて）整数形式に。

echo ceil(microtime(true)*1000);
1297317547686


おしまい。

*1:この形式で扱う方法も紹介されています。phpでミリ秒を求めるには、microtime()を利用する - くらげのChangeLog