information technology: 2020

2020년 12월 30일 수요일

Hash Join 에 대하여

MySQL 8.0.18 부터 추가된 Hash Join에 대하여 알아보자.

Hash Join 이란
조인을 수행할 테이블의 조인 컬럼을 기준으로 해쉬함수를 수행한 후, 서로 동일한 해쉬 값을 갖는 것 중에서 실제 값이 같은지 비교하면서 조인을 수행하는 메커니즘이다.

선행 테이블에서 주어진 조건을 만족하는 로우 검색
선행 테이블의 조인 키를 기준으로 해쉬 함수 적용 후, 해쉬 테이블 생성. 조인 컬럼과 검색하는 컬럼도 함께 저장
선행 테이블의 조건을 만족하는 모든 로우를 1~2 과정 수행 후, 후행 테이블에서 주어진 조건을 만족하는 로우 검색
후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 선행 테이블과 비교
조인 성공 시, 추출버퍼에 격납
후행 테이블의 조건을 만족하는 모든 로우를 3~5 과정 수행

해쉬 테이블의 크기가 메모리에 적재할 수 있는 크기보다 클 경우, 임시 영역에 해쉬 테이블을 저장하므로
사이즈가 작은 테이블을 선행으로 사용하는 것이 좋다.

Hash Join 채용 조건
해쉬 조인은 등가조건의 조인이고, 조인하는 컬럼에 Index가 없는 경우에 사용된다
해쉬 조인의 사용유무는 EXPLAIN ANALYZE 또는 EXPLAIN FORMAT=TREE: 로 확인 가능하다.

# MySQL 8.0.20 에서 Hash Join이 강화되어 채용 조건이 확대되었다

등가 조인 이외의 INNER JOIN
세미조인
안티조인
LEFT OUTER JOIN / RIGHT OUTER JOIN

상세한 내용은 공식 사이트 참고
https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

Hash Join 검증
t1, t2 테이블 작성 후 테스트용 데이터(랜덤으로 생성한 20문자) 16384건 등록하자.

mysql> CREATE TABLE t1 (a VARCHAR(20));
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 (a VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ();
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
 
～(생략)～
 
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 8192 rows affected (0.11 sec)
Records: 8192  Duplicates: 0  Warnings: 0
 
mysql> UPDATE t1 SET a = SUBSTRING(MD5(RAND()), 1, 20);
Query OK, 16384 rows affected (0.81 sec)
Rows matched: 16384  Changed: 16384  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 16384 rows affected (0.58 sec)
Records: 16384  Duplicates: 0  Warnings: 0
 
mysql> UPDATE t2 SET a = SUBSTRING(MD5(RAND()), 1, 20);
Query OK, 16384 rows affected (0.30 sec)
Rows matched: 16384  Changed: 16384  Warnings: 0

작성한 테이블 t1, t2에 대하여 아래의 쿼리를 실행해보자

mysql> EXPLAIN ANALYZE SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
+-------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.a = t2.a)  (cost=26368857.25 rows=26367169) (actual time=42.485..52.619 rows=5 loops=1)
    -> Table scan on t1  (cost=0.01 rows=16269) (actual time=0.016..10.830 rows=16384 loops=1)
    -> Hash
        -> Table scan on t2  (cost=1644.95 rows=16207) (actual time=0.051..10.163 rows=16384 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

실행계획에 "Inner hash join" 이 출력되어 있고, Hash Join이 실행된 것을 알 수 있다.

# Refer
https://mysqlserverteam.com/hash-join-in-mysql-8/

2020년 12월 27일 일요일

Clone Plugin 에 대하여

MySQL 8.0.17 부터 추가된 Clone 플러그인에 대하여 알아보자.

Clone plugin 이란
온라인으로 습득할 수 있는 풀 백업으로, 동일한 데이터의 MySQL 서버를 작성할 수 있다.

Clone plugin 도입
MySQL 서버 패키지에 들어 있고, 사용하기 위해서는 INSTALL PLUGIN 으로 설치해야 한다.
INSTALL PLUGIN 실행 후에 SHOW PLUGIN 으로 설치되어 있는 것을 확인할 수 있다.

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.49 sec)

mysql> SHOW PLGUINS;

...

| mysqlx                          | ACTIVE   | DAEMON             | NULL           | GPL     |
| clone                           | ACTIVE   | CLONE              | mysql_clone.so | GPL     |
+---------------------------------+----------+--------------------+----------------+---------+
45 rows in set (0.06 sec)

Clone으로 데이터 복사
Clone을 사용하여 데이터를 습득 하려면, CLONE LOCAL DATA DIRECTORY = <습득 하려고 하는 디렉토리 패스>를 지정해야 한다.
실행하기 위해서는 모든 스키마, 테이블(*.*)에 대하여 CLONE_ADMIN 권한이 필요하다(Local 환경에서 실행하는 경우는 BACKUP_ADMIN 권한으로 가능)

또한, Clone을 실행할 때 지정한 디렉토리가 존재하는 경우, 에러가 발생하여 데이터 습득에 실패하게 된다.

ERROR 1007 (HY000): Can't create database '/tmp/testdb'; database exists

문제 없이 실행된 경우는, 아래와 같이 Query OK가 표시 된다.

mysql> CLONE LOCAL DATA DIRECTORY = '/tmp/clone_testdb';
Query OK, 0 rows affected (2.30 sec)

지정한 디렉토리에 복사된 데이터를 확인해보자.

$ ll /tmp/clone_testdb
total 682064
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 #clone
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 d1
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 d2
-rw-r----- 1 mysql mysql     24800 Dec 22 03:12 ib_buffer_pool
-rw-r----- 1 mysql mysql  12582912 Dec 22 03:12 ibdata1
-rw-r----- 1 mysql mysql 268435456 Dec 22 03:12 ib_logfile0
-rw-r----- 1 mysql mysql 268435456 Dec 22 03:12 ib_logfile1
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 mysql
-rw-r----- 1 mysql mysql  79691776 Dec 22 03:12 mysql.ibd
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 sys
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 test
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 trxtest
-rw-r----- 1 mysql mysql  14680064 Dec 22 03:12 undo_001
-rw-r----- 1 mysql mysql  26214400 Dec 22 03:12 undo_002
-rw-r----- 1 mysql mysql  28311552 Dec 22 03:12 undo_table_space.ibu
drwxr-x--- 2 mysql mysql      4096 Dec 22 03:12 world

이를 이용하여, 다른 포트로 MySQL을 기동해보자. 필자의 환경에서는 /data 밑에 datadir를 배치하고 있으므로, 동일하게 /data 밑으로 이동시킨 후, my.cnf를 준비하여 기동시켜보자

$ mv /tmp/clone_testdb /data/clone_testdb
$ cp /data/testdb/my.cnf /data/clone_testdb/my.cnf
$ vi /data/clone_testdb/my.cnf <-port, server-id, datadir 등 변경
$ /usr/bin/mysqld_safe --defaults-file=/data/clone_testdb/my.cnf &
$ /usr/bin/mysql --defaults-file=/data/clone_testdb/my.cnf -uroot -p
mysql> SELECT @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| d1                 |
| d2                 |
| information_schema |
| mysql              |
| nyumon             |
| performance_schema |
| sys                |
| test               |
| trxtest            |
| world              |
+--------------------+
10 rows in set (0.18 sec)

GTID를 이용하고 있을 때는 GTID 관련 정보도 복사되기 때문에, MASTER_AUTO_POSITION=1 을 지정하여 CHANGE MASTER statement를 실행하면 슬레이브로 추가할 수 있다.

GTID를 이용하고 있지 않은 경우에도, performance_schema.clone_status 테이블에서 CLONE 실시 정보를 습득할 수 있으므로, 이 정보를 토대로 CHANGE MASTER statement를 실행하여 슬레이브를 추가할 수 있다.

mysql> SELECT * FROM performance_schema.clone_status\G
*************************** 1. row ***************************
             ID: 1
            PID: 12
          STATE: Completed
     BEGIN_TIME: 2020-12-22 03:12:13.358
       END_TIME: 2020-12-22 03:12:14.556
         SOURCE: LOCAL INSTANCE
    DESTINATION: /tmp/clone_testdb/
       ERROR_NO: 0
  ERROR_MESSAGE:
    BINLOG_FILE:
BINLOG_POSITION: 0
  GTID_EXECUTED:
1 row in set (0.15 sec)

리모트 서버에 대한 Clone은 아래와 같이 가능하다.
CLONE INSTANCE FROM 'clone 실행 유저'@'Donar IP':'Donar port' IDENTIFIED BY '비밀번호' (DATA DIRECTORY = '습득처')

DATA DIRECTIRY는 옵션이므로 괄호로 감싸져 있다. 만약 지정하지 않은 경우는 Recipient의 데이터가 Donar과 같은 데이터로 변경되고 서버가 재기동된다. DATA DIRECTIRY를 기술하지 않으면, 데이터가 옮겨져 Recipient에 존재하던 데이터가 사라지기 때문에 주의가 필요하다.

mysql> CLONE INSTANCE FROM 'root'@'127.0.0.1':3306 IDENTIFIED BY 'xxxxxx' DATA DIRECTORY = '/tmp/remote_testdb';
Query OK, 0 rows affected (2.17 sec)

또한, 리모트 서버로 실행하는 경우, Donar 인스턴스 정보가 Recipient의 clone_valid_donor_list에 설정되어 있어야 한다.

mysql> SET GLOBAL clone_valid_donor_list = '127.0.0.1:3306';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW VARIABLES LIKE 'clone_valid_donor_list';
+------------------------+----------------+
| Variable_name          | Value          |
+------------------------+----------------+
| clone_valid_donor_list | 127.0.0.1:3306 |
+------------------------+----------------+
1 row in set (0.07 sec)

주의점
Clone 플러그인을 이용할 때는 몇가지 주의점이 있다.

Donar측에 병렬 DDL은 허용하지 않는다. 만약, 빈번히 DDL이 실행되는 환경이라면, 참조 없고 지연되도 상관없는 슬레이브 서버 등에서 실행하는 걸 추천
Donar 과 Recipient 버전이 다른 경우, 클론 불가능
Clone 대상이 되는 데이터는 InnoDB 스토리지 엔진이다. 실행 전에 InnoDB 이외의 테이블이 없는 지 확인 필요

# Refer
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html

2020년 12월 15일 화요일

CHECK 제약에 대해서

MySQL 8.0.16 부터 새롭게 추가된 CHECK 제약애 대해서 알아보자.

CHECK 제약
테이블에 데이터를 등록 또는 갱신할 때에 조건을 만족하는 지 검증하는 기능이다.

예를 들어, UNSIGNED TINYINT로 정의된 컬럼에는 0~255 까지의 숫자를 등록할 수 있지만 CHECK 제약을 이용하여 0~10 까지의 숫자로 한정하는 것이 가능하다.

mysql> CREATE TABLE t1(num TINYINT UNSIGNED CHECK(num < 11));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1(num) VALUES (10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1(num) VALUES (11);
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

mysql> SELECT * FROM t1;
+------+
| num  |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> UPDATE t1 SET num = 11 WHERE num = 10;
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

CHECK 제약 작성
CHECK 제약은 테이블 또는 컬럼에 지정하는 게 가능하다.

컬럼 제약은 해당 컬럼만을 참조하여 정의하는 제약이고, 테이블 제약은 테이블의 컬럼들을 참조하여 정의하는 게 가능하다.

mysql> CREATE TABLE t1(
    ->   num1 INT CHECK (num1 > 1),
    ->   num2 INT CONSTRAINT num2_chk CHECK (num2 > 0),
    -> CHECK (num1 *2 < num2), # 제약 명칭을 지정하지 않으면 <table_name>_chk_<number>로 자동생성
    -> CONSTRAINT t1_chk CHECK (num1 <> 0)
    -> );
Query OK, 0 rows affected (0.01 sec)

작성한 CHECK 제약 확인
SHOW CREATE TABLE구문으로 확인하는 것이 가능하다.
또한 INFORMATION_SCHEMA.CHECK_CONSTRAINT 테이블에서도 CHECK 제약 리스트를 습득할 수 있다.

mysql> SELECT * FROM information_schema.check_constraints;
+--------------------+-------------------+-----------------+-------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE            |
+--------------------+-------------------+-----------------+-------------------------+
| def                | check_database    | t1_chk_1        | (`num1` > 1)            |
| def                | check_database    | num2_chk        | (`num2` > 0)            |
| def                | check_database    | t1_chk_2        | ((`num1` * 2) < `num2`) |
| def                | check_database    | t1_chk          | (`num1` <> 0)           |
+--------------------+-------------------+-----------------+-------------------------+
4 rows in set (0.01 sec)

CHECK 제약위반이 발생한 경우는 performance_schema.events_errors_summary_by_* 계열 테이블의 ER_CHECK_CONSTRAINT_VIOLATED 에러 카운트가 증가한다.

CHECK 제약에 의해 에러가 발생하고 있는 지 확인해야 하는 경우는 해당 계측을 참조하면 좋을 거 같다.

CHECK 제약의 제한
이용하는 데 있어서 몇 가지의 제한이 있다. 아래와 같은 경우는 CHECK 제약을 이용할 수 없다.

AUTO_INCREMENT를 지정한 컬럼
다른 테이블의 컬럼을 참조하여 CHECK 제약 정의
사용자 함수 또는 프로시저 사용
변수 사용
서브 쿼리 사용
NOW(), CURRENT_USER() 와 같은 비결정성 함수 사용

보다 상세한 조건은 공식 문서 참조.

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

CHECK 제약 변경은 ALTER TABLE 구문을 이용해야 한다.
단, 기존 데이터가 CHECK 제약을 위반하고 있는 경우는 에러가 발생하므로 사전에 확인하는 게 좋다.

mysql> insert into t1 values(0),(1),(2);
Query OK, 3 rows affected (0.01 sec)
mysql> ALTER TABLE t1 ADD CONSTRAINT CHECK(num > 0);
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

# Refer
https://mysqlserverteam.com/mysql-8-0-16-introducing-check-constraint/

2020년 12월 5일 토요일

Partial Revokes 시스템 변수에 대하여

MySQL 8.0.16에서 Partial Revokes 시스템 변수가 추가되었다.

통상적으로 모든 권한을 부여하지만, 일부 권한만 박탈할 수 기능으로 부분적인 제한 설정이 가능해졌다.
이 변수를 굳이 사용하지 않아도 동일한 권한을 부여할 수 있지만, 부여하는 스키마의 대상이 많은 경우는 해당 변수를 사용하는 게 편리할 지도 모른다.

partial_revokes 설정
기본 값이 OFF로 설정되어 있으므로, 기능을 사용하기 위해서는 ON으로 해주어야 한다.

mysql> SET GLOBAL partial_revokes = 1;
Query OK, 0 rows affected (0.04 sec)

모든 데이터베이스의 SELECT권한을 갖는 유저를 작성한 후 mysql 스키마에 대한 권한을 박탈해보자.

mysql> CREATE USER test_user@localhost IDENTIFIED BY '*****';
Query OK, 0 rows affected (0.05 sec)


mysql>GRANT SELECT ON *.* TO test_user@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>REVOKE SELECT ON mysql.* FROM test_user@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------------------+
| Grants for test_user@localhost                          |
+-----------------------------------------------------------+
| GRANT SELECT ON *.* TO `test_user `@`localhost`          |
| REVOKE SELECT ON `mysql`.* FROM `test_user `@`localhost` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

실제로 작성한 유저로 mysql 스키마에 접근해보면 에러가 발생하는 것을 확인할 수 있다.

mysql> SELECT current_user();
+-----------------------+
| current_user()        |
+-----------------------+
| test_user@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'test_user'@'localhost' for table 'user'

partial_revokes는 "일부를 제외한 모든 권한을 부여한다" 라고 이해하면 될 거 같다.
실제로 partial_revokes를 이용한 revoke 정보는 mysql.user.user_attributes 컬럼에서 확인할 수 있다.

mysql> SELECT concat(user,'@', host), user_attributes FROM mysql.user WHERE user = 'test_user' and host = 'localhost';
+------------------------+---------------------------------------------------------------------+
| concat(user,'@', host) | user_attributes                                                     |
+------------------------+---------------------------------------------------------------------+
| test_user@localhost  | {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

partial_revokes 주의점
partial_revokes를 이용하고 있는 유저가 있는 경우, 설정을 OFF로 변경하는 건 불가능하다.

mysql> SET GLOBAL partial_revokes = 0;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.

OFF로 변경하고 싶은 경우는, partial_revokes로 박탈된 권한 삭제 또는 유저 삭제로 partial_revokes를 이용하지 않는 상태로 되어야 한다.
partial_revokes가 이용되고 있는 유저의 유무 확인은 mysql.user.user_attributes 컬럼의 Restrictions 속성으로 가능하다.

partial_revokes는 스키마를 지정해야 하고 일부 테이블이나 컬럼, % 등의 와일드 카드를 이용해서 스키마를 제외하는 건 불가능하다.
또한 CREATE TABLE이나 FILE과 같은 글로벌 레벨의 권한에 대해서도 partial_revokes를 이용할 수 없다.

replication구성에서 master가 partial_revokes ON, slave가 partial_revokes OFF인 경우 에러가 발생하기 때문에 각 인스턴스의 설정을 맞춰주어야 한다.

partial_revokes: master ON, slave OFF 상황에서의 에러

mysql> SHOW SLAVE STATUS\G
〈skip〉
Last_SQL_Errno: 1141
Last_SQL_Error: Error 'There is no such grant defined for user 'test_user' on host 'localhost'' on query. Default database: '
'. Query: 'REVOKE SELECT ON mysql.* FROM test_user@localhost'
  Replicate_Ignore_Server_Ids:

권한을 부여하는 유저가 partial_revokes에 의해 REVOKE되어 있는 스키마가 있는 경우, 권한을 부여할 때 revoke된 스키마도 인계되기 때문에 주의가 필요하다.

아래의 블로그에서 위와 같은 여러 패턴을 소개하고 있다
https://mysqlserverteam.com/partial-revokes-from-database-objects/

# Refer
https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html

2020년 12월 4일 금요일

Nested subquery가 Correlated subquery처럼 동작하는 경우에 대하여

개발팀의 쿼리 검수요청을 처리하던 중 Nested subquery가 Correlated subquery처럼 동작하는 케이스를 발견하여 알아보도록 하자.
해당 DB는 MySQL 5.6에서 실험하였다

우선 간단하게 서브쿼리에 대하여 짚고 넘어가자.

Nested subquery(중첩 서브쿼리)

# 이너 쿼리를 먼저 실행
# 이너 쿼리의 결과를 통해 아우터 쿼리 조건 검색

SELECT SUM (Sales) FROM Store_Information
WHERE Store_Name IN
  ( SELECT Store_Name FROM Geography
    WHERE Region_Name = 'West');

Correlated subquery(상관 서브쿼리)

# 아우터 쿼리를 먼저 실행
# 접근한 로우의 컬럼 값을 이용해 이너 쿼리 실행 
# 아우터 쿼리의 로우가 끝날 때 까지 반복 

SELECT SUM (a1.Sales) FROM Store_Information a1
WHERE a1.Store_Name IN
  ( SELECT Store_Name FROM Geography a2
    WHERE a2.Store_Name = a1.Store_Name);

이제부터 검수 요청 받은 쿼리와 테이블에 대하여 알아보고 분석을 해보자

검수 쿼리

# Table
CREATE TABLE `table_aaa` (
  `transactionSeq` bigint(20) NOT NULL,
  `userId` varchar(100) NOT NULL,
  .
  .
  .
  `regDt` datetime NOT NULL,
  PRIMARY KEY (`transactionSeq`,`regDt`),
  KEY `TABLE_AAA_IDX1` (`regDt`,`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE `table_bbb` (
  `userId` varchar(16) NOT NULL,
  .
  .
  .
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


# Query
SELECT
    ...
FROM
    table_aaa
WHERE
    regDt >= '2020-01-01'
AND
    regDt < '2020-02-01'
AND
    userId IN (
        SELECT
            userId
        FROM
            table_bbb
        WHERE
            regDt >= '2020-01-01'
        AND
            regDt < '2020-02-01'
    )
GROUP BY
    ...;

쿼리를 보면 중첩 서브쿼리라고 생각이 들겠지만, 실제로 실행계획을 살펴보니 그게 아니었다.

# EXPLAIN
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_aaa
         type: range
possible_keys: TABLE_AAA_IDX1
          key: TABLE_AAA_IDX1
      key_len: 5
          ref: NULL
         rows: 39124
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_bbb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 50
          ref: test_db.test_aaa.userId
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

ERROR:
No query specified

분석

TABLE_AAA_IDX1을 이용하여 table_aaa.regDt 검색
TABLE_AAA_IDX1 인덱스가 (regDt, userId) 복합키로 설정되어 있으므로, 인덱스 페이지에서 조건에 맞는 로우의 userId를 이용하여 table_bbb의 PK인 userId를 검색
PK를 통해 table_bbb의 로우에 접근 후 추가적으로 regDt 조건 필터

이너쿼리 조건절에 table_bbb.userId = table_aaa.userId가 기재되어 있는 것 같은 동작을 하고 있으므로,
옵티마이저가 중첩 서브쿼리를 채용한 것으로 보인다

2020년 11월 25일 수요일

Slow query log file을 인식하지 못하는 현상에 대하여

mysqld 재기동 후, Slow query가 로그 파일에 출력되지 않았던 현상과 대응한 내용에 대하여 알아보자.
해당 DB 버전은 MySQL 5.6.17 이다

서버 설정

admin@localhost:(none) 12:32:06>show global variables like "slow%";
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_launch_time    | 2                                 |
| slow_query_log      | ON                                |
| slow_query_log_file | /mysql/logs/slow_query.log        |
+---------------------+-----------------------------------+admin@localhost:(none) 12:33:07>show global variables like "long_query%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)admin@localhost:(none) 12:33:30>show global variables like "min%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)admin@localhost:(none) 12:33:50>show global variables like "log%";
+----------------------------------------+-------------------------------------+
| Variable_name                          | Value                               |
+----------------------------------------+-------------------------------------+
| log_error                              | /mysql/logs/mysqld.err              |
| log_output                             | FILE                                |
| log_queries_not_using_indexes          | OFF                                 |
| log_slave_updates                      | ON                                  |
| log_slow_admin_statements              | ON                                  |
| log_slow_slave_statements              | OFF                                 |
| log_throttle_queries_not_using_indexes | 0                                   |
| log_warnings                           | 1                                   |
+----------------------------------------+-------------------------------------+

슬로우 쿼리 로그 파일

[root@0hostname /]# ls -al /mysql/logs/slow_query.log
-rw-r----- 1 mysql mysql 0 Nov 24 01:12 /mysql/logs/slow_query.log

원인 분석

슬로우 쿼리 설정, 파일 권한 모두 문제 없는 것으로 판명
관련 에러로그 없음
일시적으로 general log를 활성화 후, 확인한 결과 문제 없음

리서치 중, 공식 사이트에서 다음과 같은 문장을 발견하였다.

If you enable the log, the server opens the log file and writes startup messages to it.

해결 방안

공식 사이트를 보고 슬로우 쿼리 로그 파일을 확인 해보니, 아무런 메세지가 남아있지 않았다.

프로세스 기동 중에 파일을 인식하지 못한 것 같아, 아래의 명령어를 통해 파일 close & reopen

admin@localhost:(none) 12:48:31>FLUSH SLOW LOGS;
Query OK, 0 rows affected (0.04 sec)

이 후에는 문제 없이 슬로우 쿼리를 확인할 수 있게 되었다

# Refer
https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
https://dev.mysql.com/doc/refman/5.6/en/flush.html

2020년 11월 15일 일요일

TempTable storage engine에 대하여

지난 번에 알아본 Internal Temporary Table에서 소개한 MySQL 8.0부터 추가된 TempTable 스토리지 엔진에 대하여 알아보자(https://jongguheo.blogspot.com/2020/11/internal-temporary-table.html)

TempTable storage engine
MySQL 8.0은 기본 값으로 인메모리 임시 테이블과 디스크 임시 테이블이 TempTable로 되어있다.
TempTable 스토리지 엔진은 아래와 같은 특징이 있다

가변 길이 데이터 타입의 효율적인 스토리지 제공
바이너리 오브젝트 타입 서포트
글로벌 메모리 영역에서의 관리
mmap 파일에 의한 overflow

가변 길이 데이터 타입의 효율적인 스토리지 제공
종래의 인메모리 임시 테이블은 VARCHAR, VARBINARY 같은 가변 길이 데이터 타입에 대해서도, Column에 지정된 문자 수를 격납하므로 스토리지를 과소비 하는 문제가 있었다.

예) "name varchar(20)” 컬럼에 "jonggu"가 저장되어 있으면 실제 데이터는 6바이트, 인모메리 임시 테이블에서는 실제 데이터 크기가 아닌 20바이트를 사용한다

8.0부터 추가된 TempTable에서는 가변 길이 데이터 타입도 실제 데이터 크기만 소비하도록 개선되었다.

바이너리 오브젝트 타입 서포트
종래의 인메모리 임시 테이블은 BLOB, TEXT, JSON 같은 타입을 서포트 하지 않았으므로, 이러한 타입이 있는 경우는 디스크 임시 테이블을 사용하도록 되어 있었다.

TempTable에서는 이 타입들 또한 서포트 되어, 인메모리 임시 테이블에서 처리할 수 있게 되었다.

글로벌 메모리 영역에서의 관리
종래의 인메모리 임시 테이블은 스레드 별로 메모리 최대 값을 설정하고, 스레드 별로 메모리를 확보하고 있었다.

TempTable에서는 스토리지 엔진이 점유하는 메모리 최대 값을 설정할 수 있게 되었고, 모든 스레드가 공유하여 사용한다.
최대 값은 temptable_max_ram으로 설정가능하고 기본 값은 1GB이다.

mmap 파일에 의한 overflow
종래의 인메모리 임시 테이블은 디스크 임시 테이블로 전환될 때, 인메모리 임시 테이블의 데이터를 디스크 임시 테이블로 복사하는 처리가 발생하고 있었다.

temptable_use_mmap=ON인 경우, TempTable 스토리지 엔진이 mmap 임시 파일로 인메모리 임시 테이블을 위한 공간을 할당하기 때문에 데이터를 복사하는 처리는 발생하지 않는다.

설정에 대하여
MySQL 8.0에서는 내부 임시 테이블의 종류가 3가지 있는 데, 그 중에서 어떤 설정이 좋을 지 생각해보자.

In-Memory temporary table: TempTable, Disk Temporary table: TempTable(Default)
In-Memory temporary table: TempTable, Disk Temporary table: InnoDB
In-Memory temporary table: Memory, Disk Temporary table: InnoDB

필자의 개인적인 의견으로는, Default가 좋은 거 같다.
몇가지 테스트를 해본 결과 대부분의 내부 임시 테이블을 사용하는 쿼리는, 종래의 구조보다 TempTable을 사용하는 게 퍼포먼스가 좋았다.

하지만 MySQL 8.0 조기 버전에서는, 꽤 많은 버그 리포트가 있었고

이 버그들은 MySQL 8.0.21 에서 개수된게 많이 때문에, 8.0.20 이하인 경우는 안전성을 생각하여 종래의 구조를 선택하는 게 좋을 거 같다.

또한, 2. 에 대하여 설정을 고려하는 경우도 알아보자.

temptable_max_ram 파라미터를 초과하면 mmap 임시 파일을 작성하는 데, 사이즈가 큰 내부 임시 테이블을 작성하는 쿼리가 있으면, 대량의 메모리를 소비할 가능성이 있다.

내부 임시 테이블을 작성하는 쿼리가 많고, 메모리 사용량에 문제가 되는 경우는 2. 를 사용할 거 같다

# Refer
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

2020년 11월 7일 토요일

Internal Temporary Table에 대하여

MySQL이 statement처리 중에 internal temporary table(내부 임시 테이블)을 작성하는 경우에 대하여 알아보자.

Internal Temporary Table이란

Statement처리 중 최적화 또는 결과를 저장해두기 위해 내부적으로 작성되는 테이블이다. 따라서 유저가 직접 컨트롤 할 수 있는 테이블이 아니다.
내부 템프 테이블을 사용하는 쿼리를 실행하면 자동으로 생성되고, 종료 시 자동으로 삭제된다.
처음은 메모리 위에 작성되고, 처리해야 할 양이 많아지고 설정 해놓은 메모리 사용량을 초과하면 자동으로 디스크 상의 테이블로 변환된다.

Internal Temporary Table을 작성하는 조건

UNION을 사용하는 경우
ORDER BY구문과 다른 컬럼으로 GROUP BY구문을 사용하는 경우
복수의 테이블을 갱신하는 경우
GROUP_CONCAT() 또는 COUNT(DISTINCT)를 사용하는 경우
ORDER BY구문과 조합한 DISTINCT를 사용하는 경우
인라인뷰를 사용하는 경우
서브쿼리 또는 세미조인의 materialization용으로 작성된 테이블이 있는 경우
동일한 테이블 간의 INSERT ... SELECT
윈도우 함수를 사용하는 경우
CTE(common table expression)를 사용하는 경우

확인 방법
실행계획(EXPLAIN)을 보면 내부 임시 테이블을 작성 했는 지 확인할 수 있다.

Extra열에 Using temporary가 표시되는 경우
select_type열에 DRIVED가 표시되는 경우
select_type열에 SUBQUERY 또는 MATERIALIZE가 표시되는 경우

여러가지 패턴이 있으므로 EXPLAIN으로 정확한 정보를 얻는 건 어렵지만, 일반적으로 위와 같이 생각해두면 좋다.

UNION을 사용한 쿼리

mysql> EXPLAIN SELECT id FROM t0 UNION SELECT id FROM t1;
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
| id  | select_type  | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra           |
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
|  1  | PRIMARY      | t0         | NULL       | index | NULL          | id     | 5       | NULL | 491096 |   100.00 | Using index     |
|  2  | UNION        | t1         | NULL       | index | NULL          | id     | 5       | NULL | 491096 |   100.00 | Using index     |
| NULL| UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |   NULL |     NULL | Using temporary |
+-----+--------------+------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+

파생테이블

mysql> EXPLAIN SELECT t0.id FROM t0 JOIN (SELECT distinct id FROM t1) t1 USING(id);
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref       | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL      | 80597 |   100.00 | NULL        |
|  1 | PRIMARY     | t0         | NULL       | ref   | id            | id     | 8       | t1.id     |    15 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | NULL       | index | id            | id     | 8       | NULL      | 80597 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------+---------+-----------+-------+----------+-------------+

세미조인의 materialization

mysql> EXPLAIN t0.id FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref   | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE       | t0          | NULL       | ALL    | id                  | NULL                | NULL    | NULL  | 491096 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8       | t0.id |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t1          | NULL       | index  | id                  | id                  | 8       | NULL  |  80597 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------+--------+----------+-------------+

Storage engine에 대하여
내부 임시 테이블에 사용되는 스토레지 엔진은 MySQL버전에 따라 다르다.
MySQL 5.6 이하

In-Memory temporary table: MEMORY storage engine
Disk temporary table: MyISAM storage engine

In-Memory tmp table의 허용치는 tmp_table_size, max_heap_table_size 파라미터 중 작은 값이고, 초과 시 디스크를 사용한다.

MySQL 5.7

In-Memory temporary table: MEMORY storage engine
Disk temporary table: MyISAM 또는 InnoDB storage engine

디스크 임시 테이블의 Default storage engine이 InnoDB로 변경되었다.
체감상 InnoDB가 퍼포먼스가 좋았으나, 사용하는 데 주의해야할 점이 있다

InnoDB 제한에 의한 쿼리 에러 발생
InnoDB temporary table용 테이블 스페이스 사이즈 증가

1.은 InnoDB page size가 16KB인 경우 행 사이즈 맥시멈이 약 8000Byte으로, 초과 시 Row size too large 또는
Too many columns 에러가 발생하는 경우가 있다

2.은 디스크 임시 테이블을 사용한 쿼리가 종료된 후에도 ibtmp1에 해당 데이터가 남아있어 디스크 부족현상이 일어날 수 있으며, mysqld를 재기동 하지 않는 이상 줄어들지 않는다.

mysql>SELECT @@innodb_temp_data_file_path;

+------------------------------+ | @@innodb_temp_data_file_path | +------------------------------+ | ibtmp1:12M:autoextend | +------------------------------+

이에 대한 회선책은 autoextend가 아닌 max_file_size를 설정하는 방법과 디스크 임시 테이블을 MyISAM으로 변경하는 방법이 있다(온라인으로 변경 가능)

MySQL 8.0

8.0 부터는 새로운 TempTable storage engine이 등장했고 Disk temporary table MyISAM이 폐지되었다.

internal_tmp_mem_storage_engine과 temptable_use_mmap파라미터에 따라서 사용되는 스토레지 엔진이 다르다

1. internal_tmp_mem_storage_engine=TempTable, temptable_use_mmap=ON인 경우

In-Memory temporary table: TempTable storage engine

Disk temporary table: TempTable storage engine

2. internal_tmp_mem_storage_engine=TempTable, temptable_use_mmap=OFF인 경우

In-Memory temporary table: TempTable storage engine

Disk temporary table: InnoDB storage engine

3. internal_tmp_mem_storage_engine=Memory

In-Memory temporary table: MEMORY storage engine