게시글이 어느정도 되고, 커뮤니티가 활성화 되면 게시판 검색을 빈번하게 하게 됩니다.
게시판의 검색이 시스템의 부하가 작을 때는 문제가 아니지만, 접속이 많을 때는 심각한 문제가 됩니다.
bbs/list.php의 아래 코드에서 tmp table을 만들고 하는 작업이 생기게 되는데,
tmp table 작업 중에는 해당 테이블에 대한 모든 작업이 중단(lock) 되기 때문이죠.
$sql = " select distinct wr_parent from $write_table where $sql_search $sql_order limit $from_record, $board[bo_page_rows] ";
이 문제 때문에, 게시판의 인덱스도 바꿔보고 이런저런 삽질을 몇년간 했지만 마땅한 답을 못 얻고
할 수 있는 돈 질(disk를 raid로 바꾸고, 서버의 cpu를 바꾸고...)로 대응을 해 왔습니다. ㅠ.ㅠ...
그러다가 2010년 ibm sas 서버로 교체 했슴에도 불구하고 tmp table이 문제가 되어서 시스템이 엄청나게
lock 걸리고 버버버버벅~ 거려서 스트레스가 심했어요. 사실 운영자가 접속하기 힘들 정도였으니까요.
마지막 돈 질 (disk를 sas에서 ssd로 변경) 하기 전에, sql을 바꿀 수 없을까 생각을 한번 더 했습니다.
사실 워낙 단순한 SQL이라서 이것을 바꾼다는게 크게 의미가 있을거 같지도 않았지만 고민을 했어요.
구글링을 하다가 보니, mysql에도 temporary table을 만드는 것이 가능하더라구요.
그래서 위의 SQL을 temporary 테이블을 만들고
그 테이블에서 distinct를 하는 것으로 분리를 하면 어떨까 생각하게 됬습니다.
mysql이 db lock을 거는 것은 temporary 테이블을 만드는 단계 뿐이고,
이후에는 해당 테이블에 대해서 아무런 영향을 안미칠거 같더라구요 (완전히 제 맘대로 추정이었죠 ㅎㅎ)
바로 개발에 돌입해서, 어설프게 SQL을 만들고, 테스트 했습니다.
sql_query(" $sql_tmp"); // 임시 테이블 만들기
sql_query(" $sql_ord"); // distinct 하기
그런데, 이상하게 temporary 테이블이 없다고 우기는 겁니다. 왜??? 왜???
phpmyadmin에서도 2개의 SQL을 따로하면 2번째에서 temporary 테이블이 없다고 합니다.
한번 더 SQL을 디볐더니, connection이 유지될 동안만 temporary 테이블이 존재한다고 하네요.
temporary 테이블이 없다는건느 sql_query를 두번째 부를 때, mysql의 connection이 달라졌다는 거죠.
그래서 편법을 썼습니다.
sql_query(" $sql_tmp; $sql_ord ");
phpmyadmin에서는 되는데 안됩니다. ㅠ..ㅠ...
그래서, common.lib.php의 sql_query 함수를 보다가 생각없이 native 함수 그대로 2번 쓰기로 했습니다.
@mysql_query($sql_tmp) or die("<p>$sql_tmp<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
그랬더니 되는 겁니다.
sql_query 함수는 접속 connection을 부를 때마다 다시 만들지만
native mysql_query 함수는 만들어진 connection을 계속 유지하는 것 입니다.
첨 알았어요. ㅠ.ㅠ...
결론은 ~ 더이상 내용검색 때문에 db lock 안걸린다는 겁니다~!!!!
수정 쉽습니다.
bbs/list.php에서 2곳을 수정 합니다.
(수정 1)
// 원글만 얻는다. (코멘트의 내용도 검색하기 위함)
/* 불당팩 - tmp table을 만들고, 거기서 distinct를 하는게 더 빠르다
$sql = " select distinct wr_parent from $write_table where $sql_search ";
$result = sql_query($sql);
$total_count = mysql_num_rows($result);
*/
$sql = " select wr_parent from $write_table where $sql_search ";
$sql_tmp = " create TEMPORARY table list_tmp_count as $sql ";
$sql_ord = " select distinct wr_parent from list_tmp_count ";
/* 불당팩 - tmp table을 만들고, 거기서 distinct를 하는게 더 빠르다
$sql = " select distinct wr_parent from $write_table where $sql_search ";
$result = sql_query($sql);
$total_count = mysql_num_rows($result);
*/
$sql = " select wr_parent from $write_table where $sql_search ";
$sql_tmp = " create TEMPORARY table list_tmp_count as $sql ";
$sql_ord = " select distinct wr_parent from list_tmp_count ";
@mysql_query($sql_tmp) or die("<p>$sql_tmp<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$total_count = mysql_num_rows($result);
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$total_count = mysql_num_rows($result);
(수정 2)
/* 불당팩 - tmp table을 만들고, 거기서 distinct를 하는게 더 빠르다
if ($sst)
$sql_order = " order by $sst $sod ";
if ($sst)
$sql_order = " order by $sst $sod ";
if ($sca || $stx)
{
$sql = " select distinct wr_parent from $write_table where $sql_search $sql_order limit $from_record, $board[bo_page_rows] ";
}
else
{
$sql = " select $list_select from $write_table where wr_is_comment = 0 $sql_order limit $from_record, $board[bo_page_rows] ";
}
$result = sql_query($sql);
*/
if ($sst)
$sql_order = " order by $sst $sod ";
{
$sql = " select distinct wr_parent from $write_table where $sql_search $sql_order limit $from_record, $board[bo_page_rows] ";
}
else
{
$sql = " select $list_select from $write_table where wr_is_comment = 0 $sql_order limit $from_record, $board[bo_page_rows] ";
}
$result = sql_query($sql);
*/
if ($sst)
$sql_order = " order by $sst $sod ";
if ($sca || $stx)
{
//$sql = " select distinct wr_parent from $write_table where $sql_search $sql_order limit $from_record, $board[bo_page_rows] ";
{
//$sql = " select distinct wr_parent from $write_table where $sql_search $sql_order limit $from_record, $board[bo_page_rows] ";
$sql = " select wr_parent from $write_table where $sql_search $sql_order";
$sql_tmp = " create TEMPORARY table list_tmp as $sql ";
$sql_ord = " select distinct wr_parent from list_tmp limit $from_record, $board[bo_page_rows] ";
$sql_tmp = " create TEMPORARY table list_tmp as $sql ";
$sql_ord = " select distinct wr_parent from list_tmp limit $from_record, $board[bo_page_rows] ";
@mysql_query($sql_tmp) or die("<p>$sql_tmp<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
}
else
{
$sql = " select $list_select from $write_table where wr_is_comment = 0 $sql_order limit $from_record, $board[bo_page_rows] ";
$result = sql_query($sql);
}
$result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]");
}
else
{
$sql = " select $list_select from $write_table where wr_is_comment = 0 $sql_order limit $from_record, $board[bo_page_rows] ";
$result = sql_query($sql);
}
DISTINCT combined with ORDER BY may require a temporary table. Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
-
Presence of any column in a
GROUP BYorDISTINCTclause larger than 512 bytes
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.
- opencode.co.kr -
|
||||||||||||