MySQL은 클라이언트가 MySQL 서버에 접속할 때마다 해당 클라언트에 Thread를 생성하고 해당 커넥션이 종료되면 Thread를 제거한다.
생성되어 있는 Thread수가 커넥션수라고 보면된다.
Threads_cached 는 thread_cache_size 로 설정되며 설정된 값만큼 Thread 재사용을 위해서 Thread 를 가지고 있는다.
다음에 쓸께 하고 짱박는 느낌?
여기서 맞을수 있는 장애는.. 갑자기 급격하게 커넥션이 늘어나면서 Threads Cache에서 가지고 있는 Thread를 다 사용하면 Thread 를 만들게 된다. (Thread_create 발생) 이때 병목현상이 발생하여 커넥션이 늦어지거나 쿼리 응답속도가 늦어지는 현상이 발생할 수도 있다.
접속이 끊긴 세션도 발견되었다. 이건 SR 진행해봐야겠다.
mysql 메뉴얼에서는 8 + (max_connections / 100) 으로 잡으라고 되어 있다.
# 테스트용 테이블 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 |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
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) 로 바꾸어보자!
- 변경할 스키마 구조의 임시 테이블 생성 - 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 을 사용하면 된다.
- 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 테이블로 테스트를 한다.
5.6 (utf8) -> 5.7 (utf8mb4) 로 복제를 걸때 다른 캐릭터셋으로 연결되면서 오류가 발생한다.
이를 해결하기 위해 global slave_type_conversions=ALL_NON_LOSSY 를 적용해주면 된다.
이부분은 캐릭터셋을 utf8mb4로 바꿀때만 적용된다. utf8을 그대로 사용할 경우 아무 문제 없다.
2. 5.6 (utf8) 3byte -> 5.7 (utf8mb4) 4byte 로 변경되면서 컬럼사이즈가 varcahr 로는 max 수치가 오버되는 경우가 있다.
3. 5.7 에서 sql_mode에 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES 가 기본으로 적용되었다. 5.6 에서는 SQL 표준문법이 아니더라도 실행은 되었지만 5.7 에서는 에러가 발생하는 쿼리 확인이 필요하다. - ONLY_FULL_GROUP_BY : group by 하지 않은 열을 select 할 수 없다. - STRICT_TRANS_TABLES : 형식이 맞지 않는 값을 insert 할때, 예전에는 warning 이 뜨고 그냥 입력되었지만 5.7부터는 에러가 발생한다.
자 이제 복제를 걸어보자.
원본 DB에서 xtrabackup 을 받아서 신규 DB에 복원하고 복제를 걸면된다. (상세한 설명은 생략한다.)
잘된다. 싶었는데.. 문제가 생겼다.
복제가.. 밀린다! 복제 지연 발생.
해결방법은.. LOGICAL_CLOCK 방식의 병렬복제 (Multi-Threaded Slave, MTS) 를 적용해 보자. (적용 방법 생략)
