월: 2016 8월

Order By .. Low Limit Bug

MySQL 5.6 에서 ORDER BY (GROUP BY 도 영향이 있는 듯) 와 LIMIT 이 결합되는 쿼리일 때, LIMIT 0, 100 등의 increment 값이 매우 낮게 지정되어있는 경우 ORDER BY 에 적용하는 정렬 인덱스를 옵티마이저가 변경하는 경우가 있음, 본래 row estimation 해서 cost 기반으로 판단하는 것이 성능상에 이득임에도 불구하고 특정 케이스(옵티마이저가 판단하는 row estimation 값 임계치에 부합하면)에서 cost 대신 휴리스틱heuristic 기반으로 사용인덱스가 결정이 됨

optimizer tracing 을 해보면 join optimization 부분에서 recheck_reason 이 low_limit 으로
row count 가 매우 많은 인덱스로 변경하는 것을 확인할 수 있음

5.7.6 에서 cost 기반으로 항상 동작하도록 패치되었으나, 5.6 으로 백포팅 되지 않음..

5.7.6 변경로그//
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html
레퍼런스: 버그 #73837, #19579507, #16522053.

버그리포트//
https://bugs.mysql.com/bug.php?id=74602
https://bugs.mysql.com/bug.php?id=73837
https://bugs.launchpad.net/percona-server/+bug/1362212

5.7패치로그//
http://dev.mysql.com/worklog/task/?id=6986
Make switching of index due to small limit cost-based (WL#6986) : This work by Chaithra Gopalareddy makes the decision in make_join_select() of whether to switch to a new index in order to support “ORDER BY … LIMIT N” cost-based. This work fixes Bug#73837.

예시//
#cost 가 나쁜 const key IDX_BAD 가 후보로 들어감
“rows_estimation”: [
{
“table”: “`HISTORY_TABLE` `A`”,
“const_keys_added”: {
“keys”: [
“IDX_BAD”
],
“cause”: “group_by”
},
“range_analysis”: {
“table_scan”: {
“rows”: 23326957,
“cost”: 5.68e6
},

#cost 좋은 IDX_GOOD 처음에 선택했다가
“chosen_range_access_summary”: {
“range_access_plan”: {
“type”: “range_scan”,
“index”: “IDX_GOOD”,
“rows”: 78210,
“ranges”: [
“2016-08-09 00:00:00 <= COMPLETE_DT <= 2016-08-09 23:59:59”
]
},
“rows_for_plan”: 78210,
“cost_for_plan”: 93853,
“chosen”: true
}

#low_limit 의 이유로 rechecking 이 들어감
“attached_conditions_computation”: [
{
“table”: “`HISTORY_TABLE` `A`”,
“rechecking_index_usage”: {
“recheck_reason”: “low_limit”,
“limit”: 100,
“row_estimate”: 78210
}
}

#인덱스 정렬을 위한 엑세스 방식을 IDX_BAD로 변경
{
“reconsidering_access_paths_for_index_ordering”: {
“clause”: “GROUP BY”,
“index_order_summary”: {
“table”: “`HISTORY_TABLE` `A`”,
“index_provides_order”: true,
“order_direction”: “asc”,
“disabled_pushed_condition_on_old_index”: true,
“index”: “IDX_BAD”,
“plan_changed”: true,
“access_type”: “index_scan”
}
}
}

MySQL 5.6 fsp rounding 문제

https://bugs.mysql.com/bug.php?id=68760
https://bugs.mysql.com/bug.php?id=76948
https://bugs.mysql.com/bug.php?id=82325

5.6 에서 fsp 를 허용하면서 밀리세컨드 부분을 반올림(round)하는 버그가 있다는 리포트들이 있음
실제 지정한 fsp 값을 넘어서는 값을 넣게되면 rounding 함 (5.6.30 에서 테스트)

MySQL 구버전 호환성을 위해 fsp 선언이 생략되면 SQL 표준인 6이 아니라 0으로 디폴트 설정한다고 레퍼런스에 되어 있는데,
If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

higher precision 값을 lower precision 데이터타입에 집어넣는다면 rounding 하는 것이 SQL의 표준 동작방식이며 따라서 DATETIME(0)=DATETIME 에 DATETIME(1)의 값을 넣는다면 warning 없이 rounding 되는 것이 표준이긴 하다라는 것이 오라클 엔지니어 의견
다만 MySQL 구버전 호환성…을 위해 비표준 디폴트값을 가져가고 있으니 warning 이 발생하도록 조치하는 것을 고려해봐야 한다고 함

[25 Jul 18:50] Roy Lyseng
SQL standard does not require a warning when assigning a value with higher precision to a target with lower precision. Thus, assigning a datetime(1) value like ‘2016-07-22 12:49:07.5’ to a datetime(0) column will cause automatic rounding, and the inserted value should be ‘2016-07-22 12:49:08’, with no warning issued.
However, when doing the comparison, both values are converted to datetime(1), so the comparison is done as ‘2016-07-22 12:49:07.5’ = ‘2016-07-22 12:49:08.0’, which is obviously false.
I would say this is not a bug. We might consider adding a warning for the truncation as part of the assignment, but that would be a feature request.
Note also that the problem is independent of whether rounding or truncation is used.

5.5 에서는 아예 fsp 가 무시되어 truncated 되다보니 이슈가 되나 봄