서버호스팅을 하는데도 mysql 상태를 보니 select질의가 88.07 % 나 차지하고 있습니다.
물론 게시판 소스 건드린건 없고요..
CPU점유율도 90%이상이 나올 경우가 많고요..
특히 게시판 목록보기, 글보기 할때 많이 버벅거리거든요..
이유가 뭔지 좀 가르쳐 주십시요...;;;
| 전체 사용량 |
ø per hour |
ø per minute |
ø per second |
| 3,193,393 |
97,450.32 |
1,624.17 |
27.07 | |
| 질의 종류 |
ø per hour |
% |
| admin commands |
0 |
0.00 |
0.00 % |
| alter table |
732 |
22.34 |
0.02 % |
| analyze |
0 |
0.00 |
0.00 % |
| backup table |
0 |
0.00 |
0.00 % |
| begin |
0 |
0.00 |
0.00 % |
| change db |
103,528 |
3,159.28 |
3.28 % |
| change master |
0 |
0.00 |
0.00 % |
| check |
0 |
0.00 |
0.00 % |
| commit |
0 |
0.00 |
0.00 % |
| create db |
0 |
0.00 |
0.00 % |
| create function |
0 |
0.00 |
0.00 % |
| create index |
0 |
0.00 |
0.00 % |
| create table |
61 |
1.86 |
0.00 % |
| delete |
41,239 |
1,258.46 |
1.31 % |
| drop db |
0 |
0.00 |
0.00 % |
| drop function |
0 |
0.00 |
0.00 % |
| drop index |
0 |
0.00 |
0.00 % |
| drop table |
0 |
0.00 |
0.00 % |
| flush |
1 |
0.03 |
0.00 % |
| grant |
0 |
0.00 |
0.00 % |
| insert |
110,291 |
3,365.67 |
3.50 % |
| insert select |
0 |
0.00 |
0.00 % |
| kill |
88 |
2.69 |
0.00 % |
| load |
0 |
0.00 |
0.00 % |
| load master table |
0 |
0.00 |
0.00 % |
| lock tables |
0 |
0.00 |
0.00 % |
| optimize |
61 |
1.86 |
0.00 % |
| purge |
0 |
0.00 |
0.00 % |
| rename table |
0 |
0.00 |
0.00 % | |
| 질의 종류 |
ø per hour |
% |
| repair |
0 |
0.00 |
0.00 % |
| replace |
0 |
0.00 |
0.00 % |
| replace select |
0 |
0.00 |
0.00 % |
| reset |
0 |
0.00 |
0.00 % |
| restore table |
0 |
0.00 |
0.00 % |
| revoke |
0 |
0.00 |
0.00 % |
| rollback |
0 |
0.00 |
0.00 % |
| select |
2,776,298 |
84,722.16 |
88.07 % |
| set option |
0 |
0.00 |
0.00 % |
| show binlogs |
0 |
0.00 |
0.00 % |
| show create |
0 |
0.00 |
0.00 % |
| show databases |
9 |
0.27 |
0.00 % |
| show fields |
31,477 |
960.56 |
1.00 % |
| show grants |
0 |
0.00 |
0.00 % |
| show keys |
0 |
0.00 |
0.00 % |
| show logs |
0 |
0.00 |
0.00 % |
| show master status |
0 |
0.00 |
0.00 % |
| show open tables |
0 |
0.00 |
0.00 % |
| show processlist |
245 |
7.48 |
0.01 % |
| show slave status |
0 |
0.00 |
0.00 % |
| show status |
6 |
0.18 |
0.00 % |
| show innodb status |
0 |
0.00 |
0.00 % |
| show tables |
31,214 |
952.53 |
0.99 % |
| show variables |
5 |
0.15 |
0.00 % |
| slave start |
0 |
0.00 |
0.00 % |
| slave stop |
0 |
0.00 |
0.00 % |
| truncate |
0 |
0.00 |
0.00 % |
| unlock tables |
0 |
0.00 |
0.00 % |
| update |
57,008 |
1,739.67 |
1.81 % | |
댓글 2개
저의 경우 총회원이 7만을 넘어서면서 mysql 과부하가 생겼습니다.
아무래도 gnuboard의 쿼리가 최적화 되어있지 않은듯 싶네요.
저의 경우는 서버메모리를 256에서 512로 업그레이드 했으며,
my.cnf의 설정을 다시하였습니다.
아마도 회원 필드의 select가 많은거 같은데 이부분 최적화 신경을 쓰셔야 할껏 같네요..
60 processes: 58 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 78.4% 0.0% 17.0% 0.0% 0.0% 0.0% 4.6%
Mem: 503864k av, 440812k used, 63052k free, 0k shrd, 46752k buff
325120k actv, 73572k in_d, 6764k in_c
Swap: 1052216k av, 34104k used, 1018112k free 267880k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
550 mysql 15 0 22520 16M 1424 S 77.9 3.2 639:14 0 mysqld
552 root 15 0 2932 356 268 S 17.3 0.0 1:54 0 httpd
1 root 15 0 512 476 452 S 0.0 0.0 0:03 0 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
7 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
5 root 15 0 0 0 0 SW 0.0 0.0 0:02 0 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kscand
8 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kupdated
9 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
13 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
116 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
117 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
118 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
119 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
120 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
407 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 eth0
453 root 15 0 224 196 144 S 0.0 0.0 0:00 0 syslogd
457 root 22 0 96 36 32 S 0.0 0.0 0:00 0 klogd
485 root 16 0 540 396 292 S 0.0 0.0 0:00 0 sshd
496 root 22 0 244 128 124 S 0.0 0.0 0:00 0 xinetd
505 root 15 0 172 88 84 S 0.0 0.0 0:00 0 vsftpd
517 root 25 0 152 4 0 S 0.0 0.0 0:00 0 safe_mysqld
561 root 15 0 192 172 120 S 0.0 0.0 0:00 0 crond
570 root 25 0 152 52 48 S 0.0 0.0 0:00 0 saslauthd
586 root 15 0 12144 11M 1372 S 0.0 2.4 0:00 0 mdmpd
593 root 15 0 56 4 0 S 0.0 0.0 0:00 0 mingetty
594 root 15 0 56 4 0 S 0.0 0.0 0:00 0 mingetty
595 root 15 0 52 4 0 S 0.0 0.0 0:00 0 mingetty
596 root 15 0 52 4 0 S 0.0 0.0 0:00 0 mingetty
597 root 18 0 56 4 0 S 0.0 0.0 0:00 0 mingetty
598 root 18 0 56 4 0 S 0.0 0.0 0:00 0 mingetty
930 root 15 0 620 316 192 R 0.0 0.0 0:00 0 sshd
935 root 15 0 680 616 440 S 0.0 0.1 0:00 0 bash
14066 root 15 0 1216 1216 916 R 0.0 0.2 0:00 0 top