mysql 통계자료 이전을 위한 agent 자료 구축

약 5천만건 이상의 자료중 agent 정보가 있은 http_user_agent 필드에 인덱스가 걸려있지 않아서 운영서버 작업이 불가능한 상태.

TEST 서버에 DB를 와 TABLE을 다시 구축한후 작업에 필요한 필드에 인덱스를 건 후 자료를 밀어넣었더니 이틀정도의 시간이 소요되더군요. ㅡ.ㅡa

그 후 agent를 uniq로 뽑아 낸 후 php에서 제공하는 get_browser(); 로 agent를 정보를 갱신하는 작업을 진행했습니다.

막노동이 따로없군요.

mysql 자체에서는 agent정보를 기초로 브라우저, 버전, 플랫폼, 모바일, 크롤러 정보를 뽑아주는 기능이 있는지 검색을 열심히(!) 해 봤지만 없더군요.

INSERT INTO sta.agent ( agent_md5, agent_name )
  SELECT md5(a.http_user_agent), a.http_user_agent
  FROM p.analy AS a
  WHERE a.http_user_agentt IS NOT NULL
    AND a.http_user_agent != ''
    AND a.http_user_agentt NOT IN ( SELECT agent_name FROM sta.agent )
  GROUP BY a.http_user_agentt

#!/opt/lampp/bin/php -q
<?php
  $i = 1;
  while(1)
  {  
    $mysqli = @mysqli_connect('localhost', 'pickup', 'pick123$%^', 'sta');
    $qry = "SELECT agent_idx, agent_name FROM sta.agent WHERE browser_name IS NULL ";
    if(! empty($agent_idx)) $qry .= " AND agent_idx =----> {$agent_idx}";
    $qry .= " LIMIT 1000";
    $rs = $mysqli->query($qry) or die(__LINE__.' : '.$mysqli->error."\n");

    if($mysqli->affected_rows < 1)
    {
      echo $qry."\n";
      echo "fatch rows : ".$mysqli->affected_rows."\n";
      break;
    }
    
    $sql = '';
    while($row = $rs->fetch_assoc())
    {
      $agent = (! empty($row['agent_name']))?get_browser($row['agent_name'], TRUE):Array();
      $sql .= "UPDATE agent SET 
            browser_name='{$agent['browser']}', 
            browser_version='{$agent['version']}',
            platform_name='{$agent['platform']}',
            mobile='{$agent['ismobiledevice']}',
            crawler='{$agent['crawler']}'
           WHERE agent_idx = {$row['agent_idx']}
      ;";
      $agent_idx = $row['agent_idx'];
      if($i%100 == 0){
        $sql = str_replace("=''", '=NULL', $sql);
        $sql = str_replace("\n", '', $sql);
        $sql = str_replace("  ", '', $sql);
        $sql = str_replace(";", ";\n", $sql);
        $mysqli->multi_query( $sql );
        echo "{$i}({$agent_idx}) fatch ".$mysqli->affected_rows."\n";
//        echo $sql;
        $sql = '';
      }
      $i++;
    }
    $rs->free_result();
    $mysqli->close();
    echo "agent idx : {$agent_idx} fatch executed !!\n";
  }
/* 
* End of file 
*/