Home | ArchiveList | Category | About
Home > PC > トラブル > Moguraでの表示速度低下への対応(2)
前回はアクセス解析が重くなってきたなー、と感じたら手動でphpMyAdminからゴミ箱アイコンで osaka_ua テーブルのデータをTruncateする、といった事を行ってたんですが、それすらも面倒になってきたので思い切った施策をしました。

要は osaka_ua テーブルにデータが追加・変更されなければ重くならないので、最初から追加させないようにしていたわけです。アクセス解析で知りたいのは、主にカウント数とリンク元のURLくらいなので、ユーザーエージェント用のテーブルはまぁ空でもいいか、って感じですので。

変更点は下記になります。

初期設定から変えてなければ、実テーブル名は osaka_ua なんですが、ソース内では定数指定してあります(config.phpの140行目を参照)。それらがINSERTやUPDATEされてる所をコメント化してます。

変更部分が太字になります(まぁ最初と最後を /* と */ で囲んでるだけですね)。

//writelog.phpの390行あたり
//UA追加
//2014/02/20 takehana_del_s
/*

$sql = "INSERT INTO ".constant("DB_TABLE_UA")." (ua_name,os,os_v,brow,brow_v) values (".$ua.",'".$ua_list["os"]."','".$ua_list["os_v"]."','".$ua_list["browser"]."','".$ua_list["browser_v"]."');";
$res = $db->query($sql);
WriteClass::check_err($res);
*/
//2014/02/20 takehana_del_e


//control_panel フォルダ内 load_updata_ua.php の9行目あたり
/*=========================================*/
/* UA更新 */
/*=========================================*/

$updata_count = 0;

//2014/02/20 takehana_del_s
/*

$sql = array();
$sql["from"] = constant("DB_TABLE_UA");

$res = $db->query(mk_sql($sql));
check_err($res);

while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)){
if(!$row["ua_name"]) continue;
$ua = array();
list($ua["browser"], $ua["browser_v"], $ua["os"], $ua["os_v"]) = getuseragent($row["ua_name"], "../");
if($row["os"] === $ua["os"] && $row["os_v"] === $ua["os_v"] && $row["brow"] === $ua["browser"] && $row["brow_v"] === $ua["browser_v"]){
continue;
}
$sql = "UPDATE ".constant("DB_TABLE_UA")." SET ".
($row["os"] === $ua["os"] ? '' : "os = '".$ua["os"]."',").
($row["os_v"] === $ua["os_v"] ? '' : "os_v = '".$ua["os_v"]."',").
($row["brow"] === $ua["browser"] ? '' : "brow = '".$ua["browser"]."',").
($row["brow_v"] === $ua["browser_v"] ? '' : "brow_v = '".$ua["browser_v"]."',");
$sql = substr($sql, 0, -1)." WHERE ua_id = ".$row["ua_id"]." LIMIT 1;";
$sql = @mb_convert_encoding($sql, "UTF-8", "ASCII,JIS,UTF-8,EUC-JP,SJIS");
$res2 = $db->query($sql);
check_err($res2);
$row["os"] = $ua["os"];
$row["os_v"] = $ua["os_v"];
$row["brow"] = $ua["browser"];
$row["brow_v"] = $ua["browser_v"];
$form_val[] = $row;
$updata_count++;
}
$res->free();
*/
//2014/02/20 takehana_del_e


とりあえずテーブル osaka_ua にはデータが追加されなくなったので、けっこう処理が軽くなったのではないでしょうか。これで自分的に望んだ挙動になったので、このまま運用してみます。


この記事へのコメント
Please JavaScript ON for comment. %3c%61%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%22%3e%3c%2f%61%3e %3c%66%6f%72%6d%20%6d%65%74%68%6f%64%3d%22%70%6f%73%74%22%20%61%63%74%69%6f%6e%3d%22%23%6e%75%63%6c%65%75%73%5f%63%66%22%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%61%63%74%69%6f%6e%22%20%76%61%6c%75%65%3d%22%61%64%64%63%6f%6d%6d%65%6e%74%22%20%2f%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%75%72%6c%22%20%76%61%6c%75%65%3d%22%69%6e%64%65%78%2e%70%68%70%3f%69%74%65%6d%69%64%3d%36%34%39%38%26%61%6d%70%3b%63%61%74%69%64%3d%34%32%22%20%2f%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%69%74%65%6d%69%64%22%20%76%61%6c%75%65%3d%22%36%34%39%38%22%20%2f%3e %09 %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6e%61%6d%65%22%3e%4e%61%6d%65%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%75%73%65%72%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%34%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6e%61%6d%65%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6d%61%69%6c%22%3e%55%52%4c%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%75%73%65%72%69%64%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%36%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6d%61%69%6c%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%65%6d%61%69%6c%22%3e%4d%61%69%6c%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%65%6d%61%69%6c%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%31%30%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%65%6d%61%69%6c%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%62%6f%64%79%22%3e%43%6f%6d%6d%65%6e%74%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%74%65%78%74%61%72%65%61%20%6e%61%6d%65%3d%22%62%6f%64%79%22%20%20%63%6c%61%73%73%3d%22%63%6f%6d%6e%61%6d%65%22%20%63%6f%6c%73%3d%22%35%35%22%20%72%6f%77%73%3d%22%31%30%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%62%6f%64%79%22%3e%3c%2f%74%65%78%74%61%72%65%61%3e %09%09%3c%62%72%2f%3e%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%63%68%65%63%6b%62%6f%78%22%20%6e%61%6d%65%3d%22%6e%6f%74%73%70%61%6d%22%20%76%61%6c%75%65%3d%22%36%34%39%38%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%73%70%61%6d%22%20%63%68%65%63%6b%65%64%3d%22%63%68%65%63%6b%65%64%22%20%2f%3e%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%73%70%61%6d%22%3eチェックを外してください%3c%2f%6c%61%62%65%6c%3e %3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%74%69%6d%65%72%22%20%76%61%6c%75%65%3d%22%31%35%37%31%30%32%32%30%30%36%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%74%69%6d%65%72%22%20%2f%3e %09%09%3c%62%72%20%2f%3e%3c%62%72%20%2f%3e %09%09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%63%68%65%63%6b%62%6f%78%22%20%76%61%6c%75%65%3d%22%31%22%20%6e%61%6d%65%3d%22%72%65%6d%65%6d%62%65%72%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%72%65%6d%65%6d%62%65%72%22%20%20%2f%3e %09%09%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%72%65%6d%65%6d%62%65%72%22%3e%53%61%76%65%20%59%6f%75%72%20%49%6e%66%6f%3c%2f%6c%61%62%65%6c%3e %09%09%3c%62%72%20%2f%3e %09%09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%73%75%62%6d%69%74%22%20%61%6c%74%3d%22%41%64%64%20%43%6f%6d%6d%65%6e%74%22%20%76%61%6c%75%65%3d%22%41%64%64%20%43%6f%6d%6d%65%6e%74%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%62%75%74%74%6f%6e%22%20%2f%3e %09%09%3c%62%72%20%2f%3e %3c%2f%66%6f%72%6d%3e
Home | PageTop | RSS2.0 | ATOM