10.9.5 The Optimizer Cost Model

10.9.5 The Optimizer Cost Model

실행 계획을 생성하기 위해 옵티마이저는 쿼리 실행 중 발생하는 다양한 연산의 코스트 추정에 기반한 코스트 모델을 사용합니다. 옵티마이저는 실행 계획과 관련된 결정을 내리기 위해 사용할 수 있는 컴파일된 기본 “코스트 상수” 집합을 가지고 있습니다.

옵티마이저는 또한 실행 계획 구성 중 사용할 코스트 추정값 데이터베이스를 가지고 있습니다. 이러한 추정값은 mysql 시스템 데이터베이스의 server_cost 및 engine_cost 테이블에 저장되며 언제든지 설정을 변경할 수 있습니다. 이들 테이블의 목적은 옵티마이저가 쿼리 실행 계획을 도출하려고 시도할 때 사용하는 코스트 추정값을 쉽게 조정할 수 있도록 하는 것입니다.

• Cost Model General Operation

• The Cost Model Database

• Making Changes to the Cost Model Database

Cost Model General Operation

설정 가능한 옵티마이저 코스트 모델은 다음과 같이 동작합니다:

• 서버는 시작 시 코스트 모델 테이블을 메모리로 읽어 들이고 런타임 시 인메모리 값을 사용합니다. 테이블에 지정된 NULL이 아닌 코스트 추정값은 해당 컴파일된 기본 코스트 상수보다 우선합니다. NULL 추정값은 옵티마이저에게 컴파일된 기본값을 사용하라고 지시합니다.

• 런타임 시, 서버는 코스트 테이블을 다시 읽을 수 있습니다. 이는 스토리지 엔진이 동적으로 로드되거나 FLUSH OPTIMIZER_COSTS 문이 실행될 때 발생합니다.

• 코스트 테이블은 서버 관리자가 테이블의 엔트리를 변경하여 코스트 추정값을 쉽게 조정할 수 있도록 해줍니다. 엔트리의 코스트를 NULL로 설정하여 기본값으로 쉽게 되돌릴 수도 있습니다. 옵티마이저는 인메모리 코스트 값을 사용하므로, 테이블 변경 후에는 변경 사항을 적용하기 위해 FLUSH OPTIMIZER_COSTS를 실행해야 합니다.

• 클라이언트 세션이 시작될 때 현재 인메모리 코스트 추정값은 세션이 종료될 때까지 해당 세션 전체에 적용됩니다. 특히 서버가 코스트 테이블을 다시 읽더라도 변경된 추정값은 이후에 시작되는 세션에만 적용됩니다. 기존 세션에는 영향을 미치지 않습니다.

• 코스트 테이블은 특정 서버 인스턴스에만 유효합니다. 서버는 코스트 테이블 변경 사항을 레플리카로 복제하지 않습니다.

The Cost Model Database

옵티마이저 코스트 모델 데이터베이스는 쿼리 실행 중 발생하는 연산에 대한 코스트 추정 정보가 포함된 mysql 시스템 데이터베이스의 두 개의 테이블로 구성됩니다:

• server_cost: 일반적인 서버 연산에 대한 옵티마이저 코스트 추정값

• engine_cost: 특정 스토리지 엔진에 특화된 연산에 대한 옵티마이저 코스트 추정값

server_cost 테이블은 다음 컬럼을 포함합니다:

• cost_name

  코스트 모델에서 사용되는 코스트 추정값의 이름입니다. 이름은 대소문자를 구분하지 않습니다. 서버가 이 테이블을 읽을 때 코스트 이름을 인식하지 못하면 에러 로그에 워닝을 기록합니다.

• cost_value

  코스트 추정값입니다. 값이 NULL이 아니면 서버는 이를 코스트로 사용합니다. 그렇지 않으면 기본 추정값(컴파일된 값)을 사용합니다. DBA는 이 컬럼을 업데이트하여 코스트 추정값을 변경할 수 있습니다. 서버가 이 테이블을 읽을 때 코스트 값이 잘못된 값(0 이하)이라고 판단하면 에러 로그에 워닝을 기록합니다.

  기본 코스트 추정값(NULL을 지정한 엔트리에 대해)을 재정의하려면 코스트를 NULL이 아닌 값으로 설정합니다. 기본값으로 되돌리려면 값을 NULL로 설정합니다. 그런 다음 서버에 코스트 테이블을 다시 읽도록 지시하기 위해 FLUSH OPTIMIZER_COSTS를 실행합니다.

• last_update

  마지막 로우 업데이트 시간입니다.

• comment

  코스트 추정값과 관련된 설명 코멘트입니다. DBA는 이 컬럼을 사용하여 해당 코스트 추정 로우가 특정 값을 저장하고 있는 이유에 대한 정보를 제공할 수 있습니다.

• default_value

  코스트 추정값의 기본(컴파일된) 값입니다. 이 컬럼은 읽기 전용 생성 칼럼이며, 관련된 코스트 추정값이 변경되더라도 해당 값을 유지합니다. 런타임 시 테이블에 추가된 로우의 경우, 이 컬럼의 값은 NULL입니다.

server_cost 테이블의 프라이머리 키는 cost_name 컬럼이므로, 어떤 코스트 추정값에 대해서도 여러 개의 엔트리를 생성할 수 없습니다.

서버는 server_cost 테이블에 대해 다음과 같은 cost_name 값을 인식합니다:

• disk_temptable_create_cost, disk_temptable_row_cost

  디스크 기반 스토리지 엔진(InnoDB 또는 MyISAM)에 저장되는 내부 생성 임시 테이블에 대한 코스트 추정값입니다. 이 값들을 증가시키면 내부 임시 테이블 사용의 코스트 추정값이 증가하여 옵티마이저가 이러한 테이블 사용이 적은 쿼리 플랜을 더 선호하게 됩니다. 이러한 테이블에 대한 정보는
  Section 10.4.4, “Internal Temporary Table Use in MySQL”을 참조하십시오.

  이러한 디스크 파라미터의 기본값이 해당 메모리 파라미터 (memory_temptable_create_cost, memory_temptable_row_cost)의 기본값보다 더 큰 것은 디스크 기반 테이블 처리의 코스트가 더 크다는 사실을 반영합니다.

• key_compare_cost

  레코드 키를 비교하는 코스트입니다. 이 값을 증가시키면 많은 키를 비교하는 쿼리 플랜의 코스트가 더 커집니다. 예를 들어, 인덱스를 사용하여 정렬을 피하는 쿼리 플랜에 비해 filesort를 수행하는 쿼리 플랜이 상대적으로 더 비싸게 됩니다.

• memory_temptable_create_cost, memory_temptable_row_cost

  MEMORY 스토리지 엔진에 저장되는 내부 생성 임시 테이블에 대한 코스트 추정값입니다. 이 값들을 증가시키면 내부 임시 테이블 사용의 코스트 추정값이 증가하여 옵티마이저가 이러한 테이블 사용이 적은 쿼리 플랜을 더 선호하게 됩니다. 이러한 테이블에 대한 정보는
  Section 10.4.4, “Internal Temporary Table Use in MySQL”을 참조하십시오.

  이러한 메모리 파라미터의 기본값이 해당 디스크 파라미터 (disk_temptable_create_cost, disk_temptable_row_cost)의 기본값보다 더 작은 것은 메모리 기반 테이블 처리의 코스트가 더 적다는 사실을 반영합니다.

• row_evaluate_cost

  레코드 조건을 평가하는 코스트입니다. 이 값을 증가시키면 많은 로우를 검사하는 쿼리 플랜의 코스트가 적은 로우를 검사하는 쿼리 플랜에 비해 더 커집니다. 예를 들어, 테이블 스캔은 더 적은 로우를 읽는 레인지 스캔에 비해 상대적으로 더 비싸게 됩니다.

engine_cost 테이블은 다음 컬럼을 포함합니다:

• engine_name

  이 코스트 추정값이 적용되는 스토리지 엔진의 이름입니다. 이름은 대소문자를 구분하지 않습니다. 값이 default이면, 자체 이름이 있는 엔트리가 없는 모든 스토리지 엔진에 적용됩니다. 서버가 이 테이블을 읽을 때 엔진 이름을 인식하지 못하면 에러 로그에 워닝을 기록합니다.

• device_type

  이 코스트 추정값이 적용되는 디바이스 타입입니다. 이 컬럼은 하드 디스크 드라이브와 솔리드 스테이트 드라이브 같은 서로 다른 스토리지 디바이스 타입에 대해 서로 다른 코스트 추정값을 지정하는 용도로 의도되었습니다. 현재 이 정보는 사용되지 않으며, 허용되는 값은 0뿐입니다.

• cost_name

  server_cost 테이블에서와 동일합니다.

• cost_value

  server_cost 테이블에서와 동일합니다.

• last_update

  server_cost 테이블에서와 동일합니다.

• comment

  server_cost 테이블에서와 동일합니다.

• default_value

  코스트 추정값의 기본(컴파일된) 값입니다. 이 컬럼은 읽기 전용 생성 칼럼이며, 관련된 코스트 추정값이 변경되더라도 해당 값을 유지합니다. 런타임 시 테이블에 추가된 로우의 경우, 이 컬럼의 값은 NULL입니다. 예외적으로, 로우의 cost_name 값이 원래 로우 중 하나와 동일한 경우, 그 로우의 default_value 컬럼 값도 동일한 값을 갖습니다.

engine_cost 테이블의 프라이머리 키는 (cost_name, engine_name, device_type) 컬럼으로 구성된 튜플이므로, 이들 값 조합에 대해 여러 개의 엔트리를 생성할 수 없습니다.

서버는 engine_cost 테이블에 대해 다음과 같은 cost_name 값을 인식합니다:

• io_block_read_cost

  디스크에서 인덱스 또는 데이터 블록을 읽는 코스트입니다. 이 값을 증가시키면 많은 디스크 블록을 읽는 쿼리 플랜의 코스트가 더 적은 디스크 블록을 읽는 쿼리 플랜에 비해 더 커집니다. 예를 들어, 테이블 스캔은 더 적은 블록을 읽는 레인지 스캔에 비해 상대적으로 더 비싸게 됩니다.

• memory_block_read_cost

  io_block_read_cost와 유사하지만, 인메모리 데이터베이스 버퍼에서 인덱스 또는 데이터 블록을 읽는 코스트를 나타냅니다.

io_block_read_cost와 memory_block_read_cost 값이 서로 다르면, 동일한 쿼리의 두 번의 실행 사이에 실행 계획이 변경될 수 있습니다. 메모리 액세스 코스트가 디스크 액세스 코스트보다 더 낮다고 가정해 봅시다. 이 경우, 서버 시작 시 버퍼 풀에 데이터가 읽히기 전에는 쿼리 실행 후(데이터가 메모리에 있는 상태)와 다른 계획을 얻을 수 있습니다.

Making Changes to the Cost Model Database

기본값에서 코스트 모델 파라미터를 변경하려는 DBA의 경우, 값을 두 배로 늘리거나 절반으로 줄여 보고 그 효과를 측정해 보십시오.

io_block_read_cost 및 memory_block_read_cost 파라미터에 대한 변경이 가장 가치 있는 결과를 가져올 가능성이 높습니다. 이러한 파라미터 값은 데이터 액세스 메서드에 대한 코스트 모델이 서로 다른 소스에서 정보를 읽는 코스트, 즉 디스크에서 정보를 읽는 코스트와 이미 메모리 버퍼에 있는 정보를 읽는 코스트를 고려할 수 있게 해줍니다. 예를 들어, 다른 모든 조건이 동일하다면 io_block_read_cost를 memory_block_read_cost보다 더 큰 값으로 설정하면, 옵티마이저는 디스크에서 읽어야 하는 플랜보다 이미 메모리에 있는 정보를 읽는 쿼리 플랜을 더 선호하게 됩니다.

다음 예는 io_block_read_cost의 기본값을 변경하는 방법을 보여줍니다:

1UPDATE mysql.engine_cost
2  SET cost_value = 2.0
3  WHERE cost_name = 'io_block_read_cost';
4FLUSH OPTIMIZER_COSTS;

다음 예는 InnoDB 스토리지 엔진에 대해서만 io_block_read_cost 값을 변경하는 방법을 보여줍니다:

1INSERT INTO mysql.engine_cost
2  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
3  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
4FLUSH OPTIMIZER_COSTS;