Jwanna Story

Content 테이블에는 Category와 Location이라는 칼럼이 존재한다.

@Entity
public class Content extends BaseEntity {
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "category_id")
	private Category category;
    
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "location_id")
	private Location location;
}

해당 칼럼들은 외래키로 연결되어 있어 기본으로 단일 칼럼에 대한 인덱스를 가지고 있다.

하지만 카테고리와 지역 정보 두 개를 조건으로 조회하고자 할 때, 인덱스를 둘 다 사용할 수 없으므로 다중 칼럼 인덱스를 적용해주었다.

ALTER TABLE content ADD INDEX idx__category_location(category_id, location_id);

▲ 인덱스 생성

SHOW INDEX FROM content;

▲ 인덱스 조회

Spring에서 인덱스를 추가하는 방법은 해당 어노테이션을 Entity 클래스에 작성해주면 된다.

단, columnList는 실제 테이블을 기준으로 정의되므로 필드명이 아닌 JoinColumn name을 기준으로 설정

@Table(indexes = {
	@Index(name = "idx__category_location", columnList = "category_id, location_id")
})​

이후 약 200만 건의 데이터를 추가해서 단일 칼럼 인덱스와 다중 칼럼 인덱스의 속도 차이를 측정해보고자 했다.

DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
 
CREATE PROCEDURE insertLoop()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 2000000 DO
        INSERT INTO testdb.Content(member_id, content_type, title, recruiting_count, work_content, price, is_premium,
				category_id, location_id, status, dead_line)
				VALUES (1, 1, concat('title', i), i, concat('content', i), i, false,
				FLOOR(RAND() * 13) + 1, FLOOR(RAND() * 25) + 1, 'RECRUITING',
				FROM_UNIXTIME(FLOOR(unix_timestamp('2023-07-01 00:00:00')+(RAND()*(unix_timestamp('2024-07-01 00:00:00')-unix_timestamp('2023-07-01 00:00:00'))))));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL insertLoop;
$$

▲ 프로시저를 활용한 랜덤 데이터 삽입 (느림)

카테고리 ID와 지역 ID는 랜덤으로 삽입하도록 설정했다.

EasyRandom 라이브러리와 Stream의 parallel을 사용하면 빠르게 객체를 생성하고 저장할 수 있다.

결과

우선 데이터가 잘 들어갔는지 확인

단일 칼럼 인덱스 조회와 다중 칼럼 인덱스 조회 비교

다중 칼럼 인덱스를 사용한 후, 약 8배의 성능 향상을 보여주었다.

본문에서 사용된 인스턴스는 프리티어이며 Ubuntu Server 20.04 LTS를 사용하고 있습니다.

MySQL 설치

1. 패키지 업데이트

sudo apt update

2. MySQL 설치

sudo apt install mysql-server

3. MySQL Secure 초기화

sudo mysql_secure_installation

이후 Yes | No는 본인의 선택에 따라 하면 된다.

나는 처음 강력함 암호키 사용을 제외하고 모두 Yes를 선택했다.

비밀번호는 영문 대문자 + 영문 소문자 + 숫자 + 특수문자를 포함하여 8자리 이상으로 설정한다.

+ 비밀번호 변경하는 방법

-- MySQL 5.7 이전
UPDATE USER SET password=password('비밀번호') WHERE USER = '계정';

-- MySQL 5.7 이후
ALTER USER '계정'@'localhost' IDENTIFIED WITH mysql_native_password BY '비밀번호';

-- 변경사항 적용
FLUSH PRIVILEGES;

4. 외부 접속 허용을 위한 mysqld.cnf 수정

- root 계정으로 전환

sudo -i
혹은
sudo su

root 계정을 탈출할 때는 exit를 입력하면 된다.

- 디렉토리 이동

cd /etc/mysql/mysql.conf.d

- Vi 에디터로 파일 실행

vi mysqld.cnf

- bind-address를 찾아서 0.0.0.0으로 변경 혹은 bind-address, mysqlx-bind-address 주석 처리

vi 에디터 사용법

/ 를 누르고 하단에 검색할 내용을 입력한 뒤 엔터를 누른다.
n 을 누르면 해당 단어가 포함된 내용을 순차적으로 검색한다.
i 를 누르면 insert 모드로 변경되어 수정할 수 있다.
esc 혹은 ctrl+c 를 통해 해당 모드를 빠져나올 수 있다.
:wq 를 입력하여 vi 에디터를 종료할 수 있다.

5. MySQL 서버 재시작

sudo systemctl restart mysql
혹은
sudo service mysql restart

(Option) MySQL 워크벤치 사용을 위한 사용자 계정 생성

- 먼저 MySQL 서버로 접속을 한다.

mysql -uroot -p

- 외부 접근을 위한 계정 생성

CREATE USER '계정'@'%' identified by '비밀번호';

@'%' : 어떤 클라이언트에서든 접근 가능하다.
@'localhost' : 해당 컴퓨터에서만 접근 가능하다.

- 데이터베이스에 권한 추가

GRANT ALL ON DB명.* TO '계정'@'%' WITH GRANT OPTION;

-- 아래와 같이 모든 DB에 대한 권한을 줄 수도 있다.
GRANT ALL ON *.* TO '계정'@'%' WITH GRANT OPTION;

-- 변경사항 적용
FLUSH PRIVILEGES;

+ root 계정을 외부 접근이 가능하도록 설정하는 방법

UPDATE USER SET Host='%' WHERE User='root';

redis 설치

1. 패키지 업데이트

sudo apt update

2. redis 설치

sudo apt install redis-server

3. 외부 접속 허용 및 Redis 설정을 위한 redis.conf 수정

- root 계정으로 전환

sudo -i
혹은
sudo su

- 디렉토리 이동

cd /etc/redis

- Vi 에디터로 파일 실행

vi redis.conf

vi 에디터 사용법

/ 를 누르고 하단에 검색할 내용을 입력한 뒤 엔터를 누른다.
n 을 누르면 해당 단어가 포함된 내용을 순차적으로 검색한다.
i 를 누르면 insert 모드로 변경되어 수정할 수 있다.
esc 혹은 ctrl+c 를 통해 해당 모드를 빠져나올 수 있다.
:wq 를 입력하여 vi 에디터를 종료할 수 있다.

1. 외부 접속 허용

 - bind를 찾는다.

 - bind 127.0.0.1 ::1 부분을 bind 0.0.0.0 으로 변경한다.

2. 비밀번호 변경 (Option)

 - requirepass를 찾는다.

 - requirepass foobared 부분의 주석을 해제하고 foobared 자리에 원하는 비밀번호를 입력한다.

3. Max 메모리 설정

 - maxmemory를 찾는다.

 - maxmemory <bytes> 부분의 주석을 해제하고 <bytes> 자리에 max 메모리 값을 입력한다.

byte 단위의 숫자로 입력해도 되고,
500mb, 1gb 등 단위를 포함하여 원하는 메모리 값을 설정할 수 있다. (개인의 인스턴스 성능에 따라)

💡 참고 : 프리티어 인스턴스는 1GB 메모리를 가지고 있어 500mb 정도만 설정하는 것을 추천

4. 데이터 교체 알고리즘 설정

 - policy를 찾는다.

 - maxmemory-policy noeviction 부분의 주석을 해제하고 noeviction 자리에 원하는 데이터 교체 알고리즘을 입력한다.

데이터 교체 알고리즘은 redis 문서의 Eviction Policy에서 확인 가능하다.

나는 사용된 지 가장 오래된 데이터를 삭제하는 allkeys-lru 알고리즘을 선택했다.

5. redis 서버 재시작

sudo systemctl start redis-server

6. EC2 포트 개방

- 인스턴스 상세 설정에서 [보안] > 보안 그룹을 클릭

- [인바운드 규칙 편집] 클릭

- 아래와 같이 규칙 설정 후 저장

참고 자료

[AWS] EC2에 MySQL 설치하기

AWS EC2에 MySQL 서버 구축하기

[Redis] AWS EC2에 redis-server setup 하기

문제 발생

JDBC로 insert를 구현하던 중에 에러를 만났다.

우선 코드는 아래와 같다.

@RequiredArgsConstructor
@Repository
public class MemberRepository {
	private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    ...
    
	private Member insert(Member member) {
        SimpleJdbcInsertOperations simpleJdbcInsert =
            new SimpleJdbcInsert(namedParameterJdbcTemplate.getJdbcTemplate())
                .withTableName("Member")    	// INSERT INTO 'TABLE'
                .usingGeneratedKeyColumns("id");    // AUTO_INCREMENT

        SqlParameterSource params = new BeanPropertySqlParameterSource(member);

        long id = simpleJdbcInsert
            .executeAndReturnKey(params)
            .longValue();

        return Member
            .builder()
            .id(id)
            .email(member.getEmail())
            .nickname(member.getNickname())
            .birthDay(member.getBirthDay())
            .createdAt(member.getCreatedAt())
            .build();
    }
}

▲ Member의 Id 칼럼을 PK로 하여 Auto_increment를 설정해서 insert하기위한 Method

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://${LOCAL_DB_URL}?rewriteBatchedStatements=true&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
    username: ${LOCAL_DB_USERNAME}
    password: ${LOCAL_DB_PASSWORD}

▲ application.yml의 Datasource 설정

LOCAL_DB_URL=localhost:3306/fast_sns

▲ 환경 변수

해당 코드를 실행하면 아래와 같은 에러가 발생한다.

java.sql.SQLSyntaxErrorException: Unknown column 'member_id' in 'field list'

??????

내가 생성한 Column 중에 member_id는 존재하지 않는데 member_id를 왜 찾지??

원인

그래서 위쪽을 보니 아래와 같은 실행 구문이 있었다.

Retrieving meta-data for testdb2/root@localhost/member
...
Compiled insert object: insert string is [INSERT INTO Member (member_id, created_at, last_modified_at, about, email, member_status, nick_name, password, picture_url, profile_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

SimpleJdbcInsert가 현재 프로젝트에 연동된 DB가 아닌 다른 DB의 Member 테이블을 찾아서 로직을 실행하는 것 같았다.

그래서 SimpleJdbcInsert 공식 문서를 통해 관련 내용을 찾아보았다.

SimpleJdbcInsert는 테이블에 대한 쉬운 삽입 기능을 제공한다.
기본 삽입문을 구성하는데 필요한 코드를 단순화하기 위해 메타데이터 처리를 제공한다.
그러니까 테이블 이름과 열 이름, 열 값이 포함된 Map만 제공하면 된다.

메타데이터 처리는 JDBC Driver에서 제공하는 DatabaseMetaData를 기반으로 한다.
JDBC Driver가 지정된 테이블의 열 이름을 제공할 수 있는 한 자동 탐지 기능을 사용할 수 있다.

쉽게 말해 withTableName()을 통해서 제공된 테이블 명을 통해 자동으로 탐지한다는 것 같다.

그렇다면 공식 문서를 참고해 withSchemaName()이나 withCatalogName()을 활용해서 해결해보기로 했다.

해결

StackOverflow를 통해 Catalog와 Schema의 차이를 파악해서 withCatalogName()을 사용해 DB명을 지정해주었더니 문제가 해결되었다.

@RequiredArgsConstructor
@Repository
public class MemberRepository {
	private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	private Member insert(Member member) {
		SimpleJdbcInsertOperations simpleJdbcInsert =
			new SimpleJdbcInsert(namedParameterJdbcTemplate.getJdbcTemplate())
				.withCatalogName("fast_sns")	// 추가된 부분 (DB명 지정)
				.withTableName("Member")    	// INSERT INTO 'TABLE'
				.usingGeneratedKeyColumns("id");    // AUTO_INCREMENT
		SqlParameterSource params = new BeanPropertySqlParameterSource(member);

		long id = simpleJdbcInsert
			.executeAndReturnKey(params)
			.longValue();

		return Member
			.builder()
			.id(id)
			.email(member.getEmail())
			.nickname(member.getNickname())
			.birthDay(member.getBirthDay())
			.createdAt(member.getCreatedAt())
			.build();
	}
}

참고 자료

[Velog@lango - Kakao Cloud School 10번째 회고록]

[StackOverflow - What`s the difference between a catalog and a schema in a relational database?]