MySQL DB 업그레이드

포트원의 MySQL DB 는 5.5 로 상당히 오래된 버전을 사용하고 있었기에 8.0 으로 업그레이드를 진행하였습니다. 참고로 블로그 글은 지금 쓰지만, 사실 MySQL DB 업그레이드는 작년 초에 진행하였습니다.

새로운 DB 만들기

이전 DB 와 연결되어 있는 새로운 버전의 DB 를 만드는 것은 AWS RDS 나 Aurora 를 사용한다면 비교적 쉽게 할 수 있습니다. 포트원의 경우 EC2 에 직접 운영하던 MySQL DB 가 있었기 때문에, xtrabackup 을 통해 새로운 DB 를 만들고 해당 DB 를 5.5 로부터 major 버전을 하나씩 올려가며 8.0 까지 업그레이드하였습니다. changelog 를 보며 breaking change 들을 파악하고, 문제가 없도록 DB column type 조작, system variable 변경, charset 변경 등을 진행하였습니다. (해당 과정은 다른 팀원이 담당해주셨기에 자세한 내용은 적지 않습니다.) 업그레이드된 DB 를 사용해도 서버에 문제가 없는지, 성능에는 문제가 없는지 등을 서버 개발자분들이 확인하며 필요한 서버 작업들을 해주셨습니다.

번외: DB 사용하는 곳 트래킹하기

해당 MySQL DB 는 인프라 최신화 이전부터 사용되던 DB 이고, 포트원에는 레거시 시스템이 존재했기 때문에 해당 DB 를 어디서 사용하고 있는지 불분명한 부분들이 있었습니다. 그래서 해당 DB 를 사용하는 서버들을 확실하게 트래킹해볼 필요가 있었습니다. show processlist 에는 현재 존재하는 연결만 보입니다. 만약 어느 서버에서 connection pool 없이 DB 가 필요할 때마다 연결을 맺고 끊는 형태로 DB 를 사용하고 있었다면 show processlist 로는 관측이 안 될 수 있다고 판단하였습니다. 그래서 init_connect 와 procedure 를 통해, 연결이 생길 때마다 MySQL table 에 흔적을 남기도록 하였습니다. 실제로 이 방법으로 레거시 서버들을 찾아냈습니다.

-- init_connect 는 super user 는 실행이 안되기 때문에, root 빼고는 다 revoke 하자
-- super 권한을 없애주는 쿼리 뽑아내기. 뽑은걸 실행한다 
SELECT CONCAT("Revoke super on *.* from'", user, "'@'", host, "';") AS query
  FROM mysql.user
 WHERE Super_priv != 'N';

-- 연결이 생길때마다 트래킹해줄 테이블
CREATE TABLE login_tracking (
  user VARCHAR(16)
, host VARCHAR(60)
, ts TIMESTAMP
, PRIMARY KEY (user)
);

-- login_tracking 테이블에 자신의 연결정보를 넣어주는 procedure
DELIMITER //

CREATE PROCEDURE login_trigger()
SQL SECURITY DEFINER
BEGIN
  INSERT INTO login_tracking (user, host, ts)
  VALUES (SUBSTR(USER(), 1, instr(USER(), '@')-1), substr(USER(), instr(USER(), '@')+1), NOW())
  ON DUPLICATE KEY UPDATE host = substr(USER(), instr(USER(), '@')+1), ts = NOW();
END;

//
DELIMITER ;

-- procedure 실행 권한을 주는 쿼리 뽑아내는 방법. 뽑은걸 실행한다.
SELECT CONCAT("GRANT EXECUTE ON PROCEDURE {db_name}.login_trigger TO '", user, "'@'", host, "';") AS query
  FROM mysql.user
 WHERE Super_priv = 'N';

-- 각 user 로 로그인해서 문제없이 procedure 실행되는지 확인
Call {db_name}.login_trigger();

-- 잘 기록되었는지 확인해보기
select * from {db_name}.login_tracking order by ts desc limit 100;

-- init_connect 수정
show global variables like "init%"; 
SET GLOBAL init_connect="CALL {db_name}.login_trigger()"; 

ProxySQL

1편에서는 “DB 연결에 대해 잘 알고 있는 DB Proxy 가 필요하다” 정도로만 설명하였지만, DB Proxy 는 무중단 업그레이드의 핵심입니다. 연결을 draining 하는 기능, 그리고 클라이언트와 DB Proxy 사이의 연결은 유지하면서 DB Proxy 와 MySQL DB 사이의 연결은 교체하는 기능이 있어야 하고, 동시에 안정성을 신뢰할 수 있는 DB Proxy 여야 합니다. MySQL 에서는 ProxySQL 이 모든 조건을 충족하였기에 ProxySQL 을 사용하기로 결정하였습니다. 하지만 ProxySQL 을 사용하는 것만으로는 원했던 draining 이 되지 않았고, 여러 설정을 해줘야 했습니다. 그중 가장 중요한 설정 2가지를 설명하겠습니다.

1. Multiplexing

무중단 업그레이드 설계를 보면, 클라이언트와 ProxySQL 사이의 연결(앞단 연결)이 정상적인 상태에서 ProxySQL 과 MySQL DB 사이의 연결(뒷단 연결)이 바뀌어야 합니다. 그래야 서버(클라이언트) 입장에서는 아무 일도 없는 것처럼 느끼는 상태에서 뒷단의 DB 가 바뀔 수 있습니다. 하나의 앞단 연결에서 transaction 단위로 ProxySQL 뒷단의 연결을 자유롭게 바꿀 수 있는 기능을 ProxySQL 에서는 Multiplexing 이라고 합니다.

Multiplexing 이 되려면 앞단 연결에서 주고받는 query 중에 DB 연결 단위로 작동하는 개념을 사용하는 query 가 없어야 합니다. 예시로는 session variable, user variable 이 있습니다. 서버가 하나의 앞단 연결에서 다음과 같은 query 들을 보냈는데, Multiplexing 때문에 각 query 가 실제로는 뒷단 DB 로의 연결 A, B, C 에 각각 들어갔다면 ProxySQL 이 없었을 때와는 다른 결과가 나올 것입니다.

SELECT @order_id;  -- 연결 A. 아직 저장한 값이 없어서 NULL 이 반환됨

SET @order_id = 123; -- 연결 B 에만 @order_id 가 저장됨.

SELECT * FROM orders WHERE id = @order_id; -- ProxySQL 이 없었다면, 같은 연결에 저장된 123 을 사용해 id 가 123 인 주문이 나온다.
                                           -- 하지만 연결 C 로 간다면 @order_id 가 NULL 이어서 아무 row 도 나오지 않는다.

이러한 문제를 방지하기 위해서, ProxySQL 은 query 에 @ 가 들어가기만 하면, 즉 user variable 을 사용하거나 session variable 을 SELECT 만 하더라도 해당 앞단 연결에 대해서는 Multiplexing 을 꺼버립니다.¹ 그렇게 되면 해당 앞단 연결에는 하나의 뒷단 연결을 독점으로 배정해주게 됩니다. 이 외에도 Multiplexing 이 꺼지는 조건들이 있기에, 개발 환경에서 ProxySQL 의 앞단 연결들의 Multiplexing 이 꺼졌는지, 꺼졌다면 왜 꺼졌는지 알아내고 이를 해결해야 합니다. Multiplexing 정보를 보려면 ProxySQL 을 다음과 같이 설정하고 stats_mysql_processlist 를 보면 됩니다.

-- ProxySQL admin 에서 실행. 이 설정을 해야, processlist 에서 Multiplexing 관련 정보를 볼수 있다.
UPDATE global_variables SET variable_value = 1 WHERE variable_name = 'mysql-show_processlist_extended';
LOAD MYSQL VARIABLES TO RUNTIME;

# extended_info 에 JSON 형태로 적혀있는 연결 정보를 연결마다 뽑아본다.
mysql -u admin -padmin -h 127.0.0.1 -P6032 --batch --skip-column-names \
  -e "SELECT user, extended_info FROM stats_mysql_processlist" |
while read -r app info; do
  echo "$info" | jq -c --arg app "$app" '{app: $app, MultiplexDisabled: .backends[0].conn.MultiplexDisabled, status: .backends[0].conn.status, auto_increment_delay_token: .backends[0].conn.auto_increment_delay_token}'
done

포트원의 경우, Multiplexing 이 꺼지는 경우가 크게 2가지 있었습니다.

첫 번째는 MySQL driver 의 query 였습니다. MySQL driver 가 연결을 맺을 때 해당 연결의 session variable 들을 읽어오면서 Multiplexing 이 꺼졌습니다. (예시: mysql-connector-j) mysql-connector-j 에서는 session variable 들을 조회해서 그 값을 저장해놓고, 필요할 때 해당 값들을 메모리에서 읽어오고 있었습니다. 이 문제의 해결책은 “해당 값을 SELECT 만 했을 때는 Multiplexing 이 꺼지지 않도록 설정하는 것”입니다. 만약 누군가 값을 변경하면 Multiplexing 이 꺼지면서 해당 뒷단 연결은 변경한 앞단 연결에 독점 배정되고, 해당 앞단 연결이 사라지면 뒷단 연결도 사라집니다.² 따라서 Multiplexing 이 가능한 뒷단 연결의 session variable 들은 모두 DB 의 연결 초기값일 것입니다. 즉, 독점 배정되지 않은 뒷단 연결들의 session variable 값은 모두 일치하기 때문에, SELECT 에 한해서는 Multiplexing 이 꺼지지 않아도 됩니다.

이런 경우를 처리하기 위해 ProxySQL 에는 mysql_query_rules 라는 기능이 있습니다. mysql_query_rules 를 통해 “특정 regex 에 해당하는 query 에 대해서는 Multiplexing 을 끄지 않는다”는 설정을 할 수 있고, MySQL driver 같은 문제의 해결책으로 이를 사용하는 것을 ProxySQL 측에서 권장합니다.

두 번째는 auto_increment_delay 입니다. 서버 애플리케이션 중에는 INSERT query 후에 LAST_INSERT_ID() 를 조회해서 해당 값을 사용하는 경우가 있습니다. 이 경우 때문에 ProxySQL 은 INSERT query 를 실행한 후, 후속 query 5개를 실행하기 전까지 Multiplexing 을 잠시 꺼놓는 기능이 기본으로 설정되어 있습니다. (ProxySQL 문서) 포트원의 경우엔 코드 분석과 ProxySQL 에 남아있는 query 데이터(stats_mysql_query_digest)를 통해서 LAST_INSERT_ID() 를 사용하지 않는 것을 확인하였고, mysql-auto_increment_delay_multiplex 설정을 0 으로 하여 이 기능을 끔으로써 해결하였습니다.

만약 이 외에 Multiplexing 이 꺼지는 경우가 보인다면, 잘 분석해서 그 문제를 어떻게 회피할 수 있을지 고민해야 합니다. 하지만 특별한 경우가 아니라면 여기의 가이드로 해결되지 않을까 생각합니다.

2. MySQL 버전

MySQL 연결 protocol 을 보면, DB 와 클라이언트 간의 handshake 단계에서 DB 가 자신의 버전을 알려주도록 되어 있습니다. (MySQL Protocol - Initial Handshake Packet 의 server version) 그러다 보니 ProxySQL 은 클라이언트와 연결을 맺을 때부터 MySQL 의 버전을 알려줘야 하기 때문에, 자신의 뒷단에 있는 DB 들의 버전을 가져와서 넘겨주는 식의 동작을 하기가 힘듭니다. 따라서 ProxySQL 설정으로 클라이언트에게 알려줄 버전을 지정하도록 되어 있습니다. 포트원은 5.5 를 8.0 으로 옮기는 업그레이드였고, 모든 프로그램이 그렇듯 DB 역시 backward compatibility 를 지키려고 하기 때문에 ProxySQL 에 설정하는 버전은 5.5 로 두는 것이 안전합니다.

그렇다는 건 클라이언트는 5.5 라고 생각하며 query 를 보내는데, 이것이 뒷단의 8.0 DB 에 전달될 수 있다는 뜻이고, 그래서 8.0 에 생긴 breaking change 에 영향을 받을 수밖에 없습니다. 문제가 된 변경은 다음과 같습니다.

• query_cache_size, query_cache_type: 사라짐
• tx_isolation: transaction_isolation 으로 변경

해당 문제들도 mysql_query_rules 기능을 통해서 해결할 수 있습니다. match_pattern, replace_pattern 을 통해 query 의 특정 부분을 변경하는 것을 지원합니다. 이 기능으로 문제가 된 query 들을 바꾸는 규칙을 Switchover 할 때 같이 생성하는 방식으로 해결할 수도 있지만, 포트원의 경우 문제가 되는 query 들이 driver 가 연결할 때만 나오는 문제였기 때문에, 좀 더 쉽게 mysql_query_rules 에서 해당 값들을 하드코딩하는 식으로 해결하였습니다. (뒤의 mysql_query_rules 참고)

결론: ProxySQL 설정

결론적으로 mysql_query_rules 와 적당한 설정들로 문제들을 다 해결할 수 있었습니다. DB 를 어떻게 사용하느냐에 따라 모두 다르겠지만, 여기 설명한 경우가 일반적인 경우이지 않을까 생각합니다. 만약 다른 문제들이 있다면 각 문제를 나름의 방식대로 해결할 수 있을 것 같습니다. 회사마다 MySQL driver 에서 나오는 query 가 다를 수 있기 때문에 그대로 적용할 수는 없겠지만, 이해를 돕기 위해 최종적으로 사용한 mysql_query_rules 를 첨부합니다.

Switchover

Switchover 과정을 간략하게 설명하면 다음과 같습니다.

1. 5초 타이머를 시작합니다. 만약 아래 과정이 5초를 넘기면, Switchover 를 취소하고 다시 이전 DB 를 사용하도록 합니다.
   1. DB Proxy 에서 이전 DB 사용을 draining 합니다.
   2. 새로운 DB 가 이전 DB 의 변경사항을 모두 따라잡을 때까지 기다립니다.
2. DB Proxy 에서 새로운 DB 를 사용하도록 합니다.

이전 DB 사용 draining

draining 은 ProxySQL 의 OFFLINE_SOFT 라는 상태를 사용하면 됩니다. ProxySQL 은 여러 DB 를 backend 로 등록할 수 있고, 등록된 DB 의 status 를 조절해서 해당 DB 를 사용할지 말지를 결정할 수 있습니다. ONLINE 은 해당 DB 를 뒷단 연결로 사용할 수 있는 상태이고, 사용하지 않는 상태는 OFFLINE_HARD 와 OFFLINE_SOFT, 두 가지가 있습니다. OFFLINE_SOFT 는 이미 해당 DB 를 사용하고 있는 연결은 계속 사용하도록 허용하되 새로운 연결은 받지 않는 상태이고, OFFLINE_HARD 는 이미 존재하는 연결도 강제로 끊습니다. OFFLINE_SOFT 를 사용하면 무중단 업그레이드에서 원하는 draining 을 할 수 있습니다.

OFFLINE_SOFT 로 설정한 후 모든 연결이 draining 되었는지 확인하려면, 이전 DB 에 연결이 남아 있는지 확인하면 됩니다. 이는 ProxySQL 에서 확인할 수도 있고, 이전 DB 의 processlist 로 확인할 수도 있습니다. 실제 Switchover 스크립트에서는 둘 다 polling 하여 연결이 없는 것을 확인하였습니다.

변경사항을 모두 따라잡았는지 확인

이전 DB 에 연결이 모두 사라지면, 더 이상 새로운 변경사항이 쌓이지 않습니다. 이제 남은 것은 이전 DB 의 변경사항들이 새로운 DB 에 모두 반영되었는지 확인하는 것입니다. 가장 확실한 방법은 binlog replication 정보를 보는 것입니다. 이전 DB 의 binlog position 과 새로운 DB 의 replication status 의 Read_Master_Log_Pos, Exec_Master_Log_Pos, 총 3개의 값이 모두 같은지를 polling 하면서 확인하였습니다. 만약 GTID 가 설정되어 있다면 GTID 로 확인하는 것이 확실하지만, 5.5 에는 GTID 가 없기 때문에 선택할 수 없었습니다.

실행 타임아웃

Switchover 스크립트를 실행하는 중에는 새로운 DB query 들이 모두 delay 되기 때문에, 이 시간을 최소화하고 싶었습니다. 그렇다고 너무 짧게 잡으면 모든 과정을 끝내는 것이 불가능해지기 때문에 적당한 값으로 타협하는 것이 중요했습니다. 목표치는 3초, 최대 5초라고 생각했습니다. 5초를 넘어가면 일반적인 latency 를 훨씬 뛰어넘게 되면서 포트원을 사용하는 회사의 response timeout 에 걸릴까 봐 그 이상으로 높이고 싶진 않았습니다. Switchover 실행 전에, 3초가 실제로 가능한지 알아보기 위해 테스트를 하고 싶었습니다. 그래서 Switchover 스크립트에서 새로운 DB 를 사용하는 단계(아래 “결론: Switchover 과정” 의 3번 단계)를 없앤 상태로, 트래픽이 제일 적은 새벽 시간대에 여러 번 실행해보며 실험했습니다. 그 결과, 일반적으로 2초 이하로 걸리는 것을 볼 수 있어 안심하고 Switchover 를 진행할 수 있었습니다. 실제로 Switchover 를 했을 때에도 2초 이하로 걸려, 문제없이 무중단 DB 업그레이드를 완수할 수 있었습니다.

결론: Switchover 과정

실제 Switchover 스크립트의 과정은 다음과 같습니다.

1. 이전 DB 를 OFFLINE_SOFT 로 변경 ← draining 시작. 새로운 transaction 은 대기
2. 타임아웃 안에서 아래 조건들이 모두 만족될 때까지 polling
   • ProxySQL 에서 이전 DB 로의 뒷단 연결 수가 0 (stats_mysql_connection_pool 의 ConnUsed + ConnFree)
   • 이전 DB 의 processlist 에 서버 애플리케이션의 연결이 없음
   • 이전 DB 의 show master status 의 Position 과 새로운 DB 의 show slave status 의 Read_Master_Log_Pos, Exec_Master_Log_Pos 가 모두 같음
3. 성공하면, 새로운 DB 를 ONLINE 으로 변경 ← 대기하던 transaction 들이 새로운 DB 로 전달됨
4. 타임아웃이 지나거나 에러가 나면, 이전 DB 를 다시 ONLINE 으로 변경 ← 롤백

ProxySQL 의 상태 변경은 ProxySQL admin 에서 다음과 같은 query 로 합니다.

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = '<old db>';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

추가적인 내용

MySQL 8.0 테스트

서버 개발자와 QA 팀에서 MySQL 8.0 을 사용했을 때 문제가 없는지 확인해주시긴 했지만, 실제 운영 환경에서 들어오는 요청의 모든 유형을 테스트해본다는 건 사실 불가능에 가깝습니다. 또한 운영 환경의 요청 유형 조합과 빈도가 그대로 들어왔을 때의 DB 부하 등을 체크하기 위해서, 운영 환경에서 실행되고 있는 DB query 들로 테스트를 해보고 싶었습니다. 이를 위해서 ProxySQL 의 Mirroring 기능을 사용하였습니다.

ProxySQL Mirroring 을 사용하면 사용 중인 DB 와 클라이언트에 영향 없이, 들어오는 모든 query 를 그대로 다른 DB 에 미러링할 수 있습니다. DB 의 정합성, transaction 같은 것들을 모두 지켜가며 미러링하는 것은 불가능하지만, 미러링의 목적이 부하 테스트이자 query 에 문제가 있는지 확인하는 용도이기 때문에 큰 문제는 없습니다. 그래서 총 2가지의 Mirroring 실험을 진행했습니다.

1. replication 진행 중인 replica 로 SELECT query 만 미러링해보기
2. replication 을 끊고 INSERT, UPDATE, DELETE 문 전부를 미러링해보기

미러링을 한 후, ProxySQL 의 stats_mysql_query_digest 에서 response time 과 sum_rows_affected 를, stats_mysql_errors 에서 error 가 뜨는지 등을 확인하여 MySQL 8.0 에서 문제가 생기는 query 가 있는지 확인하였고, DB 의 CPU utilization 같은 지표들을 통해 문제가 없는지 파악했습니다.

ProxySQL Mirroring 은 아니지만, 개발자분이 만들어주신 benchmark 를 통해 알게 된 문제가 있었습니다. benchmark 를 돌려보니 5.5 DB 에서는 문제없이 돌았는데, 같은 부하로 8.0 DB 에 돌렸을 땐 버티지 못했습니다. 이유는 query cache 기능이었습니다. MySQL 의 query cache 는 8.0 에서 완전히 사라진 기능으로, SELECT 문의 결과값을 caching 해놓고 똑같은 SELECT 문이 들어왔을 때 caching 된 값을 바로 반환해주는 기능입니다. (cache invalidation 은 table 단위로 일어나며, SELECT 문이 읽는 table 중 하나라도 변경이 생기면 invalidate 됩니다.) index 를 타지 않는 query 가 있었는데, 해당 table 이 변경이 많지 않은 table 이다 보니 query cache 의 수혜를 많이 받아 5.5 에서는 문제가 없었지만 8.0 에서는 문제가 생긴 것이었습니다. 해당 table 에 적당한 index 를 걸어줌으로써 해결하였습니다.

롤백 플랜

만약 8.0 으로 업그레이드를 했는데 이전에 발견하지 못한 큰 문제가 있다면, 롤백을 해야 할 수도 있다고 생각했습니다. 그때를 대비하려면 8.0 DB 로부터 replication 을 받는 5.5 DB 를 만들어 놓아야만 정합성 문제 없이 DB 롤백이 가능해집니다. 하지만 MySQL 의 binlog replication 은 원래 replica 가 source 보다 major 버전이 한 단계 위인 경우, 즉 한 단계 아래 버전으로부터 받아오는 것만 정식 지원합니다. 다시 말해, 업그레이드하는 방향으로 한 버전씩의 replication 만 정식 지원하고, 역방향이나 2단계 이상 뛰어넘는 replication 은 정식 지원하지 않습니다. 그렇기에 정방향인 5.5 -> 8.0 replication 도 정식 지원을 따르려면, 중간 단계의 DB 를 하나씩 더 만들어서 5.5 -> 5.6 -> 5.7 -> 8.0 으로 한 단계씩 진행하는 것이 정확하긴 합니다. 5.5 -> 8.0 의 경우, replication 설정을 하고 정합성 체크를 여러 번 돌려서 문제가 없다는 것을 확인하여 진행할 수 있었지만, 8.0 -> 5.5 는 binlog replication 이 되지 않았습니다. 그래서 AWS DMS 를 사용해서 replication 을 진행시켰습니다. AWS DMS 는 약간의 delay 가 있기 때문에 replication lag 를 따라잡는 데 5초 이상 걸렸지만, 이 롤백을 사용할 정도의 큰 문제가 생긴 이상 5초 이상의 replication lag 는 감수하기로 결정했었습니다. 실제로 이 롤백 플랜이 사용되지는 않았습니다.

총평

MySQL 의 경우, 버전 간의 변경사항들이 많아 새 버전에 대한 테스트를 상당히 많이 진행해야 했습니다. 대신 DB Proxy 인 ProxySQL 이 지원하는 기능들도 많아 DB 업그레이드에 사용하기에 편했습니다. QA, 벤치마크 등 수많은 테스트와 모의 실험을 통해 문제가 될 만한 부분들을 지워 나갔고, 그 덕분에 실제 DB 업그레이드는 문제없이 진행되었습니다.