지금 쓰고 있는 스프라켓은 14-34t 7단 스프라켓인데 이걸 11-34t 나 11-28t로 바꿀까 고민중.

11-28t로 바뀌면 34t가 없어져서 오르막길에서 아쉬울껀데.

11-34t로 바꿀까?

고민되네.. 

14-34t : 14-16-18-20-22-24-34
11-34t : 11-13-15-18-21-24-34

MySQL에서 인덱스의 최대 크기는 얼마일까?

- innodb_large_prefix이 활성화 된 경우 3072 바이트, 비활성화 된 경우 767 바이트

- 여기서 중요한것은 바이트, 글자수가 아니라 바이트!

  - MySQL은 varchar(글자수)로 선언된다.  Oracle은 varchar(바이트) 그래서 중요함

- 해당 컬럼의 characterset 확인

  - utf8의 경우 1글자가 3바이트

    3072 / 3 = 1024

    767 / 3 = 255.6666 

  - utf8mb4 는 1글자가 4바이트 (알아서 나누기 4하셈)

    utf8mb4 에서 'a'라는 값이 저장된다고 4바이트를 쓰는게 아니다. 

    선언은 4바이트까지 받을수 있다지만 디스크에 저장될때는 1바이트로 저장된다.  (저장공간도 1바이트)

참고 : https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

MySQL 8.0 이 출시된지 3년이 지났다. 
현재 시점에서는 8.0을 사용하는것을 권장한다. 

8.0 에서 성능 향상
- Read / Write 에 대한 부분도 있지만, IO Capactiy 활용률 개선과 
  경합이 많은 작업들 컨텐션이 많은 작업들을 이전에는 FIFO 방식으로 처리 했지만 
  CATS(Contention Aware Transaction Scheduling) 라는 알고리즘을 도입해서 빈번하게 자주 들어오는것에 대해서 
  웨이트를 높게줘서 빨리 처리되게 하는 부분을 도입했다. 
- 리소스 그룹, Read, Write 별 Thread 수 할당 가능
- UTF8MB4 가 기본 케릭터셋이 되면서 관련된 성능 개선
- 5.7 에서는 JSON/BLOB 에서는 업데이트시 전체를 업데이트되어 IO가 많이 발생하였으나 

  이제 업데이트된 부분만 변경한다. 
- 데이터 딕셔너리가 도입되었다. 
  모든 시스템에 있던 파일들이 다 innodb table로 들어갔다. 
  information schema, performance schema 에 있던것들도 인덱스나 쿼리리를 사용해서 조회 가능

하드웨어/OS 튜닝 

Memory 
innodb buffer pool size 을 크게 가져갈수록 디스크 IO를 줄일수 있다. 
mysql이 가용가능한 메모리의 70~80%를 할당하고 가이드한다. 
- 10G기가 이하의 서버에서는 60~70% 권장 
- 처음에는 70~80% 할당후 사이트에 맞게 튜닝 권장 
메모리 튜닝을 할때 OS, FS Cache, Temporary 테이블을 사용하는 부분이 감안이 되어야 한다. 

CPU 
하이퍼 스레딩 활성화 
더 빠른 멀티 코어 프로세서 사용 권장 
CPU , IO, Memory 에 우선을 두자면 각 사이트의 주요 잡이 어떤것인지 봐야한다. 
보통 CPU 보다는 Memory나 IO에 더 신경을 쓰는것이 성능 향상에 도움이 된다. 
MySQL 버전에 따른 코어수 제한이 있다. 
- MySQL 5.1 : ~ 4코어 
- MySQL 5.5 : ~ 16코어 
- MySQL 5.6 : ~ 36 쓰레드(Core)
- MySQL 5.7 : ~ 64 쓰레드(32 Core-HT)
- MySQL 8.0 : ~ 100 쓰레드(48 Core-HT)

Disk 
SSD, NVMe 사용시 innodb_page_size=4K, innodb_flush_neighbors=0 로 사용을 추천함
- 신규로 세팅되는 서비스의 경우 추천
  사용중인 DB를 SSD로 변경후 적용시 성능향상 없음 
- innodb datadir, tmp 파일 및 undo 로그 모두 Ramdom IO가 발생함으로 SSD로 할당하고 
  로그(빈로그 등등)의 경우 Sequnential IO가 발생함으로 디스크로 할당하는것을 추천 

OS 
MySQL은 Linux 에 최적화 되어 있다. 
- 성능을 위해서는 Linux 를 추천한다. 
ulimit 로 파일/프로세스수 제한 
- MySQL 이외에 OS에서 설정을 해줘야되는 부분
- ulimit -n, 파일 수 제한 (connections, open tables, ...)
- ulimit -u, 스레드 수 제한 (connections, InnoDB background threads, event scheduler, ....)
NUMA 기반 서버의 경우 innodb_numa_interleave를 1로 설정 
InnoDB를 사용하는 경우 파일시스템 캐시를 사용안해도 된다. 
- buffer pool 이 데이터 캐시 형태로 사용함
- set inndbo_flush_method=O_DIRECT 
- 파일 시스템 캐시는 MySQL의 다른 부분(로그)에서 사용함으로 비활성화하지 않는다. 

MySQL 설정값 확인 방법

8.0부터  my.cnf 외에 mysqld-auto.cnf 가 추가 되었다. 

- 커맨드 라인에서 SET PERSIST 변수=값 지정시 mysqld 재기동시 설정된 값이 초기화 되었다. 

  SET PERSIST로 설정값이 mysqld-auto.cnf 파일에 저장되어 mysqld 재기동 이후에도 유지된다. 

- show [session|global] variables 로 조회 
- show [session|global] variables like '%보고 싶은것%';
- performance_schmea 에서 조회 가능 
  - performance_schmea.global_variables
  - performance_schmea.session_variables
  - performance_schmea.variables_by_thread
    - performance_schmea.threads 의 thread_id 에 connection 을 매핑 
- 설정값을 누가 언제 변경했는지 확인 가능 
  - performance_schmea 에서 확인 가능 

InnoDB buffer pool 과 redo log 
- innodb_buffer_pool_size 
  - 메모리에 DB페이지를 저장하기 위한 메모리 사이즈 
  - MySQL에서 사용가능한 메모리의 70~80% 할당 권장 
    - 각 서비스의 특성이 맞게 튜닝 필요 
  - MySQL 5.7 부터 innodb_buffer_pool_size 를 동적으로 변경 가능 
- innodb_log_file_size (redo log 사이즈)
  - 이부분을 크게 가져가면 쓰기 속도가 빨라진다. 
    - 서버가 재기동하면서 복구할때 사용하는 영역으로 사이즈를 너무 크게 잡을 경우 DB 복구시간이 느려진다. 
  - 운영서버 최소 512MB 권장 
  - innodb_log_files_in_group 에 의해 결정된 총 redo log 용량 (기본값은 2) 
    - 대부분 3을 사용하는편 

Trading performance over consistency (AICD 에 D에 해당하는 부분)
- Commit 된 트랜잭션에 대해 InnoDB flush/sync 는 언제해야 할까?
- innodb_flush_log_at_trx_commit 
  - 0 : commit 되면 redo log를 메모리에 기록하고 매 1초마다 메모리에서 디스크로 flush 
  - 1 (기본값) : 완전한 ACID 지원, 매 commit 마다 redo log 에 기록하고 디스크에 flush 
  - 2 : commit 되면 O/S Buffer로 Redo Log가 기록되고 매 1초마다 메모리(O/S Buffer)에서 디스크로 flush 함 
- 다음 경우를 제외하고 1을 권장(일관성을 위해)
 - 대량 데이터를 로딩할 때 로드 하는 동안 세션 변수를 2로 설정하고 데이터를 로드하는 경우 
   mysql 8.0.21 버전을 사용하는 경우 redo-logging 을 비활성화 할 수도 있다. 
 - 예상치 못한 엄청난 부하(디스크)를 경험하고 있는데 문제를 해결할 동안 일단 서버가 어떻게든 동작해야 하는 경우 
 
Buffers that are per client connections 
- connection(세션)당 할당되는 버퍼들 
  - read_buffer_size : Sequnential scan (full table scan)을 사용할 때 사용하는 버퍼 
  - read_rnd_buffer_size : 정렬 작업후, 정렬된 순서대로 데이터를 다시 읽어 들일 때 사용하는 버퍼 
  - join_buffer_size : 인덱스를 사용하지 않는 조인에 사용되는 버퍼 
                       Session level에서 join 단위로 생성
  - sort_buffer_size : 인덱스를 사용하지 않는 정렬에 사용하는 버퍼 
  - binlog_cache_size (if binary logginigs is enabled)
- connection이 많을 경우 이 버퍼들이 메모리를 점유하기 때문에 크게 세팅하면 안된다.
  만약 크게 필요한 경우가 있다면 필요한 세션에서만 크게 잡아서 사용한다. 
  
Analyzing queries
- EXPLAIN
  - optimizer 의 실행 계획을 보여줌
    실데이터가 아닌 카디널러티를 가지고 만든 실행계획 
- EXPLAIN ANALYZE 
  - 8.0.19에 추가됨 실제 데이터를 가지고 만든 실행계획
- SET profiing=1, 프로파일링 활성화 (SHOW PROFILES, SHOW PROFILE FOR QUERY X)
  - MySQL 8.0 기준 
- Optimizer trace (가능한 모든 optimizer 실행 계획 확인)

ibdata 파일크기가 급격하게 증가했다.

범인은.. 롱 ~ 쿼리 

실행한 사람은 쿼리 실행을 종료했다는데 쭉~ 실행되고 있었다.

UNDO에 데이터의 스냅 샷을 생성해서 해당 트랜잭션이 시작된 이후 데이터베이스의 일관된보기를 제공 할 수 있도록 이전 페이지를 실행 취소 상태로 유지한다.

데이터베이스에 쓰기로드가 많으면 많은 실행 취소 페이지가 저장되면서 UNDO의 크기는 커진다.

문제는 ibdata 에 UNDO가 있고 한번 커진 ibdata 파일을 축소하기 위해서는 DB를 다시 구성해야된다. 

1. 전체 데이터베이스 백업

2. mysql, information_schema 를 제외한 전체 데이터베이스 삭제 

3. ibdata 파일 및 ib_log 파일 삭제 

4. 전체 데이터베이스 복원 

이런 노가다를 하지 않기 위해 롱 쿼리가 돌고 있는지 모니터링 하자!

MySQL은 클라이언트가 MySQL 서버에 접속할 때마다  해당 클라언트에 Thread를 생성하고 해당 커넥션이 종료되면 Thread를 제거한다. 

생성되어 있는 Thread수가 커넥션수라고 보면된다. 

Threads_cached 는 thread_cache_size 로 설정되며 설정된 값만큼 Thread 재사용을 위해서 Thread 를 가지고 있는다.

다음에 쓸께 하고 짱박는 느낌?

여기서 맞을수 있는 장애는.. 갑자기 급격하게 커넥션이 늘어나면서 Threads Cache에서 가지고 있는 Thread를 다 사용하면 Thread 를 만들게 된다. (Thread_create 발생) 이때 병목현상이 발생하여 커넥션이 늦어지거나 쿼리 응답속도가 늦어지는 현상이 발생할 수도 있다. 

접속이 끊긴 세션도 발견되었다. 이건 SR 진행해봐야겠다. 

mysql 메뉴얼에서는 8 + (max_connections / 100) 으로 잡으라고 되어 있다. 

dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_thread_cache_siz

이걸로 장애 맞았다.

사는게 쉽지 않다. ㅋㅋㅋ

조금 신기한 현상을 봐서 정리해본다. 

# 테스트용 테이블 zipcode_01 생성 
mysql> create table zipcode_01 as select * from zipcode where 1=0;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# buildnum + zipcode 를 PK로 잡아준다. (결합인덱스)
mysql> alter table zipcode_01 add primary key (buildnum, zipcode);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 테스트용 데이터 입력 
mysql> insert into zipcode_01 select * from zipcode order by buildnum, zipcode;
Query OK, 6348671 rows affected (4 min 21.79 sec)
Records: 6348671  Duplicates: 0  Warnings: 0

# zipcode_01, zipcode 테이블의 인덱스 정보 확인 
mysql> show index from zipcode_01;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zipcode_01 |          0 | PRIMARY  |            1 | buildnum    | A         |     6236954 |     NULL | NULL   |      | BTREE      |         |               |
| zipcode_01 |          0 | PRIMARY  |            2 | zipcode     | A         |     6236954 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)

mysql> show index from zipcode;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zipcode |          0 | PRIMARY      |            1 | seq         | A         |     6097975 |     NULL | NULL   |      | BTREE      |         |               |
| zipcode |          1 | idx1_zipcode |            1 | sido        | A         |        6667 |     NULL | NULL   | YES  | BTREE      |         |               |
| zipcode |          1 | idx1_zipcode |            2 | buildname   | A         |     1945822 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

테스트용 데이터 고르기

# 아래의 데이터로 테스트를 진행한다. 
# zipcode : 06018, buildnum : 1168010700106590009000001
# zipcode : 07783, buildnum : 1150010300108490001023500
mysql> select zipcode, buildnum from zipcode where sido = '서울특별시' and  buildname like '혜성빌딩%';
+---------+---------------------------+
| zipcode | buildnum                  |
+---------+---------------------------+
| 06018   | 1168010700106590009000001 |
| 07783   | 1150010300108490001023500 |
...
19 rows in set (5.82 sec)

Select 문은 buildnum + zipcode PK 잘탄다. 

mysql> explain select * from zipcode_01 
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in (select buildnum 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+
| id | select_type | table      | type  | key          | key_len | ref                      | rows | filtered | Extra                                               |
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | zipcode    | range | idx1_zipcode | 386     | NULL                     |   19 |   100.00 | Using index condition; Using where; Start temporary |
|  1 | SIMPLE      | zipcode_01 | ref   | PRIMARY      | 102     | zipcode.zipcode.buildnum |    1 |    20.00 | Using where; End temporary                          |
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+

자 그럼 Delete는? 인덱스 안탄다.. key 도 없고 type ALL 이다. 

mysql> explain delete from zipcode_01 
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in (select buildnum 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| id | select_type        | table      | type  | key          | key_len | ref  | rows    | filtered | Extra                              |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
|  1 | DELETE             | zipcode_01 | ALL   | NULL         | NULL    | NULL | 6236954 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | zipcode    | range | idx1_zipcode | 386     | NULL |      19 |    10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+

물론 아래처럼 where 절에 in 절을 join 으로 바꾸거나 buildnum 을 직접 넣어주면 인덱스 잘탄다. 

mysql> explain delete from zipcode_01 
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in ('1168010700106590009000001','1150010300108490001023500');
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table      | type  | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+
|  1 | DELETE      | zipcode_01 | range | PRIMARY | 124     | const,const |    4 |   100.00 | Using where |
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+

mysql> explain delete a 
    -> from zipcode_01 a 
    -> inner join zipcode b on a.zipcode = b.zipcode and a.buildnum = b.buildnum 
    -> where b.sido = '서울특별시' and b.buildname like '혜성빌딩%'
    ->  and a.zipcode in ('06018', '07783');
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+
| id | select_type | table | type   | key          | key_len | ref                                  | rows | filtered | Extra       |
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | b     | range  | idx1_zipcode | 386     | NULL                                 |   19 |   100.00 | Using where |
|  1 | DELETE      | a     | eq_ref | PRIMARY      | 124     | zipcode.b.buildnum,zipcode.b.zipcode |    1 |   100.00 | NULL        |
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+

혹시나 하는 생각에 buildnum + zipcode의 반대인 zipcode + buildnum 결합인덱스를 생성하고 다시 쿼리!

zipcode + buildnum 결합인덱스 잘탄다.

Delete 시에는 결합인덱스의 첫번째 컬럼을 조회하는 IN 절에 서브 쿼리가 들어가 있는 경우는 인덱스를 안타는구나..

mysql> explain delete from zipcode_01 
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in (select buildnum 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type        | table      | type  | key            | key_len | ref   | rows | filtered | Extra                              |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
|  1 | DELETE             | zipcode_01 | range | idx_zipcode_01 | 22      | const |  351 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | zipcode    | range | idx1_zipcode   | 386     | NULL  |   19 |    10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+

여기서 또 하나 궁금해지는것 그렇다면 zipcode + buildnum 결합인덱스를 삭제하고 buildnum + zipcode 결합인덱스만있는 상태에서 쿼리를 고쳐서 하면? 인덱스 잘탄다. 

# idx_zipcode_01 (zipcode + buildnum) 인덱스 삭제 
mysql> alter table zipcode_01 drop index idx_zipcode_01 ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain delete from zipcode_01 
    -> where buildnum in ('1168010700106590009000001','1150010300108490001023500')
    ->   and zipcode in (select zipcode 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+
| id | select_type        | table      | type  | key          | key_len | ref   | rows | filtered | Extra                              |
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+
|  1 | DELETE             | zipcode_01 | range | PRIMARY      | 102     | const |    2 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | zipcode    | range | idx1_zipcode | 386     | NULL  |   19 |    10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+

그렇다면 Update는 어떨까? 

동일하다!

mysql> explain update zipcode_01 
    -> set sido = '강남시'
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in (select buildnum 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| id | select_type        | table      | type  | key          | key_len | ref  | rows    | filtered | Extra                              |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
|  1 | UPDATE             | zipcode_01 | index | PRIMARY      | 124     | NULL | 6236954 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | zipcode    | range | idx1_zipcode | 386     | NULL |      19 |    10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+

mysql> alter table zipcode_01 add index idx_zipcode_01 (zipcode, buildnum);
Query OK, 0 rows affected (41.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain update zipcode_01 
    -> set sido = '강남시'
    -> where zipcode in ('06018', '07783')
    ->   and buildnum in (select buildnum 
    ->                    from zipcode 
    ->                    where sido = '서울특별시' 
    ->                      and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type        | table      | type  | key            | key_len | ref   | rows | filtered | Extra                              |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
|  1 | UPDATE             | zipcode_01 | range | idx_zipcode_01 | 22      | const |  351 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | zipcode    | range | idx1_zipcode   | 386     | NULL  |   19 |    10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+

alter table algorithm=inplace 로 컬럼사이즈 변경할 때 주의사항 

메뉴얼에는 256 바이트 미만에서는 된다고 나와 있다.

dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

문제는 256바이트 미만이다. 

utf8mb4 의 경우 4바이트.. 

256자가 아니라 256 바이트다.

utf8mb4면 4 * 64 = 256, varchar(63)까지 된다.

테스트 

테스트용으로 우편번호 테이블을 만들어 보자 

CREATE DATABASE zipcode;
use zipcode;

CREATE TABLE zipcode ( 
 zipcode VARCHAR(5) NULL,
 sido VARCHAR(25) NULL,
 sido_en VARCHAR(20) NULL,
 sigungu VARCHAR(30) NULL,
 sigungu_en VARCHAR(30) NULL,
 eupmyun VARCHAR(20) NULL,
 eupmyun_en VARCHAR(25) NULL,
 doro_code VARCHAR(12) NULL,
 doro VARCHAR(40) NULL,
 doro_en VARCHAR(50) NULL,
 under_yn VARCHAR(1) NULL,
 buildno1 VARCHAR(5) NULL,
 buildno2 VARCHAR(4) NULL,
 buildnum VARCHAR(25) NULL,
 multiple VARCHAR(1) NULL,
 buildname VARCHAR(70) NULL,
 dong_code VARCHAR(10) NULL,
 dong VARCHAR(20) NULL,
 ri VARCHAR(20) NULL,
 dong_hj VARCHAR(30) NULL,
 mount_yn VARCHAR(1) NULL,
 jibun1 VARCHAR(4) NULL,
 eupmyundong_no VARCHAR(2) NULL,
 jibun2 VARCHAR(4) NULL,
 zipcode_old VARCHAR(7) NULL,
 zipcode_seq VARCHAR(3) NULL,
 seq BIGINT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(seq)
) NGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/root/zipcode_DB/강원도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경기도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경상남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경상북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/광주광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/대구광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/대전광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/부산광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/서울특별시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/세종특별자치시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/울산광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/인천광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/전라남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/전라북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/제주특별자치도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/충청남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/충청북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;

sigungu 를 algorithm=inplace 로 varchar(30) -> varchar(63) 로 바꾸어보자!

mysql> alter table zipcode algorithm=inplace, change sigungu sigungu varchar(63);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

다시 sigungu 를 algorithm=inplace 로 varchar(63) -> varchar(64) 로 바꾸어보자!

mysql> alter table zipcode algorithm=inplace, change sigungu sigungu varchar(64);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Online DDL 작업을 할때 ALGORITHM=INPLACE 를 줘서 에러가 발생하지 않은 경우는 서비스중에 작업을 한다.

ALGORITHM=COPY 를 써야되면 점검걸고 하면되는데 점검을 걸지 못하는 경우라면?

방법이 있겠지. 찾아보자 구글검색! 구글신은 위대하니까.

pt-online-schema-change 라는게 있다. (gh-ost 라는것도 있는데 이건 다음에)

www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

어떻게 동작할까?  

- 변경할 스키마 구조의 임시 테이블 생성
- insert/update/delete 트리거를 생성하여 변경 데이터 동기화

- 지정한 청크 사이즈로 데이터를 끊어 데이터 전체를 임시 테이블로 복사

- 임시 테이블로 복사가 완료되면 테이블 이름을 변경하여 작업완료 

주의사항은?

- 적용 테이블에 PRIMARY KEY 또는 UNIQUE INDEX 가 있어야 한다.

- 서비스에 영향이 없도록 청크 사이즈를 잘 조절해서 작업 한다.

- 임시 테이블로 데이터를 복사하는 경우도 Binary Log에 기록된다. 

  Binary Log Format이 Row 일경우 로그 사이즈가 급격하게 늘어날 수 있다.

  작업시 Binary Log 사이즈에 대한 모니터링이 필요하다.

설치

- pt-online-schema-change 가 perl 기반이라 perl 관련 패키지 설치가 필요하다.

# perl 관련 패키지 설치 
sudo yum -y install perl-DBI perl-DBD-MySQL perl-TermReadKey
sudo yum -y install perl perl-IO-Socket-SSL perl-Time-HiRes
sudo yum -y install perl-Digest-MD5 perl-devel

# percona-toolkit 설치
wget percona.com/get/percona-toolkit.tar.gz
tar xzvf percona-toolkit.tar.gz
# percona-toolkit-3.3.0 디렉토리는 버전에 따라 다를 수 있다.
cd percona-toolkit-3.3.0/
perl ./Makefile.PL
make
sudo make install

percona-toolkit-3.3.0/bin 디렉토리의 pt-online-schema-change 을 사용하면 된다.

사용법

pt-online-schema-change --alter "변경할 Alter 정보" D=데이터베이스,t=테이블 \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--recursion-method=dsn=t=mysql.dsns \
--max-lag=5 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--password=password \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=200" \
--chunk-index=PRIMARY \
--charset=utf8 \
--set-vars="tx_isolation='repeatable-read',innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
--no-check-alter \
--execute

- no-drop-old-table : 원본 테이블 rename 후 drop 안함
- no-drop-new-table : 작업 실패했을 때 생성한 new 테이블 drop 안함
- chunk-size : 복사할 데이터 크기

- recursion-method : slave 를 찾는 방법, DB Port가 기본 Port (3306)이 아닐 경우 Slave를 찾지 못한다. 
                            dsn 로 지정시 접속할 Slave 정보를 조회할 테이블을 지정한다. 

                            mysql.dsns 테이블을 지정했고 해당 테이블에 Slave 정보를 Insert 해야한다.
- max-lag : 지정값 이상의 복제지연(Slave에서)이 발생하면 작업을 중단하고 기다린다.
- progress : 각 줄을 복사하는 동안에 STDERR를 보고서에 작성해서 출력한다. 출력 간격 설정
- max-load : 작업 도중 서버 부하를 막기위해 지정한 한계점보다 부하가 크다면 작업을 일시 중지한다.
- critical-load : 지정한 한계점보다 부하가 크다면 작업을 중지한다.
- set-vars : 작업 시작시 설정할 session variables

- no-check-alter : --alter에서 명시된 구문을 분석하지 않는다. 

테스트

테스트를 위해 아래와 같이 복제를 구성한다.

- Master : 192.168.137.11 MySQL 5.7.34

- Slave : 192.168.137.12 MySQL 5.7.34

alter table alter table algorithm=inplace 컬럼사이즈 변경 에서 만든 zipcode 테이블로 테스트를 한다. 

# dsn 테이블에 slave 정보 입력
mysql> use mysql;
 
mysql> CREATE TABLE `dsn` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `parent_id` int(11) NOT NULL,
    ->   `dsn` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

# slave IP:port insert
mysql> INSERT INTO mysql.dsn(parent_id,dsn) VALUES(1,'h=192.168.137.12,P=3306'); 
Query OK, 1 row affected (0.01 sec)


# pt-online-schema-change 로 컬럼 사이즈 변경 실행 
[root@localhost percona-toolkit-3.3.0]# pt-online-schema-change --alter "change sigungu sigungu varchar(64)" D=zipcode,t=zipcode \
> --no-drop-old-table \
> --no-drop-new-table \
> --chunk-size=500 \
> --recursion-method=dsn=t=mysql.dsn \
> --max-lag=5 \
> --defaults-file=/etc/my.cnf \
> --host=127.0.0.1 \
> --port=3306 \
> --user=root \
> --password=패스워드 \
> --progress=time,30 \
> --max-load="Threads_running=100" \
> --critical-load="Threads_running=200" \
> --chunk-index=PRIMARY \
> --charset=utf8mb4 \
> --set-vars="tx_isolation='repeatable-read',innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
> --no-check-alter \
> --execute
Found 1 slaves:
localhost.localdomain -> 192.168.137.12:3306
Will check slave lag on:
localhost.localdomain -> 192.168.137.12:3306
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `zipcode`.`zipcode`...
Creating new table...
Created new table zipcode._zipcode_new OK.
Altering new table...
Altered `zipcode`.`_zipcode_new` OK.
2021-05-02T13:35:35 Creating triggers...
2021-05-02T13:35:35 Created triggers OK.
2021-05-02T13:35:35 Copying approximately 6074412 rows...
Copying `zipcode`.`zipcode`:   5% 09:00 remain
Copying `zipcode`.`zipcode`:  10% 08:27 remain
Copying `zipcode`.`zipcode`:  15% 07:55 remain
Copying `zipcode`.`zipcode`:  21% 07:26 remain
Copying `zipcode`.`zipcode`:  25% 07:09 remain
Copying `zipcode`.`zipcode`:  31% 06:36 remain
Copying `zipcode`.`zipcode`:  36% 06:11 remain
Copying `zipcode`.`zipcode`:  40% 05:46 remain
Copying `zipcode`.`zipcode`:  46% 05:15 remain
Copying `zipcode`.`zipcode`:  50% 04:49 remain
Copying `zipcode`.`zipcode`:  56% 04:17 remain
Copying `zipcode`.`zipcode`:  61% 03:47 remain
Copying `zipcode`.`zipcode`:  66% 03:19 remain
Copying `zipcode`.`zipcode`:  71% 02:50 remain
Copying `zipcode`.`zipcode`:  76% 02:18 remain
Copying `zipcode`.`zipcode`:  81% 01:49 remain
Copying `zipcode`.`zipcode`:  86% 01:21 remain
Copying `zipcode`.`zipcode`:  91% 00:48 remain
Copying `zipcode`.`zipcode`:  96% 00:20 remain
2021-05-02T13:45:54 Copied rows OK.
2021-05-02T13:45:54 Analyzing new table...
2021-05-02T13:45:54 Swapping tables...
2021-05-02T13:45:55 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2021-05-02T13:45:55 Dropping triggers...
2021-05-02T13:45:55 Dropped triggers OK.
Successfully altered `zipcode`.`zipcode`.


# --chunk-size=500 만큼 끊어서 데이터를 복사하는것이 확인된다. 
# WHERE ((`seq` >= '771401')) AND ((`seq` <= '771900'))
mysql> select * from information_schema.processlist where user='root'\G;
     ID: 20
   USER: root
   HOST: localhost:45894
     DB: zipcode
COMMAND: Query
   TIME: 0
  STATE: query end
   INFO: INSERT LOW_PRIORITY IGNORE INTO `zipcode`.`_zipcode_new` (`zipcode`, `sido`, `sido_en`, `sigungu`, `sigungu_en`, `eupmyun`, `eupmyun_en`, `doro_code`, `doro`, `doro_en`, `under_yn`, `buildno1`, `buildno2`, `buildnum`, `multiple`, `buildname`, `dong_code`, `dong`, `ri`, `dong_hj`, `mount_yn`, `jibun1`, `eupmyundong_no`, `jibun2`, `zipcode_old`, `zipcode_seq`, `seq`) SELECT `zipcode`, `sido`, `sido_en`, `sigungu`, `sigungu_en`, `eupmyun`, `eupmyun_en`, `doro_code`, `doro`, `doro_en`, `under_yn`, `buildno1`, `buildno2`, `buildnum`, `multiple`, `buildname`, `dong_code`, `dong`, `ri`, `dong_hj`, `mount_yn`, `jibun1`, `eupmyundong_no`, `jibun2`, `zipcode_old`, `zipcode_seq`, `seq` FROM `zipcode`.`zipcode` FORCE INDEX(`PRIMARY`) WHERE ((`seq` >= '771401')) AND ((`seq` <= '771900')) LOCK IN SHARE MODE /*pt-online-schema-change 11751 copy nibble*/
3 rows in set (0.00 sec)