'쇠똥굴리기(BOOK)'에 해당되는 글 29건

일반적으로 '인덱스'라고 하면 이는 테이블에 사용자가 생성해돈 B-Tree 인덱스를 의미한다. 
인덱스가 사용하는 알고리즘이 B-Tree는 아니더라도, 사용자가 직접 테이블에 생성해둔 인덱스가 우리가 일반적으로 알고 있는 인덱스일 것이다.
하지만 여기서 언급하는 '어댑티브 해시 인덱스'는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는
데이터에 대해 자동으로 생성하는 인덱스이며, 사용자는 innodb_adptive_hash_index 시스템 변수를 이용해서
어댑티브 해시 인덱스 기능을 활성화하거나 비활성화 할 수 있다.

-> innodb_adptive_hash_index 시스템 변수를 통해서 옵티마이져가 어댑티브 해시인덱스를 사용할지 말지

사용자가 on/off를 결정한다.

B-Tree 인덱스에서 특정 값을 찾는 과정은 매우 빠르게 처리된다고 많은 사람이 생각한다. 하지만 결국 빠르냐 느리냐의 기준은 상대적인 것이며,
데이터베이스 서버가 얼마나 많은 일을 하느냐에 따라 B-tree 인덱스에서 값을 찾는 과정이 느려질 수도 있고 빨라질 수도 있다.
B-Tree 인덱스에서 특징값을 찾기 위해서는 B-Tree의 루트 노드를 거쳐서 브랜치 노드, 그리고 최종적으로 리프 노드까지 찾아가야 원하는 레코드를 읽을 수 있다.
적당한 사양의 컴퓨터에서 이런 작업을 동시에 몇 개 실행한다고 해서 성능 저하가 보이지는 않을 것이다.
하지만 이런 작업을 동시에 몇천 개의 스레드로 실행하면 컴퓨터의 CPU는 엄청난 프로세스 스케줄링을 하게 되고 자연히 쿼리의 성능은 떨어진다.

adaptive hash index는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다. 
InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가
저장된 데이터 페이지를 즉시 찾아갈 수 있다.
B-Tree를 루트노드부터 리프 노드까지 찾아가는 비용이 없어지고 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다.
그와 동시에 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 된다.

해시 인덱스는 '인덱스 키값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소'의 쌍으로 관리되는데,
인덱스 키 값은 'B-Tree 인덱스의 고유번호(id)와 B-Tree 인덱스의 실제 키 값' 조합으로 생성된다.

어뎁티브 해시 인덱스의 키 값에 B-Tree 인덱스의 고유번호가 포함되는 이유는 InnoDB 스토리지 엔진에서 
어댑티브 해시 인덱스는 하나만 존재(물론 파티션되는 기능이 있지만)하기 때문이다.
즉, 모든 B-Tree 인덱스에 대한 어댑티브 해시인덱스가 하나의 해시 인덱스에 저장되며, 특정 키값이 어느 인덱스에 
속한 것인지도 구분해야 하기 때문이다. 

그리고 '데이터 페이지 주소'는 실제 키 값이 저장된 데이터 페이지의 메모리 주소를 가지는데, 이는 InnoDB 버퍼풀에 로딩된 페이지의 주소를 의미한다.
그래서 어댑티브 해시인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리하고, 버퍼 풀에서 해당 데이터 페이지가 없어지면
어댑티브 해시 인덱스에서도 해당 페이지의 정보는 사라진다.

-> 해시인덱스 구조 = B-Tree (고유 id + key value) + 데이터 페이지의 메모리 주소

CPU 사용률 변화

초당 쿼리 처리수 변화

예전 버전까지는 어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로 어댑티브 해시 인덱스의 경합이 상당히 심했다.
그래서 Mysql 8.0부터는 내부 잠금(세마포어) 경함을 줄이기 위해 어댑티브 해시 인덱스의 파티션 기능을 제공한다.
innodb_apaptive_hash_index_parts 시스템 변수를 이용해 파티션 개수를 변경할 수 있는데,
기본값은 8개이며 만약 어댑티브 해시 인덱스가 성능에 많은 도움이 된다면 파티션 개수를 더 많이 설정하는 것도 

어댑티브 해시 인덱스의 내부 잠금 경합을 줄이는데 많은 도움이 될 것이다.

여기까지만 보며 InnoDB 스토리지 엔진의 어댑티브 해시 인덱스는 팔방미인처럼 보이지만, 실제 어댑티브 해시 인덱스를 의도적으로 비활성화하는 경우도 많다.

1. 성능향상에 크게 도움이 되지 않는 경우

1) 디스크 읽기가 많은 경우
2) 특정 패턴의 쿼리가 많은 경우(Join이나 Like 패턴 검색)
3) 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

2. 성능향상에 도움이 되는 경우

1) 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
2) 동등 조건 검색(동등 비교와 In 연산자)이 많은 경우
3) 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

하지만 단순히 어댑티브 해시 인덱스가 도움이 될지 아닐지를 판단하기는 쉽지않다. 
한가지 확실한 것은 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에
데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다는 점이다.

하나 더 기억해야 할 것은 어댑티브 해시 인덱스는 '공짜 점심'이 아니라는 것이다.
어댑티브 해시인덱스 또한 저장 공간인 메모리를 사용하며, 때로는 상당히 큰 메모리 공간을 사용할 수도 있다.
어댑티브 해시 인덱스 또한 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요한 경우 제거되어야 하며,
어댑티브 해시 인덱스가 활성화되면 InnoDB 스토리지 엔진의 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 한다는 것이다.
즉, 해시인덱스 효율이 없는 경우에도 InnoDB는 계속 해시 인덱스를 사용할 것이다.

어댑티브 해시 인덱스는 테이블의 삭제 작업에도 많은 영향을 미친다.
어떤 테이블의 인덱스가 어댑티브 해시 인덱스에 적재되어 있다고 가정해보자.
이때 이 테이블을 삭제(Drop)하거나 변경(Alter)하려고 하면 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야한다.
이로 인해 테이블이 삭제되거나 스키마가 변경되는 동안 상당히 많은 CPU 자원을 사용하고,
그만큼 데이터베이스 서버의 처러 성능은 느려진다. 이후 버전에서는 개선되겠지만 
Mysql 8.0.20 버전에서는 다음과 같은 Instant 알고리즘의 Online DDL도 상당한 시간이 소요되기도 한다.

mysql> alter table employees ADD address varchar(200), ALGORITHM=instant;

어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업(Online DDL 포함)은 

더 치명적인 작업이 되는 것이다.
이는 어댑티브 해시 인덱스의 사용에 있어서 매우 중요한 부분이므로 꼭 기억해두자.

#어댑티브 해시 인덱스 사용유무 확인

#어댑티브 해시인덱스 사용유무 확인
mysql> show global variables like 'innodb_adaptive_hash%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)


show engine innodb status\G;
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 11577, seg size 11579, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4730347, node heap has 2 buffer(s)
Hash table size 4730347, node heap has 0 buffer(s)
Hash table size 4730347, node heap has 653 buffer(s)
Hash table size 4730347, node heap has 4 buffer(s)
Hash table size 4730347, node heap has 4 buffer(s)
Hash table size 4730347, node heap has 2 buffer(s)
Hash table size 4730347, node heap has 2 buffer(s)
Hash table size 4730347, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

#어댑티브 해시인덱스의 메모리사용량 확인

select EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
from performance_schema.memory_summary_global_by_event_name
where EVENT_NAME='memory/innodb/apaptive hash index';

#결론
mysql 8.0을 검토하면서 online ddl의 instant 알고리즘에 대한 기대가 많았는데 
어댑티브 해시 인덱스를 쓰게 된다면 있으나마한 기능이 되버릴 수도 있겠다.

ONLINE DDL은 mysql 5.7에서도 서비스 DB에 반영시 부하여부를 테스트하고 여러가지 장애포인트를 확인하고 

반영하는데 mysql 8.0으로 업그레이드시 어댑티브 해시인덱스를 ON 하게되면 
더 많은 장애 포인트를 검토해봐야 한다는 점에서 적용이 망설여진다.

향후 mysql 8.1.x 버전에서 이 기능이 Hint 기능의 하나로 제공되어 
특정 쿼리에서만 사용 가능하게 되고

query에 사용한 테이블 목록만 별도 관리하기만 한다면
그때는 adaptive hash index 사용을 긍정적으로 검토해봐도 좋을 거 같다.

그전까진 성능보단 안정성에 우선을..






#그래프 이미지 참조
https://gywn.net/2015/01/innodb-adaptive-hash-index/

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제로 종료시킨다.

이때 어느 트랙잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 undo log 양이며, undo log 레코드를 더 적게 가진 트랜잭션이 일반적으로 rollback 의 대상이 된다.
트랜잭션이 undo record를 적게 가졌다는 이야기는 rollback을 해도 undo 처리를 해야할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 Mysql 서버의 부하도 덜 유발하기 떄문이다.

참고로 InnoDB 스토리지 엔진은 상위 레이어인 Mysql 엔진에서 관리되는 테이블 잠금 (LOCK TABLES 명령으로 잠긴 테이블)은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데,
innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 된다.
특별한 이유가 없다면 innodb_table_locks 시스템 변수를 활성화하자.

#시스템 변수 설정 확인
mysql> show global variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

일반적인 서비스에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다.
하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
데드락 감시 스레드는 잠금 목록을 검사해야하기 떄문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다.
데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다.
이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소보할 수도 있다.

#시스템 변수 설정 확인
mysql> show global variables like 'innodb_deadlock%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.01 sec)

이런 문제점을 해결하기 위해 Mysql 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, innodb_deadlock_detect을 off로 설정하면
데드락 감지 스레드는 더는 작동하지 않게 된다.
데드락 감지 스레드가 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락 상황)이 발생해도
누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다.

하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면
자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. innodb_lock_wait_timeout 은 초 단위로 설정할 수 있으며, 잠금을 설정한 시간동안 
획득하지 못하면 쿼리는 실패하고 에러를 반환한다.
데드락 감지 스레드가 부담되어 innodb_deadlock_detect를 off로 설정해서 비활성화하는 경우라면 innodb_lock_wait_timeout을 기본값인 50초보다 훨씬 낮은
시간으로 변경해서 사용할 것을 권장한다.

ex)
구글(google.com)에서는 pk 기반의 조회 및 변경이 아주 높은 빈도로 실행되는 서비스가 많았는데, 이런 서비스는 매우 많은 트랜잭션을 동시에 실행하기 때문에
데드락 감지 스레드가 상당히 성능을 저하시킨다는 것을 알아냈다. 그리고 Mysql 서버의 소스코드를 변경해 데드락 감지 스레드를 활성화 또는 비활성화할 수 있게 변경해서 사용했다.
이 기능의 필요성을 인지하고 오라클에 이 기능을 요청해서 Mysql 서버에 추가된 것이다. 
만약 PK 또는 secondary index를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화해서 
성능비교를 해보는 것도 새로운 기회가 될 것이다.

정리하면
innodb_deadlock_detect는 즉시 데드락 감지해서 처리하고
innodb_lock_wait_timeout은 설정된 시간(default 50초)만큼 기다렸다가 처리한다.

구글관련 예시를 저자가 들고 있긴 한데 좀 찾아보니 관련 시스템 변수를 직접 테스트한 내용도 있어서 추가한다.

#참조 
https://fromdual.com/innodb-deadlock-detect-rather-hands-off

#요약
INNODB_DEADLOCK_DETECT - 차라리 손을 떼!


최근에 우리는 새로운 고객이 있었는데, 그는 때때로 그가 이해하지 못하는 엄청난 데이터베이스 문제를 겪었습니다. 
MySQL 구성 파일(my.cnf)을 검토한 결과 이 고객이 Innodb_deadlock_detect를 비활성화한 것으로 확인되었습니다.

우리는 지금까지 이것을 하지 말라고 충고했지만, 나는 실제로 이 문제를 우연히 발견한 적이 없기 때문에, 
나는 nodb_deadlock_detect의 MySQL 변수에 대해 조금 더 조사했습니다.


InnoDB Deadlock 탐지를 비활성화하는 기능은 WebScale용 Facebook에서 개발되었습니다
.
..
...
즉, InnoDB Deadlock 탐지를 비활성화하는 것은 흥미롭습니다. 
만약 당신이 facebook 만큼이나 가지고 있다면 말이죠. 거의 또는 현재 충돌하지 않을 것으로 예상되는 짧고 작은 트랜잭션입니다.
그 경우엔 MySQL 변수 innodb_lock_wait_timeout을 매우 작은 값(몇 초)으로 설정하는 것이 좋습니다.

그러나 대부분의 고객들이 Facebook과 같은 크기를 가지고 있지 않고, 
동시에 발생하는 짧고 작은 거래가 많지 않고, 적지만 긴 거래(잠금이 많고 따라서 교착 상태가 발생할 확률이 높음)를 가지고 있기 때문에, 
저는 짐작해 볼 수 있습니다, 이 매개 변수를 비활성화한 것은 고객 시스템의 히크업(잠금이 쌓이는 중)의 원인이었습니다.
이는 max_connections를 초과하게 되고 마지막으로 전체 시스템이 고착됩니다.

따라서 InnoDB Deadlock 탐지를 활성화할 것을 강력히 권장합니다. 
(약 2주간의 광범위한 테스트 및 측정 후) 자신이 무엇을 하고 있는지 정확히 알고 있다는 점만 제외합니다.



#결론

구글이나 페이스북 같이 엄청난 동시성 트랙잭션을 처리하는 서버를 제외하고는
웬만하면 innodb_deadlock_detect=on 으로 두자!

끝.

데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 드으이 정보를 데이터 딕셔너리 또는 메타데이터라고 하는데, Mysql 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램
또한 파일(*TRN, *TRG, *PAR...) 기반으로 관리했다. 
하지만 이러한 파일기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 Mysql 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.
많은 사용자들이 이같은 현상을 가리켜 '데이터베이스나 테이블이 깨졌다' 라고 표현한다.

Mysql 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선됐다.
Mysql 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다.
Mysql 서버 8.0 버전부터는 이런 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선했으며, 

시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다.

-> mysql 8.0 부터 데이터 딕셔너리 정보라는 것이 생기므로 해서 fork DB라고 불리는 Maridb 와는 다른 길을 가게 된다.

    다시말하면 MariaDB 의 스키마 단위 백업해서 재생성은 가능하지만 통째로 백업해서 mysql 8.0에 넣을 수는 없다.

mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다. 
그래서 Mysql 서버의 데이터 디렉터리에 존재하는 mysql.ibd라는 파일은 다른 *.ibd 파일과 함께 특별하게 주의해야 한다.

-> 서버의 비정상적인 종료가 되더라도 innodb 엔진을 사용하여 mysql.ibd 파일에 쌓음으로써 데이터 정합성을 보장하게 수정되었다는 얘기

mysql DB에 데이터 딕셔너리를 저장하는 테이블이 저장된다고 했는데, 실제 mysql DB에서 테이블의 목록을 살펴보면 

실제 테이블의 구조가 저장된 테이블은 보이지 않을 것이다.
데이터 딕셔너리 테이블의 데이터를 사용자가 임의로 수정하지 못하게 사용자의 화면에 보여주지만 않을 뿐 실제로 테이블은 존재한다.
대신 mysql 서버는 데이터 딕셔너리 정보를 information_schema DB의 TABLE와 COLUMNS 등과 같은 뷰를 통해 조회할 수 있게 하고 있다.

실제 information_schema에서 TABLES 테이블의 구조를 보면 뷰로 만들어져 있고 TABLES 뷰는 mysql DB의 table이라는 이름의 테이블을 참조하고 있음을 확인할 수 있다.

mysql> show create table information_schema.tables;

+--------+-------------------------------------+
| Table  | Create Table                     
+--------+-------------------------------------+
| TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8           
+--------+-------------------------------------+

// 그리고 mysql DB에서 tables 라는 이름의 테이블에 대해 select 를 실행해보면 '테이블이 없음' 에러가 아니라 다음과 같이 '접근이 거절됨'이라고 출력된다.

mysql> select * from mysql.tables limit 1;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

mysql 8.0 버전부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜젝션 기반의 innoDB 스토리지 엔진에 저장되도록 개선되면서
이제 스키마 변경 작업 중간에 Mysql 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.
기존의 파일 기반 메타데이터를 사용할 때와 같이 적업 진행 중인 상태로 남으면서 문제를 유발하지 않게 개선된 것이다.

->비정상적인 DB 종료시 파일이 깨짐으로 DB가 정상적으로 startup 못하는 현상 개선

Mysql 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 기반의 딕셔너리에 저장되지만
MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.
Mysql 서버는 InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다.
InnoDB 이외의 테이블들에 대해서는 SDI 포맷의 *.sdi 파일이 존재하며, 이 파일은 기존의 *.FRM 파일과 동일한 역할을 한다.
그리고 SDI는 이름 그대로 직렬화(Serialized)를 위한 포맷이므로 InnoDB 테이블들의 구조도 SDI 파일로 변환할 수 있다.
ibd2sdi 유틸리티를 이용하면 InnoDB 테이블스페이스에서 스키마 정보를 추출할 수 있는데,
다음 예제는 mysql DB에 포함된 테이블의 스키마를 JSON 파일로 덤프한 것이다.
ibd2sdi 유틸리티로 추출한 테이블 정보 중에는 Mysql 서버에서 show tables 명령으로는 확인할 수 없던
mysql.tables 딕셔너리 테이블을 위한 테이블 구조도 볼 수 있다.

Mysql 서버의 시스템 변수는 동적 변수와 정적 변수로 구분되는데, 동적 변수의 경우 Mysql 서버에서 set global 명령으로 변경하면 즉시 Mysql 서버에 반영된다.

예를 들어, Mysql 서버의 max_connections라는 시스템 변수가 있는데, 이 시스템 변수는 Mysql 서버로 접속할 수 있는 최대 커넥션의 개수를 제한하는 동적 시스템 변수다.
Mysql 서버에 커넥션을 더많이이 사용 중이라면 최대 연결 가능 커넥션의 개수를 더 늘리기 위해서 다음과 같이 Mysql 서버의 시스템 변수를 즉시 변경하게 될 것이다.

SET GLOBAL max_connections=5000;

문제는 이렇게 변경한 후 Mysql 서버의 설정 파일에서도 이 내용을 적용해야 하는데, 응급조치를 하다 보면 Mysql 서버의 설정 파일에 변경한 내용을 적용하는 것을 잊어버릴 때도 있다.

그리고 시간이 지나서 Mysql 서버를 재시작하면 다시 예전의 max_connections 시스템 변수의 값으로 Mysql 서버가 시작되고, 이로 인해 장애가 반복적으로 발생하게 된다.
실제로 이런 상황은 꽤 빈번하게 일어난다.

Mysql 8.0에서는 이런 문제점을 보안하기 위해 SET PERSIST 명령을 도입했다.

mysql> SET PRESIST max_connections=5000;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

위 예제와 함께 SET PRESIST 명령으로 시스템 변수를 변경하면 Mysql 서버는 변경된 값을 즉시 적용하고 별도로 mysqld-auto.cnf 파일에 변경된 내용을 추가기록해둔다.

그리고 DB가 재시작할 때 my.cnf파일 뿐만아니라 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수를 적용한다.
SET PRESIST 명령은 세션 변수에는 적용되지 않으며, GLOBAL 시스템 변수의 변경으로 인식하고 변경한다.

만약 현재 시스템말고 재시작할 때만 변경 내용을 반영하고 싶다면 SET PERSIST_ONLY 명령을 사용한다.

mysql> SET PRESIST_ONLY max_connections=5000;
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

DB가 재시작되어야만 반영되는 정적변수 예를 들어 innodb_doublewrite 같은 변수도 미리 반영해둘 수 있다.

mysql> SET PRESIST innodb_doublewrite=ON;
ERROR 1238 (HY000) : Variable 'innodb_doublewrite' is read only variable


mysql> SET PRESIST_ONLY innodb_doublewrite=ON;
Query OK, 0 rows affected (0.00 sec)

다만!  이렇게 편리한 명령어도..

SET PERSIST나 SET PERSIST_ONLY 명령으로 추가된 시스템 변수의 내용을 삭제해야 할 때도 있다.
이때 mysqld-auto.cnf 파일의 내용을 직접 변경하다가 내용상 오류를 만드는 경우 Mysql 서버가 시작되지 못할 수도 있다.

mysqld-auto.cnf 은 직접  파일 건드리지 말자.;

그래서 mysqld-auto.cnf 파일의 내용을 삭제해야 하는 경우에는 다음과 같이 명령어를 사용하는 것이 안전하다.

##특정 시스템 변수만 삭제
mysql> RESET PRESIST max_connections;
mysql> RESET PRESIST IF_EXISTS max_connections;

##mysql-auto.cnf 파일의 모든 시스템 변수를 삭제
mysql> RESET PERSIST;

자동화는 어디서나 양날의 검이다.
제대로 알고 사용하면 가장 편한 수단이지만 
어설프게 알고 사용하면 DB가 죽을 수도 있다.

1) 사용자 인증 방식 변경 

Mysql 8.0 버전부터는 Caching SHA-2 Authentication 인증 방식이 기본 인증 방식으로 바뀌었다.
Mysql 5.7 에 존재했던 사용자 계정은 여전히 Native Authentication 인증 방식을 사용하겠지만 

Mysql 8.0 버전에서 별도의 옵션 없이 생성되는 

사용자 계정은 Caching SHA-2 Authentication 인증 방식을 사용하게 될 것이다.
만약 Native Authentication 을 계속 사용하고자 한다면 Mysql 서버를 시작할 때 --default-authentication-plugin=mysql_native_password 파라미터를 활성화하자.

mysql 5.7 그대로 쓰려면 mysql_native_password 활성화 해야한다. 

2) Mysql 8.0과의 호환성 체크

8.0 업그레이드 전에 5.7 버전에서 손상된 FRM 파일이나 호환되지 않는 데이터 타입 또는 함수가 있는지 mysqlcheck 유틸리티를 이용해 확인해 볼 것을 권장한다.

3) 외래키 이름의 길이

Mysql 8.0에서는 외래키(foreign key)의 이름이 64글자로 제한된다.
그래서 기존의 Mysql 서버에서 외래키 이름이 64글자 이상인 것이 있는지 확인하고 필요하면 변경하자.

4) 인덱스 힌트

Mysql 5.x에서 사용되던 인덱스 힌트가 있다면 Mysql 8.0에서 먼저 성능 테스트를 수행하자.

이거는 어느 데이터베이스라도 동일하다. 버전업 전에 전체 SQL은 검수를 못하더라도
/* sql ID */별로 중요한 프로세스와 관련된 sql은 필수적으로
실행계획을 확인하고 성능체크를 해서 문제가 있으면 튜닝을 해야한다.

5) Group by 에 사용된 정렬 옵션

Mysql 5.x에서 Group by 절의 컬럼 뒤에 'ASC'나 'DESC'를 사용(GROUP BY field_name [ASC | DESC]) 하고 있다면 먼저 제거하거나 다른 방식으로 변경하자.


6) 파티션을 위한 공용 테이블스페이스

mysql 8.0에서는 파티션의 각 테이블스페이스를 공용 테이블스페이스에 저장할 수 없다.
그래서 파티션의 테이블 스페이스가 공용 테이블스페이스에 저장된 것이 있는지 먼저 확인하고,
있다면 ALTER TABLE .. REORANIZE 명령을 실행해 개별 테이블 스페이스를 사용하도록 변경해야 한다.

#공용 테이블 스페이스에 저장된 파티션이 있는지 확인
select 
distinct NAME
, SPACE
, SPACE_TYPE
from information_schema.INNODB_SYS_TABLES
where NAME like '%#P#%'
and SPACE_TYPE NOT LIKE '%Single%';
Empty set (0.01 sec)

my.cnf의 시스템 설정에서

innodb_file_per_table = 1 로 설정되어있지 않다면

공용 테이블 스페이스가 있을 수도 있다 확인필요!

7) mysql 8.0.15 버전과 이후 버전에 따라 업그레이드 방법 차이

# mysql 5.7.x -> mysql 8.0.15 까지 upgrade시

1. mysql shutdown
2. mysql 5.7 프로그램 삭제
3. mysql 8.0 프로그램 설치
4. mysql 8.0 서버(mysqld) 시작(mysql 서버가 데이터 딕셔너리 업그레이드 자동실행)
5. mysql_upgrade 프로그램 실행(mysql_upgrade 프로그램이 시스템 테이블의 구조를 Mysql 8.0에 맞게 변경)

# mysql 5.7.x -> mysql 8.0.16 부터 upgrade시
1. mysql shutdown
2. mysql 5.7 프로그램 삭제
3. mysql 8.0 프로그램 설치
4. mysql 8.0 서버(mysqld) 시작(mysql 서버가 데이터 딕셔너리 업그레이드를 실행 후, 시스템 테이블 구조를 mysql 8.0 에 맞게 변환)
// 이제 mysql_upgrade 안써도 된다.

#추가로 찾아본 부분정리


5) Group by 절의 'ASC'나 'DESC'를 제거하라?

이해가 안되서 좀 찾아보니 mysql 5.x에서는 Group by 로 정렬시 order by를 굳이 사용하지 않아도 (ASC)라는 

암묵적인 지원이 있었는데 
mysql 8.0에서는 그런것이 없고 order by를 사용해야지만 정확한 정보를 얻을 수 있다는 얘기다.


#참조 
https://dev.mysql.com/worklog/task/?id=8693

MySQL에서 GROUP BY는 역사적으로 정렬에 사용되었다. 
쿼리가 GROUP BY를 지정하면 쿼리에 동일한 열에 대한 ORDER BY가 있는 것처럼 출력 행이 열별로 그룹화되어 정렬됩니다. 
이것은 MySQL 버전 5.7 이전에 있었다. 그러나 GROUP BY에 대한 암묵적 정렬이 제거되면서 MySQL 8.0에서 변경되었다. 
그러나 여전히 남아 있는 것은 GROUP BY 열 - GROUP BY 열 ASC/DESC의 순서를 지정하기 위해 MySQL이 GROUP BY 절과 함께 제공하는 비표준 확장입니다.

이 작업 로그의 목적은 ASC 및 DESC를 기준으로 그룹화에 대한 구문을 제거하는 것입니다. 
이것과 GROUP BY에 대한 암시적 정렬의 이전 제거를 통해, 우리는 또한 현재 GROUP BY가 항상 정렬한다고 가정하는 코드의 일부를 제거할 수 있다. 
예를 들어 쿼리가 그룹당 하나의 행만 생성하므로 GROUP BY를 제거할 수 있는 경우, Optimizer는 가능한 경우 GROUP BY를 주문 기준으로 현재 다시 씁니다. 
이 작업은 GROUP BY를 지정할 때 사용자가 예상한 결과로 수행됩니다.

그러나 제거되지 않는 것은 다음과 같습니다:

MySQL Optimizer는 현재 ORDER BY 열이 GROUP BY 열과 호환되는지 확인합니다. 호환되는 것으로 확인되면 GROUP BY가 필요한 정렬을 제공한다고 생각하여 ORDER를 제거합니다. 
나중에 그룹화된 결과를 생성하기 위해 그룹화 기준을 정렬해야 하는 것으로 결정된 경우 이는 좋은 최적화입니다.

그러나 그룹화에 임시 테이블을 사용하는 경우처럼 그룹화 기준으로 데이터를 정렬하여 그룹화된 결과를 얻을 필요가 없다면 비용이 많이 들 수 있습니다. 
이 경우, 서버는 임시 테이블의 그룹당 하나의 행을 업데이트하여 나중에 정렬할 수 있는 행 수를 줄입니다. 
데이터를 그룹화하기 전에 모든 행을 정렬해야 하는 것에 비해 이 방법이 더 좋습니다.

MySQL 옵티마이저는 현재 위의 작업을 비교하기 위한 비용 모델이 없기 때문에 호환성이 발견되면 ORDER BY를 GROUP BY로 이동하는 코드가 남아 있다. 
위의 변경 사항은 이 작업 로그에 의해 도입되지 않습니다.

따라서 이 작업 로그를 사용하여 GROUP BY ASC 및 GROUP BY DESC의 비표준 구문을 제거하고 위에서 언급한 코드를 재팩터링하여 이를 따를 것입니다.

MySQL 5.7의 ASC/DESC에 의한 GROUP BY ASC 구문 사용 중지.

참고: 8.0.12 이전까지 Mysql은 롤업과 함께 ORDER BY를 허용하지 않았습니다. 
그 이유는 ROLLUP이 행에서 NULLs를 생성하고 Mysql은 NULLs를 최소 값으로 처리하는 동안 바람직하지 않은 결과를 초래하기 때문입니다. 
따라서 ROLLUP을 사용하여 쿼리에 대한 주문 결과를 얻기가 어렵습니다. 
따라서 이 경우 정렬된 결과를 제공하기 위해 GROUP BY ASC/DESC가 있어야 했다. 
그러나 롤업 및 그룹화() 기능이 포함된 ORDER BY(주문 기준) 기능을 사용하면 조항별로 그룹화()를 사용하여 다음 문서에서 언급한 대로 원하는 결과를 얻을 수 있습니다.

#rollup and order by
http://mangalpardeshi.blogspot.co.uk/2009/08/rollup-and-order-by.html

Mysql 서버에 접속하는 방법은 Mysql 서버프로그램 (mysqld)과 함께 설치된 Mysql 

기본 클라이언트 프로그램인 mysql을 실행하면 된다. 
다음과 같이 여러가지 형태의 명령행 인자를 넣어 접속을 시도할 수 있다.

#1)
linux> mysql -uroot -p --host=localhost --socket=/tmp/mysql.sock

#2)
linux> mysql -uroot -p --host=127.0.0.1 --port=3306

#3)
linux> mysql -uroot -p

1) 예제는 Mysql 소켓 파일을 이용해 접속하는 예제다. 

2) 예제는 TCP/IP를 통해 127.0.0.1(로컬호스트)로 접속하는 예제인데, 이 경우에는 Port를 명시하는 것이 일반적이다.
로컬 서버에 설치된 Mysql이 아니라 원격 호스트에 있는 Mysql 서버에 접속할 때는 반드시 두 번째 방법을 사용해야 한다.

Mysql 서버에서 접속할 때는 호스트를 localhost로 명시하는 것과 127.0.0.1로 명시하는 것은 각각 의미가 다르다.

--host=localhost 옵션을 사용하면 Mysql 클라이언트 프로그램은 항상 소캣 파일을 통해 Mysql 서버에 접속하게 되는데, 
이는 Unix domain socket 을 이용하는 방식으로 TCP/IP를 통한 통신이 아니라 유닉스의 프로세스 간 통신(IPC: Inter Process Communicatioin)의 일종이다.

#IPC (inter process communication) 란?
서버 내에서 각 프로세스들이 통신하는 모든 형태를 일컽는다. 이에는 다양한 형태의 메세지 전달 방식이 포함된다.

하지만 127.0.0.1 을 사용하는 경우에는 자기 서버를 가리키는 루프백(loopback) IP이기는 하지만 TCP/IP 통신방식을 사용하는 것이다. (서버-클라이언트 통신으로 풀린다는 얘기)

3)번째 방식은 별도로 호스트 주소와 포트를 명시하지 않는다. 이 경우에는 기본값으로 호스트는 localhost가 되며 소켓 파일을 사용하게 되는데, 
소켓파일의 위치는 Mysql 서버의 설정 파일에서 읽어서 사용한다.

ex) my.cnf 파일내
socket          = /tmp/mysql.sock

Mysql 서버가 기동될 때 만들어지는 유닉스 소켓 파일은 Mysql 서버를 재시작하지 않으면 

다시 만들어 낼 수 없기 때문에 실수로 삭제하지 않도록 주의한다. 
유닉스나 리눅스에서 mysql 클라이언트 프로그램을 실행하는 경우에는 mysql 프로그램의 경로를 PATH 환경변수로 등록해둔다.

--initialize-insecure 옵션으로 mysql 서버가 초기화 되었다면 비밀번호 없이 로그인 가능하다.
--initialize 옵션으로 Mysql 서버가 초기화 되었다면 Mysql 서버의 로그 파일에 기록돼 있는 비밀번호를 이용해서 로그인하면 된다.

유닉스 계열 운영체제에서 RPM 패키지로 Mysql을 설치했다면 자동으로 /usr/lib/systemd/system/mysqld.service 파일이 생성되고,
systemctl 유틸리티를 이용해 Mysql을 기동하거나 종료하는 것이 가능하다.
윈도우 인스톨러 버전의 Mysql을 설치했다면 설치 중 선택사항으로 윈도우의 서비스로 Mysql을 등록할 수 있다.

#1) mysql DB 시작
systemctl start mysqld


#주의
앞의 예제와 같이 Mysql 서버의 systemd를 이용해 시작하고 종료할 수도 있지만 Mysql 배포판과 함께 

제공되는 mysqld_safe 스크립트를 이용해서 Mysql 서버를 시작하고 종료할 수도 있다.
mysqld_safe 스크립트를 이용하면 Mysql 설정파일(my.cnf)의 [mysqld_safe] 섹션의 설정들을 참조해서 

Mysql 서버를 시작하게 되지만,
앞의 예제와 같이 systemd를 이용해서 Mysql 서버를 시작하면 mysqld_safe 스크립트를 사용하지 않고 Mysql 서버를 시작하고 종료하게 된다.

그래서 systemd를 이용하는 경우에는 Mysql 설정 파일의 [mysqld_safe] 섹션을 무시하게 된다.

만약 Mysql 서버의 설정파일에 [mysqld_safe] 섹션에만 설정 가능한 malloc-lib 같은 시스템 설정을 적용하고자 한다면 mysqld_safe 스크립트를 이용해 Mysql 서버를 시작해야 한다.

물론 systemd를 이용해 Mysql 서버를 시작하는 경우에도 메모리 할당(Memory allocator)를 변경하고자 한다면
LD_PRELOAD 환경변수를 이용해서 Mysql 서버를 시작할 수도 있다.


#2) mysql DB 종료
systemctl stop mysqld

Mysql 서버에서는 실제 트랙잭션이 정상적으로 커밋돼도 데이터 파일에 

변경된 내용이 기록되지 않고 로그파일(redo log)에만 기록돼 있을 수 있다.

심지어 Mysql 서버가 종료되고 다시 시작된 이후에도 계속 이 상태로 남아있을 수도 있다. 

사용량이 많은 Mysql 서버에서는 이런 현상이 더 일반적인데, 이는 결코 비정상적인 상황이 아니다.

하지만 Mysql 서버가 종료될 때 모든 커밋된 내용을 데이터 파일에 기록하고 종료하게 할 수도 있는데, 이 경우에는 다음과 같이 Mysql 서버의 옵션을 변경하고 Mysql 서버를 종료하면 된다.

#클린 셧다운
mysql> SET GLOBAL innodb_fast_shutdown=0;
linux> systemd stop mysqld.service

#원격으로 mysql 서버 종료시
mysql> SET GLOBAL innodb_fast_shutdown=0;
mysql> SHUTDOWN;

이렇게 모든 커밋된 데이터를 데이터 파일에 적용하고 종료하는 것을 클린 셧다운(Clean shutdown)이라고 표현한다.
클린 셧다운으로 종료되면 다시 Mysql 서버가 기동할 때 별도의 트랜잭션 복구 과정을 진행하지 않기 때문에 

빠르게 시작할 수 있다.

2회독 중 정리할 부분은 발췌하고 기록한다.

Mysql 과 오라클을 비교해 본다면 당연히 Mysql의 경쟁력은 가격이나 비용일 것이다.
<real mysql>을 처음 집필하던 시점만 해도 오라클과 Mysql은 주요 고객이 완전히 달랐다.
물론 DBMS는 보수적인 소프트웨어이기 때문에 쉽게 기존 DBMS를 다른 DBMS로 바꾸려고 하지 않을 것이다.

하지만 이제는 금전적인 트랜잭션 처리라고 해서 Mysql 서버를 처음부터 배제하지는 않는다. 
국내 유명 포털 사이트도 빌린 시스템을 Mysql 서버로 구현해서 사용하고 있으며,
코어뱅킹 시스템은 아닐지라도 국내 대형은행 시스템에서도 Mysql 서버를 사용하고 있다.
-> 전북은행, 카카오뱅크를 얘기하는 듯

그런데 최근 10여 년간의 전자 제품 발전과 서버 컴퓨터 시장의 변화는 이전에는 없던 새롭고 엄청난 양의 데이터를 만들어 내기 시작했다.
Mysql 서버가 오라클 RDBMS 와 경쟁하지 않아도 사용될 곳이 무한정 늘어나고 있는 것이다.
이런 방대한 양의 데이터를 저장하기에 오라클 RDBMS는 너무 비싸다.

예전에 페이스북에 근무하는 DBA에게 "페이스북은 돈을 많이 벌 텐데, 왜 상용 DBMS를 선택하지 않고 Mysql 서버를 선택했는가?"라고 물었더니
"페이스북이 가진 데이터를 모두 오라클 RDBMS에게 저장하면 페이스북은 망할 것이다." 라는 답변을 들은 적 있다.

앞으로 10년은 지금까지 만들어지던 데이터의 몇백 배, 아니 몇천 배로 늘어날 것이다.
그때는 Mysql 서버 이외의 다른 선택지가 없을지도 모른다.

"어떤 DBMS를 사용해야 할 지 모르겠습니다. 어떤 DBMS가 좋은가요?"

많은 사람들로부터 듣는 질문 중 하나다. 이런 질문을 들을 때마다 저자는 항상 "자기가 가장 잘 활용할 수 있는 DBMS가 가장 좋은 DBMS입니다." 라고 답변한다.
이 답변은 지금도 변함이 없다.
이 질문의 답을 듣고도 아직 고민된다면 다음 순서로 고려하라고 조언하고 싶다.

1) 안정성
2) 성능과 기능
3) 커뮤니티나 인지도

DB-Engines.com 에서 이 순위를 만들기 위해 점수를 부여할 때 사용되는 대표적인 기준은 다음과 같다.

1) 웹사이트 언급 횟수 (website mentions)
2) 검색 빈도 (search frequency)
3) 기술 토론 빈도 (technical discussion frequency)
4) DBMS별 구인 (current job offers)
5) 전문가 인맥 (professional network profiles)



개인적인 생각으로도 
상용 서비스 DB의 핵심은 안정성이 가장 우선이고 
(ex MariaDB가 성능이 좋아도 상용서비스는 mysql을 선택하는 것처럼)

다음이 성능이다. 
(ex mysql 8.0의 성능이 우수한 것은 알지만 아직까지 잦은 패치로

상용서비스 DB는 mysql 5.7에서 버전업을 망설이고 있는 것처럼)

그리고 인지도도 중요한 것이 

많이 알려졌다는 것은  많은 사람이 사용한다는 것이고 

그것은 에러관련 참조할 레퍼런스가 많다는 것을 의미한다.

오픈소스의 매력은 수많은 에러와 장애의 퍼즐조각을 누군가는 맞추어 공유하고
그것을 바탕으로 소스의 안정성이 강화되어 간다는 것이다.