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 되다보니 이슈가 되나 봄

xtrabackup apply-log 시 assertion failure 발생

MySQL 5.6.29, Percona xtrabackup 2.1.9

apply-log 시 마지막 단계에서 assertion failure 발생
로그//
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
2016-04-25 18:05:41 7f7fd164f700 InnoDB: Assertion failure in thread 140186950629120 in file buf0flu.cc line 2501
InnoDB: Failing assertion: UT_LIST_GET_LEN(buf_pool->flush_list) == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
innobackupex: Error:
innobackupex: xtrabackup (2nd execution) failed at /usr/bin/innobackupex line 2572.

버그리포트//
https://bugs.launchpad.net/percona-xtrabackup/+bug/1368846
https://bugs.launchpad.net/percona-xtrabackup/+bug/1399873 (duplicated)

https://www.percona.com/doc/percona-xtrabackup/2.3/release-notes/2.2/2.2.10.html
현재 2.2, 2.3 버전에서 fix 릴리즈가 된 것으로 보이나 2.1 은 아님
Fixed Percona XtraBackup assertion caused by dirty pages remaining in the buffer pool after the log was fully applied. Bug fixed #1368846.

https://github.com/percona/percona-xtrabackup/pull/19
Looking at the stack trace I see pending IO complete (read)
operations which caused ibuf merge. This merge happened after
page_cleaner thread stopped, so there were no thread to flush dirty
pages from buffer pool. We should not allow page_cleaner to stop
when there are pending reads.
페이지클리너가 스탑된 상태에서 ibuf merge 발생시 버퍼풀에서 더티페이지 플러싱할 쓰레드가 없어서 에러, 펜딩된 읽기작업이 있을 때 페이지클리너를 스탑시키지 않게끔 하였음

결론은 .. 업데이트 해야할 때

IN 서브쿼리 SELECT 리플리케이션 시 지연

슬레이브: 5.6, 마스터: 5.5

IN절에 다건을 검색하여 업데이트 하는 쿼리로 5.5 에서는 서브쿼리 처리가 어려워 다음과 같이 풀스캔 실행계획으로 롱런됨
id select_type table type possible_keys key key_len ref rows Extra
—— —————— —————– ————— ——————— ——- ——- —— —— ————-
1 PRIMARY extra ALL (NULL) (NULL) (NULL) (NULL) 985424 Using where
2 DEPENDENT SUBQUERY deal unique_subquery PRIMARY,srl PRIMARY 8 func 1 Using where

IN절 처리가 제대로되는 5.6에서는 실행계획이 괜찮으나,
id select_type table type possible_keys key key_len ref rows Extra
—— ———– —————– —— ——————— ————- ——- ————————– —— ————-
1 SIMPLE deal ref PRIMARY,srl srl 8 const 1 Using index
1 SIMPLE extra eq_ref PRIMARY PRIMARY 8 srl 1 (NULL)

리플리케이션을 받을 때 preparing 상태로 지연이 발생

버그리포트에 IN 서브쿼리 리플리케이션 시 지연되는 리포트가 있으며, 현 버전까지 딱히 fix 가 된 것 같지 않음
Query gets stuck in the “PREPARING” https://bugs.mysql.com/bug.php?id=29423
Subquery keeps the mysqld server in preparing phase https://bugs.mysql.com/bug.php?id=45365

대처는 .. 조인 업데이트 하던지, 로직에서 srl 을 분리 처리하던지

MySQL 8.0(5.8)

Percona Live 2016 가 진행중인데 5.8 (8.0?) 에 대한 트윗이 Percona 쪽에서 올라옴
https://www.percona.com/about-percona/team/david-bennett

8h8 hours ago
MySQL 5.8 will be called MySQL 8.0 5.8 버전은 8.0 으로 불리게 될 것

8h8 hours ago
MySQL 8.0 Data Dictionary. No more FRM files, separate DDL log for changes, Atomic DDL changes.
메타데이터를 저장하던 FRM 파일이 없어짐, 변경을 위한 DDL 로그가 분할됨, DDL 변경을 atomic 한 단위로 진행

8h8 hours ago
MySQL 8.0 can be completely MyISAM free.
완전히 MyISAM 을 버리게 됨

7h7 hours ago
MySQL 8.0 virtual indexes can ref JSON doc arrays.
가상컬럼이 JSON 다큐먼트 어레이를 참조할 수 있음

7h7 hours ago
MySQL 8.0 optimizer will consider memory, SSD, HDD in query plan.
옵티마이저는 이제 실행계획에서 H/W 에 대한 부분-memory, SSD, HDD-에 대해 고려함

sys schema 1.5

https://github.com/mysql/mysql-sys

performance_schema, information_schema 를 좀 더 쉽게 유저 친화적으로 집계해주는 뷰와 간단한 함수, 프로시져의 모음

현재 MySQL Workbench 에서 설치가능한 Helper 는 sys schema 1.4 버전으로 MySQL 5.7.9 부터는 sys schema 1.5 가 탑재됨, 5.6은 github 에서 받아서 설치

#sys schema 1.5 설치본 (x$ 는 raw data 형식)
use sys;
select * from sys.version; #(뷰) 시스스키마버전 1.5.0
select * from sys.sys_config; #(테이블) 시스스키마 설정
select * from sys.processlist; #(뷰)넌블락킹 프로세스리스트 (백그라운드 쓰레드 포함)
select * from sys.session; #(뷰)넌블락킹 프로세스리스트 (유저 쓰레드만) from threads, events_waits_current
select * from sys.statement_analysis; #(뷰)노말라이즈된 구문들의 집계 정보 from events_statements_summary_by_digest
select * from sys.user_summary order by file_ios desc; #(뷰)유저별 statement 활동, 파일 IO
select * from sys.schema_table_statistics; #(뷰)테이블사용정보집계, 총 지연시간 내림차순
select * from sys.schema_unused_indexes; #(뷰)집계이후 사용되지 않는 인덱스
select * from sys.schema_tables_with_full_table_scans; #(뷰)풀테이블 스캔되어지고 있는 테이블
select * from sys.schema_redundant_indexes; #(뷰)dominant index 와 중복되는 키
select * from sys.schema_object_overview; #(뷰)스키마별 오브젝트 개수
select * from sys.schema_index_statistics; #(뷰)인덱스 통계, 총 지연시간 내림차순
select * from sys.schema_auto_increment_columns order by auto_increment_ratio desc; #(뷰)테이블별 auto_increment 현황, 사용률
select * from sys.metrics; #(뷰)global status, InnoDB metrics, memory 사용률 (5.7) union
select * from sys.io_global_by_file_by_bytes; #(뷰)파일별 IO사용이 집계, 바이트순
select * from sys.io_global_by_file_by_latency; #(뷰)파일별 IO사용이 집계, 지연순
select * from sys.io_by_thread_by_latency; #(뷰)쓰레드별 IO사용 집계
select * from sys.innodb_lock_waits; #(뷰)from information_schema
select * from sys.host_summary order by file_ios desc; #(뷰)호스트별 액티비티 집계
select * from sys.host_summary_by_statement_type order by total desc;
select * from sys.host_summary_by_statement_latency;

#5.6 퍼포먼스스키마
use performance_schema;
select * from performance_schema.threads order by processlist_time desc; #현재 쓰레드
select * from performance_schema.accounts order by current_connections desc; #현재 유저별@호스트 커넥션과 누적 커넥션
select * from performance_schema.users order by current_connections desc; #현재 유저별 커넥션과 누적 커넥션
select * from performance_schema.hosts order by current_connections desc; #현재 호스트별 커넥션과 누적 커넥션
select * from performance_schema.file_instances order by open_count desc; #이벤트별 파일 오픈 스테이터스
#셋업
select * from performance_schema.setup_actors; #어떤 유저, 호스트를 집계할 것인가
select * from performance_schema.setup_consumers; #어떤 종류의 집계를 할 것인가 Statement 시작-종료 모니터링, 각 Stage 모니터링, Wait 모니터링 (event_* 테이블)
select * from performance_schema.setup_instruments; #어떤 인터널 섹션의 정보를 집계, 시간측정 할 것 인가
select * from performance_schema.setup_objects; #어떤 오브젝트에 대해 집계할 것인가
select * from performance_schema.setup_timers; #이벤트 주기
#집계, 탑레벨 Global Instrumentation 및 Thread Instrumentation (Statement/Stage/Wait, Statement digest)
select * from performance_schema.events_statements_current; #(기본 ON)
select * from performance_schema.events_statements_summary_by_digest; #(기본 ON)
select * from performance_schema.events_statements_history_long; #(변경 ON) em3 쿼리분석기용 변수 추가
select * from performance_schema.events_stages_current; #(기본 OFF)
select * from performance_schema.events_waits_current; #(기본 OFF)

추가로…

#5.6 mysql 스키마
use mysql;
select * from mysql.innodb_index_stats; #인덱스통계정보
select * from mysql.innodb_table_stats; #테이블통계정보
select * from mysql.slave_master_info; #리플리카가 바라보는 마스터 정보
select * from mysql.slave_relay_log_info; #리플리카의 릴레이로그 정보
select * from mysql.slave_worker_info; #멀티쓰레드 리플리케이션 정보

#5.6 인포메이션스키마
use information_schema;
select * from information_schema.INNODB_METRICS; #InnoDB 집계정보
select * from information_schema.INNODB_SYS_TABLESTATS order by num_rows desc; #테이블상세정보 레코드개수
select * from information_schema.INNODB_SYS_TABLES order by n_cols desc; #테이블간략정보 컬럼개수
select * from information_schema.TABLE_CONSTRAINTS; #키제약정보

MySQL 함수 사용 시 NAME_CONST 변환

프로덕션에서 character set 관련 변수들이 모두 utf8 임에도 불구하고 쿼리 내 함수 수행 시 입력 변수 부분에 NAME_CONST latin1 이 붙는 현상

select if( NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’) < NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’) , datediff( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) – dbtest.fn_count( NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’)) , datediff( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) + dbtest.fn_count( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) ) into diffdays

+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+

http://www.justincarmony.com/blog/2011/02/02/mysql-stored-procedure-name_const-and-character-sets/

위 블로그에서는 @variable 을 설정하여 NAME_CONST 변환을 우회하여 속도 향상을 시켰다고 함
(utf8 에서 latin1 으로 변경), 적용 시 개선 확인이 어려웠음