웹마스터 팁
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.
댓글 2
제목 | 글쓴이 | 날짜 |
---|---|---|
나만의 미니홈 만들기 ㅡ메뉴에 게시판 연결 [5] | 예뜨락 | 2004.12.17 |
인스타그램 해시태그 회원가입 없이 끌어오기 | 외인 | 2017.02.08 |
XE 1.8 대응 apache 2.2 / php 5.5 / MariaDB 10 설치 [11] | 간장게장같은남자 | 2015.04.19 |
DB에서 직접 시퀀스 생성하기 [3] | 기진곰 | 2015.07.18 |
구글 API 활용(뉴스가져오기) | pixfine | 2014.09.24 |
이거 어디다 올려야 될 지 몰라 여기다 올립니다. 프로그래머님들 읽어보세요. [2] | 유샤인 | 2014.06.10 |
짧은 주소를 가능 하게 하는 서버(아파치, httpd) 설정 [3] | 꿈섬 | 2014.07.27 |
해외 아이피 차단 방법입니다. [3] | Flolida | 2014.03.01 |
Windows IIS 에서 F5 연타 방지하기 (mod_evasive , limit_req) [3] | StyleRoot | 2014.04.08 |
페이지로딩시 메모리 부족이란 오류메시지가 뜰때... | 똑디 | 2013.11.07 |
rewrite mod 가 안 돼서 고생한 경험담입니다. [2] | obok | 2013.06.29 |
-추가- 당신의 XE 기반 홈페이지를 훅가게 하는 방법 4가지 [26] | 老姜君 | 2009.10.08 |
메인화면에 설문조사 위젯(?) 만들기 [3] | 3D매니아 | 2010.12.14 |
'신고 수' 노출 함수 및 신고시 게시글 이동팁 [11] | 인터니즈™ | 2012.07.03 |
IIS 7.5 + XE 업로드 용량 개선 방법 [1] | 류군 | 2012.07.16 |
첨부파일 100% 에서 사라지는 문제 저도 해결 [2] | 안나오네 | 2010.06.09 |
큰 파일의 썸네일이 안생길때 해결방법-추가 [7] | HIKARU | 2008.03.18 |
간단한 시간제한 자료실 만들기 [2] | 옥수수밭 | 2010.03.17 |
이유는 모르겠는데 뷰테이블을 쓰면 XE의 속도가 크게 떨어지는 것 같습니다. [2] | 철갑 | 2011.04.25 |
레이아웃에 소스추가해서 트래픽보기! [2] | 막시민 | 2011.03.07 |