원본 :  http://www.interworx.com/forums/showthread.php?p=2346
원문 저자 : 아이디 pascal (Carat Hosting 업체 CEO)

-------------------------------------------------------------------------------------------------------------------

MySQL 최적화

1. 두개의 가장 중요한 변수 : Table_cache, Key_buffer_size
1. 만약 Opened_tables가 크다면, table_cache 변수는 아마도 너무 작은 거다.
1. Table_cache 64
2. Open_tables 64
3. Opened_tables 5444468
2. 이것은 가장 나쁜 문제가 있다. "table_cache는 모든 쓰레드가 테이블을 여는(읽는) 수 이다. 멀티 쓰레드인 MySQL은 동시에 하나의 테이블에서 많은 쿼리를 수행한다. 그리고 각각의 쿼리는 테이블을 열 것이다." 때문에, 우리가 몇 개의 테이블만 가지고 있더라도, 우리는 더 많은 open_tables를 필요할 것이다.
3. Opened_tables 값이 높다면 cache miss 수를 봐라. Table_cache 크기를 올바르게 설정하는 것은 당신이 성능향상을 하기 위해 할 수 있는 두가지 좋은 방법 중 하나이다.

4. 만약 Key_reads 가 크다면, 당신의 key_buffer_size 변수가 아마도 너무 작은 거다. Cache hit 비율은 Key_reads/Key_read_requests로 계산할 수 있다.
1. Key_buffer_size 16M
2. Key_read_requests 2973620399
3. Key_reads 8490571
4. (cache hit 비율 = 0.0028)

5. "key_buffer_size는 인덱스 버퍼의 크기와 핸들링할 인덱스의 속도에 영향을 준다. 특히 read시." MySQL 매뉴얼에서는 "key_reads/key_read_request 비율이 0.01보다 작아야만 한다."라고 말한다. 이는 중요한 것이다. 그 값은 0.01보다 작아야 좋을 것이다.
6. 또한 key_write_requests와 key_writes도 체크해봐라. Key_writes/key_writes_request는 정상적으로 1보다 작다.(거의 0.5정도가 좋은 것 같다)

2. 나머지 중요한 설정 : wait_timeout, max_connection, thread_cache
1. 보통 당신은 idle인 상태의 많은 MySQL 프로세스를 가지고 있다. 왜냐하면 wait_timeout 설정이 작게 설정되지 않았기 때문이다. 그래서 나는 wait_timeout 설정은 매우 작은 값인 15초로 설정하기를 권장한다. 이것은 MySQL이 15초이상 idle이였던 connection을 닫는다는 걸 의미한다.
2. 문제는 당신의 max_connection을 증가시켜야만 한다는 것이다. Connection을 유지하고 있는 많은 idle상태의 client가 없다
3. 문제는 새로운 쓰레드를 생성해야만 한다는 것이다. 그것은 많은 CPU time을 사용할 수도 있다.

4. 그래서 해결책은 thread_cache를 사용하는 것이다.(MySQL document참조)
1. 우리가 재사용하기 위해 cache에 유지시켜야 할 쓰레드는 얼만큼인가. 하나의 client가 disconnect할 때, 그 client의 쓰레드는 cache로 들어간다. 만약 thread_cache_size보다 크지 않다면. 모든 새로운 쓰레드들은 우선 cache로부터 가져온다. 그리고 cache가 비어 있을때 새로운 쓰레드를 생성한다. 이 변수는 성능향상을 위해 증가될 수 있다. 만약 당신이 많은 새로운 connection을 가지고 있다면. (보통 이 값은 현저한 성능 향상에 기여하지 않는다. 만약 당신이 쓰레드 구현을 잘 했다면.) connections와 thread_created 사이에 차이를 시험하는 것으로, 당신은 당신에게 알맞은 thread_cache를 확인할 수 있다.

5. 만약 threads_created가 크다면, 당신은 thread_cache_size 변수를 증가시켜야 할지도 모른다. Cache hit 비율은 thread_created/Connections 로 계산될 수 있다.
1. Thread_cache_size 0
2. Threads_created 150022
3. Connections 150023

6. 이것은 두번째 문제가 있다. Cache size가 0 라는 것은 my-medium.cnf의 디폴트이다. 그러나 추천하는 사이즈는 8이다. My-large.cnf에서
7. 이 수식을 함 해봐라 : table_cache = opend_table / max_used_connection

3. 마지막으로 요것도 함 봐라 : tmp_table_size and Handler_read_궁/Handler_read_궁_next
1. 만약 created_tmp_disk_tables가 크다면, 당신은 tmp_table_size 변수가 disk 기반 대신 메모리기반에 임시 테이블을 가질 수 있게 증가시켜야 할 지 모른다.
1. Tmp_table_size 32M
2. Created_tmp_disk_tables 3227
3. Created_tmp_tables 159832
4. Created_tmp_files 4444
2. Created_tmp_disk_tables는 "statement를 실행하는 동안 disk에다 생성하는 임시 테이블수"이다. 그리고 created_tmp_tables는 메모리 기반이다. 분명, 메모리 대신 disk를 사용하는 것은 나쁜것이다. 임시 테이블의 약 2%는 disk로 간다. 그리고 매우 나쁘진 안다. 그러나 tmp_table_size를 증가한다고 해서 지장을 주진 않는다.
3. 만약 Handler_read_궁 가 크다면 당신은 아마 전체 테이블을 스캔하는 쿼리를 많이 가지고 있다거나 적절한 키를 사용하지 않는 조인을 가지고 있다는 것이다.
1. Handler_read_궁 27712353
2. Handler_read_궁_next 283536234
4. 이 값들은 높다. 그리고 우리는 아마도 인덱스와 쿼리를 향상하기 위해 .

4. 사용된 MySQL 메모리 = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)
1. Connection이 증가하면 메모리사용량도 증가한다.
2. Key_buffer를 늘리는 것 만큼 connection은 늘지 않는다.
3. 만약 당신이 이중 한 설정을 높게 변경하면 당신의 시스템은 swap될 수 있다.
4. 만약 당신의 시스템이 swap 된다면 이 설정값을 줄이도록 하라.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.