인프라 공방 3주차 - ⏱️안정적인 인프라 만들기 후기 2부

NEXTSTEP - 인프라공방 후기로 3주차 분량이 많아 1, 2부로 나눠서 작성한다.

인프라공방은 직접 인프라를 경험해보는 실습 강의로

AWS 사용, 성능 테스트 및 개선, 모니터링, 스케일 아웃, 쿼리 최적화, MySQL Replication 등을 진행해볼 수 있다. 

미션 진행과정을 요약하기 때문에 자세한 내용이 궁금하면 수강을 추천한다.

인프라 공방

---

⏱️안정적인 인프라 만들기 - 학습 목표

🎯 HTTP 개선에 따른 차이를 이해하고 Reverse Proxy 성능 개선을 해봅니다.
🎯 HTTP Cache 전략을 이해하여 적절한 정책을 설정해봅니다.
🎯 쿼리를 최적화하여 조회 성능을 개선해봅니다.
🎯 인덱스를 설정하여 조회 성능을 개선해봅니다.

---

🚀3단계 - 쿼리 최적화

[3단계] 쿼리 최적화 by loop-study · Pull Request #213 · next-step/infra-subway-performance

요구사항

- 활동중인(Active) 부서의 현재 부서관리자(manager) 중 연봉 상위 5위안에 드는 사람들이 최근에 각 지역별로 언제 퇴실(O)했는지 조회해보세요.
(사원번호, 이름, 연봉, 직급명, 지역, 입출입 구분, 입출입 시간)

- 인덱스 설정을 추가하지 않고 1s 이하로 반환합니다.

    - M1의 경우엔 시간 제약사항을 달성하기 어렵습니다. 2배를 기준으로 해보시고 어렵다면, 일단 리뷰 요청 부탁드려요

- 급여 테이블의 사용 여부 필드는 사용하지 않습니다. 현재 근무 중인지 여부는 종료일자 필드로 판단해주세요.

똑같은 결과가 나오면 된다.

이 미션은 인덱스가 없는 데이터에서 요구사항대로 결과를 1초 이내에 조회하는 목표로 

쿼리 연습을 할 수 있게 실습사이트와 연습문제를 제공해주기도 한다.

열심히 시간 내에 조회할 수 있도록 쿼리를 작성하고 PR 요청하면 끝난다.

아래는 작성한 쿼리의 일부분과 해당 쿼리에서 피드백받은 내용이다. 

피드백 해주신 모습.

---

🚀4단계 - 인덱스 설계

[4단계] 인덱스 설계 by loop-study · Pull Request #220 · next-step/infra-subway-performance

생각해보기

 

요구사항

- 주어진 데이터셋을 활용하여 아래 조회 결과를 100ms 이하로 반환

    * M1의 경우엔 시간 제약사항을 달성하기 어렵습니다. 2배를 기준으로 해보시고 어렵다면, 일단 리뷰 요청 부탁드려요

   - Coding as a Hobby 와 같은 결과를 반환하세요.

   - 프로그래머 별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)

   - 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)

   - 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)

   - 서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요. (user.Exercise)

 

여러 쿼리를 작성하는 인덱스 설계 미션이다.

제공해주는 데이터는 stack overflow의 Developer Survey Resluts 2018 결과물과 Covid 데이터가 서로 혼합된 형태다.

주로 사용되는 테이블

programmer 테이블은 컬럼이 대략 130~140개로 요구사항에서 어떤 컬럼을 사용하는지 파악하는 게 우선이다.

쿼리를 작성하면서 수시로 실행계획을 확인해보고 모수가 적은 게 먼저 오게 하면서 인덱스 설계를 진행하는데

인덱스 추가 전/후가 너무 다른 걸 볼 수 있다.

인덱스가 없을 땐 조회에 30초가 넘어서 커넥션이 끊어지는데

인덱스를 추가하면 매우 다른 결과가 나온다.

실행계획을 수시로 확인하면서 쿼리를 작성하고 인덱스를 추가하면서 PR 요청으로 미션은 끝난다.

 

피드백으로 받은 내용 중에 이런 게 있다.

Duration은 쿼리 실행시간에 관련있고, Fetch Time은 결과를 전송된 시간에 관련이 있다.

Fetch Time이 긴 경우에는 페이징 쿼리를 고려해보라고 한다.

---

🚀추가 - MySQL Replication

[추가] MySQL Replication by loop-study · Pull Request #223 · next-step/infra-subway-performance

요구사항

- 데이터조작쿼리(INSERT, UPDATE, DELETE)는 마스터로, 데이터조회쿼리(SELECT)는 슬레이브로 받아서 부하를 분산

 

추가 미션은 참고만 하고 넘어가도 되지만 MySQL Replication이 궁금해서 로컬에서 진행한다.

 

master 설정은 다음과 같다.

$ docker run --name mysql-master -p 13306:3306 -v ~/mysql/master:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=masterpw -d mysql

$ docker exec -it mysql-master /bin/bash
$ mysql -u root -p  
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password by 'replication_pw';  
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; 

mysql> SHOW MASTER STATUS\G  
*************************** 1. row ***************************
             File: binlog.000002
         Position: 683
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

master db를 docker로 실행시키면서 계정 패스워드를 masterpw로 설정해준다.

해당 컨테이너에 접속해서 mysql에 접근한다.

패스워드는 설정한 masterpw를 입력하면 mysql 에 접속된다.

가이드를 참고하여 명령어를 실행하다가 SHOW MAStER STATUS\G로 master 상태를 출력하는데

File, Postion 값을 어딘가에 복사해두자.

File은 master db의 바이너리 로그가 쌓이는 파일이다. 이 파일의 바이너리 로그를 통해 slave가 읽어와서 복제한다.

 

slave 설정은 다음과 같다.

$ docker run --name mysql-slave -p 13307:3306 -v ~/mysql/slave:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=slavepw -d mysql

$ docker exec -it mysql-slave /bin/bash
$ mysql -u root -p  

mysql> SET GLOBAL server_id = 2;
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.1', MASTER_PORT = 13306, MASTER_USER='replication_user', MASTER_PASSWORD='replication_pw', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=683;  

mysql> START SLAVE;  
mysql> SHOW SLAVE STATUS\G
...
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

정상적으로 실행되는 모습을 보려면 Slave_IO_Running / Slave_SQL_Running 상태값을 확인하자.

Slave 상태 내역

정상적으로 연동이 되었으면 애플리케이션에 datasource 설정을 master / slave에 맞게 변경한다.

spring.datasource.hikari.master.username=root
spring.datasource.hikari.master.password=masterpw
spring.datasource.hikari.master.jdbc-url=jdbc:mysql://localhost:13306/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true

spring.datasource.hikari.slave.username=root
spring.datasource.hikari.slave.password=slavepw
spring.datasource.hikari.slave.jdbc-url=jdbc:mysql://localhost:13307/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true

애플리케이션이 디비를 구분할 수 있는 클래스를 추가해준다.

public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
    public static final String DATASOURCE_KEY_MASTER = "master";
    public static final String DATASOURCE_KEY_SLAVE = "slave";

    @Override
    protected Object determineCurrentLookupKey() {
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return (isReadOnly)
            ? DATASOURCE_KEY_SLAVE
            : DATASOURCE_KEY_MASTER;
    }
}

@Configuration
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = {"nextstep.subway"})
class DataBaseConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource master,
                                        @Qualifier("slaveDataSource") DataSource slave) {
        ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();

        HashMap<Object, Object> sources = new HashMap<>();
        sources.put(DATASOURCE_KEY_MASTER, master);
        sources.put(DATASOURCE_KEY_SLAVE, slave);

        routingDataSource.setTargetDataSources(sources);
        routingDataSource.setDefaultTargetDataSource(master);

        return routingDataSource;
    }

    @Primary
    @Bean
    public DataSource dataSource(@Qualifier("routingDataSource") DataSource routingDataSource) {
        return new LazyConnectionDataSourceProxy(routingDataSource);
    }
}

이제 어노테이션 @Transactional(readOnly = true) 는 slave를 향하고, 그 외에는 master를 향하게 된다.

애플리케이션 실행결과에 따라 테이블과 데이터가 복제되는지 확인한다.

똑같이 복제되는 걸 확인.

PR를 보내면서 미션은 끝이난다.

 

이번 단계를 진행하면서 레플리카에 대한 여러 궁금증이 있어서 질문했다.

 

- 첫번째로 여러 서적에서는 Writer(Master) / Reader(Slave) 구조를 다양하게 언급한다.

현재 미션에서는 1:1 구조였지만, 서적에서 언급하는 구조는 기본으로 1:n으로 시작하면서 나중에는 Writer도 n 대로 구성하는 모습을 보여준다.

그렇다면 도대체 얼마나 트래픽이 발생되어야 Writer가 여러대로 구성이 되는 걸까?

그래서 강사님에게 대규모 트래픽에서는 Writer / Reader 어떻게 구성되는지 질문했는데 다음과 같이 답변받았다.

현재 일하고 계신 곳도 Writer는 1대로 구성하고 트래픽에 따라 Reader를 4~5대까지 늘린다고 하신다. 1:n 구조다.

 

- 두번째로 가이드처럼 Writer / Reader를 하나의 인스턴스 서버에 놓고 사용하는지 각 인스턴스마다 하나씩 분리하는 지 물어봤다.

 

- 세번째로 애플리케이션도 Writer / Reader 따라 분리하는 구조도 진행하는지 물어봤다.

---

📃 3주차 3단계 & 4단계 & 추가단계 후기

3주차 후반부 미션은 쿼리가 주인공이다.

데이터베이스는 MySQL를 중심으로 강의도 그에 맞춰서 진행된다.

쿼리를 어떻게 작성해야 하는지, 쿼리의 실행계획을 어떻게 봐야 하는지 알려준다.

실행 계획

간략히 설명하자면 id는 실행 순서이고 select_type는 sleect 문의 유형을 뜻한다.

type은 데이터 조회 유형으로 인덱스 풀 스캔이나 테이블 풀 스캔 등을 보여준다.

그 외에도 여러 가지 의미를 설명해주는데 이 실행계획을 참고하여 쿼리를 어떻게 튜닝을 해야 할지 방향성을 제공한다.

 

쿼리 튜닝을 알려주는 데 인덱스 컬럼 가공 안하기와 인덱스 순서 고려하기, 그리고 인덱스를 제대로 사용하는지 확인하는 등 여러 가지를 알려준다.

다른 미션과 다르게 익숙한 환경이어서 쿼리 미션은 금방 진행되었다. (강사님도 3주차 강의중에 쿼리 미션부터 진행하는게 좋다고 언급하신다.)

 

미션을 진행하면서 삽질 좀 했는데

4단계 미션을 진행하는데 이틀이나 걸렸다... 요구사항인 100ms를 0.01초로 잘못 이해하는 바람에

한 문제의 쿼리를 수십, 수백번을 작성하면서 이게 가능한 수치인지 고민도 하기도 했다.

나중에 0.1초라는 걸 뒤늦게 깨달으면서 허탈하기도 했다.

 

Replication을 진행하다가 잘못된 설정으로 삽질을 했다.

Master 상태의 File, Postion이 가이드와 다르게 출력이 되었지만 가이드 코드를 그대로 복붙 하다 보니

Master는 테이블과 데이터가 정상적으로 변경되었지만 Slave가 제대로 연동이 안되었다.

가이드를 다시 보면서 Slave 상태를 확인해보니

잘못된 File, Postion으로 읽지 못하고 있었다.

다시 Master를 확인해보니 파일명은 맞았지만, Postion은 683이 아니라 684였다.

다시 세팅하면서 제대로 입력하니 정상적인 진행이 되었다.

 

3주차에서 정리할 내역

3주차도 정리할 내역이 많다.

- HTTP2

- Contents-encoding, GZIP 텍스트 압축

- 캐싱 활용하기 (HTTP Cache, ETag, Cache-Control 인프런 HTTP 강의도 참고)

- MySQL 기본, DB 최적화 대상, 동작 방식 등

- SQL 실행계획과 인덱스 등

- 쿼리 튜닝하기 팁 정리

- 미션 단계마다 언급된 개념 정리 등등