Databases

Connection Pooling: Reducing Connection Overhead

커넥션 풀링의 개념과 중요성

데이터베이스 연결은 상당한 리소스를 소모하는 작업이다.

각 연결을 설정할 때마다 다음과 같은 과정이 필요하다:

네트워크 소켓 생성
데이터베이스 서버와의 TCP 핸드셰이크
사용자 인증 및 권한 확인
세션 변수 설정
트랜잭션 상태 초기화

이러한 과정은 수 밀리초에서 수백 밀리초까지 시간이 소요될 수 있다. 따라서 사용자 요청마다 새로운 연결을 생성하고 종료하는 것은 상당한 오버헤드를 발생시킨다.

커넥션 풀링은 이러한 문제를 해결하기 위한 기술로, 미리 여러 데이터베이스 연결을 생성하고 이를 풀(pool)에 보관하여 필요할 때 재사용한다. 이는 새로운 연결 설정에 드는 시간과 리소스를 크게 절약한다.

실제 구현 예시

Java/Spring Boot 환경에서의 HikariCP 설정:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
@Configuration
public class DatabaseConfig {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("user");
        config.setPassword("password");
        
        // 커넥션 풀 설정
        config.setMinimumIdle(5);           // 풀에서 유지할 최소 연결 수
        config.setMaximumPoolSize(20);      // 풀의 최대 연결 수
        config.setConnectionTimeout(30000); // 연결 타임아웃 (30초)
        config.setIdleTimeout(600000);      // 유휴 연결 타임아웃 (10분)
        config.setMaxLifetime(1800000);     // 연결 최대 수명 (30분)
        
        return new HikariDataSource(config);
    }
}

Node.js에서의 MySQL 커넥션 풀 설정:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0,
  idleTimeout: 60000
});

// 풀에서 연결 가져오기
async function query(sql, params) {
  const promisePool = pool.promise();
  const [rows] = await promisePool.query(sql, params);
  return rows;
}

module.exports = { query };

성능 향상 효과

커넥션 풀링을 적용하면 다음과 같은 성능 향상을 기대할 수 있다:

응답 시간 감소: 연결 설정 시간을 절약하여 즉시 쿼리 실행 가능
처리량 증가: 동일 시간 내 더 많은 요청 처리 가능
서버 부하 감소: 빈번한 연결 생성/종료로 인한 CPU 및 메모리 사용량 감소
안정성 향상: 연결 수 제한으로 데이터베이스 서버 과부하 방지

실제 성능 테스트에서는 커넥션 풀링 적용 시 응답 시간이 50-80% 감소하는 사례가 많다.

Optimizing Connection Pool Settings

커넥션 풀을 단순히 구현하는 것뿐만 아니라, 애플리케이션 특성에 맞게 설정을 최적화하는 것이 중요하다.

주요 최적화 매개변수

최소/최대 연결 수(Min/Max Pool Size)
- 최소 연결 수: 풀에서 항상 유지되는 연결 수
- 최대 연결 수: 풀이 관리할 수 있는 최대 연결 수
연결 타임아웃(Connection Timeout)
- 새 연결을 얻기 위해 대기하는 최대 시간
유휴 타임아웃(Idle Timeout)
- 비활성 상태의 연결이 풀에서 제거되기 전에 유지되는 시간
연결 수명(Max Lifetime)
- 연결이 재사용되다가 종료되고 새로운 연결로 대체되는 최대 시간

최적 설정 가이드라인

최대 풀 크기 결정:
최대 풀 크기는 다음 공식을 참고할 수 있다:
1
최대 풀 크기 = (CPU 코어 수 * 2) + 유효 디스크 수
예를 들어 8코어 CPU와 1개의 SSD가 있는 경우:
1
최대 풀 크기 = (8 * 2) + 1 = 17
그러나 이는 시작점일 뿐, 실제 워크로드에 따라 조정이 필요하다.
최소 풀 크기 설정:
일반적으로 최대 풀 크기의 25-50%로 설정하는 것이 좋다. 이는 초기 연결 생성 비용을 절약하면서 기본 부하를 처리할 수 있는 연결을 유지한다.
타임아웃 설정:
- 연결 타임아웃: 일반적으로 5-30초 (워크로드에 따라 다름)
- 유휴 타임아웃: 일반적으로 5-10분
- 최대 수명: 일반적으로 30분-2시간 (데이터베이스 서버 설정에 따라 다름)

모니터링 및 조정 전략

커넥션 풀 성능을 지속적으로 모니터링하고 다음 지표를 추적해야 한다:

활성 연결 수: 현재 사용 중인 연결 수
유휴 연결 수: 사용 가능한 연결 수
대기 시간: 연결을 얻기 위해 대기한 시간
타임아웃 발생 빈도: 연결을 얻지 못한 요청 수

이러한 지표를 기반으로 풀 설정을 조정할 수 있다:

대기 시간이 길거나 타임아웃이 자주 발생하면 최대 풀 크기를 늘림
유휴 연결이 많으면 최소 풀 크기를 줄임
데이터베이스 서버 부하가 높으면 최대 풀 크기를 줄임

Efficient Database Indexing

데이터베이스 성능을 최적화하는 가장 효과적인 방법 중 하나는 적절한 인덱스를 생성하는 것이다.

인덱스의 역할과 중요성

인덱스는 데이터베이스 테이블에서 데이터를 빠르게 찾기 위한 자료 구조이다. 인덱스가 없으면 데이터베이스는 테이블의 모든 행을 검사하는 ‘전체 테이블 스캔(Full Table Scan)‘을 수행해야 한다.

예를 들어, 1백만 개의 레코드가 있는 테이블에서 특정 사용자를 찾는 경우:

인덱스 없이: 최악의 경우 1백만 개 모든 레코드 검사 필요
인덱스 사용: B-Tree 인덱스의 경우 약 log2(1,000,000) ≈ 20번의 비교로 찾을 수 있음

인덱스 유형

단일 컬럼 인덱스(Single Column Index): 하나의 컬럼에 대한, 가장 기본적인 인덱스
복합 인덱스(Composite Index): 여러 컬럼을 포함하는 인덱스
고유 인덱스(Unique Index): 중복 값을 허용하지 않는 인덱스
부분 인덱스(Partial Index): 테이블의 일부 행만 포함하는 인덱스
함수 기반 인덱스(Function-Based Index): 컬럼 값이 아닌 함수 결과에 대한 인덱스

효과적인 인덱스 전략

인덱스가 필요한 컬럼:

WHERE 절에서 자주 사용되는 컬럼
JOIN 절에서 사용되는 컬럼
ORDER BY 또는 GROUP BY 절에서 사용되는 컬럼
고유성이 높은 컬럼 (선택성이 좋은 컬럼)

인덱스 생성 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 단일 컬럼 인덱스
CREATE INDEX idx_user_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_product_category_price ON products(category_id, price);

-- 고유 인덱스
CREATE UNIQUE INDEX idx_order_number ON orders(order_number);

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;

-- 함수 기반 인덱스
CREATE INDEX idx_lower_email ON users(LOWER(email));

인덱스 관리 및 모니터링

인덱스는 정기적으로 관리해야 한다:

사용되지 않는 인덱스 식별 및 제거: 불필요한 인덱스는 쓰기 성능에 부담을 줌
인덱스 단편화 확인 및 재구성: 시간이 지남에 따라 인덱스 효율성이 저하될 수 있음
인덱스 통계 업데이트: 쿼리 최적화기가 정확한 실행 계획을 생성하도록 함

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- MySQL에서 인덱스 사용 통계 확인
SELECT * FROM mysql.schema_index_statistics;

-- PostgreSQL에서 사용되지 않는 인덱스 찾기
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size,
       idx_scan AS index_scans
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- 스캔 횟수가 0인 인덱스
  AND 0 <> ALL(i.indkey)  -- 표현식이 아닌 경우
  AND i.indisunique IS FALSE; -- 고유 인덱스가 아닌 경우

Fine-Tuning ORM Queries

ORM(Object-Relational Mapping)은 객체지향 프로그래밍 언어와 관계형 데이터베이스 사이의 다리 역할을 한다. 그러나 ORM을 잘못 사용하면 성능 문제가 발생할 수 있다.

ORM의 일반적인 성능 문제

N+1 문제: 부모 레코드 하나에 대해 자식 레코드를 조회할 때 추가 쿼리가 N번 발생하는 문제
과도한 데이터 로딩: 필요 이상의 데이터를 불러오는 경우
비효율적인 쿼리 생성: ORM이 최적화되지 않은 SQL 쿼리를 생성하는 경우
캐시 미사용: ORM의 캐싱 기능을 활용하지 않는 경우

N+1 문제 해결

Hibernate/JPA에서의 N+1 문제 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// N+1 문제 발생
@Entity
public class Department {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "department")
    private List<Employee> employees;
    
    // getters and setters
}

// 이 코드는 N+1 문제를 발생시킵니다
List<Department> departments = entityManager.createQuery("SELECT d FROM Department d", Department.class).getResultList();
for (Department dept : departments) {
    System.out.println("Department: " + dept.getName());
    // 각 부서마다 직원 목록을 조회하는 추가 쿼리 발생
    for (Employee emp : dept.getEmployees()) {
        System.out.println("Employee: " + emp.getName());
    }
}

해결책: Fetch Join 사용:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// Fetch Join으로 N+1 문제 해결
List<Department> departments = entityManager
    .createQuery("SELECT d FROM Department d LEFT JOIN FETCH d.employees", Department.class)
    .getResultList();

// 이제 추가 쿼리 없이 직원 정보 접근 가능
for (Department dept : departments) {
    System.out.println("Department: " + dept.getName());
    for (Employee emp : dept.getEmployees()) {
        System.out.println("Employee: " + emp.getName());
    }
}

ORM 쿼리 최적화 기법

필요한 필드만 선택:

1
2
3
4
5
// 전체 엔티티 대신 필요한 필드만 선택
List<Object[]> results = entityManager
    .createQuery("SELECT e.id, e.name, e.salary FROM Employee e WHERE e.department.id = :deptId")
    .setParameter("deptId", 1L)
    .getResultList();

네이티브 쿼리 활용:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// 복잡한 쿼리는 네이티브 SQL 사용
List<Employee> employees = entityManager
    .createNativeQuery(
        "SELECT e.* FROM employees e " +
        "JOIN departments d ON e.department_id = d.id " +
        "WHERE d.name = :deptName AND e.salary > :minSalary", 
        Employee.class)
    .setParameter("deptName", "Engineering")
    .setParameter("minSalary", 50000)
    .getResultList();

배치 처리:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
// 대량 데이터 처리 시 배치 처리
Session session = entityManager.unwrap(Session.class);
ScrollableResults scrollableResults = session
    .createQuery("FROM Employee")
    .setCacheMode(CacheMode.IGNORE)
    .scroll(ScrollMode.FORWARD_ONLY);

int count = 0;
while (scrollableResults.next()) {
    Employee employee = (Employee) scrollableResults.get(0);
    processEmployee(employee);

    if (++count % 100 == 0) {
        session.flush();
        session.clear();
    }
}
scrollableResults.close();

캐시 활용:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// 2차 캐시 활용
@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Product {
    @Id
    private Long id;
    private String name;
    private BigDecimal price;

    // getters and setters
}

ORM 성능 모니터링

ORM 쿼리 성능을 모니터링하려면 다음 도구를 활용할 수 있다:

로깅 활성화: Hibernate의 경우 show_sql 및 format_sql 속성 활성화
프로파일링 도구: p6spy, datasource-proxy 등을 사용한 SQL 모니터링
APM 도구: New Relic, Datadog 등을 이용한 쿼리 성능 추적

Optimizing Data Retrieval with Lazy Loading, Eager Loading, and Batch Processing

데이터 로딩 전략은 애플리케이션의 성능에 큰 영향을 미친다.

지연 로딩(Lazy Loading)

지연 로딩은 데이터가 실제로 필요할 때까지 로딩을 지연시키는 전략이다.

장점:

초기 로딩 시간 단축
메모리 사용량 감소

단점:

데이터 접근 시 추가 쿼리 발생 가능(N+1 문제)
세션 종료 후 접근 시 문제 발생(LazyInitializationException)

Hibernate/JPA에서의 지연 로딩 예시:

1
2
3
4
5
6
7
8
9
@Entity
public class Order {
    @Id
    private Long id;
    
    // 지연 로딩 (기본값)
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;
}

즉시 로딩(Eager Loading)

즉시 로딩은 연관된 데이터를 초기 쿼리 시점에 함께 로딩하는 전략이다.

장점:

추가 쿼리 없이 연관 데이터 접근 가능
세션 종료 후에도 데이터 접근 가능

단점:

초기 로딩 시간 증가
불필요한 데이터까지 로딩 가능성

Hibernate/JPA에서의 즉시 로딩 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@Entity
public class OrderItem {
    @Id
    private Long id;
    
    // 즉시 로딩
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id")
    private Product product;
}

배치 로딩(Batch Loading)

배치 로딩은 여러 개체를 한 번에 로딩하여 쿼리 수를 줄이는 기법이다.

Hibernate에서의 배치 로딩 설정:

1
2
3
4
5
6
7
8
9
// 엔티티 클래스
@Entity
@BatchSize(size = 25)
public class Order {
    // …
}

// 또는 글로벌 설정 (hibernate.properties)
hibernate.default_batch_fetch_size = 25

배치 로딩 실행 예시:

1
2
3
4
5
6
7
8
9
// 주문 목록 조회
List<Order> orders = session.createQuery("FROM Order").list();

// 각 주문의 항목에 접근 (배치 로딩 적용)
for (Order order : orders) {
    // 처음 접근 시 최대 25개 주문의 항목을 한 번에 로딩
    List<OrderItem> items = order.getItems();
    // …
}

상황별 최적 로딩 전략

상황	권장 전략	이유
대부분의 경우 연관 데이터가 필요한 경우	즉시 로딩	추가 쿼리 감소
연관 데이터가 거의 필요 없는 경우	지연 로딩	불필요한 데이터 로딩 방지
목록에서 연관 데이터에 자주 접근하는 경우	배치 로딩	N+1 문제 방지, 쿼리 수 최소화
매우 큰 데이터셋 처리	지연 로딩 + 스트리밍	메모리 사용량 최소화

Efficient Pagination for Large Datasets

대용량 데이터를 처리할 때 페이지네이션은 필수적인 기법이다.

전통적인 페이지네이션의 문제점

전통적인 오프셋 기반 페이지네이션(OFFSET/LIMIT)은 데이터가 많아질수록 성능이 저하된다:

1
2
3
4
-- 오프셋 기반 페이지네이션
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 980;

이 쿼리는 다음과 같은 문제가 있다:

데이터베이스는 981번째 행까지 모두 읽어야 함
페이지가 클수록 성능이 저하됨
데이터 변경 시 결과가 중복되거나 누락될 수 있음

커서 기반 페이지네이션

커서 기반 페이지네이션은 마지막으로 조회한 항목의 식별자를 기준으로 다음 항목을 가져오는 방식이다:

1
2
3
4
5
-- 커서 기반 페이지네이션
SELECT * FROM products
WHERE id > :lastSeenId  -- 마지막으로 본 ID 이후의 항목만 조회
ORDER BY id
LIMIT 20;

장점:

페이지 크기에 상관없이 일정한 성능
인덱스를 효율적으로 활용 가능
데이터 변경에도 안정적

JPA/Spring Data 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// 엔티티 클래스
@Entity
public class Product {
    @Id
    private Long id;
    private String name;
    private BigDecimal price;
    // …
}

// 리포지토리 인터페이스
public interface ProductRepository extends JpaRepository<Product, Long> {
    // 커서 기반 페이지네이션 쿼리
    @Query("SELECT p FROM Product p WHERE p.id > :lastId ORDER BY p.id ASC LIMIT :limit")
    List<Product> findByIdGreaterThan(@Param("lastId") Long lastId, @Param("limit") int limit);
}

// 서비스 클래스
@Service
public class ProductService {
    private final ProductRepository productRepository;
    
    // 생성자…
    
    public List<Product> getProductsPage(Long lastId, int pageSize) {
        if (lastId == null) {
            lastId = 0L; // 첫 페이지인 경우
        }
        return productRepository.findByIdGreaterThan(lastId, pageSize);
    }
}

키셋 페이지네이션

키셋 페이지네이션은 커서 기반 페이지네이션의 확장으로, 여러 컬럼을 기준으로 정렬할 때 사용한다:

1
2
3
4
5
-- 키셋 페이지네이션 (가격 내림차순, 동일 가격은 ID 오름차순)
SELECT * FROM products
WHERE (price < :lastPrice) OR (price = :lastPrice AND id > :lastId)
ORDER BY price DESC, id ASC
LIMIT 20;

이 방식은 복합 인덱스를 효율적으로 활용할 수 있다.

스트리밍 API

매우 큰 데이터셋의 경우, 페이지네이션보다는 스트리밍 API를 고려할 수 있다:

1
2
3
4
5
6
// Spring WebFlux를 사용한 스트리밍 API
@GetMapping(value = "/products/stream", produces = MediaType.APPLICATION_STREAM_JSON_VALUE)
public Flux<Product> streamProducts() {
    return productRepository.findAll()
        .delayElements(Duration.ofMillis(100)); // 백프레셔 시뮬레이션
}

이 방식은 클라이언트가 데이터를 처리할 수 있는 속도로 서버가 데이터를 전송한다.

Data Optimization: Avoid Select * Queries and Fetch Only Required Columns

불필요한 데이터 조회는 성능에 부정적인 영향을 미친다.

`SELECT *` 사용의 문제점

SELECT * 쿼리의 주요 문제점:

불필요한 데이터 전송: 필요하지 않은 컬럼 데이터까지 전송
네트워크 대역폭 낭비: 더 많은 데이터 전송으로 대역폭 소비
메모리 사용량 증가: 애플리케이션에서 더 많은 메모리 사용
인덱스 커버링 불가: 인덱스만으로 쿼리를 해결할 수 없음
스키마 변경 시 위험: 테이블 구조 변경 시 예상치 못한 동작 발생 가능

필요한 컬럼만 선택하는 방법

SQL 쿼리:

1
2
3
4
5
6
7
-- 나쁜 예
SELECT * FROM orders WHERE customer_id = 123;

-- 좋은 예
SELECT order_id, order_date, total_amount 
FROM orders 
WHERE customer_id = 123;

JPA/Hibernate:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// 나쁜 예
List<Order> orders = entityManager
    .createQuery("SELECT o FROM Order o WHERE o.customer.id = :customerId", Order.class)
    .setParameter("customerId", 123L)
    .getResultList();

// 좋은 예 (DTO 프로젝션 사용)
List<OrderSummary> orderSummaries = entityManager
    .createQuery("SELECT new com.example.dto.OrderSummary(o.id, o.orderDate, o.totalAmount) " +
                 "FROM Order o WHERE o.customer.id = :customerId", OrderSummary.class)
    .setParameter("customerId", 123L)
    .getResultList();

Spring Data JPA:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// 인터페이스 기반 프로젝션
public interface OrderProjection {
    Long getId();
    LocalDate getOrderDate();
    BigDecimal getTotalAmount();
}

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<OrderProjection> findByCustomerId(Long customerId);
}

대용량 테이블에서의 영향

특히 대용량 테이블에서는 필요한 컬럼만 선택하는 것이 중요하다.

예를 들어, 1,000만 행을 가진 테이블에서 각 행이 20개의 컬럼을 가지고 있다고 가정해 보면:

모든 컬럼을 선택할 경우: 10,000,000 × 20 = 200,000,000개의 값 전송
필요한 3개 컬럼만 선택할 경우: 10,000,000 × 3 = 30,000,000개의 값 전송

이는 약 85%의 데이터 전송량 감소를 의미하며, 이에 따라 네트워크 대역폭 사용량, 메모리 사용량, 처리 시간 모두가 크게 줄어든다.

성능 개선 사례

실제 프로젝트에서 SELECT * 쿼리를 최적화한 사례를 살펴보면:

쿼리 최적화 전:

1
SELECT * FROM product_catalog WHERE category_id = 5 ORDER BY price DESC LIMIT 50;

쿼리 최적화 후:

1
2
3
4
5
SELECT product_id, name, price, thumbnail_url
FROM product_catalog
WHERE category_id = 5
ORDER BY price DESC
LIMIT 50;

최적화 결과:

쿼리 실행 시간: 250ms → 60ms (76% 감소)
전송 데이터 크기: 1.2MB → 180KB (85% 감소)
메모리 사용량: 1.4MB → 220KB (84% 감소)

이러한 최적화는 특히 고부하 상황이나 모바일 환경에서 상당한 성능 향상을 가져온다.

Denormalizing Database Schema for Read-Heavy Workloads and Reducing Join Operations

정규화와 비정규화의 이해

데이터베이스 설계에서 정규화는 중복을 제거하고 데이터 일관성을 유지하는 과정이다. 그러나 읽기 중심 애플리케이션에서는 비정규화가 성능 향상에 도움이 될 수 있다.

정규화된 스키마 (3NF):

1
2
3
4
5
6
7
customers (id, name, email, address_id)
addresses (id, street, city, state, zip, country_id)
countries (id, name, code)
orders (id, customer_id, order_date, status)
order_items (id, order_id, product_id, quantity, price)
products (id, name, description, category_id, price)
categories (id, name, parent_id)

비정규화된 스키마 (읽기 최적화):

1
2
3
4
customers (id, name, email, street, city, state, zip, country_name, country_code)
orders (id, customer_id, customer_name, customer_email, order_date, status)
order_items (id, order_id, product_id, product_name, category_name, quantity, price)
products (id, name, description, category_name, parent_category_name, price)

비정규화 전략

자주 조인되는 테이블 병합: 조회 시 항상 함께 사용되는 테이블은 병합 고려

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 비정규화 전: 두 테이블 조인 필요
SELECT p.id, p.name, p.price, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.id = 5;

-- 비정규화 후: 단일 테이블 조회만으로 충분
SELECT id, name, price, category_name
FROM products_denormalized
WHERE category_id = 5;

계산된 필드 저장: 자주 계산하는 값을 미리 저장

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 비정규화 전: 주문 총액 계산을 위한 서브쿼리 필요
SELECT o.id, o.order_date,
       (SELECT SUM(oi.price * oi.quantity)
        FROM order_items oi
        WHERE oi.order_id = o.id) as total_amount
FROM orders o
WHERE o.customer_id = 123;

-- 비정규화 후: 미리 계산된 총액 사용
SELECT id, order_date, total_amount
FROM orders_denormalized
WHERE customer_id = 123;

집계 테이블 활용: 통계 및 분석을 위한 별도 테이블 유지

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 비정규화 전: 매일 집계를 위한 복잡한 쿼리
SELECT DATE(order_date) as date,
       COUNT(*) as order_count,
       SUM(total_amount) as daily_revenue
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY DATE(order_date);

-- 비정규화 후: 미리 계산된 집계 테이블 사용
SELECT date, order_count, daily_revenue
FROM daily_sales_stats
WHERE date BETWEEN '2023-01-01' AND '2023-01-31';

비정규화 구현 방법

트리거 사용: 데이터 변경 시 비정규화 테이블 자동 업데이트

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TRIGGER update_product_denormalized
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    UPDATE products_denormalized
    SET name = NEW.name,
        price = NEW.price,
        category_name = (SELECT name FROM categories WHERE id = NEW.category_id)
    WHERE id = NEW.id;
END;

배치 처리: 주기적으로 비정규화 테이블 갱신

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@Scheduled(cron = "0 0 * * * *") // 매시간 실행
public void updateProductDenormalizedTable() {
    jdbcTemplate.update(
        "INSERT INTO products_denormalized (id, name, price, category_name) " +
        "SELECT p.id, p.name, p.price, c.name " +
        "FROM products p JOIN categories c ON p.category_id = c.id " +
        "ON DUPLICATE KEY UPDATE " +
        "name = VALUES(name), price = VALUES(price), category_name = VALUES(category_name)"
    );
}

이벤트 기반 업데이트: 메시지 큐를 활용한 비동기 업데이트

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
@TransactionalEventListener
public void handleProductUpdateEvent(ProductUpdatedEvent event) {
    Product product = event.getProduct();
    Category category = categoryRepository.findById(product.getCategoryId()).orElse(null);

    if (category != null) {
        productDenormalizedRepository.updateProductWithCategory(
            product.getId(),
            product.getName(),
            product.getPrice(),
            category.getName()
        );
    }
}

비정규화의 장단점

장점:

읽기 작업 성능 향상
복잡한 조인 쿼리 감소
애플리케이션 로직 단순화

단점:

데이터 중복으로 인한 저장 공간 증가
데이터 일관성 유지의 어려움
쓰기 작업 성능 저하 가능성

비정규화는 읽기 작업이 쓰기 작업보다 훨씬 많은 애플리케이션(예: 전자상거래 카탈로그, 콘텐츠 관리 시스템)에 가장 적합하다.

Optimizing Join Operations and Avoiding Unnecessary Joins

조인 작업의 성능 영향

조인은 관계형 데이터베이스의 핵심 기능이지만, 잘못 사용하면 성능 저하의 주요 원인이 될 수 있다. 테이블 크기가 클수록 조인 작업의 비용은 기하급수적으로 증가할 수 있다.

조인 유형과 성능 특성

내부 조인(INNER JOIN):

1
2
3
SELECT p.id, p.name, c.name as category
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

양쪽 테이블에 모두 일치하는 행만 반환
일반적으로 가장 빠른 조인 유형

왼쪽 외부 조인(LEFT OUTER JOIN):

1
2
3
SELECT o.id, o.order_date, c.name as customer
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행 반환
내부 조인보다 약간 느림

오른쪽 외부 조인(RIGHT OUTER JOIN):

1
2
3
SELECT p.id, p.name, r.rating
FROM products p
RIGHT JOIN product_reviews r ON p.id = r.product_id;

오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행 반환
LEFT JOIN과 유사한 성능 특성

전체 외부 조인(FULL OUTER JOIN):

1
2
3
4
SELECT e.id as employee_id, e.name as employee_name,
       d.id as department_id, d.name as department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

양쪽 테이블의 모든 행 반환
일반적으로 가장 느린 조인 유형

조인 최적화 기법

적절한 인덱스 생성: 조인 컬럼에는 반드시 인덱스 생성

1
2
3
-- 조인 컬럼에 인덱스 생성
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

조인 순서 최적화: 가능하면 작은 테이블을 먼저 참조

1
2
3
4
5
6
7
-- 나쁜 예: 큰 테이블이 왼쪽에 위치
SELECT * FROM order_items oi
JOIN orders o ON oi.order_id = o.id;

-- 좋은 예: 작은 테이블이 왼쪽에 위치
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id;

조인 조건 최적화: 가능한 한 동등 조인(Equi-Join) 사용

1
2
3
4
5
6
7
-- 나쁜 예: 범위 조인
SELECT * FROM products p
JOIN categories c ON p.category_id <= c.id;

-- 좋은 예: 동등 조인
SELECT * FROM products p
JOIN categories c ON p.category_id = c.id;

불필요한 조인 제거: 실제로 필요한 테이블만 조인

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 나쁜 예: 불필요한 조인
SELECT p.id, p.name, p.price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

-- 좋은 예: 필요한 테이블만 사용
SELECT id, name, price
FROM products
WHERE price > 100;

서브쿼리 vs. 조인

경우에 따라 조인 대신 서브쿼리를 사용하는 것이 더 효율적일 수 있다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 조인을 사용한 방식
SELECT o.id, o.order_date, c.name as customer
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'SHIPPED';

-- 서브쿼리를 사용한 방식 (고객 데이터가 실제로 필요한 경우만 조회)
SELECT o.id, o.order_date,
       (SELECT name FROM customers WHERE id = o.customer_id) as customer
FROM orders o
WHERE o.status = 'SHIPPED';

서브쿼리는 다음과 같은 경우에 조인보다 효율적일 수 있다:

관련 데이터가 적은 경우
1:1 관계에서 모든 필드가 필요하지 않은 경우
특정 조건에 따라 다른 테이블의 데이터가 필요한 경우

조인 최적화 모니터링

쿼리 실행 계획을 분석하여 조인 작업의 효율성을 모니터링할 수 있다:

1
2
3
4
5
-- MySQL에서 실행 계획 확인
EXPLAIN SELECT o.id, o.order_date, c.name as customer
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'SHIPPED';

실행 계획에서 다음 사항을 확인해야 한다:

조인 방식(nested loop, hash join, merge join 등)
테이블 액세스 방법(인덱스 스캔, 전체 테이블 스캔 등)
조인 순서
필터링 시점

Regular Maintenance and Cleanup of Data

데이터 유지보수의 중요성

데이터베이스는 시간이 지남에 따라 성능이 저하될 수 있다. 정기적인 유지보수는 데이터베이스 성능을 최적의 상태로 유지하는 데 필수적이다.

주요 유지보수 작업

테이블 및 인덱스 단편화 해소
테이블과 인덱스는 데이터 삽입, 수정, 삭제로 인해 시간이 지남에 따라 단편화된다. 이는 디스크 공간 낭비와 성능 저하를 초래한다.

MySQL에서의 테이블 최적화:

1
2
3
4
5
-- 테이블 재구성 (단편화 제거)
OPTIMIZE TABLE orders, order_items, products;

-- 또는 InnoDB 테이블의 경우
ALTER TABLE orders ENGINE=InnoDB;

PostgreSQL에서의 테이블 최적화:

1
2
3
4
5
-- 테이블 완전 재구성
VACUUM FULL orders;

-- 또는 정기적인 자동 청소 (덜 방해적)
VACUUM ANALYZE orders;

통계 업데이트
쿼리 최적화기는 통계 정보를 기반으로 실행 계획을 수립한다. 오래된 통계는 비효율적인 실행 계획을 초래할 수 있다.
MySQL에서의 통계 업데이트:
1 2
-- 테이블 통계 업데이트 ANALYZE TABLE orders, order_items, products;
PostgreSQL에서의 통계 업데이트:
1 2
-- 테이블 통계 업데이트 ANALYZE orders;

미사용 인덱스 식별 및 제거
미사용 인덱스는 쓰기 작업의 성능을 저하시키고 디스크 공간을 낭비한다.

MySQL에서 미사용 인덱스 식별:

1
SELECT * FROM sys.schema_unused_indexes;

PostgreSQL에서 미사용 인덱스 식별:

1
2
3
4
5
6
7
8
9
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

데이터 아카이빙 및 정리
오래된 데이터는 아카이빙하고, 불필요한 데이터는 정리하여 데이터베이스 크기를 관리한다.

단계적 데이터 아카이빙:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 1. 아카이브 테이블 생성
CREATE TABLE orders_archive LIKE orders;

-- 2. 오래된 데이터를 아카이브 테이블로 이동
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

-- 3. 원본 테이블에서 아카이브된 데이터 삭제
DELETE FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

로그 및 임시 데이터 정리
로그 테이블과 임시 데이터는 빠르게 커질 수 있으며 정기적인 정리가 필요하다.

1
2
3
4
5
6
-- 오래된 로그 삭제
DELETE FROM system_logs
WHERE log_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);

-- 임시 테이블 비우기
TRUNCATE TABLE temp_import_data;

자동화된 유지보수 스크립트

유지보수 작업은 자동화하여 정기적으로 실행하는 것이 좋다.

MySQL 유지보수 스크립트 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#!/bin/bash

# 데이터베이스 연결 정보
DB_USER="admin"
DB_PASS="password"
DB_NAME="myapp"

# 미사용 테이블 공간 정리
echo "Optimizing tables…"
mysql -u$DB_USER -p$DB_PASS $DB_NAME -e "
  SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';')
  FROM information_schema.tables
  WHERE table_schema = '$DB_NAME'
  AND engine = 'InnoDB';" | grep -v CONCAT | mysql -u$DB_USER -p$DB_PASS

# 통계 업데이트
echo "Updating statistics…"
mysql -u$DB_USER -p$DB_PASS $DB_NAME -e "
  SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
  FROM information_schema.tables
  WHERE table_schema = '$DB_NAME';" | grep -v CONCAT | mysql -u$DB_USER -p$DB_PASS

# 오래된 로그 삭제
echo "Cleaning old logs…"
mysql -u$DB_USER -p$DB_PASS $DB_NAME -e "
  DELETE FROM system_logs
  WHERE log_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);"

echo "Maintenance completed."

유지보수 일정 수립

유지보수 작업은 시스템 부하가 적은 시간에 실행하는 것이 좋다.

유지보수 작업	권장 주기	비고
통계 업데이트	매일	데이터 변경이 많은 경우 더 자주
인덱스 재구성	주간	쓰기 작업이 많은 경우 더 자주
미사용 인덱스 확인	월간	개발 주기에 맞춰 조정
데이터 아카이빙	월간/분기	데이터 증가 속도에 따라 조정
전체 데이터베이스 점검	분기	대규모 유지보수 작업 포함

Slow-Query Logging and Regular Monitoring

슬로우 쿼리 로깅의 중요성

슬로우 쿼리 로깅은 성능 최적화의 첫 번째 단계이다. 이를 통해 성능 문제를 일으키는 쿼리를 식별하고 최적화할 수 있다.

슬로우 쿼리 로깅 설정

MySQL에서의 슬로우 쿼리 로깅 설정:

1
2
3
4
5
6
7
-- 슬로우 쿼리 로깅 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1.0; -- 1초 이상 걸리는 쿼리 로깅

-- 인덱스를 사용하지 않는 쿼리도 로깅 (선택 사항)
SET GLOBAL log_queries_not_using_indexes = 'ON';

PostgreSQL에서의 슬로우 쿼리 로깅 설정:

1
2
3
4
5
6
7
-- postgresql.conf 파일 설정
log_min_duration_statement = 1000  -- 1000ms(1초) 이상 걸리는 쿼리 로깅
log_statement = 'none'             -- 모든 SQL 문 로깅하지 않음
log_duration = off                 -- 실행 시간만 로깅하지 않음
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_destination = 'csvlog'         -- CSV 형식으로 로깅
logging_collector = on             -- 로그 파일로 저장

슬로우 쿼리 분석

슬로우 쿼리 로그를 분석하여 최적화가 필요한 쿼리를 식별한다.

MySQL용 pt-query-digest 도구 사용:

`1`	`pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt`

PostgreSQL용 pgBadger 도구 사용:

`1`	`pgbadger /var/log/postgresql/postgresql-10-main.log -o slow_query_report.html`

쿼리 성능 모니터링 도구

지속적인 쿼리 성능 모니터링을 위한 도구는 다음과 같다:

Prometheus + Grafana: 시계열 데이터 수집 및 시각화
MySQL Enterprise Monitor: MySQL 전용 모니터링 도구
pganalyze: PostgreSQL 성능 모니터링 도구
New Relic / Datadog: 애플리케이션 및 데이터베이스 통합 모니터링
PMM (Percona Monitoring and Management): MySQL, MongoDB, PostgreSQL 모니터링

성능 메트릭 모니터링

다음 메트릭을 정기적으로 모니터링해야 한다:

쿼리 실행 시간: 평균 및 최대 실행 시간
쿼리 실행 빈도: 가장 자주 실행되는 쿼리
테이블 스캔: 인덱스 없이 수행되는 전체 테이블 스캔
캐시 적중률: 쿼리 캐시, 버퍼 풀 적중률
락 대기 시간: 락으로 인한 대기 시간
임시 테이블 사용: 디스크 기반 임시 테이블 생성
연결 수: 활성 및 대기 연결 수

대시보드 구성 예시

Grafana와 Prometheus를 사용한 데이터베이스 모니터링 대시보드는 다음과 같은 패널을 포함할 수 있다:

쿼리 성능 개요:
- 초당 쿼리 수
- 평균 쿼리 실행 시간
- 슬로우 쿼리 비율
리소스 사용량:
- CPU 사용률
- 메모리 사용률
- 디스크 I/O
- 연결 수
쿼리 분석:
- 상위 10개 슬로우 쿼리
- 인덱스 사용 현황
- 테이블 스캔 빈도
캐시 성능:
- 버퍼 풀 적중률
- 쿼리 캐시 적중률
- 테이블 캐시 적중률

알림 설정

중요한 성능 문제가 발생할 때 알림을 받을 수 있도록 설정한다:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
# Prometheus 알림 규칙 예시
groups:
- name: DatabaseAlerts
  rules:
  - alert: SlowQueriesIncreasing
    expr: rate(mysql_global_status_slow_queries[5m]) > 10
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "슬로우 쿼리 증가"
      description: "최근 5분 동안 초당 10개 이상의 슬로우 쿼리가 발생했습니다."
      
  - alert: HighConnectionUsage
    expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "높은 연결 사용률"
      description: "데이터베이스 연결 사용률이 80%를 초과했습니다."

Database Replication for Redundancy and Enhanced Read Performance

데이터베이스 복제의 개념

데이터베이스 복제는 하나의 데이터베이스(마스터)의 데이터를 다른 데이터베이스(복제본)에 복사하여 동기화하는 프로세스이다. 이를 통해 데이터 중복성을 확보하고 읽기 작업을 분산시킬 수 있다.

복제 구성 유형

마스터-슬레이브 복제:
- 모든 쓰기 작업은 마스터에서 수행
- 읽기 작업은 마스터와 슬레이브에 분산
- 슬레이브는 마스터의 변경 사항을 비동기적으로 복제
마스터-마스터 복제:
- 양방향 복제로 두 서버 모두에서 쓰기 가능
- 복잡성 증가 및 충돌 가능성
- 특정 사용 사례에만 권장
다중 슬레이브 구성:
- 하나의 마스터와 여러 슬레이브
- 읽기 작업을 여러 슬레이브에 분산
- 지리적으로 분산된 배포 지원

MySQL 복제 설정

마스터 서버 설정:

1
2
3
4
5
6
# my.cnf
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = myapp  # 복제할 데이터베이스 지정

슬레이브 서버 설정:

1
2
3
4
5
6
# my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log_bin = mysql-bin
read_only = 1  # 슬레이브를 읽기 전용으로 설정

복제 설정 명령:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 마스터 서버에서 복제 사용자 생성
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- 마스터 상태 확인
SHOW MASTER STATUS;
-- 출력 결과에서 File 및 Position 값 기록

-- 슬레이브 서버에서 복제 설정
CHANGE MASTER TO
  MASTER_HOST='master_server_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001', -- 마스터 상태에서 확인한 File 값
  MASTER_LOG_POS=123;                -- 마스터 상태에서 확인한 Position 값

-- 슬레이브 시작
START SLAVE;

-- 슬레이브 상태 확인
SHOW SLAVE STATUS\G

PostgreSQL 복제 설정

마스터 서버 설정 (postgresql.conf):

1
2
3
4
# 복제 설정
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 32

마스터 서버 설정 (pg_hba.conf):

1
2
# 복제 연결 허용
host    replication     repl_user    slave_server_ip/32     md5

슬레이브 서버 설정:

1
2
# 초기 데이터 복사
pg_basebackup -h master_server_ip -D /var/lib/postgresql/data -U repl_user -P -v -X stream

슬레이브 서버 설정 (recovery.conf):

1
2
3
4
# 복제 설정
standby_mode = 'on'
primary_conninfo = 'host=master_server_ip port=5432 user=repl_user password=password'
trigger_file = '/tmp/postgresql.trigger'

애플리케이션에서의 복제 활용

복제 구성을 최대한 활용하려면 애플리케이션에서 읽기 및 쓰기 작업을 적절하게 분리해야 한다.

Java/Spring Boot 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        
        ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();
        
        Map<Object, Object> dataSources = new HashMap<>();
        dataSources.put("master", masterDataSource);
        dataSources.put("slave", slaveDataSource);
        
        routingDataSource.setTargetDataSources(dataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        
        return routingDataSource;
    }
}

// 트랜잭션 타입에 따라 데이터 소스 선택
public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? "slave" : "master";
    }
}

애플리케이션 속성 설정:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
spring:
  datasource:
    master:
      url: jdbc:mysql://master-db:3306/myapp
      username: app_user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      url: jdbc:mysql://slave-db:3306/myapp
      username: app_user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

서비스 계층 구현:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
@Service
public class ProductService {
    private final ProductRepository productRepository;
    
    // 생성자...
    
    // 읽기 전용 트랜잭션 (슬레이브 데이터 소스 사용)
    @Transactional(readOnly = true)
    public List<Product> getAllProducts() {
        return productRepository.findAll();
    }
    
    // 쓰기 트랜잭션 (마스터 데이터 소스 사용)
    @Transactional
    public Product createProduct(Product product) {
        return productRepository.save(product);
    }
}

복제 모니터링 및 관리

복제 환경을 안정적으로 운영하려면 다음 사항을 모니터링해야 한다:

복제 지연(Replication Lag): 마스터와 슬레이브 간의 데이터 동기화 지연 시간
복제 오류: 복제 프로세스 중 발생하는 오류
연결 상태: 마스터와 슬레이브 간의 연결 상태
디스크 공간: 특히 바이너리 로그 및 릴레이 로그 크기

MySQL 복제 모니터링 쿼리:

1
2
3
4
5
6
7
-- 슬레이브 상태 확인
SHOW SLAVE STATUS\G

-- 주요 확인 사항:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 복제 지연 시간 (초)

PostgreSQL 복제 모니터링 쿼리:

1
2
3
4
5
-- 복제 상태 확인
SELECT * FROM pg_stat_replication;

-- 복제 지연 확인
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

복제 장애 대응

복제 장애 발생 시 대응 방법이다:

슬레이브 재동기화: 복제가 중단된 경우 슬레이브를 재동기화

1
2
3
4
5
6
7
8
9
-- MySQL 슬레이브 재동기화
STOP SLAVE;
CHANGE MASTER TO
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;
START SLAVE;

-- PostgreSQL 슬레이브 재동기화
pg_basebackup -h master_server_ip -D /var/lib/postgresql/data -U repl_user -P -v -X stream

마스터 장애 대응(장애 조치, Failover): 마스터 서버 장애 시 슬레이브를 마스터로 승격

1
2
3
4
5
6
7
-- MySQL 슬레이브를 마스터로 승격
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;

-- PostgreSQL 슬레이브를 마스터로 승격
touch /tmp/postgresql.trigger  -- recovery.conf에 지정된 트리거 파일 생성

Database Sharding for Data Distribution

샤딩의 개념

샤딩은 대규모 데이터베이스를 더 작고 관리하기 쉬운 조각(샤드)으로 분할하는 기술이다. 각 샤드는 별도의 서버에 호스팅되어 부하를 분산시킨다.

샤딩 전략

수평 샤딩(Horizontal Sharding): 동일한 스키마를 가진 여러 서버에 행을 분산

1
2
3
서버 1: 고객 ID 1-1000
서버 2: 고객 ID 1001-2000
서버 3: 고객 ID 2001-3000

수직 샤딩(Vertical Sharding): 테이블 또는 스키마를 여러 서버에 분산

1
2
3
서버 1: 고객, 주문 테이블
서버 2: 제품, 인벤토리 테이블
서버 3: 결제, 배송 테이블

디렉토리 기반 샤딩: 중앙 디렉토리를 사용하여 데이터 위치 추적

샤딩 키 선택

샤딩 키는 데이터를 어떤 샤드에 배치할지 결정하는 중요한 요소이다.

좋은 샤딩 키의 특성:

균등한 데이터 분포
최소한의 크로스-샤드 쿼리
확장성 고려

일반적인 샤딩 키:

고객 ID
지리적 위치
날짜/시간
해시 값

샤딩 구현 방법

애플리케이션 레벨 샤딩:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public class ShardingDataSource {
    private final Map<Integer, DataSource> shards = new HashMap<>();

    public ShardingDataSource() {
        // 샤드 데이터 소스 초기화
        shards.put(0, createDataSource("jdbc:mysql://shard0:3306/myapp"));
        shards.put(1, createDataSource("jdbc:mysql://shard1:3306/myapp"));
        shards.put(2, createDataSource("jdbc:mysql://shard2:3306/myapp"));
    }

    public DataSource getShardForCustomerId(long customerId) {
        // 고객 ID를 샤드 번호로 매핑 (모듈로 샤딩)
        int shardNumber = (int) (customerId % shards.size());
        return shards.get(shardNumber);
    }

    private DataSource createDataSource(String url) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername("user");
        config.setPassword("password");
        return new HikariDataSource(config);
    }
}

@Service
public class CustomerService {
    private final ShardingDataSource shardingDataSource;

    // 생성자...

    public Customer getCustomerById(long customerId) {
        // 적절한 샤드 결정
        DataSource shard = shardingDataSource.getShardForCustomerId(customerId);

        // 선택한 샤드에서 고객 조회
        JdbcTemplate jdbcTemplate = new JdbcTemplate(shard);
        return jdbcTemplate.queryForObject(
            "SELECT * FROM customers WHERE id = ?",
            new Object[]{customerId},
            (rs, rowNum) -> new Customer(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getString("email")
            )
        );
    }
}

샤딩 미들웨어 사용:
여러 데이터베이스 시스템은 샤딩을 지원하는 미들웨어를 제공한다:

MySQL: MySQL Cluster, Vitess
PostgreSQL: Citus, Postgres-XL
MongoDB: 내장 샤딩 기능
범용: ShardingSphere, Apache GORA

Vitess 설정 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# vitess 토폴로지 설정
topo_cell: local
topo_implementation: etcd2
topo_global_root: /vitess/global
topo_global_server_address: localhost:2379

# keyspace 정의 (MySQL의 데이터베이스에 해당)
keyspaces:
  - name: customer_keyspace
    sharding_strategy: customer_id_hash
    replica_count: 2
    rdonly_count: 1

# 샤드 정의
shards:
  - name: "0"
    db_name_override: "vt_customer_0000000000"
    key_range:
      start: ""
      end: "8000000000000000"
  - name: "1"
    db_name_override: "vt_customer_8000000000"
    key_range:
      start: "8000000000000000"
      end: ""

클라우드 기반 샤딩 서비스:
많은 클라우드 제공업체가 자동 샤딩을 지원하는 분산 데이터베이스 서비스를 제공한다:
- Amazon Aurora
- Google Cloud Spanner
- Azure Cosmos DB

샤딩의 과제와 해결 방법

크로스-샤드 쿼리:
- 문제: 여러 샤드에 걸친 데이터 조회 필요
- 해결책: 샤드 간 조인 최소화, 쿼리 팬아웃, 결과 집계
분산 트랜잭션:
- 문제: 여러 샤드에 걸친 트랜잭션 일관성
- 해결책: 2단계 커밋 프로토콜, 분산 트랜잭션 관리자
스키마 변경:
- 문제: 여러 샤드에서 일관된 스키마 변경 관리
- 해결책: 롤링 업데이트, 스키마 버전 관리
샤드 재분배:
- 문제: 데이터 증가에 따른 샤드 재조정 필요
- 해결책: 일관성 해시, 온라인 마이그레이션 도구
ID 생성:
- 문제: 분산 환경에서 고유 ID 생성
- 해결책: UUID, Snowflake ID, 중앙 ID 생성기

Use of Profiling Tools in Database Management

프로파일링 도구의 중요성

데이터베이스 프로파일링 도구는 성능 병목 지점을 식별하고 최적화 기회를 발견하는 데 필수적이다.

주요 데이터베이스 프로파일링 도구

MySQL 프로파일링 도구:

Performance Schema: MySQL 내장 모니터링 시스템
MySQL Enterprise Monitor: Oracle의 상용 모니터링 도구
Percona Toolkit: 다양한 성능 분석 도구 모음
MySQL Workbench: 쿼리 실행 계획 분석 도구

Performance Schema 활성화:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- Performance Schema 활성화
SET GLOBAL performance_schema = ON;

-- 특정 이벤트 모니터링 활성화
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- 주요 쿼리 통계 확인
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT, MAX_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

PostgreSQL 프로파일링 도구:

pg_stat_statements: 쿼리 실행 통계 수집
auto_explain: 쿼리 실행 계획 자동 로깅
pgBadger: 로그 기반 성능 분석 도구
pg_stat_activity: 활성 세션 모니터링

pg_stat_statements 활성화:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- postgresql.conf 설정
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 확장 모듈 생성
CREATE EXTENSION pg_stat_statements;

-- 쿼리 통계 확인
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

범용 데이터베이스 프로파일링 도구:
- Datadog: 클라우드 기반 모니터링 서비스
- New Relic: 애플리케이션 및 데이터베이스 성능 모니터링
- SolarWinds Database Performance Analyzer: 데이터베이스 성능 분석
- Prometheus + Grafana: 오픈 소스 모니터링 스택

애플리케이션 레벨 SQL 모니터링

애플리케이션에서 SQL 쿼리를 모니터링하는 방법이다:

Java/Spring Boot에서의 SQL 로깅:

1
2
3
4
# application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace

P6Spy를 사용한 고급 SQL 모니터링:

1
2
3
4
5
6
<!-- Maven 의존성 -->
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

1
2
3
# application.properties
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/myapp

1
2
3
4
# spy.properties
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=Time: %(executionTime)ms | SQL: %(sqlSingleLine)

실행 계획 분석

쿼리 실행 계획은 데이터베이스가 쿼리를 어떻게 실행할지 보여주는 로드맵이다.

MySQL 실행 계획 분석:

1
2
3
4
5
6
7
8
EXPLAIN SELECT c.name, o.order_date, SUM(oi.price * oi.quantity) as total
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.country = 'USA' AND o.order_date > '2023-01-01'
GROUP BY c.id, o.id
ORDER BY total DESC
LIMIT 10;

PostgreSQL 실행 계획 분석:

1
2
3
4
5
6
7
8
EXPLAIN ANALYZE SELECT c.name, o.order_date, SUM(oi.price * oi.quantity) as total
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.country = 'USA' AND o.order_date > '2023-01-01'
GROUP BY c.id, o.id
ORDER BY total DESC
LIMIT 10;

실행 계획에서 주의해야 할 사항:

테이블 스캔(Full Table Scan): 대용량 테이블에서 인덱스 없이 전체 스캔
비효율적인 조인 방식: 특히 중첩 루프 조인(Nested Loop Join)
인덱스 미사용: 인덱스가 있음에도 사용되지 않는 경우
임시 테이블 생성: 정렬이나 그룹화에 임시 테이블 사용
비효율적인 정렬: 대용량 데이터의 외부 정렬

실시간 모니터링 및 알림

지속적인 모니터링과 문제 발생 시 자동 알림은 사전 예방적 관리에 중요하다.

프로메테우스 모니터링 설정 예시:

1
2
3
4
5
6
7
8
9
# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']
    
  - job_name: 'postgresql'
    static_configs:
      - targets: ['postgres-exporter:9187']

그라파나 대시보드 예시:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
  "title": "Database Performance Dashboard",
  "panels": [
    {
      "title": "Query Response Time",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "rate(mysql_global_status_questions[5m])",
          "legendFormat": "Queries/sec"
        }
      ]
    },
    {
      "title": "Slow Queries",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "rate(mysql_global_status_slow_queries[5m])",
          "legendFormat": "Slow queries/sec"
        }
      ]
    }
    // 추가 패널 생략
  ]
}

용어 정리

용어	설명
연결 타임아웃(Connection Timeout)	클라이언트가 Connection Pool에서 새로운 연결을 요청했을 때, 해당 요청이 처리될 수 있는 최대 대기 시간을 의미한다. 이 시간이 초과되면 예외(Exception)가 발생하며, 클라이언트는 실패 메시지를 받게 된다. 사용 목적 - 과도한 대기 방지: 요청이 너무 오래 대기하지 않도록 설정하여 사용자 경험을 개선. - 자원 관리: 풀에서 사용 가능한 연결이 부족할 때 적절히 실패 처리를 제공.
유휴 타임아웃(Idle Timeout)	Connection Pool에서 사용되지 않는 상태로 대기 중인 연결이 제거되기까지의 시간을 의미한다. 일정 시간 동안 유휴 상태인 연결은 풀에서 제거되어 자원을 회수한다. 사용 목적 - 자원 절약: 사용되지 않는 연결을 제거하여 메모리와 네트워크 자원을 절약. - 풀 크기 유지: 최소 커넥션 수(`minimum-idle`)를 유지하면서 불필요한 커넥션 제거.
최대 수명(Max Lifetime)	Connection Pool에서 생성된 각 연결이 유지될 수 있는 최대 시간을 의미한다. 이 시간이 초과되면 해당 연결은 풀에서 제거되고 새로 생성된다. 사용 목적 - 오래된 연결 제거: 장시간 유지된 연결이 네트워크 변경이나 데이터베이스 상태 변화에 영향을 받을 수 있으므로 이를 방지. - 성능 최적화: 주기적으로 새 연결을 생성하여 최신 상태를 유지.
정규화(Normalization)	데이터 중복을 최소화하고 데이터 무결성을 보장하기 위해 데이터를 여러 작은 테이블로 분리하는 과정이다. 관계형 데이터베이스에서 데이터가 정상 형태(Normal Forms)를 만족하도록 설계된다. 주요 목표 - 데이터 중복 제거: 동일한 데이터가 여러 곳에 저장되지 않도록 함. - 데이터 무결성 유지: 삽입, 삭제, 갱신 시 데이터 불일치 방지. - 저장 공간 최적화: 중복 데이터를 제거하여 스토리지 사용량 감소. 정규화 단계 - 1NF (First Normal Form): 각 컬럼이 원자값(Atomic Value)을 가져야 함. - 2NF (Second Normal Form): 부분 종속성 제거 (모든 비키 속성은 기본 키에 전적으로 종속). - 3NF (Third Normal Form): 이행 종속성 제거 (비키 속성이 다른 비키 속성에 종속되지 않음). - BCNF (Boyce-Codd Normal Form): 모든 결정자가 후보 키여야 함. - 4NF, 5NF: 다중 값 종속성과 조인 종속성을 제거.
비정규화(Denormalization)	성능 최적화를 위해 정규화된 데이터베이스에 일부 데이터 중복을 의도적으로 추가하는 과정이다. 주로 읽기 성능 향상을 위해 사용되며, JOIN 연산을 줄이고 데이터를 더 빠르게 검색할 수 있도록 한다. 주요 목표 - 쿼리 성능 향상: 복잡한 JOIN 연산을 줄여 읽기 작업 속도를 개선. - 데이터 접근 단순화: 필요한 데이터를 한 번의 조회로 가져올 수 있도록 설계.

Databases#

Connection Pooling: Reducing Connection Overhead#

커넥션 풀링의 개념과 중요성#

실제 구현 예시#

성능 향상 효과#

Optimizing Connection Pool Settings#

주요 최적화 매개변수#

최적 설정 가이드라인#

모니터링 및 조정 전략#

Efficient Database Indexing#

인덱스의 역할과 중요성#

인덱스 유형#

효과적인 인덱스 전략#

인덱스 관리 및 모니터링#

Fine-Tuning ORM Queries#

ORM의 일반적인 성능 문제#

N+1 문제 해결#

ORM 쿼리 최적화 기법#

ORM 성능 모니터링#

Optimizing Data Retrieval with Lazy Loading, Eager Loading, and Batch Processing#

지연 로딩(Lazy Loading)#

즉시 로딩(Eager Loading)#

배치 로딩(Batch Loading)#

상황별 최적 로딩 전략#

Efficient Pagination for Large Datasets#

전통적인 페이지네이션의 문제점#

커서 기반 페이지네이션#

키셋 페이지네이션#

스트리밍 API#

Data Optimization: Avoid Select * Queries and Fetch Only Required Columns#

SELECT * 사용의 문제점#

필요한 컬럼만 선택하는 방법#

대용량 테이블에서의 영향#

성능 개선 사례#

Denormalizing Database Schema for Read-Heavy Workloads and Reducing Join Operations#

정규화와 비정규화의 이해#

비정규화 전략#

비정규화 구현 방법#

비정규화의 장단점#

Optimizing Join Operations and Avoiding Unnecessary Joins#

조인 작업의 성능 영향#

조인 유형과 성능 특성#

조인 최적화 기법#

서브쿼리 vs. 조인#

조인 최적화 모니터링#

Regular Maintenance and Cleanup of Data#

데이터 유지보수의 중요성#

주요 유지보수 작업#

자동화된 유지보수 스크립트#

유지보수 일정 수립#

Slow-Query Logging and Regular Monitoring#

슬로우 쿼리 로깅의 중요성#

슬로우 쿼리 로깅 설정#

슬로우 쿼리 분석#

쿼리 성능 모니터링 도구#

성능 메트릭 모니터링#

대시보드 구성 예시#

알림 설정#

Database Replication for Redundancy and Enhanced Read Performance#

데이터베이스 복제의 개념#

복제 구성 유형#

MySQL 복제 설정#

PostgreSQL 복제 설정#

애플리케이션에서의 복제 활용#

복제 모니터링 및 관리#

복제 장애 대응#

Database Sharding for Data Distribution#

샤딩의 개념#

샤딩 전략#

샤딩 키 선택#

샤딩 구현 방법#

샤딩의 과제와 해결 방법#

Use of Profiling Tools in Database Management#

프로파일링 도구의 중요성#

주요 데이터베이스 프로파일링 도구#

애플리케이션 레벨 SQL 모니터링#

실행 계획 분석#

실시간 모니터링 및 알림#

용어 정리#

참고 및 출처#

Databases

Connection Pooling: Reducing Connection Overhead

커넥션 풀링의 개념과 중요성

실제 구현 예시

성능 향상 효과

Optimizing Connection Pool Settings

주요 최적화 매개변수

최적 설정 가이드라인

모니터링 및 조정 전략

Efficient Database Indexing

인덱스의 역할과 중요성

인덱스 유형

효과적인 인덱스 전략

인덱스 관리 및 모니터링

Fine-Tuning ORM Queries

ORM의 일반적인 성능 문제

N+1 문제 해결

ORM 쿼리 최적화 기법

ORM 성능 모니터링

Optimizing Data Retrieval with Lazy Loading, Eager Loading, and Batch Processing

지연 로딩(Lazy Loading)

즉시 로딩(Eager Loading)

배치 로딩(Batch Loading)

상황별 최적 로딩 전략

Efficient Pagination for Large Datasets

전통적인 페이지네이션의 문제점

커서 기반 페이지네이션

키셋 페이지네이션

스트리밍 API

Data Optimization: Avoid Select * Queries and Fetch Only Required Columns

`SELECT *` 사용의 문제점

필요한 컬럼만 선택하는 방법

대용량 테이블에서의 영향

성능 개선 사례

Denormalizing Database Schema for Read-Heavy Workloads and Reducing Join Operations

정규화와 비정규화의 이해

비정규화 전략

비정규화 구현 방법

비정규화의 장단점

Optimizing Join Operations and Avoiding Unnecessary Joins

조인 작업의 성능 영향

조인 유형과 성능 특성

조인 최적화 기법

서브쿼리 vs. 조인

조인 최적화 모니터링

Regular Maintenance and Cleanup of Data

데이터 유지보수의 중요성

주요 유지보수 작업

자동화된 유지보수 스크립트

유지보수 일정 수립

Slow-Query Logging and Regular Monitoring

슬로우 쿼리 로깅의 중요성

슬로우 쿼리 로깅 설정

슬로우 쿼리 분석

쿼리 성능 모니터링 도구

성능 메트릭 모니터링

대시보드 구성 예시

알림 설정

Database Replication for Redundancy and Enhanced Read Performance

데이터베이스 복제의 개념

복제 구성 유형

MySQL 복제 설정

PostgreSQL 복제 설정

애플리케이션에서의 복제 활용

복제 모니터링 및 관리

복제 장애 대응

Database Sharding for Data Distribution

샤딩의 개념

샤딩 전략

샤딩 키 선택

샤딩 구현 방법

샤딩의 과제와 해결 방법

Use of Profiling Tools in Database Management

프로파일링 도구의 중요성

주요 데이터베이스 프로파일링 도구

애플리케이션 레벨 SQL 모니터링

실행 계획 분석

실시간 모니터링 및 알림

용어 정리

참고 및 출처