DML Explain Bug

MySQL 5.6 에서부터 DML구문에 대한 실행계획 조회가 가능
https://dev.mysql.com/doc/refman/5.6/en/explain.html

하지만 readonly 서버로 설정이 되어있을 때 ERROR:1290 – The MySQL server is running with the –read-only option so it cannot execute this statement 에러가 발생함

DML Explain 시 com_delete, com_update 등 엔진 내부의 커맨드 수행 카운터가 올라가는 것으로 보아 추측컨데 DB에서 DML 쿼리가 수행(explain 시 반영되지 않음)될 때 서버옵션인 readonly 체크가 사전 동작해서 explain 이 막히는 것 같음

MySQL 버그리포트에 확인됨verified 상태로 오픈되어있으며 MySQL 5.7 에서도 동일 문제 발생하는 것을 확인

Non-critical 레벨로 버그에 대한 이제까지의 오라클의 행태?로 봐서는 언제 fix 될지 의문임
https://bugs.mysql.com/bug.php?id=73258

쿼리를 SELECT 로 바꿔서 참고 해야할 듯

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.1 (updated)

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 에서 받아서 설치
현 버전 1.5.1

1.테이블
sys_config 주로 SYS 루틴에 대한 옵션 설정2.뷰
version SYS 버전
ps_check_lost_instrumentation P_S 집계 빠지는lost instrument 와 개수
metrics 서버 수치, P_S 의 global_status, I_S 의 INNODB_METRICS, P_S memory instrumentation 에 기반한 현재, 총 메모리 할당, 스냅샷 시간 합침–세션/쓰레드
processlist 백그라운드 쓰레드 포함 넌블락킹 프로세스리스트
session 유저 넌블락킹 프로세스리스트
session_ssl_status 커넥션 ssl 정보 (n/a)
schema_table_lock_waits 메타데이터락을 위해 대기하는 세션 (n/a)
innodb_lock_waits InnoDB 락 스냅샷 정보
io_by_thread_by_latency 쓰레드별 I/O 소비
latest_file_io 파일,쓰레드별 가장 최근의 I/O (NULL)

–스키마
schema_auto_increment_column 전체 AI 정보 조회 (00s+)
schema_index_statistics 전체 인덱스 정보 조회 (5s+)
schema_unused_indexes 집계이후 사용되지 않는 인덱스
schema_redundant_indexes 도미넌트 인덱스와 중복되는 인덱스 (3s+)
schema_object_overview 데이터베이스별 오브젝트 숫자
schema_table_statistics 테이블 스키마 통계
schema_table_statistics_with_buffer 버퍼풀 포함한 테이블 스키마 통계 (3m+)
schema_tables_with_full_table_scans 테이블별 풀스캔 레코드, 지연

–호스트
host_summary 호스트별 구문 액티비티, 파일 I/O, 커넥션 집계 (5.7이면 메모리사용)
host_summary_by_stages 호스트별 구문 스테이지 (NULL)
host_summary_by_file_io_type 호스트,이벤트별 파일 I/O
host_summary_by_statement_latency 호스트별 구문 통계
host_summary_by_statement_type 호스트,구문별 수행 통계
waits_by_host_by_latency 호스트별 지연

–구문
statement_analysis 구문 집계 통계, P_S의 events_statements_summary_by_digest
statements_with_errors_or_warnings 에러와 경고를 발생시킨 구문
statements_with_full_table_scans 풀스캔이 된 구문
statements_with_runtimes_in_95th_percentile 높은 평균 수행시간을 가지는 구문
statements_with_sorting 정렬이 진행된 구문
statements_with_temp_tables 임시테이블 사용한 구문

–파일
io_global_by_file_by_bytes 파일,바이트별 글로벌 I/O 소비 (5s+)
io_global_by_file_by_latency 파일,지연별 글로벌 I/O 소비 (5s+)
io_global_by_wait_by_bytes 바이트별 글로벌 I/O 소비
io_global_by_wait_by_latency 지연별 글로벌 I/O 소비

–유저/이벤트
user_summary 유저 구문, 커넥션 활동
user_summary_by_file_io 유저별 파일 I/O
user_summary_by_file_io_type 유저별,이벤트별 파일 I/O
user_summary_by_stages 유저별 스테이지 이벤트 (NULL)
user_summary_by_statement_latency 유저별 구문 통계
user_summary_by_statement_type 유저별,구문별 수행된 구문
waits_by_user_by_latency 유저별,이벤트별 지연
wait_classes_global_by_avg_latency 이벤트별 평균 지연
waits_global_by_latency 이벤트별 지연

–메모리
innodb_buffer_stats_by_schema 스키마별 버퍼 정보 (00s+)
innodb_buffer_stats_by_table 스키마,테이블별 버퍼 정보 (00s+)
memory_by_host_by_current_bytes 호스트별 메모리사용 (5.7)
memory_by_thread_by_current_bytes 쓰레드별 메모리사용 (5.7)
memory_by_user_by_current_bytes 유저별 메모리사용 (5.7)
memory_global_by_current_bytes 할당 타입별 메모리사용 (5.7)
memory_global_total 총 메모리사용 (5.7)

3. 프로시져
create_synonym_db() 지정한 데이터베이스의 synonym 스키마 생성 내부에 테이블들을 view 로 가지고 있음
diagnostics() 진단 목적으로 현재의 서버 현황 리포트를 생성
execute_prepared_stmt() prepared statement 로 쿼리를 수행
ps_setup_disable_background_threads() P_S의 모든 백그라운드 쓰레드를 비활성화
ps_setup_disable_consumer() 주어진 이름을 가진 P_S의 consumer를 비활성화
ps_setup_disable_instrument() 주어진 이름을 가진 P_S의 instrument를 비활성화
ps_setup_disable_thread() 주어진 커넥션ID에 대한 P_S instrument를 비활성화
ps_setup_enable_background_threads() P_S의 모든 백그라운드 쓰레드를 활성화
ps_setup_enable_consumer() 주어진 이름을 가진 P_S consumer를 활성화
ps_setup_enable_instrument() 주어진 이름을 가진 P_S instrument를 활성화
ps_setup_enable_thread() 주어진 커넥션ID에 대한 P_S instrument를 활성화
ps_setup_reload_saved() 같은 세션내 ps_setup_save()로 저장한 P_S설정을 복구
ps_setup_reset_to_default() P_S 설정을 디폴트세팅으로 리셋
ps_setup_save() 디버깅이나 다른 목적으로 설정을 ALTER 전, 현재상태의 P_S 설정을 저장
ps_setup_show_disabled() 현재 비활성화된 모든 P_S 설정을 보여줌
ps_setup_show_disabled_consumers() 현재 비활성화된 모든 consumer를 보여줌
ps_setup_show_disabled_instruments() 현재 비활성화된 모든 instrument를 보여줌, 결과가 길 수 있음
ps_setup_show_enabled() 현재 활성화된 P_S 설정을 보여줌 configuration.
ps_setup_show_enabled_consumers() 현재 활성화된 모든 consumer를 보여줌
ps_setup_show_enabled_instruments() 현재 활성화된 모든 instrument를 보여줌, 결과가 길 수 있음
ps_statement_avg_latency_histogram() P_S events_statements_summary_by_digest의 노말라이즈쿼리 평균지연시간을 보여주는 히스토그래프
ps_trace_statement_digest() 주어진 statement digest(MD5값)에 대해 모든 P_S instrumentation을 트레이싱
ps_trace_thread() P_S 데이터를 .dot 포맷의 그래프파일로 덤프
ps_truncate_all_tables() P_S 써머리 테이블을 truncate, 모든 집계된 instrumentation 을 리셋
statement_performance_analyzer() 서버에서 수행되고있는 구문들의 리포트를 생성
table_exists() 주어진 이름의 일반테이블/임시테이블/뷰가 존재하는지 확인

4. 뷰
extract_schema_from_file_name() 파일 경로에서 데이터베이스 명을 추출
extract_table_from_file_name() 파일 경로에서 테이블 명을 추출
format_bytes() byte 값을 가독성이 높은 units 단위 값(KiB, MiB, GiB, TiB ..)으로 변환
format_path() 파일 경로를 @@datadir, @@tmpdir 등 내부적으로 알려진 경로명으로 대체하여 줄임
format_statement() 긴 구문을 statement_truncate_len 에 설정된 고정된 길이로 truncate, 길면 생략문자(…)가 들어감
format_time() P_S 지연에 대한 picoseconds 값을 가독성이 높은 units 단위 값(ns, us, ms, s, m, h, d, w)으로 변환
list_add() 리스트에 아이템을 추가
list_drop() 리스트에서 아이템을 제거
ps_is_account_enabled() 인자로 주어진 account 를 위한 instrumentation 이 활성화되어있는지 여부 리턴
ps_is_consumer_enabled() 인자로 주어진 P_S consumer 가 활성화인지 여부 리턴
ps_is_instrument_default_enabled() 인자로 주어진 P_S instrument 가 기본적으로 활성화인지
ps_is_instrument_default_timed() 인자로 주어진 P_S instrument 가 기본적으로 timed 인지 여부
ps_is_thread_instrumented() 인자로 주어진 커넥션ID에 대해 P_S instrumentation이 켜져있는지 여부
ps_thread_account() 인자로 주어진 쓰레드ID에 대해 쓰레드와 연관된 user_name@host_name을 리턴
ps_thread_id() 커넥션ID에 대한 내부 쓰레드ID를 리턴
ps_thread_stack() 주어진 쓰레드ID에 대해 모든 구문, 스테이지, 이벤트의 스택정보를 JSON 포맷으로 리턴
ps_thread_trx_info() 주어진 쓰레드ID에 대한 정보를 JSON 포맷으로 리턴, P_S events_transactions_current, events_statements_history
quote_identifier() 인자로 주어진 스트링을 `식별자`로 리턴
sys_get_config() 설정 옵션 파일명을 인자로 받아 sys_config 테이블에서 옵션 값 리턴
version_major() MySQL 서버 메이저 버전 넘버 리턴
version_minor() MySQL 서버 마이너 버전 넘버 리턴
version_patch() MySQL 서버 패치 릴리즈 버전 넘버 리턴