MariaDB 성능 최적화 및 모니터링

1️⃣ slow_query_log 활성화 및 분석

MariaDB에서 slow_query_log는 실행 시간이 오래 걸린 쿼리를 기록하는 기능입니다. 이를 통해 성능이 저조한 쿼리를 찾아 최적화할 수 있습니다.

slow_query_log 활성화

slow_query_log를 활성화하려면 MariaDB 설정 파일인 my.cnf에 다음 항목을 추가합니다.

예시:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

위 설정은 실행 시간이 2초 이상 걸리는 쿼리를 /var/log/mysql/slow-query.log 파일에 기록합니다.

쿼리 분석

mysqldumpslow 명령어를 사용하여 기록된 느린 쿼리 로그를 분석할 수 있습니다.

예시:

mysqldumpslow -s t /var/log/mysql/slow-query.log

이 명령어는 느린 쿼리 로그에서 가장 많이 실행된 쿼리를 출력합니다.

2️⃣ SHOW PROCESSLIST를 활용한 실시간 쿼리 모니터링

SHOW PROCESSLIST 명령어를 사용하면 현재 실행 중인 쿼리 및 연결 상태를 실시간으로 확인할 수 있습니다. 이를 통해 쿼리의 상태를 모니터링하고, 시스템 부하를 파악할 수 있습니다.

SHOW PROCESSLIST 사용 예시

SHOW PROCESSLIST;

이 명령어는 현재 MariaDB 서버에서 실행 중인 쿼리 목록을 반환합니다. 각 항목은 다음과 같은 정보를 포함합니다:

• Id: 쿼리의 ID
• User: 쿼리를 실행한 사용자
• Host: 클라이언트의 IP 주소
• Command: 실행 중인 명령어 (예: Query, Sleep)
• Time: 쿼리가 실행된 시간 (초 단위)
• State: 현재 쿼리의 상태
• Info: 실행 중인 쿼리 내용

실시간 모니터링

SHOW PROCESSLIST를 주기적으로 실행하여 시스템 상태를 점검할 수 있습니다. 또한, WHERE 조건을 추가하여 특정 쿼리나 사용자를 필터링할 수 있습니다.

예시:

SHOW PROCESSLIST WHERE State = 'Locked';

이 쿼리는 Locked 상태인 쿼리만 필터링하여 보여줍니다.

3️⃣ INNODB STATUS를 이용한 성능 분석

INNODB STATUS 명령어는 InnoDB 스토리지 엔진의 상태를 확인하는 데 유용합니다. 이 명령을 사용하면 데이터베이스 성능과 관련된 중요한 정보를 확인할 수 있습니다.

INNODB STATUS 사용 예시

SHOW ENGINE INNODB STATUS;

이 명령어는 InnoDB의 현재 상태 및 통계를 출력합니다. 여기에는 버퍼 풀 사용 상태, 트랜잭션 정보, 잠금 상태 등이 포함됩니다. 특히, TRANSACTIONS 섹션은 현재 진행 중인 트랜잭션에 대한 중요한 정보를 제공합니다.

예시 분석:

----------------------------
TRANSACTIONS
----------------------------
Trx id counter 1 239
Purge done for trx's n:o < 1 238  trx id

이 부분에서는 InnoDB에서 진행 중인 트랜잭션의 상태를 확인할 수 있습니다.

4️⃣ Connection Pooling 개념 및 활용 (max_connections, thread_cache_size)

데이터베이스에서 많은 연결을 효율적으로 처리하기 위해서는 Connection Pooling을 활용하는 것이 중요합니다. 이를 통해 연결을 재사용하여 성능을 개선할 수 있습니다.

max_connections

max_connections는 MariaDB 서버가 허용하는 최대 연결 수를 설정하는 변수입니다. 이 값을 너무 높게 설정하면 시스템 자원을 과다하게 사용할 수 있기 때문에 적절한 값으로 설정해야 합니다.

예시:

[mysqld]
max_connections = 200

이 설정은 MariaDB에서 동시에 허용할 수 있는 최대 연결 수를 200으로 설정합니다.

thread_cache_size

thread_cache_size는 MariaDB가 사용한 스레드를 캐시하는 크기입니다. 이 값을 적절히 설정하면 새로운 연결을 위해 스레드를 생성하는 비용을 줄일 수 있습니다.

예시:

[mysqld]
thread_cache_size = 8

이 설정은 8개의 스레드를 캐시하여, 연결 요청 시 스레드를 재사용할 수 있게 합니다.

5️⃣ MariaDB 캐시 최적화 (query_cache_size, table_open_cache)

MariaDB에서 캐시는 쿼리 성능을 향상시키는 중요한 역할을 합니다. query_cache_size와 table_open_cache를 조정하면 성능을 최적화할 수 있습니다.

query_cache_size

query_cache_size는 쿼리 결과를 캐시할 수 있는 메모리 크기를 설정합니다. 쿼리 캐시가 활성화되면 동일한 쿼리가 다시 실행될 때 캐시된 결과를 반환하여 성능을 개선할 수 있습니다.

예시:

[mysqld]
query_cache_size = 64M

이 설정은 MariaDB에서 쿼리 결과를 64MB까지 캐시하도록 설정합니다.

table_open_cache

table_open_cache는 MariaDB가 동시에 열 수 있는 테이블 수를 설정합니다. 테이블을 자주 여는 작업이 많은 시스템에서 이 값을 적절히 설정하면 성능을 개선할 수 있습니다.

예시:

[mysqld]
table_open_cache = 2000

이 설정은 MariaDB에서 한 번에 열 수 있는 테이블 수를 2000으로 설정합니다.

6️⃣ 데이터베이스 파티셔닝 및 샤딩 전략

데이터베이스 파티셔닝과 샤딩은 큰 데이터베이스의 성능을 향상시키고 관리성을 높이는 중요한 기법입니다. 두 가지 방법을 사용하여 데이터 분산 및 성능 개선을 도모할 수 있습니다.

파티셔닝

파티셔닝은 대용량 테이블을 작은 여러 파티션으로 나누어 데이터 처리 성능을 향상시키는 기법입니다. 이를 통해 데이터를 더 효율적으로 저장하고, 쿼리 성능을 개선할 수 있습니다.

예시:

CREATE TABLE orders (
  order_id INT,
  order_date DATE,
  amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p0 VALUES LESS THAN (2000),
  PARTITION p1 VALUES LESS THAN (2010),
  PARTITION p2 VALUES LESS THAN (2020)
);

이 예시는 orders 테이블을 연도별로 파티셔닝하여, 데이터를 효율적으로 분배합니다.

샤딩

샤딩은 데이터베이스를 여러 서버에 분산하여 저장하는 방법입니다. 이를 통해 대규모 트래픽을 처리할 수 있으며, 데이터의 확장성을 높일 수 있습니다.

샤딩을 구현하는 방법은 복잡하지만, 보통 데이터의 키를 기준으로 분산하여 데이터를 나누게 됩니다. 예를 들어, 고객 ID를 기준으로 데이터를 여러 서버에 분산할 수 있습니다.

이와 같은 성능 최적화 및 모니터링 기법들을 활용하면 MariaDB의 성능을 개선하고, 데이터베이스의 효율성을 극대화할 수 있습니다.