최적화 개요

MySQL 디자인 상의 제약 사항 및 트레이드오프 (Tradeoff)

이식성 (Portability)을 갖도록 어플리케이션 디자인하기

MySQL 벤치 마크 슈트

여러분 자신의 벤치 마크 사용하기

시스템의 성능은 무엇보다도 시스템의 기본 디자인을 어떻게 하느냐에 달려 있다. 여러분은 사용 중에 있는 시스템이 어떤 종류의 프로세싱을 하고 있는지, 그리고 어떤 병목 현상 (bottleneck)이 발생하는지에 대해서도 알고 있어야 한다. 대부분의 경우, 시스템 병목 현상은 아래와 같은 곳에서 발생하게 된다:

• 디스크 검색 (Disk seeks). 디스크는 데이터 조각을 찾는데 시간을 소비한다. 최신의 디스크들은, 평균 검색 시간이 보통10ms 이하이기 때문에, 이론적으로 초당 약 100개의 검색 (seek)을 수행할 수가 있다. 이 시간은 점진적으로 개선되고 있으며 단일 테이블에 대해 이것을 최적화 시키기는 매우 어렵다. 검색 시간을 최적화 할 수 있는 방법은 데이터를 한 개 이상의 디스크에 분산하는 것이다.
• 디스크 읽기 및 쓰기. 최신의 디스크들은 하나의 디스크가 최소 10-20MB/s의 처리량 (throughput)을 가지고 있다. 여러 개의 디스크에서 병렬로 데이터를 읽을 수 있기 때문에 디스크 검색 보다는 쉽게 최적화를 시킬 수가 있다.
• CPU 싸이클 (cycle). 데이터가 메인 메모리에 있을 때 그것을 처리해서 결과를 얻는다. 메모리에 비해 작은 양의 테이블을 가지도록 만드는 것이 가장 일반적인 제약 요소이다. 하지만 작은 테이블을 가지고 있다면, 속도는 문제가 되지 않는다.
• 메모리 대역폭 (Memory bandwidth). CPU가 자신의 캐시가 보유할 수 있는 양보다 많은 데이터를 필요로 하게 되면, 메인 메모리 대역폭에서 병목 현상이 발생한다. 대부분의 시스템에서는 일반적으로 이러한 현상이 발생하지는 않지만, 이 점에 대해서는 인지를 하고 있어야 한다.

MySQL 디자인 상의 제약 사항 및 트레이드오프 (Tradeoff)

MyISAM 스토리지 엔진을 사용하면, 여러 개의 리더 (reader) 또는 하나의 라이터 (writer)가 매우 빠른 속도로 빠른 테이블 잠금을 사용할 수가 있다. 하지만, 단일 테이블에서 업데이트와 느린 선택을 꾸준하게 하는 경우에는 문제가 발생한다. 만일 특정 테이블에 대해서 이러한 문제가 발생을 한다면, 다른 스토리지 엔진을 사용하도록 한다. Chapter 14, 스토리지 엔진과 테이블 타입을 참조할 것.

MySQL은 트랜젝셔널 (transactional) 및 논-트랜젝셔널 (non-transactional) 테이블 모두를 처리한다. MySQL은 다음과 같은 규칙을 사용해서 논-트랜젝셔널 테이블을 보다 자연스럽게 처리한다. 이러한 규칙은 스트릭트 (strict) SQL 모드를 구동하지 않거나, 또는 IGNORE를 INSERT 또는 UPDATE의 스페시파이어 (specifier)로 사용하는 경우에만 적용된다는 것을 알아두기 바란다.

• 모든 컬럼은 디폴트 값을 가지고 있다.
• 만일 여러분이 적절하지 못하거나 또는 범위를 벗어난 값을 컬럼에 삽입하면, MySQL 은 에러를 보고하는 대신에 “가장 가능성이 높은 값 (best possible value)”을 컬럼에 설정한다. 숫자 값에 대해서는, 0이 되거나, 가장 가능성이 낮은 값 또는 가장 가능성이 높은 값으로 적용된다. 스트링 값에 대해서는, 빈 스트링 (empty string) 또는 컬럼에 저장할 수 있는 최대 크기의 스트링 중의 하나가 된다.
• 계산된 모든 수식은 에러 상태를 알려주는 대신에 사용할 수 있는 값을 리턴 한다. 예를 들면, 1/0은 NULL을 리턴 한다.

위의 동작을 변경하기 위해서는, 서버SQL 모드를 적당히 설정해서 스트릭터 (stricter) 데이터 처리를 활성화 시키면 된다. 데이터 처리에 대해서는 Section 5.2.5, “서버 SQL모드”, 및 Section 13.2.4, “INSERT 신텍스”를 참조할 것.

이식성 (Portability)을 갖도록 어플리케이션 디자인하기

모든 SQL 서버는 표준 SQL을 서로 다르게 구현하기 때문에, 이식 가능한 데이터 베이스로 작성을 해야 한다. 단순한 선택 및 삽입에 대해서 이식성을 갖도록 만드는 것은 쉬운 일이지만, 보다 복잡한 작업의 경우에는 많은 부분을 고려해서 작성해야 한다.

모든 데이터 베이스 시스템은 각기 서로 다른 장점 및 단점들을 가지고 있다.

복잡한 어플리케이션이 이식성을 갖도록 하기 위해서는, 어플리케이션을 어떤 SQL 서버와 구동을 시킬 것인지, 그리고 그 서버가 지원하는 기능에는 어떤 것들이 있는지를 파악해야 한다. MySQL crash-me 프로그램을 사용해서 데이터베이스 서버를 선택하면 서버의 함수, 타입, 그리고 한계점을 파악할 수가 있다. crash-me가 모든 가능 기능들을 검사하지는 못하지만, 약 450번의 테스트를 거쳤기 때문에 믿고 사용할 수는 있다. crash-me가 제공하는 정보의 예를 본다면, 만일 여러분이 Informix 또는 DB2를 사용하는 경우에는 18개 문자 이상으로 컬럼 이름을 사용할 수 없다는 것을 알게 될 것이다.

crash-me 프로그램과 MySQL 벤치마크는 모두 데이터 베이스와는 무관하게 동작한다. 이것들이 어떻게 작성되어 있는지 알수 있다면 여러분이 어플리케이션을 데이터베이스와 무관하게 만들기 위해서 해야 할 일이 어떤 것인지를 알 수 있게 될 것이다. 이 프로그램들은 MySQL 소스 배포판의 sql-bench 디렉토리에서 찾을 수가 있다. 이것들은 Perl로 쓰여졌고 DBI 데이터 베이스 인터페이스를 사용한다. 자체 DBI를 사용함으로써 데이터베이스-무관 접근 방식을 사용하기 때문에 이식성에 관련된 문제를 해결하고 있다.

만일 여러분이 데이터베이스 무관성에 대해 노력을 하고 있다면, 각 SQL 서버의 병목 현상 문제에 대해서 잘 알고 있어야 한다. 예를 들면, MySQL은 MyISAM 테이블에 대한 열 추출 (retrieving) 및 업데이트는 매우 빠르게 처리하지만, 동일 테이블에서 슬로우 리더 (slow reader) 및 슬로우 라이터 (slow writer)를 혼용해서 사용하면 문제가 생기게 된다. 반면에, Oracle의 경우, 여러분이 최근에 업데이트를 한 열을 접근하고자 할 때에는 커다란 문제가 생기게 된다 (업데이트한 것들이 디스크에 플러시 될 때까지는). 트랜젝셔널 데이터베이스 시스템은 일반적으로 로그 테이블에서 요약 테이블 (summary table)을 만드는 경우에는 좋은 성능을 내지 못하는데, 왜냐하면 그와 같은 경우에는 열 잠금 (row locking)이 거의 쓸모없기 되기 때문이다.

어플리케이션이 진정으로 데이터 베이스와 무관한 것이 되기 위해서는, 데이터 인테페이스를 손쉽게 확장 가능하도록 만들어야 한다. 예를 들면, C++은 거의 모든 시스템에서 사용 가능하기 때문에 데이터 베이스가 C++ 클래스-기반 인터페이스를 사용하도록 하는 것이 좋다.

만일 여러분이 지정 데이터 베이스 시스템에 관련된 기능들을 사용하고 있다면 (예를 들면, MySQL에만 관련되어 있는 REPLACE 명령문), 다른 SQL 서버에 대해서는 동일한 기능을 수행하도록??들이 속도는 느릴지라도, 다른 SQL 서버가 동일한 업무를 처리할 수 있도록 해 준다.

MySQL을 사용한다면, /*! */ 신텍스를 사용해서 명령문에 MySQL-관련 키워드를 추가할 수가 있다. /* */ 내부에 있는 코드는 대부분의 다른 SQL 서버에 의해 코멘트로 인식된다 (무시됨).

만일 정확성 보다는 성능이 보다 중요한 요소일 경우에는, 모든 정보를 캐시하는 어플리케이션 레이어 (application layer)를 생성할 수 있을 것이다. 일정 시간이 지나면 이전 결과를 없애 버림으로써, 캐시를 참신한 상태로 유지 시킬 수 있게 된다. 이렇게 하면, 캐시의 크기를 동적으로 증가 시킬 수가 있고 모든 것들이 정상적으로 될 때까지는 폐기 타임 아웃 (expiration timeout)을 높게 설정할 수 있기 때문에, 고 부하 시스템을 처리할 수가 있다.

이와 같은 경우, 테이블 생성 정보는 초기 캐시의 크기와 테이블이 얼마나 자주 정상적으로 개선 (refresh )되는지에 대한 정보를 가지게 된다.

MySQL 쿼리 캐시를 사용해서 어플리케이션 캐시를 구현 할 수도 있다. 쿼리 캐시를 활성화 시키면, 서버는 쿼리 결과를 다시 사용할 수 있는지를 자세하게 판단한다. 이렇게 하면, 어플리케이션을 단순화 시킬 수가 있다.

MySQL 벤치 마크 슈트

벤치 마크 슈트는 주어진 SQL 동작이 제대로 구현되는지 여부를 사용자에게 알려준다. 여러분은 벤치 마크의 코드와 MySQL 소스 배포판의 sql-bench 디렉토리에 있는 결과를 검토함으로써 벤치 마크가 어떻게 동작을 하는지를 알아볼 수가 있다.

이 벤치 마크는 아직은 단일 쓰레드로만 동작을 하며, 따라서 수행된 동작에 대해서는 최소의 시간을 계산한다는 점을 알아두자. 우리는 향후에 다중 쓰레드 테스트를 추가할 예정이다.

벤치 마크 슈트를 사용하기 위해서는, 아래의 조건이 충족되어야 한다:

• 벤치 마크 슈트는 MySQL 소스 배포판에서 제공한다. 여러분은 http://dev.mysql.com/downloads/에서 다운로드를 하거나, 또는 개발 소스 트리를 사용하도록 한다.
• 벤치 마크 스크립트는 Perl로 작성이 되었으며 데이터 베이스 서버에 대한 접근은 Perl DBI 모듈을 사용하고 있기 때문에 서버에는 DBI가 설치되어 있어야 한다. 또한, 여러분은 테스트 하고자 하는 각각의 서버에 대해 서버-관련 DBD 드라이버도 가지고 있어야 한다. 예를 들면, MySQL, PostgreSQL, 그리고 DB2를 테스트 하기 위해서는, DBD::mysql, DBD::Pg, 그리고 DBD::DB2 모듈을 설치해야 한다.

MySQL 소스 배포판을 다운 로드하면 sql-bench 디렉토리에서 벤치 마크 슈트를 볼 수가 있다. 벤치 마크 테스트를 구동 시키기 위해서는, MySQL를 구축한 후에, sql-bench 디렉토리로 이동해서 run-all-tests 스크립트를 실행해야 한다:

shell> cd sql-bench

shell> perl run-all-tests --server=host-name

host-name은 지원되는 서버 중의 하나의 이름이다. 모든 옵션 및 지원 서버의 리스트를 얻기 위해서는, 아래의 명령어를 실행한다:

shell> perl run-all-tests --help

crash-me 스크립트도 sql-bench 디렉토리에 있다. crash-me는 데이터 베이스 시스템이 어떤 것을 지원하며, 실제로 구동 중인 쿼리에 대해서는 어떤 제약 사항이 있는지 알아 보고자 할 때 사용할 수 있다. 예를 들면, 이 프로그램을 사용하면 다음과 같은 것을 알아볼 수가 있다:

• 지원되는 데이터의 타입
• 지원되는 인덱스의 숫자
• 지원되는 함수
• 실행할 수 있는 쿼리의 최대 크기
• 실행할 수 있는 VARCHAR 컬럼의 최대 크기

여러분은 많은 종류의 데이터 베이스 서버에 대한 crash-me 결과를 http://dev.mysql.com/tech-resources/crash-me.php에서 볼 수 있을 것이다. 벤치 마크 결과에 대한 정보는, http://dev.mysql.com/tech-resources/benchmarks/에서 찾아 보기 바란다.

여러분 자신의 벤치 마크 사용하기