P_S Statement Digest

https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.6/en/statement-summary-tables.html
https://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-digests.html

기본 구문 이벤트 테이블: events_statements_current, events_statements_history, events_statements_history_long
구문 집계 테이블: events_statements_summary_by_digest

DIGEST_TEXT: 노말라이즈된 구문 요약digest
DIGEST: MD5 해시값

statement_digest P_S 의 consumer가 켜져있으면(setup_consumers 참고), 구문이 끝날때 events_statements_summary_by_digest 집계가 발생
– 막 끝난 구문에 대한 digest 값이 events_statements_summary_by_digest row 에 이미 존재한다면 통계가 그 row 에 집계
– 만약 막 끝난 구문에 대한 digest 값이 있는 row 가 없다면, 그리고 테이블이 full 이 아니라면, 그 구문에 대한 row 가 새로 생성
– 만약 막 끝난 구문에 대한 digest 값이 있는 row 가 없고 테이블이 full 이라면, DIGEST=NULL row 하나에 포함되어 집계

P_S 테이블의 최대 사이즈 때문에 DIGEST=NULL row 가 존재할 수 있음
존재하는 row 와 매칭되지 않는 digest 들은 이 row 에 동일하게 취급되어 카운트됨
COUNT_STAR (집계된 이벤트 숫자) 값이 전체 digest COUNT_STAR 대비 50% 를 넘으면, digest 집계 테이블 정보가 모든 이벤트를 대표한다고 볼 수 없음, 5% 까지 대표한다고 봄

events_statements_summary_by_digest 테이블은 고정된 사이즈를 가짐
사이즈를 늘릴려면 performance_schema_digests_size 시스템변수 값을 조정, 지정하지 않는다면 서버가 알아서 기동시 지정 기본값 -1 (자동)

performance_schema_max_digest_length 시스템 변수는 digest 를 구하기 위한 최대 버퍼 사이즈를 결정
구문 키워드나 리터럴값의 내부 인코딩 때문에 사용 가능한 이 버퍼 사이즈보다 실제 더 길게 digest 가 표기될 수 있음
어플리케이션이 끝부분만 다른 매우 긴 구문을 생성한다면 이 값을 상향하여 집게시 구문을 분리하고 그렇지 않다면 하나의 집계 row에 포함시킬 수 있음
performance_schema_max_digest_length 은 세션별로 메모리가 할당되므로 동시다발적인 많은 센션이 로드를 준다면 높은값은 주의!

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-status-variables.html
– Performance_schema_digest_lost: events_statements_summary_by_digest 테이블에 digest 가 기록되지 못환 횟수, performance_schema_digests_size 가 너무 작아서 발생할 수 있음
– Performance_schema_accounts_lost: accounts 테이블이 full 이라 기록되지 못한 횟수, performance_schema_accounts_size 조정
– Performance_schema_hosts_lost: hosts 테이블이 full 이라 기록되지 못한 횟수, performance_schema_hosts_size 조정

summary 테이블 TRUNCATE 수행 시,
– events_statements_summary_by_digest 는 row 들을 삭제
– account, host, user 로 집계하지않는 다른 summary 테이블들은 row 들을 삭제하는 대신에 summary 컬럼을 0으로 세팅
– account, host, user 로 집계하는 다른 summary 테이블들은 커넥션 이력이 없는 account, host, user 관련 row들을 삭제하고 나머지 존재하는 row 들의 summary 컬럼들을 0으로 세팅
– account, host, user, thread 로 집계되는 각각의 statement summary 테이블들은 연관된 커넥션 테이블(accounts, hosts, users)이나 events_statements_summary_global_by_event_name 의 truncatation 으로 암시적으로 truncate 됨

MySQL PK Constraint symbol

http://dev.mysql.com/doc/refman/5.6/en/create-table.html
MySQL CREATE TABLE 레퍼런스에 보면 PK 도 임의의 이름을 가질 수 있을 것 처럼 해놓음

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...

symbol 로 표현할 수 있는데, syntax error 는 발생하지 않지만 I_S 어디에서도 찾을 수 없음
아마 KEY 선언 시 DESC 과 같은 순서 지정이 무시discard 되는 것처럼 요것도 그런게 아닐까 함
레퍼에는 또 친절하게 다음과 같이 쓰여있음- _-)..

  • In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name.

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 을 분리 처리하던지