[김근태]님이 남기신 글:

>이질문을 여기에 드려도 되는지모르겠지만 mysql status 개발자님이신
>것같아서 실례지만 문의드립니다.
>해당프로그램 돌리면 아래와 같은 결과가 나오고있습니다.
>
>여기서 key_buffer_size부분이요..이걸
 4기가까지 늘려도 더 늘리라는
>
>말이 나옵니다.초당 쿼리 요청수가 20회에서 100회정도 나오는데..

절대적인 MAX 값 지정이 없기 때문에 계산상의 어쩔 수 없는 버그(?)입니다. ㅠㅠ
빨간색이 많이 나온다고 해서 절대적으로 잘 못된 것은 아닙니다.

일단 위의 key buffer 를 너무 크게 잡아 놓았네요.

이 값을 너무 크게 잡다 보니 다른 thread 에서 사용할 메모리가 상대적으로 작아서
아마도 disk 를 사용하는 듯 합니다.

반대로 join_buffer, read_rnd_buffer 를 너무 작게 잡아 놓았네요.

현재 mysql 전용 서버로 사용하고 있고 잘 만 튜닝하면 1G 정도에서
충분히 매끄럽게(?) 돌아갈 듯 합니다.

일단 아래 제가 순식간(?)에 튜닝한 값을 적용해 보세요.

적용후 하루 정도 지나고 mysql_status 로 확인해보세요.
(결과가 궁금하네요 ㅎㅎ)

-------------------------------------
set-variable	 = key_buffer=384M	; // 튜닝 필요(메모리 증설시 512M로 설정)
set-variable	 = table_cache=512	; // 512~1024 정도(테이블 개수와 관련됨)
set-variable	 = thread_cache=8	; // 8~16정도면 충분
set-variable	 = join_buffer=2M	; // 튜닝 필요
set-variable	 = myisam_sort_buffer_size=64M
set-variable	 = read_buffer=2M	; // 2M 정도면 충분
set-variable	 = read_rnd_buffer=2M	; // 2M 정도면 충분
set-variable	 = sort_buffer=2M	; // 2M 정도면 충분
set-variable	 = wait_timeout=10	; // 10초 이하로 무의미함
set-variable	 = max_connections=200	; // 300 이상시 메모리 2G 이상
set-variable	 = open_files_limit=0
set-variable	 = tmp_table_size=64M	; // 64M 정도
-------------------------------------

참고로 위의 설정은 메모리가 1G~2G 또는 그 이상이 요구됩니다.

한가지 더 참고로, 메모리가 부족하거나 잘못 튜닝하면 mysql 로그에

key_buffer_size + (read_buffer + sort_buffer) * max_connections = available of
memory

친철하게 이런 비슷한 메시지가 보입니다. 

위의 계산대로라면 max_connections 이 160 정도 나오는데 200 까지
설정해도 됩니다.


>
>쿼리는 단순 select,insert,update문이고
 쿼리는 최적화 되어있습니다.
>
>key_buffer_size를 4기가 이상으로 늘려야될지..아니면 다른 문제가
>
>있는것인지 궁금하구요.서버사양은
 참고로 amd 옵테론 2800 듀얼입니다.
>
>램은 1기가입니다..
>
>하드웨어 성능을 더 높여야 되는것인지..실제로 mysql_connect 실패율이
>
>상당히 많습니다.손실을 줄일려면 램을 올려야 될까요...
>
>이거저것 옵션을 바꿔봤지만..그대로네요..

>
>답변기다리겠습니다.감사합니다.

>
>
>-----------------------결과---------------------------------------

>
>TOTAL_STATUS 19.27 very busy (1초당 평균 쿼리 요청수) 
>CRITICAL_LIFE_TIME 2초 커넥션당 평균적인(추세) 임계 life time(CUR) 
>CRITICAL_LIFE_TIME_MAX 100초 커넥션당 평균적인(최대) 임계 life time(EXP)

>All_databases 13 or more 데이터베이스 수 
>Aborted_clients 0 연결 취소 Clients 수, refer 28,800(8시간 0초)[wait_timeout] and
28,800(8시간 0초)[interactive_timeout]
 
>Aborted_connects 3 연결 실패수, refer 3(3초)[connect_timeout]
 
>Aborted_clients_percent 0.00% 연결 취소율, 정상[참고] 
>Aborted_connects_percent 0.17% 연결 실패율, 정상 
>Bytes_received 270 KB 수신량(총) 
>Bytes_sent 1 MB 전송량(총) 
>Bytes_per_rs 5.22 받고(1) 보내는(x) 비율, 1:x 
>Bytes_sent_per_sec 4 KB 전송량(초당) 
>Bytes_sent_per_min 245 KB 전송량(분당) 
>Bytes_sent_per_hour 14 MB 전송량(시간당) 
>Bytes_sent_per_day 345 MB 전송량(하루평균), 예상수치 
>Com_delete 0.00% DELETE 쿼리 사용율(전체 DB 통계) 
>Com_insert 5.73% INSERT 쿼리 사용율(전체 DB 통계) 
>Com_select 27.29% SELECT 쿼리 사용율(전체 DB 통계) 
>Com_update 6.19% UPDATE 쿼리 사용율(전체 DB 통계) 
>Com_replace 0.00% REPLACE 쿼리 사용율(전체 DB 통계) 
>Com_lock_tables 0.00% LOCK TABLES 쿼리 사용율(전체 DB 통계) 
>Connections 1,740 총 connections 수 
>Connections_per_sec 5.06 초당 connections 수 
>Created_tmp_tables 0 메모리에 생성된 임시 테이블 수 
>Created_tmp_disk_tables 0 DISK에 생성된 임시 테이블 수, 33,554,432(32
MB)[tmp_table_size]보다
 많은 메모리를 요구하는 SQL 구문을 실행시 누계됨 
>Create_tmp_disk_tables_per 0.00% DISK에 생성된 임시 테이블 사용 비율 
>Intervals_of_connection 0.198 커넥션 주기(초) 
>Intervals_of_question 0.052 쿼리 주기(초) 
>Key_reads 570 DISK에서 Key 읽은 수 
>Key_read_requests 9,334 캐시에서 Key 읽기 요청수 
>Key_writes 270 DISK에 Key를 쓴 수 
>Key_write_requests 270 캐시에 Key 쓰기 요청수 
>Key_reads_per_request 0.06% DISK에서 Key를 읽는 비율,
>DISK에서 Key를 읽는 요청이 많습니다(기준 0.01%). 1,073,741,824(1,024
MB)(key_buffer_size)값을
 올리세요. 
>Key_writes_per_request 1.00% DISK에 Key를 쓰는 비율, 보통 1에 가까워야 정상

>Max_used_connections 10 동시에 연결된 최대값, 정상 
>Open_tables 8 현재 열려있는 Tables 수 
>Opened_tables 14 열렸던 Tables 수, refer 512[table_cache], 0.01/Connection

>Questions 6,628 현재까지 쿼리 요청수 
>Questions_per_connect 3.81 커넥션당 평균 쿼리 요청수 
>Select_full_join 0 Key 없이 FULL-JOIN 횟수 
>Slow_queries 0 10(10초)(long_query_time)초
 보다 큰 쿼리 요청수 
>Table_locks_waited 0초 Lock wait 총시간 
>Threads_cached 5 캐시된 쓰레드수, 17[thread_cache_size]보다
 항상 작음 
>Threads_connected 6 현재 열려있는 커넥션 수 
>Threads_created 11 현재까지 handle 커넥션에 생성된 쓰레드 수 
>Threads_running 1 현재 구동중인 쓰레드 수(not sleeping) 
>Threads_created_per 0.01% 커넥션당 생성된 평균 쓰레드 수(기준 0.01%), 정상[참고]

>Uptime 5분 44초 최근 MySQL 서버 구동 시간 
> 
>
>MYSQL SERVER VARIABLES (connected by TCP/IP), 누적된 통계자료가 너무 적습니다. 항목
VARIABLES commnets, [***] 표시는 성능 향상과 관련됨 
>back_log 50 man 2 listen, net.ipv4.tcp_max_syn_backlog 
>connect_timeout 3(3초) bad handshake timeout(초), 정상 
>join_buffer_size 131,072(128 KB) [***] FULL-JOIN에 사용되는 메모리 
>key_buffer_size 1,073,741,824(1,024 MB) [***] INDEX key buffer에 사용되는 메모리,
refer [Key_xxx],
>DISK에서 Key를 읽는 요청이 많습니다(기준 0.01%). 1,073,741,824(1,024
MB)(key_buffer_size)값을
 올리세요. 
>log OFF 로그 기록 여부 
>log_update OFF UPDATE 로그 기록 여부 
>log_bin OFF binary 로그 기록 여부 
>long_query_time 10(10초) refer 0[Slow_queries] 
>lower_case_table_names 0 테이블 대소문자 구별유무(0 구별) 
>max_allowed_packet 104,856,576(100 MB) 최대 허용할 패킷 
>max_connections 1,000 [***] 최대 동시 접속 커넥션 수, refer
10[Max_used_connections], 정상 
>max_delayed_threads 20 최대 delayed 쓰레드 수, INSERT DELAYED 구문과 관련됨

>max_join_size 18,446,744,073,709,557,000(17,179,869,136.0 GB) JOIN에 사용될 최대
크기(메모리 아님) 
>max_sort_length 1,024(1 KB) TEXT, BLOB의 정렬에 사용되는 최대 크기 
>max_user_connections 1,000 최대 동시 user 수(0은 제한없음) 
>myisam_max_extra_sort_file_size 268,435,456(256 MB) 빠른 INDEX를 생성시 사용되는
최대 임시 파일 크기 
>myisam_max_sort_file_size 9,223,372,036,854,779,000(8,589,934,568.0 GB) REPAIR,
ALTER, LOAD...등 INDEX를 재생성시 사용되는 임시 파일 크기 
>myisam_sort_buffer_size 67,108,864(64 MB) [***] REPAIR, INDEX, ALTER 정렬에
사용하는 메모리 
>open_files_limit 5,010 파일 open 제한 수(0은 제한없음) 
>read_buffer_size 2,093,056(2 MB) [***] 순차적인 검색에 사용되는
메모리(read_buffer_size)
 
>read_rnd_buffer_size 262,144(256 KB) [***] ORDER BY 정렬에 사용되는 메모리(to avoid
a disk seeks) 
>skip_external_locking ON 외부 lock 을 사용한다면 OFF로 설정. 
>skip_networking OFF TCP/IP 연결 여부(ON은 UNIX domain socket, OFF는 TCP/IP 연결),
정상 
>skip_show_database OFF SHOW DATABASES 구문 사용 여부(ON은 원천봉쇄, OFF는 사용가능)

>sort_buffer_size 2,097,144(2 MB) [***] ORDER BY, GROUP BY 정렬에 사용되는 메모리

>table_cache 512 [***] 한번에(all thread) 열 수 있는 테이블 수, refer
14[Opened_tables] 
>thread_cache_size 17 [***] 쓰레드 캐시 재사용 수, refer 1,740[Connections],
11[Threads_created], 계산된 추정값(최소:6, 권장:13, 최대:20), 정상[참고]

>tmp_table_size 33,554,432(32 MB) [***] 복잡한 GROUP BY 정렬에 사용되는 메모리(to
avoid a disk seeks), refer 0[Created_tmp_disk_tables] 
>interactive_timeout 28,800(8시간 0초) interactive -> re-active에 기다리는 시간(이후
closed), 정상[참고] 
>wait_timeout 28,800(8시간 0초) none interactive -> re-active에 기다리는 시간(이후
closed), 계산된 추정값(추세:2초, 임계:74초), 정상[참고] 
>timezone KST 현재 MySQL 서버의 TIME-ZONE 
>version 4.0.26 현재 MySQL 서버 버전 

========================================