29.10 Performance Schema Statement Digests and Sampling

29.10 Performance Schema Statement Digests and Sampling

MySQL 서버는 statement digest 정보를 유지할 수 있습니다. digesting 과정은 각 SQL statement를 정규화된 형태( statement digest)로 변환하고, 정규화된 결과로부터 SHA-256 hash 값( digest hash value)을 계산합니다. 정규화는 서로 비슷한 statement들을 그룹화하고 요약할 수 있게 하여, 서버가 어떤 유형의 statement를 얼마나 자주 실행하는지에 대한 정보를 드러낼 수 있게 합니다. 각 digest마다, 그 digest를 생성하는 대표 statement가 sample로서 저장됩니다. 이 절에서는 statement digesting과 sampling이 어떻게 발생하는지, 그리고 그것이 어떻게 유용할 수 있는지 설명합니다.

Digesting은 Performance Schema 사용 가능 여부와 관계없이 parser에서 수행되므로, MySQL Enterprise Firewall 및 쿼리 리라이트 플러그인과 같은 다른 기능이 statement digest에 접근할 수 있습니다.

• Statement Digest General Concepts

• Statement Digests in the Performance Schema

• Statement Digest Memory Use

• Statement Sampling

Statement Digest General Concepts

Parser가 SQL statement를 수신하면, 그 digest가 필요할 경우 statement digest를 계산합니다. 다음 조건들 중 하나라도 참이면 digest가 필요합니다:

• Performance Schema digest 인스트루멘테이션이 활성화되어 있음

• MySQL Enterprise Firewall이 활성화되어 있음

• 쿼리 리라이트 플러그인이 활성화되어 있음

Parser는 또한 애플리케이션이 호출하여 SQL statement로부터 각각 정규화된 statement digest와 digest hash value를 계산할 수 있는 STATEMENT_DIGEST_TEXT() 및 STATEMENT_DIGEST() 함수에 의해 사용됩니다.

max_digest_length 시스템 변수 값은 세션당 정규화된 statement digest 계산에 사용 가능한 최대 바이트 수를 결정합니다. digest 계산 중 그만큼의 공간이 사용되면, 잘림(truncation)이 발생합니다. 즉, 파스된 statement의 추가 토큰은 더 이상 수집되지 않으며 digest 값에 반영되지 않습니다. 파스된 토큰이 그만큼의 바이트를 초과하는 부분에서만 서로 다른 statement는 동일한 정규화된 statement digest를 생성하며, 비교되거나 digest 통계를 위해 집계될 경우 동일한 것으로 간주됩니다.

주의

max_digest_length 시스템 변수를 0으로 설정하면 digest 생성이 비활성화되고, 이는 digest를 필요로 하는 서버 기능 역시 비활성화합니다.

정규화된 statement가 계산된 후, 그로부터 SHA-256 hash 값이 계산됩니다. 추가로:

• MySQL Enterprise Firewall이 활성화되어 있으면, 이것이 호출되며 계산된 digest가 이에 제공됩니다.

• 어떤 쿼리 리라이트 플러그인이 활성화되어 있으면, 이것이 호출되며 statement digest와 digest value가 이에 제공됩니다.

• Performance Schema에 digest 인스트루멘테이션이 활성화되어 있으면, 정규화된 statement digest의 사본을 만들며, 이를 위해 최대 performance_schema_max_digest_length 바이트를 할당합니다. 따라서 performance_schema_max_digest_length가 max_digest_length보다 작으면, 원본에 비해 사본이 잘립니다. 정규화된 statement digest의 사본은 적절한 Performance Schema 테이블에 원본 정규화된 statement로부터 계산된 SHA-256 hash 값과 함께 저장됩니다. (Performance Schema가 원본에 비해 정규화된 statement digest의 사본을 잘라내더라도, SHA-256 hash 값을 다시 계산하지는 않습니다.)

Statement normalization은 statement 텍스트를 보다 표준화된 digest 문자열 표현으로 변환하여, 일반적인 statement 구조를 유지하는 동시에 구조에 필수적이지 않은 정보를 제거합니다:

• 데이터베이스 및 테이블 이름과 같은 오브젝트 식별자는 유지됩니다.

• 리터럴 값은 파라미터 마커로 변환됩니다. 정규화된 statement는 이름, 비밀번호, 날짜 등과 같은 정보를 유지하지 않습니다.

• 코멘트는 제거되고 공백(whitespace)은 조정됩니다.

다음 statement를 살펴보십시오:

1SELECT * FROM orders WHERE customer_id=10 AND quantity>20
2SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100

이 statement들을 정규화하기 위해, parser는 데이터 값을 ?로 대체하고 공백을 조정합니다. 두 statement는 동일한 정규화된 형태를 생성하며, 따라서 “동일한 것”으로 간주됩니다:

1SELECT * FROM orders WHERE customer_id = ? AND quantity > ?

정규화된 statement는 더 적은 정보를 포함하지만 여전히 원본 statement를 대표합니다. 서로 다른 데이터 값을 가지는 다른 유사한 statement들도 동일한 정규화 형태를 가지게 됩니다.

이제 다음 statement를 살펴보십시오:

1SELECT * FROM customers WHERE customer_id = 1000
2SELECT * FROM orders WHERE customer_id = 1000

이 경우, 오브젝트 식별자가 다르므로 정규화된 statement도 서로 다릅니다:

1SELECT * FROM customers WHERE customer_id = ?
2SELECT * FROM orders WHERE customer_id = ?

정규화 결과가 digest 버퍼에서 사용 가능한 공간(이는 max_digest_length에 의해 결정됨)을 초과하면, 잘림이 발생하고 텍스트는 “...”로 끝납니다. “...” 뒤에 나오는 부분에서만 서로 다른 긴 정규화 statement는 동일한 것으로 간주됩니다. 다음 statement를 보십시오:

1SELECT * FROM mytable WHERE cola = 10 AND colb = 20
2SELECT * FROM mytable WHERE cola = 10 AND colc = 20

만약 cutoff가 AND 바로 뒤에서 발생하면, 두 statement는 다음과 같은 동일한 정규화 형태를 갖습니다:

1SELECT * FROM mytable WHERE cola = ? AND ...

이 경우, 두 번째 컬럼 이름의 차이는 손실되고 두 statement는 동일한 것으로 간주됩니다.

Statement Digests in the Performance Schema

Performance Schema에서 statement digesting은 다음 요소를 포함합니다:

• setup_consumers 테이블의 statements_digest 컨슈머는 Performance Schema가 digest 정보를 유지할지 여부를 제어합니다. 자세한 내용은 Statement Digest Consumer를 참조하십시오.

• statement 이벤트 테이블 (events_statements_current, events_statements_history, events_statements_history_long)에는 정규화된 statement digest와 해당 digest SHA-256 hash 값을 저장하는 컬럼이 있습니다:

  • DIGEST_TEXT는 정규화된 statement digest의 텍스트입니다. 이는 최대 max_digest_length 바이트까지 계산된 원본 정규화 statement의 사본이며, 필요하다면 performance_schema_max_digest_length 바이트까지 추가로 잘립니다.

  • DIGEST는 원본 정규화 statement로부터 계산된 digest SHA-256 hash 값입니다.

Section 29.12.6, “Performance Schema Statement Event Tables”를 참조하십시오.

• events_statements_summary_by_digest summary 테이블은 집계된 statement digest 정보를 제공합니다. 이 테이블은 statement 정보를 SCHEMA_NAME과 DIGEST 조합별로 집계합니다. Performance Schema는 aggregation에 SHA-256 hash 값을 사용합니다. 이는 계산이 빠르고 충돌을 최소화하는 유리한 통계적 분포를 가지기 때문입니다. Section 29.12.20.3, “Statement Summary Tables”를 참조하십시오.

일부 Performance 테이블에는 digest가 계산되는 원본 SQL statement를 저장하는 컬럼이 있습니다:

• SQL_TEXT 컬럼: events_statements_current, events_statements_history, events_statements_history_long statement 이벤트 테이블.

• QUERY_SAMPLE_TEXT 컬럼: events_statements_summary_by_digest summary 테이블.

Statement 표시에 사용 가능한 최대 공간은 기본적으로 1024바이트입니다. 이 값을 변경하려면 서버 시작 시 performance_schema_max_sql_text_length 시스템 변수를 설정하십시오. 변경 사항은 앞에서 언급한 모든 컬럼에 필요한 스토리지에 영향을 줍니다.

performance_schema_max_digest_length 시스템 변수는 Performance Schema에서 statement당 digest 값 스토리지에 사용 가능한 최대 바이트 수를 결정합니다. 그러나 키워드 및 리터럴 값과 같은 statement 요소의 내부 인코딩 때문에 statement digest의 표시 길이는 사용 가능한 버퍼 크기보다 길 수 있습니다. 따라서 statement 이벤트 테이블의 DIGEST_TEXT 컬럼에서 선택된 값은 performance_schema_max_digest_length 값을 초과하는 것처럼 보일 수 있습니다.

events_statements_summary_by_digest summary 테이블은 서버가 실행한 statement에 대한 프로파일을 제공합니다. 이 테이블은 애플리케이션이 어떤 종류의 statement를 얼마나 자주 실행하는지 보여줍니다. 애플리케이션 개발자는 이 정보를 테이블의 다른 정보와 함께 사용하여 애플리케이션의 성능 특성을 평가할 수 있습니다.

예를 들어, wait 시간, lock 시간 또는 인덱스 사용을 보여주는 테이블 컬럼은 비효율적인 쿼리 유형을 강조할 수 있습니다. 이는 개발자에게 애플리케이션의 어떤 부분이 주의가 필요한지에 대한 통찰을 제공합니다.

events_statements_summary_by_digest summary 테이블은 고정 크기를 가집니다. 기본적으로 Performance Schema는 startup 시 사용할 크기를 추정합니다. 테이블 크기를 명시적으로 지정하려면 서버 시작 시 performance_schema_digests_size 시스템 변수를 설정하십시오. 테이블이 가득 차면, Performance Schema는 SCHEMA_NAME과 DIGEST 값이 테이블 내 기존 값과 일치하지 않는 statement를 SCHEMA_NAME과 DIGEST가 NULL로 설정된 특수 row에 그룹화합니다.

이를 통해 모든 statement를 카운트할 수 있습니다. 그러나 이 특수 row가 실행된 statement의 상당 비율을 차지한다면, performance_schema_digests_size를 증가시켜 summary 테이블 크기를 늘리는 것이 바람직할 수 있습니다.

Statement Digest Memory Use

끝부분만 다른 매우 긴 statement를 생성하는 애플리케이션의 경우, max_digest_length를 증가시키면, 그렇지 않으면 동일한 digest로 집계될 statement를 구별할 수 있는 digest 계산이 가능해집니다. 반대로 max_digest_length를 줄이면 서버가 digest 스토리지에 할당하는 메모리는 줄어들지만, 더 긴 statement가 동일한 digest로 집계될 가능성은 증가합니다. 관리자는 특히 많은 수의 동시 세션을 포함하는 워크로드의 경우( 서버는 세션당 max_digest_length 바이트를 할당함), 더 큰 값을 사용하면 그에 상응하여 더 많은 메모리가 필요하다는 점을 명심해야 합니다.

앞에서 설명했듯이, parser에 의해 계산되는 정규화된 statement digest는 최대 max_digest_length 바이트로 제한되지만, Performance Schema에 저장되는 정규화된 statement digest는 performance_schema_max_digest_length 바이트를 사용합니다. max_digest_length와 performance_schema_max_digest_length의 상대적 값에 대해 다음 메모리 사용 고려사항이 적용됩니다:

• max_digest_length가 performance_schema_max_digest_length보다 작은 경우:

  • Performance Schema 이외의 서버 기능은 최대 max_digest_length 바이트를 차지하는 정규화된 statement digest를 사용합니다.

  • Performance Schema는 저장하는 정규화된 statement digest를 추가로 잘라내지는 않지만, digest당 max_digest_length 바이트보다 많은 메모리를 할당하며, 이는 불필요합니다.

• max_digest_length가 performance_schema_max_digest_length와 같은 경우:

  • Performance Schema 이외의 서버 기능은 최대 max_digest_length 바이트를 차지하는 정규화된 statement digest를 사용합니다.

  • Performance Schema는 저장하는 정규화된 statement digest를 추가로 잘라내지 않으며, digest당 max_digest_length 바이트와 동일한 양의 메모리를 할당합니다.

• max_digest_length가 performance_schema_max_digest_length보다 큰 경우:

  • Performance Schema 이외의 서버 기능은 최대 max_digest_length 바이트를 차지하는 정규화된 statement digest를 사용합니다.

  • Performance Schema는 저장하는 정규화된 statement digest를 추가로 잘라내며, digest당 max_digest_length 바이트보다 적은 메모리를 할당합니다.

Performance Schema statement 이벤트 테이블은 많은 digest를 저장할 수 있으므로, performance_schema_max_digest_length를 max_digest_length보다 작게 설정하면 관리자가 다음 요소 간 균형을 맞출 수 있습니다:

• Performance Schema 외부의 서버 기능에서 긴 정규화 statement digest를 사용할 필요

• 각 세션이 digest 계산 메모리를 할당하는 많은 동시 세션

• 많은 statement digest를 저장할 때 Performance Schema statement 이벤트 테이블의 메모리 소비를 제한할 필요

performance_schema_max_digest_length 설정은 세션당이 아니라 statement당이며, 세션은 events_statements_history 테이블에 여러 statement를 저장할 수 있습니다. 이 테이블의 일반적인 statement 수는 세션당 10개이므로, 각 세션은 이 테이블만으로도 performance_schema_max_digest_length 값의 10배에 해당하는 메모리를 소비합니다.

또한, 전역적으로 수집되는 statement(및 digest)가 많이 있으며, 특히 events_statements_history_long 테이블이 그렇습니다. 여기에서도 저장된 N 개의 statement는 performance_schema_max_digest_length 값의 N 배에 해당하는 메모리를 소비합니다.

SQL statement 스토리지 및 digest 계산에 사용되는 메모리 양을 평가하려면, SHOW ENGINE PERFORMANCE_SCHEMA STATUS statement를 사용하거나 다음 인스트루먼트를 모니터링하십시오:

1mysql> SELECT NAME
2       FROM performance_schema.setup_instruments
3       WHERE NAME LIKE '%.sqltext';
4+------------------------------------------------------------------+
5| NAME                                                             |
6+------------------------------------------------------------------+
7| memory/performance_schema/events_statements_history.sqltext      |
8| memory/performance_schema/events_statements_current.sqltext      |
9| memory/performance_schema/events_statements_history_long.sqltext |
10+------------------------------------------------------------------+
11
12mysql> SELECT NAME
13       FROM performance_schema.setup_instruments
14       WHERE NAME LIKE 'memory/performance_schema/%.tokens';
15+----------------------------------------------------------------------+
16| NAME                                                                 |
17+----------------------------------------------------------------------+
18| memory/performance_schema/events_statements_history.tokens           |
19| memory/performance_schema/events_statements_current.tokens           |
20| memory/performance_schema/events_statements_summary_by_digest.tokens |
21| memory/performance_schema/events_statements_history_long.tokens      |
22+----------------------------------------------------------------------+

Statement Sampling

Performance Schema는 events_statements_summary_by_digest 테이블에서 각 digest 값에 대해 해당 digest를 생성하는 대표 statement를 수집하기 위해 statement sampling을 사용합니다. 다음 컬럼이 sample statement 정보를 저장합니다: QUERY_SAMPLE_TEXT (statement의 텍스트), QUERY_SAMPLE_SEEN (statement가 관측된 시점), QUERY_SAMPLE_TIMER_WAIT (statement wait 또는 실행 시간). Performance Schema는 sample statement를 선택할 때마다 이 세 컬럼을 모두 갱신합니다.

새 테이블 row가 삽입될 때, row digest 값을 생성한 statement는 digest와 연관된 현재 sample statement로 저장됩니다. 이후 서버가 동일한 digest 값을 가진 다른 statement를 볼 때마다, 새 statement를 사용해 현재 sample statement를 대체할지(즉, resample할지) 여부를 결정합니다. resampling 정책은 현재 sample statement와 새 statement의 wait 시간 비교 및 선택적으로 현재 sample statement의 age를 기준으로 합니다:

• wait 시간 기반 resampling: 새 statement wait 시간이 현재 sample statement의 wait 시간보다 길면, 새 statement가 현재 sample statement가 됩니다.

• age 기반 resampling: performance_schema_max_digest_sample_age 시스템 변수 값이 0보다 크고 현재 sample statement가 그 값(초 단위)보다 오래되었으면, 현재 statement는 “너무 오래된” 것으로 간주되며 새 statement가 이를 대체합니다. 이는 새 statement의 wait 시간이 현재 sample statement의 wait 시간보다 짧더라도 발생합니다.

기본적으로 performance_schema_max_digest_sample_age는 60초(1분)입니다. age로 인해 sample statement가 얼마나 빨리 “만료”되는지를 변경하려면 이 값을 증가 또는 감소시키십시오. resampling 정책의 age 기반 부분을 비활성화하려면 performance_schema_max_digest_sample_age를 0으로 설정하십시오.