MySQL vs PostgreSQL

Mysql, PostgreSQL 에 각각 2,073,677 건의 데이터를 넣고 테스트를 진행하였다.

DB 성능 분석을 할것이다.

 

똑같은 환경 똑같은 테스트코드로 진행할 예정이다.

또한, hibernate 에서 제공해주는 성능 체크를 할 예정이고, 프로덕션 코드가 아닌 단지 성능을 체크하기 위해 만들었기 때문에, hibernate 선택하여 진행하였다.

• git hub 저장소 : https://github.com/final-idea-rush/db-performance-check

테이블 구조

데이터는 1차 가공을 통해 사용하였습니다.

데이터 출처 : https://www.data.go.kr/data/15096283/standard.do

 

동일하게 2,073,677 건의 데이터로 진행할 예정이다.

전체 조회

Mysql

Execution time for findAll: 15366ms
Number of Queries Executed: 1.0
2023-08-07T21:06:08.308+09:00  INFO 13392 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    24600 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    2253700 nanoseconds spent preparing 1 JDBC statements;
    4116328200 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    20900 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

mysql 일 경우 → 15366ms즉, 약 15초 정도 걸렸다.

PostgreSQL

Execution time for findAll: 11688ms
Number of Queries Executed: 1.0
2023-08-07T21:42:19.072+09:00  INFO 22248 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    397300 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    100000 nanoseconds spent preparing 1 JDBC statements;
    2794180300 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    32500 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

PostgreSQL 일 경우 → 11688ms즉, 약 11초 정도 걸렸다.

단건 조회

Mysql

Execution time for findAll: 2544ms
Number of Queries Executed: 1.0
2023-08-07T21:12:49.307+09:00  INFO 9420 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    400500 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    2137700 nanoseconds spent preparing 1 JDBC statements;
    2383923800 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    23800 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

mysql 일 경우 → 2544ms 즉, 약 2초 정도 걸렸다.

PostgreSQL

Execution time for findAll: 219ms
Number of Queries Executed: 2.0
2023-08-07T21:42:19.308+09:00  INFO 22248 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    13600 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    107100 nanoseconds spent preparing 1 JDBC statements;
    191744400 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    4600 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

PostgreSQL 일 경우 → 219ms즉, 약 0.219초 정도 걸렸다.

like 조회

Mysql

Execution time for findAll: 2816ms
Number of Queries Executed: 1.0
2023-08-07T21:18:57.887+09:00  INFO 20788 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    36800 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    1910300 nanoseconds spent preparing 1 JDBC statements;
    2683487600 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    16200 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

mysql 일 경우 → 2816ms 즉, 약 2초 정도 걸렸다.

PostgreSQL

Execution time for findAll: 236ms
Number of Queries Executed: 3.0
2023-08-07T21:42:19.548+09:00  INFO 22248 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    13500 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    94400 nanoseconds spent preparing 1 JDBC statements;
    228424500 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    4600 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

PostgreSQL 일 경우 → 236ms즉, 약 0.236초 정도 걸렸다.

단건조회 + index

Mysql

Execution time for findAll: 134ms
Number of Queries Executed: 1.0
2023-08-07T21:23:03.963+09:00  INFO 12264 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    443200 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    2291800 nanoseconds spent preparing 1 JDBC statements;
    923100 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    17300 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

mysql 일 경우 → 134ms즉, 약 0.134초 정도 걸렸다.

PostgreSQL

Execution time for findAll: 133ms
Number of Queries Executed: 1.0
2023-08-07T21:24:03.012+09:00  INFO 7576 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    27400 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    151400 nanoseconds spent preparing 1 JDBC statements;
    3492900 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    17800 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

PostgreSQL 일 경우 → 133ms즉, 약 0.133초 정도 걸렸다.

이로써 알수 있는점

약 207만건 데이터를 테스트 조회 해본결과 join 까지는 안해봤지만, 기본적으로 조회 기능을 이용하였을때는

성능적으로 PostgreSQL 이 좀더 좋다는 성능 결과가 나왔다.

테스트 해보면서 놀랐다..

쓰기 부분이다

차례대로 1만, 10만, 100만 으로 차례대로 해볼예정이다.

한번에 1만 10만 100만 들어올때 시간을 체크 하였다.

PostgreSQL 부분이다.

1만건 - 약 1초

Execution time for findAll: 1617ms
Number of Queries Executed: 0.0
2023-08-07T22:56:40.899+09:00  INFO 21884 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    15100 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    6566600 nanoseconds spent preparing 10000 JDBC statements;
    1215003800 nanoseconds spent executing 10000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

10만건 - 약 17초

Execution time for findAll: 17861ms
Number of Queries Executed: 0.0
2023-08-07T22:53:56.728+09:00  INFO 21884 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    344100 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    120624400 nanoseconds spent preparing 100000 JDBC statements;
    12974553200 nanoseconds spent executing 100000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

100만건 - 약 2분 42초

Execution time for findAll: 162229ms
Number of Queries Executed: 0.0
2023-08-07T22:56:39.275+09:00  INFO 21884 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    14700 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    696795500 nanoseconds spent preparing 1000000 JDBC statements;
    124038918300 nanoseconds spent executing 1000000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

결과

1만건 : 약 1초

10만건 : 약 17초

100만건 : 약 2분 42초

MySQL 부분이다.

1만건 - 약 2초

Execution time for findAll: 2085ms
Number of Queries Executed: 0.0
2023-08-07T22:51:00.888+09:00  INFO 7376 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    13500 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    117660700 nanoseconds spent preparing 10000 JDBC statements;
    1406728000 nanoseconds spent executing 10000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

10만건 - 약 24초

Execution time for findAll: 24683ms
Number of Queries Executed: 0.0
2023-08-07T22:47:20.586+09:00  INFO 7376 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    473300 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    1429092600 nanoseconds spent preparing 100000 JDBC statements;
    15771510700 nanoseconds spent executing 100000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

100만건 - 약 3분 29초

Execution time for findAll: 208652ms
Number of Queries Executed: 0.0
2023-08-07T22:50:58.713+09:00  INFO 7376 --- [           main] i.StatisticalLoggingSessionEventListener : Session Metrics {
    15000 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    12131579300 nanoseconds spent preparing 1000000 JDBC statements;
    141517436700 nanoseconds spent executing 1000000 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

결과

1만건 : 약 2초

10만건 : 약 24초

100만건 : 약 3분 29초

총 결과 - 읽기

구분	PostgreSLQ	MySQL
전체 조회	약 11초11688ms	약 15초15366ms
단건 조회	약 0.219초219ms	약 2초2544ms
like 조회	약 0.236초236ms	약 2초2816ms
idex 단건 조회	약 0.133초133ms	약 0.134초134ms

총 결과 - 쓰기

구분	PostgreSQL	MySQL
1만건	약 1초1617ms	약 2초2085ms
10만건	약 17초17861ms	약 24초24683ms
100만건	약 2분 42초162229ms	약 3분 29초208652ms