昨年、め組のVPS に移行した後、チマチマとパフォーマンスチューニングを行ってきたのですが、そこで得られた情報をシェアしておきます。
VPS に移行したことで共用レンタルサーバでは弄れなかった MySQL や Apache の設定なんかも弄れるようになったので、MySQL で実行に時間のかかっているクエリーをログに残してチューニングするようなことも手軽にできます。
スロークエリログの取得方法は、以下を参考に。
漢(オトコ)のコンピュータ道: MySQL 5.1のスロークエリログ
さて、このブログでの関連記事表示には Yet Another Related Posts Plugin を使用しています。
実行に2秒以上かかったクエリーをログに残したところ Yet Another Related Posts Plugin が生成したクエリーがログに記録されたので、これを修正しました。
書き換えた箇所は、関連投稿を取得するためのクエリーを生成している magic.php の yarpp_sql() 関数。
ちなみに使用しているバージョンは 3.2b3 です。
function yarpp_sql($type,$args,$giveresults = true,$reference_ID=false,$domain='website') {
global $wpdb, $post, $yarpp_debug, $yarpp_cache;
if (is_object($post) and !$reference_ID) {
$reference_ID = $post->ID;
}
// set the "domain prefix", used for all the preferences.
if ($domain == 'rss')
$domainprefix = 'rss_';
else
$domainprefix = '';
$options = array('limit'=>"${domainprefix}limit",
'threshold'=>'threshold',
'show_pass_post'=>'show_pass_post',
'past_only'=>'past_only',
'cross_relate'=>'cross_relate',
'body'=>'body',
'title'=>'title',
'tags'=>'tags',
'categories'=>'categories',
'distags'=>'distags',
'discats'=>'discats',
'recent_only'=>'recent_only',
'recent_number'=>'recent_number',
'recent_units'=>'recent_units');
$optvals = array();
foreach (array_keys($options) as $option) {
if (isset($args[$option])) {
$optvals[$option] = stripslashes($args[$option]);
} else {
$optvals[$option] = stripslashes(stripslashes(yarpp_get_option($options[$option])));
}
}
extract($optvals);
// Fetch keywords
$body_terms = $yarpp_cache->get_keywords($reference_ID,'body');
$title_terms = $yarpp_cache->get_keywords($reference_ID,'title');
if ($yarpp_debug) echo "<!--TITLE TERMS: $title_terms-->"; // debug
if ($yarpp_debug) echo "<!--BODY TERMS: $body_terms-->"; // debug
// get weights
$bodyweight = (($body == 3)?3:(($body == 2)?1:0));
$titleweight = (($title == 3)?3:(($title == 2)?1:0));
$tagweight = (($tags != 1)?1:0);
$catweight = (($categories != 1)?1:0);
$weights = array();
$weights['body'] = $bodyweight;
$weights['title'] = $titleweight;
$weights['cat'] = $catweight;
$weights['tag'] = $tagweight;
$totalweight = $bodyweight + $titleweight + $tagweight + $catweight;
// get disallowed categories and tags
$disterms = implode(',', array_filter(array_merge(explode(',',$discats),explode(',',$distags)),'is_numeric'));
$usedisterms = count(array_filter(array_merge(explode(',',$discats),explode(',',$distags)),'is_numeric'));
$criteria = array();
if ($bodyweight)
$criteria['body'] = "(MATCH (post_content) AGAINST ('".$wpdb->escape($body_terms)."'))";
if ($titleweight)
$criteria['title'] = "(MATCH (post_title) AGAINST ('".$wpdb->escape($title_terms)."'))";
if ($tagweight)
$criteria['tag'] = "tag.tagtax";
// $criteria['tag'] = "COUNT( DISTINCT tagtax.term_taxonomy_id )";
if ($catweight)
$criteria['cat'] = "cat.cattax";
// $criteria['cat'] = "COUNT( DISTINCT cattax.term_taxonomy_id )";
$newsql = "SELECT $reference_ID as reference_ID, ID, "; //post_title, post_date, post_content, post_excerpt,
$newsql .= 'ROUND(0';
foreach ($criteria as $key => $value) {
$newsql .= "+ $value * ".$weights[$key];
}
$newsql .= ',1) as score';
$newsql .= "\n from $wpdb->posts \n";
if ($usedisterms)
$newsql .= " left join $wpdb->term_relationships as blockrel on ($wpdb->posts.ID = blockrel.object_id)
left join $wpdb->term_taxonomy as blocktax using (`term_taxonomy_id`)
left join $wpdb->terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in ($disterms))\n";
if ($tagweight)
$newsql .= " left JOIN (
select tagrel.object_id, COUNT( DISTINCT tagtax.term_taxonomy_id ) as tagtax
from $wpdb->term_relationships AS thistag
left JOIN $wpdb->term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id)
left JOIN $wpdb->term_taxonomy AS tagtax ON (tagrel.term_taxonomy_id = tagtax.term_taxonomy_id AND tagtax.taxonomy = 'post_tag')
where thistag.object_id = $reference_ID
group by tagrel.object_id) as tag
ON (tag.object_id = $wpdb->posts.ID )\n";
// $newsql .= " left JOIN $wpdb->term_relationships AS thistag ON (thistag.object_id = $reference_ID )
// left JOIN $wpdb->term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
// AND tagrel.object_id = $wpdb->posts.ID)
// left JOIN $wpdb->term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
// AND tagtax.taxonomy = 'post_tag')\n";
if ($catweight)
$newsql .= " left JOIN (
select catrel.object_id, COUNT( DISTINCT cattax.term_taxonomy_id ) as cattax
from $wpdb->term_relationships AS thiscat
left JOIN $wpdb->term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id)
left JOIN $wpdb->term_taxonomy AS cattax ON (catrel.term_taxonomy_id = cattax.term_taxonomy_id AND cattax.taxonomy = 'category')
where thiscat.object_id = $reference_ID
group by catrel.object_id) as cat
ON (cat.object_id = $wpdb->posts.ID )\n";
// $newsql .= " left JOIN $wpdb->term_relationships AS thiscat ON (thiscat.object_id = $reference_ID )
// left JOIN $wpdb->term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
// AND catrel.object_id = $wpdb->posts.ID)
// left JOIN $wpdb->term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
// AND cattax.taxonomy = 'category')\n";
// WHERE
$newsql .= " where (post_status IN ( 'publish', 'static' ) and ID != '$reference_ID')";
if ($past_only) { // 3.1.8: revised $past_only option
if ( is_object($post) && $reference_ID == $post->ID )
$reference_post_date = $post->post_date;
else
$reference_post_date = $wpdb->get_var("select post_date from $wpdb->posts where ID = $reference_ID");
$newsql .= " and post_date <= '$reference_post_date' ";
}
if (!$show_pass_post)
$newsql .= " and post_password ='' ";
if ($recent_only)
$newsql .= " and post_date > date_sub(now(), interval $recent_number $recent_units) ";
if ($type == array('page') && !$cross_relate)
$newsql .= " and post_type = 'page'";
else
$newsql .= " and post_type = 'post'";
$newsql .= (($categories == 3)?' and '.$criteria['cat'].' >= 1':'');
$newsql .= (($categories == 4)?' and '.$criteria['cat'].' >= 2':'');
$newsql .= (($tags == 3)?' and '.$criteria['tag'].' >= 1':'');
$newsql .= (($tags == 4)?' and '.$criteria['tag'].' >= 2':'');
// GROUP BY
$newsql .= "\n group by id \n";
// HAVING
// safethreshold is so the new calibration system works.
// number_format fix suggested by vkovalcik! :)
$safethreshold = number_format(max($threshold,0.1), 2, '.', '');
$newsql .= " having score >= $safethreshold";
if ($usedisterms)
$newsql .= " and count(blockterm.term_id) = 0";
// $newsql .= (($categories == 3)?' and '.$criteria['cat'].' >= 1':'');
// $newsql .= (($categories == 4)?' and '.$criteria['cat'].' >= 2':'');
// $newsql .= (($tags == 3)?' and '.$criteria['tag'].' >= 1':'');
// $newsql .= (($tags == 4)?' and '.$criteria['tag'].' >= 2':'');
$newsql .= " order by score desc limit ".$limit;
if (!$giveresults) {
$newsql = "select count(t.ID) from ($newsql) as t";
}
// if we're looking for a X related entries, make sure we get at most X posts and X pages if
// we cross-relate
if ($cross_relate)
$newsql = "($newsql) union (".str_replace("post_type = 'post'","post_type = 'page'",$newsql).")";
if ($yarpp_debug)
echo "<!--$newsql-->";
return $newsql;
}
サブクエリを多用する形に修正したので MySQL のバージョンによっては動作しないかもしれません。
MySQL 5.1.54 では、問題なく動作しました。