[3주차] Real MySQL 5~7장 발표자료

월간-CS, RealMySQL 토론스터디 2주차 (4장) 발표자료입니다. - [Ref]
11.14. ~ 11.19.화 까지 작성한 DIL을 기반으로 준비하였습니다. - [Ref]

발표에 앞서

이번 주차는 트랜잭션과 잠금, 데이터 압축, 데이터 암호화로 구성되어 있습니다.

5. 트랜잭션과 잠금 (155~183p)

MySQL에는 트랜잭션*과 잠금*이 존재합니다.

• 잠금(Lock) : 데이터의 동시성을 제어하기 위한 기술

• 트랜잭션(Transaction) : 데이터의 정합성을 보장하기 위한 기술

따라서 다음의 3가지로 구분하여 공부하고자 합니다.

• 트랜잭션 (155~160p)

• MySQL 엔진의 잠금 (160~165p)

• InnoDB 엔진의 잠금 (166~176p)

5.1. 트랜잭션 (155~160p)

MySQL의 트랜잭션에 대한 기본 컨셉을 3가지 부문으로 다루었습니다.

1. 일련의 규칙

2. InnoDB, MyISAM 트랜잭션 비교

3. Storage engine MyISAM is disabled 에러

5.1.1. 일련의 규칙

1. 데이터 INSERT, UPDATE, DELETE 부분에서만 트랜잭션을 써라

2. 비슷한 INSERT 끼리만 묶고 다른 성질인 경우 다른 트랜잭션을 써라

3. 서버가 외부와 FTP, HTTPS 등의 통신을 하는 경우에는 트랜잭션과 분리해라

5.1.2. InnoDB, MyISAM 트랜잭션 테스트

InnoDB에서는 작업들이 실패하면 모든 작업들이 롤백되지만,
MyISAM에서는 작업들이 실패하면 성공한 작업들은 커밋됩니다.

5.1.3. Storage Engine 비활성화/활성화

156p의 실습을 따라하려고 하니 MyISAM 테이블 생성에서 에러가 발생합니다.

mysql> CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

저는 MacOS에서 MySQL Community v,8.0.40 을 사용하고 있습니다.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)

또한 아래처럼 MyISAM은 활성화되어 있는 상태입니다.

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

구글링해보았더니 my.cnf에 파일이 있다고 했는데 찾아보니까 텅 비어 있습니다.
일부 설정값이 남아 있을 수 있으므로 아래 값을 추가해줍니다.

[mysqld]
disabled_storage_engines=""

이후 테이블을 추가할 수 있습니다.

mysql> CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

5.2. MySQL 엔진의 잠금 (160~165p) - [Ref]

5.3. InnoDB 엔진의 잠금 (166~176p) - [Ref]

MySQL 기반의 잠금과 다르게
InnoDB 잠금은 스토리지 엔진에 기반한 레코드 기반 잠금을 채택하고 있습니다. 따라서 이에 파생한 다양한 Lock 들이 존재하며, 이에 대한 이해도를 기반으로 쿼리를 작성해야 한다고 생각합니다.

단순하게 단건 쿼리 튜닝의 관점이 아니라
동시성 환경에서 발생 가능한 Lock 까지 고려 가능한 단계?

5.3.1. InnoDB 스토리지 엔진의 잠금 (167~170p)

5.3.1.1. Record Lock (169p)

InnoDB에서 Transaction은 기본적으로 Record Lock을 생성하며 자세한 내용은 5.3.2. 인덱스와 잠금 및 5.3.3. 레코드 수준의 잠금 확인 및 해제에서 다루고자 합니다.

실제로 SHOW ENGINE INNODB STATUS\G 명령어로 Record Lock을 확인할 수 있습니다. - Ref

5.3.1.2. Gap Lock (169p)

MySQL에서는 SELECT 범위 내,
새로운 값이 들어오지 못하도록 Gap Lock 설정이 가능합니다.

Index Record 사이의 잠금 또는 첫 번째 또는 마지막 레코드 인덱스 앞뒤의 갭에 대한 잠금입니다. - Ref

Gap Lock을 사용하기 위해서
START TRANSACTION* 과 SELECT FOR UPDATE* 구문을 함께 사용해야 합니다.

아래 쿼리를 실행하면 다른 MySQL Connection에서
m_id가 10~20 사이에 포함되는 새로운 값을 INSERT 할 수 없게 됩니다.

START TRANSACTION;
SELECT * FROM member WHERE m_id BETWEEN 10 and 20 FOR UPDATE;

둘 중 하나라도 사용하지 않으면 Gap Lock은 발생하지 않습니다. - [Ref]

5.3.1.3. Next Key Lock (169p)

Record Lock과 Gap Lock이 섞인 형태이다.
공식 문서를 읽었는데 전체적으로 무슨 내용인지 모르겠습니다. - Ref

5.3.1.4. Auto Increment Lock (170p)

AUTO_INCREMENT를 가지는 칼럼은 TRANSACTION 유무와 관계없이 이 락이 걸립니다.
innodb_autoinc_lock_mode를 통해서 세부 모드를 조절할 수 있습니다.

5.3.2. 레코드 수준의 잠금 확인 및 해제 (172~176p)

아래와 같이 레코드 수준의 잠금을 걸거나 풀 수 있습니다.

SHOW PROCESSLIST;
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
| Id | User            | Host      | db         | Command | Time | State                  | Info             |
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL       | Daemon  | 3465 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | monthly_cs | Query   |    0 | init                   | SHOW PROCESSLIST |
|  9 | root            | localhost | monthly_cs | Sleep   | 1206 |                        | NULL             |
+----+-----------------+-----------+------------+---------+------+------------------------+------------------+
3 rows in set, 1 warning (0.00 sec)

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON         b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON         r.trx_id = w.requesting_engine_transaction_id;

Empty set (0.01 sec)

5.4. MySQL의 격리 수준 (176~183p) - [Ref]

4장 아키텍처 - 트렌잭션 격리 레벨(Transaction Isolation Level)에서 배운 내용입니다.
각 격리 수준에 대해서 조금 더 깊이 있게 배우고자 합니다.

6. 페이지 압축 (185~194p)

MySQL 서버에서는 2가지 방식의 데이터 압축을 제공합니다.

1. 페이지 압축

2. 테이블 압축

6.1. 페이지 압축 (185~187p)

페이지 압축은 Transparent Page Compression이라고도 불린다.

페이지 압축은 단순하게 읽기/쓰기가 반영되는 사이에 적용됩니다. - Ref

• Wirte : Page -> Transform -> Write transformed page to disk -> Punch Hole

• Read : Page from disk -> Transform -> Original Page

다만 이 방식은 운영체제 파일 시스템의 클러스터 크기와 Punch Hole 지원 여부에 따라서 다르다고 합니다.
실제로 상용 제품군에서 페이지 압축으로 인한 이점을 논의해야 할 것 같습니다.

• 6.1.1. 페이지 압축 확인하는 점

• 6.1.2. 페이지 압축 효율성에 대한 생각

• 6.1.3. 페이지 압축 활성화/비활성화 하는 법

6.1.1. 페이지 압축 확인하는 법

다음과 같이 information_schema.tables에서 페이지 압축 활성화를 볼 수 있습니다.

SELECT table_name, row_format, create_options
FROM    information_schema.tables
WHERE   table_schema = 'monthly_cs'
AND     table_name   = 'member';
+------------+------------+----------------+
| TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS |
+------------+------------+----------------+
| member     | Dynamic    |                |
+------------+------------+----------------+
1 row in set (0.00 sec)

테이블 압축이 활성화 되어 있을 때, 실제 데이터 압축률은 다음으로 볼 수 있습니다.

SELECT space, name, fs_block_size, file_size, allocated_size
FROM  information_schema.innodb_tablespaces
WHERE name="monthly_cs/member"\G
*************************** 1. row ***************************
         space: 3
          name: monthly_cs/member
 fs_block_size: 4096
     file_size: 131072
allocated_size: 131072
1 row in set (0.00 sec)

6.1.2. 페이지 압축의 효율성에 대한 생각

MacOS 환경에서 Hole Punch Size을 다음 CLI로 측정할 수 있습니다.

diskutil info / | grep "Block Size"

-------------------------------------------------

   Device Block Size:         4096 Bytes
   Allocation Block Size:     4096 Bytes

이후 MySQL Client에서 다음과 같이 페이지 사이즈를 측정할 수 있습니다.

SELECT @@innodb_page_size;
+--------------------+
| @@innodb_page_size |
+--------------------+
|              16384 |
+--------------------+
1 row in set (0.00 sec)

기본적으로 페이지 압축은 압축 이후의 크기가 두 크기의 차이보다 작을 수 있는 경우에만 작동합니다.

MySQL 16 KB - MacOS Hole Punch 4 KB = 12 KB, after compressed.

이 수치가 Linux와 동일하네요.. UNIX 파일 시스템을 거의 그대로 사용하고 있어서 그럴까요?

6.1.3. 페이지 압축 활성화/비활성화 하는 법

활성화

-- ALTER
ALTER TABLE monthly_cs.member COMPRESSION="zlib";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- OPTIMIZE
OPTIMIZE TABLE monthly_cs.member;
+-------------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                                                                                                  |
+-------------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| monthly_cs.member | optimize | note     | Table does not support optimize, doing recreate + analyze instead                                                                         |
| monthly_cs.member | optimize | error    | Compression failed with the following error : Punch hole not supported by the filesystem or the tablespace page size is not large enough. |
| monthly_cs.member | optimize | status   | Operation failed                                                                                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

-- SELECT
SELECT table_name, row_format, create_options
FROM    information_schema.tables
WHERE   table_schema = 'monthly_cs'
AND     table_name   = 'member';
+------------+------------+--------------------+
| TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS     |
+------------+------------+--------------------+
| member     | Dynamic    | COMPRESSION="zlib" |
+------------+------------+--------------------+
1 row in set (0.00 sec)

비활성화

-- ALTER
ALTER TABLE monthly_cs.member COMPRESSION="None";
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- OPTIMIZE
OPTIMIZE TABLE monthly_cs.member;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| monthly_cs.member | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| monthly_cs.member | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

-- SELECT
SELECT table_name, row_format, create_options
FROM    information_schema.tables
WHERE   table_schema = 'monthly_cs'
AND     table_name   = 'member';

6.2. 테이블 압축 (187~194p)

SELECT @@innodb_cmp_per_index_enabled;              -- 0
SELECT @@innodb_compression_level;                  -- 6
SELECT @@innodb_compression_failure_threshold_pct;  -- 5
SELECT @@innodb_compression_pad_pct_max;            -- 50
SELECT @@innodb_log_compressed_pages;               -- 1

7. 데이터 암호화 (196~212p)

PCI-DSS 등의 각종 보안 규제를 충족하기 위해서
MySQL에서 지원하는 데이터 암호화 기능에 대해서 다룹니다.

7.1. MySQL 서버의 데이터 암호화 (196~197p)

MySQL 서버에서 암호화 활성화의 유무는 Application level에서 큰 차이가 없다.
또한 Network, Memory, Disk 등의 통신 계층에서의 암호화를 일컫는 말도 아니다.
그저 디스크에 저장된 내용이 암호화 되었는 지를 의미하는 TDE(Transparent Data Encryption)이라고 한다.

MacOS에서는 keyring_plugin이 없는 것 같다...

SHOW PLUGINS;

+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
...
| mysqlx                          | ACTIVE   | DAEMON             | NULL    | GPL     |
+---------------------------------+----------+--------------------+---------+---------+
45 rows in set (0.00 sec)

7.1.1. 2단계 키 관리 (197~198p)

아래의 플러그인들이 있다고 하는데 별도 설치해야 하는 것일까?

keyring_file
keyring_encrypted_file
keyring_okv
keyring_aws

7.1.2. 암호화와 성능 (198~200p)

• 복호화 작업은 버퍼풀에 적재되지 않은 페이지의 경우에는 별도의 시간이 처리되어 작업 시간이 늘어난다. (SELECT, INSERT, UPDATE, DELETE 모두)

• 암호화 작업은 백그라운드 작업에 의해 처리되기 떄문에 INSERT, UPDATE 작업에는 적용되지 않는다.

• 암호화 알고리즘에 다를 수 있으나 AES 알고리즘은 평문의 길이가 짧은 경우 암호화 키에 따라서 암호화 후 용량이 증가 가능

암호화는 35, 쓰기는 56배 정도 느린 속도를 보인다.

7.1.3. 암호화와 복제 (200~201p)

• RR을 사용하면 RR 별로 별도의 keyring을 사용하므로 백업이 없으면 데이터 복구가 어려울 수 있다.

7.2. keyring_file 플러그인의 설치

• HashiCorp Vault와 MySQL keyring_file과 연동?

7.3. 테이블 암호화 (203~204p)

모든 테이블에 암호화 적용

SELECT @@default_table_encryption;

+----------------------------+
| @@default_table_encryption |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

테이블 생성 시점에 암호화 적용

CREATE TABLE table_name () ENCRYPTED='Y';

7.4. 언두 로그 및 리두 로그 암호화 (206~208p)

SELECT @@innodb_undo_log_encrypt; -- 0
SELECT @@innodb_redo_log_encrypt; -- 0