忘れないように記録しとこ

カバの樹

さくらインターネットのレンタルサーバーで突然のDB障害が発生した原因判明！！

完全版を表示する

動作環境

サーバー： さくらインターネット 共用レンタルサーバー プレミアムプラン
Wordpress : 4.5.2

 

頻発するDB障害

以前、さくらインターネットでDB障害が起きるー、という記事を書きました。
以下、そのリンク
https://www.kabanoki.net/879

この記事を書いた以降もDB障害が頻発し、クライアントもどうしたもんかねー、と言う始末。

これは、いい加減に直さなきゃなと思い立ち、早速調査開始！

 

原因は検索フォーム

対象のサイトでは、求人情報を掲載しています。
どうもそこにある求人を検索する機能が怪しいと判明！

 


一見すると、ただの検索フォームに思えます。
しかし、queryを調べてみると、とんでも仕様になっているのが解ります。

以下query

SELECT 
 SQL_CALC_FOUND_ROWS  hoge_posts.ID 
FROM 
 hoge_posts  
INNER JOIN
 hoge_postmeta 
ON
 ( hoge_posts.ID = hoge_postmeta.post_id )  
INNER JOIN
 hoge_postmeta AS mt1 
ON
 ( hoge_posts.ID = mt1.post_id )  
INNER JOIN
 hoge_postmeta AS mt2 
ON
 ( hoge_posts.ID = mt2.post_id )  
INNER JOIN
 hoge_postmeta AS mt3 
ON
 ( hoge_posts.ID = mt3.post_id )  
INNER JOIN
 hoge_postmeta AS mt4 
ON
 ( hoge_posts.ID = mt4.post_id )  
INNER JOIN
 hoge_postmeta AS mt5 
ON
 ( hoge_posts.ID = mt5.post_id )  
INNER JOIN
 hoge_postmeta AS mt6 
ON
 ( hoge_posts.ID = mt6.post_id )  
INNER JOIN
 hoge_postmeta AS mt7 
ON
 ( hoge_posts.ID = mt7.post_id )  
INNER JOIN
 hoge_postmeta AS mt8 
ON
 ( hoge_posts.ID = mt8.post_id )  
INNER JOIN
 hoge_postmeta AS mt9 
ON
 ( hoge_posts.ID = mt9.post_id ) 
WHERE
 1=1  
AND
 ((
    SELECT COUNT(1)
    FROM hoge_term_relationships
    WHERE term_taxonomy_id IN (10)
    AND object_id = hoge_posts.ID
  ) = 1 
  AND 
  (
   SELECT COUNT(1)
   FROM hoge_term_relationships
   WHERE term_taxonomy_id IN (21)
   AND object_id = hoge_posts.ID
			) = 1 
  AND 
  (
   SELECT COUNT(1)
   FROM hoge_term_relationships
   WHERE term_taxonomy_id IN (11)
   AND object_id = hoge_posts.ID
			) = 1 
  AND 
  (
   SELECT COUNT(1)
   FROM hoge_term_relationships
   WHERE term_taxonomy_id IN (12)
   AND object_id = hoge_posts.ID
			) = 1 
  AND 
  (
   SELECT COUNT(1)
   FROM hoge_term_relationships
   WHERE term_taxonomy_id IN (22)
   AND object_id = hoge_posts.ID
			) = 1
) AND ( 
  ( hoge_postmeta.meta_key = 'start_day' AND CAST(hoge_postmeta.meta_value AS CHAR) <= '1469530350' ) AND ( mt1.meta_key = 'end_day' AND CAST(mt1.meta_value AS CHAR) >= '1469530350' ) 
  AND 
  ( mt2.meta_key = 'merritt' AND CAST(mt2.meta_value AS CHAR) LIKE '%交通費支給%' ) 
  AND 
  ( mt3.meta_key = 'merritt' AND CAST(mt3.meta_value AS CHAR) LIKE '%大学生歓迎%' ) 
  AND 
  ( mt4.meta_key = 'merritt' AND CAST(mt4.meta_value AS CHAR) LIKE '%フリーター歓迎%' ) 
  AND 
  ( mt5.meta_key = 'working_days' AND CAST(mt5.meta_value AS CHAR) LIKE '%週３～４日%' ) 
  AND 
  ( mt6.meta_key = 'time_zone' AND CAST(mt6.meta_value AS CHAR) LIKE '%夕方・夜%' ) 
  AND 
  ( mt7.meta_key = 'search-hours' AND CAST(mt7.meta_value AS CHAR) LIKE '%フルタイムの仕事%' ) 
  AND 
  ( mt8.meta_key = 'employment_status' AND CAST(mt8.meta_value AS CHAR) LIKE '%パート%' ) 
  AND 
  ( mt9.meta_key = 'employment_status' AND CAST(mt9.meta_value AS CHAR) LIKE '%アルバイト%' )
) AND hoge_posts.post_type = 'offer' AND (hoge_posts.post_status = 'publish' OR hoge_posts.post_status = 'private') GROUP BY hoge_posts.ID ORDER BY hoge_posts.post_date DESC LIMIT 0, 10

お分かり頂けたでしょうか？
ぱッと見で、吐き気を催すような気持ち悪いqueryです。

何故、こんな仕様になっているのか、担当にインタビュー

 

「Advanced Custom Fields」の検索フォームはダメ、絶対！

担当に確認したところ、このqueryを吐き出しているのは、「Advanced Custom Fields」プラグインだとのこと。
なるほど、それならばこの気持ち悪いqueryも納得だ。

そんなわけあるか！

このプラグインが、多機能な上に見た目重視で今回のような検索フォームを作るような案件とはミスマッチなのは、承知してます。
解っています。

だけど、このqueryはあまりにお粗末でしょう！

同じテーブルを何回JOINしてるんねん！
なんでDATE型の値をchar型に変換してるねん！
なんで値が、サニタイズされてるねん！

ねん！ねん！ねん！

 

重いQueryよ、バイバイ

さて、文句は書いたものの、カスタムフィールドを使った仕様を作り変えるのは面倒なので、queryを自作します。

まず、今回のDB障害の最大の要因となっているのが、JOINの個数です。
今回のqueryでは10個のJOINが行われています。

例えば、Aテーブルが50行、Bテーブルが1000行あるとします。
今回の場合は、

Aテーブルに対して、Bテーブルを10回JOINします。
つまり、

50 X 1000 X 1000 X 1000 X 1000 X 1000 X 1000 X 1000 X 1000 X 1000 X 1000 行

のデータを取得する事になります。

もちろんJOINの際に、条件文があるのでもっと数は減るとは思いますが、
計算するのも面倒なくらいデータを取得しようとしています。

これは、DBも障害を起こすわー

という訳で、JOINを削って１個にします。

完成イメージは、以下の通り


SELECT
 ID
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'start_day' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS start_day
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'end_day' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS end_day
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'merritt' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS merritt
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'working_days' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS working_days
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'time_zone' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS time_zone
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'search-hours' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS `search-hours`
 ,CAST(MAX(CASE hoge_postmeta.meta_key WHEN 'employment_status' THEN hoge_postmeta.meta_value ELSE 0 END) AS CHAR) AS employment_status
FROM
 `hoge_posts` 
LEFT JOIN
 hoge_postmeta
ON
 hoge_postmeta.post_id = hoge_posts.ID
WHERE
 `post_type` = 'offer'
AND
 post_status = 'publish'
GROUP BY
 `hoge_posts`.ID
HAVING
 (start_day <= '1469530350' AND end_day >=  '1469530350')
AND
 (merritt LIKE '%交通費支給%' AND merritt LIKE '%大学生歓迎%' AND merritt LIKE '%フリーター歓迎%')
AND
 (working_days LIKE '%週３～４日%')
AND
 (time_zone LIKE '%夕方・夜%')
AND
 (`search-hours` LIKE '%フルタイムの仕事%')
AND
 (employment_status LIKE '%パート%' AND employment_status LIKE '%アルバイト%')
ORDER BY
 hoge_posts.post_date DESC

現状のカスタムフィールドの状態ですと、びみょーに気持ち悪さが残りますが、こんな感じになります。
行として登録されている値を列に並べ替える感じです。
ポイントは、GROUPした後に、CASEとMAXを使って値を割り出すところです。
型変換はマジ余計

さて、完成型のイメージができたので、queryを生成する側の関数を作ります。
ここを参考にしながら、「functions.php」に関数を記載します。


function search_posts()
{
	global $wpdb;

	$selet = join(',', array(
		'ID'
		,'post_title'
		,'post_excerpt'
		,'post_content'
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'start_day' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS start_day"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'end_day' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS end_day"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'area' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS area"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'merritt' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS merritt"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'working_days' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS working_days"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'time_zone' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS time_zone"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'search-hours' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS `search-hours`"
		,"CAST(MAX(CASE tbkb_postmeta.meta_key WHEN 'employment_status' THEN tbkb_postmeta.meta_value ELSE 0 END) AS CHAR) AS employment_status"
	));

	$sql = $wpdb->prepare(
		"
				SELECT
				 {$selet}
				FROM
				 `{$wpdb->posts}`
				LEFT JOIN
				 `{$wpdb->postmeta}`
				ON
				 `{$wpdb->postmeta}`.`post_id` = `{$wpdb->posts}`.`ID`
				WHERE
				 `post_type` = 'offer'
				AND
				 `post_status` = 'publish'
				GROUP BY
				 `{$wpdb->posts}`.`ID`
				HAVING
				 `start_day` <= %s AND `end_day` >=  %s

				 {having}

				ORDER BY
				 `{$wpdb->posts}`.`post_date` DESC
			"
		, current_time( 'timestamp' )
		, current_time( 'timestamp' )
	);


	$sql = str_replace('{having}', having() === '' ? '' : 'AND '.having(), $sql);

	return $sql;
}

function having()
{
	global $wpdb;
	$havings = array();

　　　　// タクソノミー型のkeyを記述
	$type_tax = array('area');
　　　　// 上記以外を記述
	$type_text = array('merritt', 'working_days', 'time_zone', 'search-hours', 'employment_status');

	if(!isset($_GET['s']))
		return '';

	foreach($_GET AS $key => $value)
	{
		if(in_array($key, $type_tax))
		{
			foreach($value AS $meta_value)
			{
				$tax_id = get_term_by('slug', $meta_value, $key)->term_taxonomy_id;

				if($tax_id === FALSE)
					continue;

				$esc_meta_value = $wpdb->esc_like($tax_id);
				$like_meta_value = '%"' . $esc_meta_value . '"%';

				$sql[$key][] = $wpdb->prepare(
					"`{$key}` LIKE %s"
					,$like_meta_value
				);
			}

			$havings[$key] = '('.join(' AND ', $sql[$key]).')';
		}
		else if(in_array($key, $type_text))
		{
			foreach($value AS $meta_value)
			{
				$esc_meta_value = $wpdb->esc_like($meta_value);
				$like_meta_value = '%' . $esc_meta_value . '%';

				$sql[$key][] = $wpdb->prepare(
					"`{$key}` LIKE %s"
					,$like_meta_value
				);
			}

			$havings[$key] = '('.join(' AND ', $sql[$key]).')';
		}
		else if($key == 's')
		{
			if(empty($value))
				continue;

			$esc_meta_value = $wpdb->esc_like($value);
			$like_meta_value = '%' . $esc_meta_value . '%';

			$havings[$key] = $wpdb->prepare(
				"(
				  `{$wpdb->posts}`.`post_title` LIKE %s
				  OR `{$wpdb->posts}`.`post_excerpt` LIKE %s
				  OR `{$wpdb->posts}`.`post_content` LIKE %s
				)"
				, $like_meta_value
				, $like_meta_value
				, $like_meta_value
			);
		}

	}

	return join(' AND ', $havings);
}

これでqueryが生成できると思います。

 

8/10 問題なし

これらの処理を設定してから、DB障害の発生がパッタリと無くなりました。
今のところ問題ないです。

今回の教訓としては、仕様に沿って適切なプラグインを選択しようってことですね。

勘違いしてはいけないのが、「Advanced Custom Fields」は良いプラグインです。

解りやすくて、使い易いです。

しかし、状況によってはバッドチョイスになります。

そこら辺は、しっかりテストしていきましょーよ。

今日は、こんなところでー

 

  • B!