MySQL에서 PostgreSQL 데이터베이스 마이그레이션 : 사례 연구

MySQL 데이터베이스를 PostgreSQL로 마이그레이션하는 것은 스키마 변환, 데이터 전송, 쿼리 최적화 및 성능 튜닝과 관련된 복잡한 프로세스입니다. 이 사례 연구에서는 MySQL 8.0에서 PostgreSQL 15로 전자 상거래 플랫폼 데이터베이스의 실제 마이그레이션을 문서화합니다.이 연구에는 마이그레이션 전후의 성능 벤치 마크가 포함됩니다.

프로젝트 개요

마이그레이션 성능 벤치 마크

마이그레이션 전에 MySQL 8.0을 사용하여 주요 성능 메트릭을 수집했습니다. sysbench 그리고 EXPLAIN ANALYZE.

1. 성능 읽기 (쿼리 선택)

• 간단한 선택 (1m 행) = 120ms
• 쿼리 (10m 행) = 450ms에 가입하십시오
• 집계 쿼리 (sum) = 320ms
• JSON 쿼리 (JSON_EXTRACT) = 600ms

2. 성능 쓰기 (쿼리 삽입)

• 단일 줄 삽입 = 10ms
• 벌크 삽입 (10k 행) = 850ms

마이그레이션 단계

스키마 변환

MySQL 및 PostgreSQL에서 스키마 변환에는 안전한 유형 매핑, DBMS (Database Management System) 간의 SQL 구문 차이와 관련하여 안전한 유형 매핑, 기본값 변환 및 기타 열 속성이 포함됩니다.

MySQL에서 :

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated DATETIME DEFAULT '0000-00-00 00:00:00',
    comment LONGTEXT,
    ...
    rawdata JSON
);

postgresql에서 :

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total_price NUMERIC(10,2) NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated TIMESTAMP DEFAULT NULL,
  comment TEXT,
  ...
  rawdata JSONB
);

주요 변경

• INT AUTO_INCREMENT 변환됩니다 SERIAL
• DATETIME 변환됩니다 TIMESTAMP
• DECIMAL 변환됩니다 NUMERIC
• DEFAULT '0000-00-00 00:00:00' 변환됩니다 DEFAULT NULL
• JSON 변환됩니다 JSONB
• LONGTEXT 변환됩니다 TEXT

데이터 마이그레이션

스키마 및 데이터 마이그레이션 모두에 MySQL-to-PostgresQL 변환기를 사용했습니다. 이는 대량 읽기 및 쓰기 데이터의 고속 멀티 스레딩 기술로 인해 효율적인 데이터 전송 옵션입니다.

마이그레이션 시간: 120GB의 데이터의 경우 ~ 3.5 시간
데이터 손실: 0% (마이그레이션 후 검증 된 행 카운트)

트리거 마이그레이션

MySQL 및 PostgreSQL 지원 트리거이지만 구문 및 구조는 다릅니다. PostgreSQL에는 직접 내장 트리거 로직 대신 별도의 트리거 기능이 필요합니다.

MySQL 버전 :

DELIMITER //
CREATE TRIGGER before_order_insert 
BEFORE INSERT ON orders 
FOR EACH ROW 
BEGIN
    SET NEW.created_at = NOW();
END;
//
DELIMITER ;

Postgresql 동등한 :

CREATE OR REPLACE FUNCTION before_order_insert_function() RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION before_order_insert_function();

MySQL을 PostgreSQL 마이그레이션에 검증합니다

MySQL 데이터베이스가 PostgreSQL로 마이그레이션되면 유효성 검사를 통해 데이터 무결성, 쿼리 정확도 및 응용 프로그램 호환성이 보장됩니다.

1. 스키마 검증. 스키마가 올바르게 마이그레이션되었는지 확인하여 다음을 확인하십시오.

• 테이블, 열 및 데이터 유형이 일치합니다.
  • MySQL에서 : SHOW CREATE TABLE my_table; 
  • postgresql에서 :

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_name="my_table";

• 인덱스, 제약 및 관계는 손상되지 않습니다.
  • MySQL에서 : SHOW INDEX FROM my_table;
  • postgresql에서 : SELECT indexname, indexdef FROM pg_indexes WHERE tablename="my_table";

2. 데이터 무결성 검증. 부패없이 모든 기록이 올바르게 마이그레이션되었는지 확인하십시오.

• 쿼리를 실행하여 행 카운트 비교 SELECT COUNT(*) FROM my_table; 소스 및 대상 테이블에 대해.
• 체크섬 검증
  • MySQL에서 : SELECT MD5(GROUP_CONCAT(column1, column2 ORDER BY id)) FROM my_table;
  • postgresql에서 : SELECT MD5(string_agg(column1 || column2, ',' ORDER BY id)) FROM my_table;

3. 쿼리/뷰 유효성 검사. MySQL에서 PostgreSQL 마이그레이션 후 뷰를 확인하는 유일한 방법은 두 DBM의 SQL 방언 간의 차이와 관련하여 소스 및 대상 데이터베이스에서 각보기의 선택 진술을 비교하는 것입니다. 이 작업에는 데이터베이스 프로그래밍에 대한 깊은 지식이 필요하며 일부 규칙은이 기사에서 탐색됩니다.

MySQL은 쿼리를 사용하여 데이터베이스의 모든 뷰 목록을 노출시킵니다.

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

PostgreSQL은 쿼리를 통해 동일하게 수행 할 수 있습니다.

SELECT table_name FROM INFORMATION_SCHEMA.views;

마이그레이션 후 최적화

다음으로 중요한 단계는 PostgreSQL을 성능을 최적화하는 것입니다. PostgreSQL 및 MySQL은 다른 최적화 기술을 가지고 있으므로 PostgreSQL을 튜닝하면 시스템이 효율적으로 실행되도록합니다.

1. MySQL과 비교하여 관련 쿼리 속도를 4 배 향상시키는 JSON 검색의 Gin Index :

     CREATE INDEX idx_orders_json ON orders USING GIN(rawdata);

2. PostgreSQL에서 사용 가능한 병렬 쿼리 실행 :

     SET max_parallel_workers_per_gather = 4;

3. 메모리 및 캐시 설정 조정 :

     shared_buffers = 4GB  work_mem = 64MB  effective_cache_size = 12GB

4. 더 나은 쿼리 성능을 위해 큰 테이블을 분할했습니다.

     CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

5. 최적화 된 진공 및 분석.

마이그레이션 후 성능 벤치마킹

1. 쿼리 성능을 선택하십시오

2. 쿼리 성능을 삽입하십시오

요약

• PostgreSQL이 조인, 집계 및 JSON 쿼리에서 MySQL을 크게 능가했습니다.
• 벌크 인서트에 대한 쓰기 성능은 개선되었지만 MVCC (Multi-Version Concurrency Control)로 인해 단일 줄 인서트가 약간 느 렸습니다.

내 이전 기사를 놓친 경우 :