information technology: 11월 2020

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

Disk temporary table: InnoDB storage engine

위 같이 세 종류가 있으며 Default는 1이다.

8.0 또한 InnoDB 디스크 임시 테이블을 채용하고 있지만, 8.0.13부터는 ibtmp1가 비대화되는 현상이 수정되었다

# Refer
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

information technology