웹마스터 팁
page_full_width" class="col-xs-12" |cond="$__Context->page_full_width">
이거 어디다 올려야 될 지 몰라 여기다 올립니다. 프로그래머님들 읽어보세요.
2014.06.10 03:42
그동안 저가 운영하는 여러 웹( www.kg55.net www.kb34.net )의 데이터베이스가 너무 커져서 퀘리하는 데 너무 시간이 많이 걸려 share hosting은 안된다고 해서 돈 더 주고 vps 계정으로 옮겼으나 여전 퀘리시에 데이터 베이스 (MySql)가 hangup 되어(서버쪽의 메로리가 다해서? 축나서?) recycle이 안되어 다름 사람들이 로그인 못하는 상태가 자주 일어 나더라고요.
그래서 여러번 호스팅 전문가들에게 도움을 청했더니 아래와 같은 글을 보내왔습니다.
솔직히 저는 그가 하는 말들이 영어는 다 이해했지만 콤퓨터내용이 무슨 말인줄 이해를 못해 "그저 당신들에게모든 것 맡기니 잘해주시요" 하고 말았습니다.
맨 밑쪽에 가면 제로 보드 프로그램머들에게 그들이 제안하고 픈 말이 있으니 읽어 보시고 참고하세요.
I'm happy to help. We have finished the slow query logging, and I have
copied the log to /home/kg55ne6/slowqueries.2. I have run the MySQL tuner
utility, which has suggested the following changes:
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size
equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_cache (> 128)
Additionally, you asked us to compare this with the settings your other VPS
had. First, I did notice that there were still OOM (or "Out Of Memory") kills
happening on MySQL on the other server, though it doesn't appear to be as
frequent. Running MySQL tuner on the other VPS gave the following
recommendations:
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be
inaccurate
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
thread_cache_size (start at 4)
table_cache (> 64)
Additionally, here are the two configuration files, as requested:
CT-12146-bash-4.1# cat /etc/my.cnf
[mysqld]
max_connections = 100
max_user_connections = 75
query_cache_size = 32M
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 8M
table_open_cache = 128
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
innodb_file_per_table
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 4
# This setting ensures that aio limits are not exceeded
# (default is 65536, each instance of mysql takes 2661 with this
enabled)
innodb_use_native_aio = 0
open_files_limit=4004
log-slow-queries = /var/log/slowqueries
long_query_time = 3
Other VPS:
[mysqld]
safe-show-database
max_connections=150
max_user_connections=30
query_cache_size=32M
key_buffer_size=256M
open_files_limit=7326
log-slow-queries = /var/log/slowqueries
long_query_time = 3
One glaring difference I noticed was your PHP handler; the server with
fewer out of memory kills was using DSO and mod_ruid2, compared with vps12146
which is using SuPHP. If you'd like we can switch the PHP handler to DSO on
vps12146, although the issue of the slow MySQL queries will still need to be
solved.
Finally, just looking through the slow query logs, there seem to be a much
larger number of rows to analyze on the server with more frequent kills, as well
as much larger queries in general. This is a query that was flagged as being
slow on vps12146:
# Time: 140609 4:08:00
# User@Host: kg55ne6_kg55er[kg55ne6_kg55er] @ localhost [127.0.0.1]
# Query_time: 6.448442 Lock_time: 0.000093 Rows_sent: 5 Rows_examined:
14294
SET timestamp=1402312080;
SELECT `documents`.* FROM `xe_documents` as `documents` , `xe_modules` as
`modules` WHERE ( `modules`.`module` = 'board' and `modules`.`module_srl` in
(178440,178443,178446,85913,140038,139898,548,66172,173113,181546,233243,139520,140039,178561,190341,141432)
and `documents`.`module_srl` = `modules`.`module_srl` ) and
`documents`.`list_order` <= 2100000000 ORDER BY `documents`.`list_order`
asc LIMIT 5;
While here is a query from the one that is running smoother:
# User@Host: youshi5_kb34mgr[youshi5_kb34mgr] @ localhost []
# Query_time: 5.880789 Lock_time: 0.000083 Rows_sent: 1 Rows_examined:
2759
SET timestamp=1402298176;
SELECT count(`document_srl`) as `count` FROM `xe_documents` as
`documents` WHERE `module_srl` in (63) and `status` in ('SECRET','PUBLIC') and
( `list_order` <= -66254 );
Notice that the number of rows examined is much lower, and the amount of
logic in the SQL statement itself appears to be much smaller. Both of these
could be contributing greatly towards causing MySQL to crash. If you'd like for
us to make any changes to the MySQL configuration file, or change the PHP
handler to DSO, we can certainly do so for you; however, as far as the queries
themselves go, I would recommend having a developer check over those and see
what can be done to optimize those. Please let us know if you have any other
questions, or if there's anything else that we can assist you with.
Please let us know if you have any further questions; we are more than
happy to help.
Inmotionhosting.com support team.
제목 | 글쓴이 | 날짜 |
---|---|---|
좌측 로그인 회원 리스트가 많아지면 스크롤 처리 [6] | 똑디 | 2008.01.29 |
본인외 회원정보 제어 [1] | edgarkim | 2008.02.03 |
왼쪽메뉴줄이고 오른쪽에 박스만들어 내용쉽게넣기 [15] | plruto | 2008.02.07 |
로그인후 보여줄 페이지 설정 [4] | 권인해 | 2008.02.11 |
제로보드용 플래시시계 위젯 설치방법 | 푸른커튼 | 2008.02.12 |
글을 읽을때 마다 조회수 증가시키기 (0.2.9수정#3) [1] | SKYMARU | 2008.02.20 |
우분투에서 rewrite 모드 사용하기 [2] | 용꿍 | 2008.03.06 |
로그인 풀림현상 원인 파악 [3] | 남국 | 2008.03.19 |
''권한이 없습니다'' 라는 문구를 조금 더 부드럽게 바꾸기 [3] | RainSky | 2008.03.21 |
로그인풀림방지 - 자동 www 붙이는 다른 방법 [23] | RainSky | 2008.03.28 |
레이아웃수정시 참고할 zbXe레이아웃스킨의 css와 layout 상관관계(1.0.0 기본스킨) [21] | 갯가 | 2008.04.08 |
글을 읽을때 마다 조회수가 증가 하는 팁 (1.0.0↑) [5] | SKYMARU | 2008.04.12 |
회원 가입 임시 제한 일자 버그 수정 방법 [2] | 껄껄껄 | 2008.04.13 |
상단로그인..? [10] | 빽짱구 | 2008.04.15 |
관리자 로그인시 원하는걸 보여주자 [5] | 빽짱구 | 2008.04.15 |
수정,삭제 버튼을 "이 게시물을.."에 넣기 [1] | hangoon | 2008.04.16 |
졸졸이 스토커 가두어보기 2탄(보드우측에 포함된 것처럼) [1] | 갯가 | 2008.04.26 |
링크가 걸린 글의 색을 무지개색으로 나타내기 | 재미니님 | 2008.04.29 |
windows Live writer로 게시글 쓰기 [3] | tindrum | 2008.04.29 |
주문형게시판 만들기 [22] | 팔공산 | 2008.05.04 |
mysql 튜너를 이용해서 나오는 my.cnf 파일을 수정 했더니 쿼리시간이 6.44초에서 5.88초로 감소했다.
라는 내용이 주된 골자 입니다.