[MySQL] 파티셔닝(Partitioning) 적용 (3) 성능 테스트

안녕하세요 안젤라입니다 😇

오늘은 파티셔닝 성능 테스트에 대해서 포스팅을 준비했습니다.

테스트 시나리오

환경
- macOS 13.1 22C65, Apple M1 Pro
- 32GB Memory
- Mysql 5.7.39
테스트 데이터 갯수
- 50만개, 100만, 200만, 400만, 450만, 500만
- 같은 파티션 내의 데이터 조회 (=같은 날짜)

테스트 1 : 한개의 파티션에 50-400만건 데이터 넣고 속도 측정

50만건

Duration	Query
0.33381200	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-02 23:59:59'
0.36677500	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%'

100만건

Duration	Query
0.76859900	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-02 23:59:59'
0.68901500	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%'

200만건

Duration	Query
1.41036700	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-02 23:59:59'
1.38261900	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%'

400만건

Duration	Query
2.51236800	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-02 23:59:59'
2.75301500	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/category/K1%'

테스트 2 : 파티셔닝 한 테이블 / 파티셔닝 하지 않은 테이블에 총 200만건의 데이터를 넣고 같은 index를 넣고 같은 쿼리 속도 측정

Table	Size (MB)	비고
tb_api_log	571.16	파티셔닝 된 테이블
tb_api_log_nopart	533.03	파티셔닝 안된 테이블

데이터 200만건 (한달 약 16만건)

tb_api_log partition 추정치 및 인덱스

TABLE_NAME	PARTITION_NAME	POSITION	PART_DESC	TABLE_ROWS	AVG_ROW_LENGTH
tb_api_log	p202301	1	738917	169249	139
tb_api_log	p202302	2	738945	151427	142
tb_api_log	p202303	3	738976	169954	139
tb_api_log	p202304	4	739006	166320	142
tb_api_log	p202305	5	739037	166301	142
tb_api_log	p202306	6	739067	167208	135
tb_api_log	p202307	7	739098	169406	139
tb_api_log	p202308	8	739129	170875	138
tb_api_log	p202309	9	739159	161055	140
tb_api_log	p202310	10	739190	171997	137
tb_api_log	p202311	11	739220	161229	140
tb_api_log	p202312	12	739251	169312	139
tb_api_log	pmax	13	MAXVALUE	0	0

-- auto-generated definition
CREATE TABLE tb_api_log
(
    id           bigint UNSIGNED AUTO_INCREMENT COMMENT 'API 로그 ID',
    type         varchar(3)                         NOT NULL COMMENT 'API 타입 (S: 송신, R: 수신, E: 에러)',
    target       varchar(10)                        NOT NULL COMMENT '요청 타겟 (core, api)',
    method       varchar(10)                        NOT NULL COMMENT '요청자 메소드',
    url          varchar(255)                       NOT NULL COMMENT '요청자 주소',
    ip           varchar(50)                        NOT NULL COMMENT '요청자 IP',
    content      json                               NULL COMMENT '요청 또는 처리에 대한 내용 (JSON 형식)',
    created_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'API 로그 등록 시간',
    PRIMARY KEY (id, created_date)
)
    COMMENT 'API 통신로그' CHARSET = utf8mb4 PARTITION BY RANGE (TO_DAYS(created_date));

CREATE INDEX idx_ip
    ON tb_api_log (ip);

CREATE INDEX idx_method
    ON tb_api_log (method);

CREATE INDEX idx_target
    ON tb_api_log (target);

CREATE INDEX idx_type
    ON tb_api_log (type);

CREATE INDEX idx_url
    ON tb_api_log (url);

tb_api_log_nopart 인덱스
-- auto-generated definition CREATE TABLE tb_api_log_nopart ( id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY, type varchar(3) NULL, target varchar(10) NULL, method varchar(10) NULL, url varchar(255) NULL, ip varchar(50) NULL, content json NULL, created_date datetime NULL ); CREATE INDEX idx_created_date ON tb_api_log_nopart (created_date); CREATE INDEX idx_url ON tb_api_log_nopart (url);

Table	Duration	Query
tb_api_log	0.16619200	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log	0.15283100	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log_nopart	0.46422600	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log_nopart	0.41117000	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'

데이터 총 800만건 (한달 약 60만건)

tb_api_log partition 추정치 및 인덱스

TABLE_NAME	PARTITION_NAME	POSITION	PART_DESC	TABLE_ROWS	AVG_ROW_LENGTH
tb_api_log	p202301	1	738917	671041	138
tb_api_log	p202302	2	738945	606732	137
tb_api_log	p202303	3	738976	670269	138
tb_api_log	p202304	4	739006	660764	139
tb_api_log	p202305	5	739037	670945	136
tb_api_log	p202306	6	739067	654073	138
tb_api_log	p202307	7	739098	657783	139
tb_api_log	p202308	8	739129	673198	139
tb_api_log	p202309	9	739159	651297	136
tb_api_log	p202310	10	739190	680745	138
tb_api_log	p202311	11	739220	646668	137
tb_api_log	p202312	12	739251	679686	138
tb_api_log	pmax	13	MAXVALUE	0	0

-- auto-generated definition
CREATE TABLE tb_api_log
(
    id           bigint UNSIGNED AUTO_INCREMENT COMMENT 'API 로그 ID',
    type         varchar(3)                         NOT NULL COMMENT 'API 타입 (S: 송신, R: 수신, E: 에러)',
    target       varchar(10)                        NOT NULL COMMENT '요청 타겟 (core, api)',
    method       varchar(10)                        NOT NULL COMMENT '요청자 메소드',
    url          varchar(255)                       NOT NULL COMMENT '요청자 주소',
    ip           varchar(50)                        NOT NULL COMMENT '요청자 IP',
    content      json                               NULL COMMENT '요청 또는 처리에 대한 내용 (JSON 형식)',
    created_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'API 로그 등록 시간',
    PRIMARY KEY (id, created_date)
)
    COMMENT 'API 통신로그' CHARSET = utf8mb4 PARTITION BY RANGE (TO_DAYS(created_date));

CREATE INDEX idx_ip
    ON tb_api_log (ip);

CREATE INDEX idx_method
    ON tb_api_log (method);

CREATE INDEX idx_target
    ON tb_api_log (target);

CREATE INDEX idx_type
    ON tb_api_log (type);

CREATE INDEX idx_url
    ON tb_api_log (url);

tb_api_log_nopart 인덱스
-- auto-generated definition CREATE TABLE tb_api_log_nopart ( id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY, type varchar(3) NULL, target varchar(10) NULL, method varchar(10) NULL, url varchar(255) NULL, ip varchar(50) NULL, content json NULL, created_date datetime NULL ); CREATE INDEX idx_created_date ON tb_api_log_nopart (created_date); CREATE INDEX idx_url ON tb_api_log_nopart (url);

Query_ID	Duration	Query
tb_api_log	0.61454400	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log_nopart	4.83330800	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log	0.63064100	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'
tb_api_log_nopart	4.85918700	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-03-01 00:00:00' and '2023-03-30 23:59:59'

3월1일 - 4월30일 (2달치) / 1월1일 - 12월31일 (1년치) 데이터 조회

Query_ID	Duration	Query
tb_api_log_nopart	5.39927600	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-04-30 23:59:59'
tb_api_log	2.21097300	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-04-30 23:59:59'
tb_api_log_nopart	6.11229100	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-12-31 23:59:59'
tb_api_log	7.08853300	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-12-31 23:59:59'
tb_api_log	6.53073700	select id, type, target, method, url, ip, content, created_date from tb_api_log where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-12-31 23:59:59'
tb_api_log_nopart	6.21193000	select id, type, target, method, url, ip, content, created_date from tb_api_log_nopart where url like '%/home/product/simple/N171401NN02%' and created_date BETWEEN '2023-01-01 00:00:00' and '2023-12-31 23:59:59'

오늘도 조금 더 성장하는 하루가 되었길 바랄게요 🙏

'DB > MySQL' 카테고리의 다른 글

[MySQL] 파티셔닝(Partitioning) 적용 (2) 새로운 테이블 생성할때 함께 파티셔닝 설정하기 (0)	2024.01.23
[MySQL] 파티셔닝(Partitioning) 적용 (1) 기존의 테이블을 변경하기 - 연도,월,일별 파티셔닝 (0)	2023.03.27
mysql 복원 및 백업 (0)	2017.10.27
(Windows / zip버전) Mysql 설치 [1] 다운로드 (0)	2017.08.03
(Windows / msi버전) Mysql 설치 [2] toad 설정 (0)	2017.08.03

Angella's Programming

[MySQL] 파티셔닝(Partitioning) 적용 (3) 성능 테스트

테스트 시나리오

테스트 1 : 한개의 파티션에 50-400만건 데이터 넣고 속도 측정

테스트 2 : 파티셔닝 한 테이블 / 파티셔닝 하지 않은 테이블에 총 200만건의 데이터를 넣고 같은 index를 넣고 같은 쿼리 속도 측정

'DB > MySQL' 카테고리의 다른 글

티스토리툴바

« 2025/02 »
일	월	화	수	목	금	토
						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

테스트 시나리오

테스트 1 : 한개의 파티션에 50-400만건 데이터 넣고 속도 측정

테스트 2 : 파티셔닝 한 테이블 / 파티셔닝 하지 않은 테이블에 총 200만건의 데이터를 넣고 같은 index를 넣고 같은 쿼리 속도 측정

'DB > MySQL' 카테고리의 다른 글

검색

티스토리툴바