board.php에서 디비 전체 선택 부하 유발
본문
안녕하세요.
호스팅 업체에서 아래와 같이 연락이 왔는데요,
어떻게 수정을 하면 될까요? ㅠㅠ
조언 좀 부탁드립니다.
======================================
고객님께서 현재 이용중이신 hongiktravel.com 홈페이지에서 부하를 유발하고있어
서버내 부하 및 사이트의 접속지연현상을 발생시키고 있습니다.
www/bbs/board.php 경로에 설정된 소스상에서 디비를 전체선택하는 부분이있으며
해당 소스를 확인하시어 소스의 수정이 필요할것으로 보이니 소스를 확인하시어 수정을 부탁드리겠습니다.
----------------------------------------------------------------------------------------------------
장애 확인:
222.122.84.49: wa64-075.cafe24.com High Load( 17 ) - 04-02 21:30
top - 21:41:55 up 27 days, 16:44, 3 users, l! oad average: 13.92, 13.28, 12.75
Tasks: 289 total, 59 running, 226 sleeping, 0 stopped, 4 zombie
Cpu(s): 44.3% us, 9.8% sy, 0.0% ni, 45.9% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 16458428k total, 15587040k used, 871388k free, 2026488k buffers
Swap: 1959924k total, 47180k used, 1912744k free, 7148008k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8784 mysql 20 0 99.1m 48m 4624 S 108 0.3 23:36.64 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/u
14197 sandesh 20 0 272m 42m 13m R 9 0.3 0:18.98 /usr/local/apache/bin/httpd &nb! sp;
4413 sandesh 20 0 268m 39m 13m R 4 0.2 0:21.83 /usr/local/apache/bin/httpd
9104 ilwonm 20 0 213m 29m 12m S 4 0.2 0:01.83 /usr/local/apache/bin/httpd
9241 sandesh 20 0 214m 30m 12m R 4 0.2 0:01.68 /usr/local/apache/bin/httpd
9267 sandesh 20 0 223m &! nbsp;39m 12m R 4 0.2 0:02.52 /usr/local/apache/bin/httpd
10033 ilwonm 20 0 220m 36m 12m S 4 0.2 0:01.35 /usr/local/apache/bin/httpd &! nbsp;
10048 sandesh 20 0 214m 30m 12m S 4 0.2 0:01.46 /usr/local/apache/bin/httpd
10153 sandesh 20 0 214m 30m 12m R 4 0.2 0:01.49 /usr/local/apache/bin/httpd
10607 sandesh 20 0 221m 37m 12m R 4 0.2 0:01.63 /usr/local/apache/bin/httpd
14689 soc! radav &n bsp;20 0 267m 38m 13m S 4 0.2 0:16.67 /usr/local/apache/bin/httpd
14864 sandesh 20 0 214m 30m 12m R 4 0.2 0:00.86 /usr/local/apache/bin/httpd  ! ;
16038 sandesh 20 0 214m 30m 12m R 4 0.2 0:00.55 /usr/local/apache/bin/httpd
16135 sandesh 20 0 214m 29m 11m R 4 0.2 0:00.39 /usr/local/apache/bin/httpd
19945 root 20 0 6568 1944 1468 R 4 0.0 0:00.03 top -c
26638 sandesh 20 0 261m 32m 12m R 4 0.2 0:09.72 /usr/local/apache/bin/httpd
26669 sandesh 20 0 263m 34m ! 13m R 4 0.2 0:11.85 /usr/local/apache/bin/httpd
27101 sandesh 20 0 268m 38m 12m R 4 0.2 0:11.85 /usr/local/apache/bin/httpd !  ! ;
27102 sandesh 20 0 271m 41m 13m R 4 0.3 0:12.49 /usr/local/apache/bin/httpd
*디비쿼리 / 스팸아님
| 2513 | sandesh | localhost | sandesh | Query | 6 | Sorting result | select * from g4_write_B18 where wr_parent = ''247000'' and wr_is_comment = 1 order by wr_comment, wr_comment_reply |
| 2514 | sandesh | localhost | sandesh | Query | 4 | Sorting result | select * from g4_write_B18 where wr_is_comment = 0 order by wr_hit desc limit 156080, 80 |
| 2515 | sandesh | localhost | sandesh | Query | 6 | Sorting result | select wr_id, wr_subject from g4_write_B18 where wr_is_comment = 0 and wr_num > ''-99207'' order by wr_num, wr_reply limit 1 |
| 2523 | sandesh | localhost | sandesh | Query | 4 | Sorting result | select * from g4_write_B18 where wr_is_comment = 0 order by wr_hit asc limit 560, 80 |
| 2524 | sandesh | localhost | sandesh | Query | 6 | Sorting result | select * from g4_write_B18 where wr_parent = ''243387'' and wr_is_comment = 1 order by wr_comment, wr_comment_reply |
| 2526 | sandesh | localhost | sandesh | Query | 0 | Locked | select * from g4_write_B18 where wr_is_comment = 0 order by wr_hit desc limit 157040, 80 |
| 2529 | sandesh | localhost | sandesh | Query | 4 | Sorting result | select * from g4_write_B18 where wr_is_comment = 0 order by wr_hit desc limit 156720, 80 |
| 2534 | sandesh | localhost | sandesh | Query | 3 | Sorting result | select * from g4_write_B18 where wr_parent = ''247782'' and wr_is_comment = 1 order by wr_comment, wr_comment_reply |
| 2538 | sandesh | localhost | sandesh | Query | 3 | Sorting result | select * from g4_write_B18 where wr_parent = ''231938'' and wr_is_comment = 1 order by wr_comment, wr_comment_reply |
| 2541 | sandesh | localhost | sandesh | Query | 2 | Locked
*웹로그
[root@wa64-075 ~]# tail -f /usr/local/apache/logs/hongiktravel_com.log | grep 200
61.252.77.80 - - [02/Apr/2016:21:42:16 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=247503&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=7 HTTP/1.1" 200 147735 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 49 10607
110.232.96.20 - - [02/Apr/2016:21:42:16 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=248155&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=desc&sop=and&page=1963 HTTP/1.1" 200 149727 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 69 10034
58.136.6.153 - - [02/Apr/2016:21:42:19 +0900] "GET /skin/board/mw.basic/style.co! mmon.css?1459600939 HTTP/1.1" 200 23711 "http://hongiktravel.com/bbs/board.php?bo_table=B01&wr_id=15" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.110 Safari/537.36" 0 20324
58.136.6.153 - - [02/Apr/2016:21:42:19 +0900] "GET /bbs/board.php?bo_table=B01&wr_id=15 HTTP/1.1" 200 53790 "http://hongiktravel.com/bbs/board.php?bo_table=B01&wr_id=30" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWeb! Kit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.110 Safari/537! .36" 1 9 081
110.232.97.90 - - [02/Apr/2016:21:42:20 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=245589&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=7 HTTP/1.1" 200 151472 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 52 26681
61.252.76.50 - - [02/Apr/2016:21:42:20 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=247060&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=8 HTTP/1.1" 200 148428 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 48 13368
110.232.97.110 - - [02/Apr/2016:21:42:20 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=245077&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=8 HTTP/1.1" 200 148000 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 55 14831
110.232.99.40 - - [02/Apr/2016:21:42:22 +0900] "GET /bbs/login.php?url=%2Fbbs%2Fboard.php%3Fbo_table%3DB18%26wr_id%3D240762%26sfl%3Dwr_subject%257C%257Cwr_content%26stx%3D%26sst%3Dwr_hit%26sod%3Dasc%26sop%3Dand%26page%3D8 HTTP/1.1" 200 6248 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 0 18895
61.252.76.30 - - [02/Apr/2016:21:42:24 +0900] "GET /bbs/login.php?url=%2Fbbs%2Fboard.php%3Fbo_table%3DB18%2! 6wr_id%3D247983%26sfl%3Dwr_subject%257C%257Cwr_content%26stx%3! D%26sst% 3Dwr_hit%26sod%3Ddesc%26sop%3Dand%26page%3D1961 HTTP/1.1" 200 6252 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 0 10103
110.232.98.130 - - [02/Apr/2016:21:42:27 +0900] "GET /bbs/password.php?w=u&bo_table=B18&wr_id=238502&page=1952&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=desc&sop=and&page=1952 HTTP/1.1" 200 21085 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/2! 0100101 Firefox/40.0" 0 13368
61.252.76.60 - - [02/Apr/2016:21:42:27 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=240236&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=4 HTTP/1.1" 200 155329 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 66 14839
110.232.99.60 - - [02/Apr/2016:21:42:27 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=240430&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=asc&sop=and&page=6 HTTP/1.1" 200 146202 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 57 27101
110.45.153.20 - - [02/Apr/2016:21:42:27 +0900] "GET /bbs/board.php?bo_table=B18&wr_id=243389&sfl=wr_subject%7C%7Cwr_content&stx=&sst=wr_hit&sod=desc&sop=and&page=1961 HTTP/1.1" 200 146649 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0" 60 27246
...
*웹에서 해당 ! 일 접근 시 지연현상 있음
[root@wa64-075 www]# vi /home/hosting_users/sandesh/www/bbs/board.php
$sql = " select count(*) as cnt
from $g4[group_member_table]
where gr_id = ''$board[gr_id]'' and mb_id = ''$member[mb_id]'' ";
$row = sql_fetch($sql);
답변 1
예약/질문쪽 게시판 부하가 심하네요. DB 튜닝 및 소스수정이 필요한데요.
가장 간단하게 해결하는 방법은 현재 사용하고 있는 예약/질문게시판은 백업을 하고
새로 예약/질문게시판을 만들어서 연결하시는겁니다.