10.15.4 Tuning Trace Purging

10.15.4 Tuning Trace Purging

기본적으로 각 새로운 trace는 이전 trace를 덮어씁니다. 따라서 statement가 stored procedure, stored function, trigger 호출과 같은 substatement를 포함하는 경우, 최상위 statement와 각 substatement는 각각 하나의 trace를 생성하지만, 실행이 끝나면 마지막 substatement의 trace만 보이게 됩니다.

다른 substatement의 trace를 보고자 하는 사용자는 원하는 substatement에 대해 tracing을 활성화 또는 비활성화할 수 있지만, 이를 위해서는 루틴 코드를 수정해야 하며, 이는 항상 가능한 것은 아닙니다. 또 다른 해결책은 trace purging을 튜닝하는 것입니다. 이는 optimizer_trace_offset 및 optimizer_trace_limit 시스템 변수를 다음과 같이 설정하여 수행합니다:

1SET optimizer_trace_offset=offset, optimizer_trace_limit=limit;

_offset_은 부호 있는 정수(기본값 -1)이고, _limit_은 양의 정수(기본값 1)입니다. 이러한 SET statement는 다음과 같은 효과를 가집니다:

• 이전에 저장된 모든 trace가 메모리에서 제거됩니다.

• 이후 OPTIMIZER_TRACE table에 대해 수행되는 SELECT는 _offset_이 0 이상인 경우 저장된 trace 중 _offset_번째로 오래된 trace들부터 시작하여 처음 _limit_개의 trace를 반환하거나, _offset_이 0보다 작은 경우 저장된 trace 중 _-offset_번째로 최신 trace들부터 시작하여 처음 _limit_개의 trace를 반환합니다.

예:

• SET optimizer_trace_offset=-1, optimizer_trace_limit=1: 가장 최근 trace가 표시됩니다(기본값).

• SET optimizer_trace_offset=-2, optimizer_trace_limit=1: 마지막에서 두 번째 trace가 표시됩니다.

• SET optimizer_trace_offset=-5, optimizer_trace_limit=5: 마지막 다섯 개의 trace가 표시됩니다.

따라서 _offset_의 음수 값은 관심 있는 substatement가 stored routine의 마지막 몇 개일 때 유용할 수 있습니다. 예를 들면 다음과 같습니다:

1SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
2
3CALL stored_routine(); # 이 routine에는 5개보다 많은 substatement가 있음
4
5SELECT * FROM information_schema.OPTIMIZER_TRACE; # 마지막 5개의 trace만 확인

양수 _offset_은 관심 있는 substatement가 stored routine의 처음 몇 개라는 것을 알고 있을 때 유용할 수 있습니다.

이 두 변수를 보다 정확하게 설정할수록 사용되는 메모리는 더 적어집니다. 예를 들어 SET optimizer_trace_offset=0, optimizer_trace_limit=5는 다섯 개의 trace를 저장하기에 충분한 메모리가 필요하므로, 처음 세 개만 필요하다면 SET optimizer_trace_offset=0, optimizer_trace_limit=3을 사용하는 것이 더 좋습니다. 왜냐하면 _limit_개의 trace 이후에는 tracing이 중지되기 때문입니다.

stored routine에는 많은 substatement를 실행하는 루프가 있을 수 있고, 이는 많은 trace를 생성하여 많은 메모리를 사용할 수 있습니다. 이러한 경우 offset 및 _limit_에 적절한 값을 선택하면 예를 들어 루프의 한 번의 iteration으로 tracing을 제한할 수 있습니다. 이는 또한 tracing이 실행 속도에 미치는 영향도 감소시킵니다.

_offset_이 0 이상인 경우, _limit_개의 trace만 메모리에 유지됩니다. _offset_이 0보다 작은 경우에는 그렇지 않습니다. 대신 _-offset_개의 trace가 메모리에 유지됩니다. _limit_이 _-offset_보다 작더라도, 마지막 statement를 제외하면, 마지막 statement는 여전히 trace되어야 합니다. 왜냐하면 하나의 statement를 더 실행한 후에는 그 statement가 limit 내에 있게 되기 때문입니다. offset이 0보다 작으면 끝에서부터 계산되므로, 더 많은 statement가 실행될수록 “window”가 이동합니다.

trace producer 레벨에서의 제한인 optimizer_trace_offset 및 optimizer_trace_limit을 사용하는 것은 trace consumer (SQL) 레벨에서 SELECT * FROM OPTIMIZER_TRACE LIMIT limit OFFSET offset으로 offset이나 limit을 설정하는 것보다 더 나은(더 빠른) 속도와 더 적은 메모리 사용량을 제공합니다. 후자의 방법은 거의 아무것도 절약하지 못합니다.