PostgreSQL Performance Test - goddes4/python-study-wiki GitHub Wiki

Python Connector 설치

# pip install psycopg2

테스트 코드

import psycopg2
import time

p_list = []
current_time = time.time()
for i in range(100000):
    p_list.append(
        (
            "Joshuaxx{}".format(i), "layoutyy{}".format(i),
            "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890",
            "12345678901234567890123456789012345678901234567890", "nfc", current_time,
            "layoutkkk{}".format(i),
            "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890",
            "12345678901234567890123456789012345678901234567890", "nfc", current_time
        ))

conn = psycopg2.connect(dbname='test', user='postgres', host='localhost', password='rainus!@#')
cur = conn.cursor()
cur_time = time.time()

cur.executemany('INSERT INTO t_product (pr_code, layout_id, pr_info, page, nfc, last_modified) '
                'VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (pr_code) '
                'DO UPDATE SET layout_id=%s, pr_info=%s, page=%s, nfc=%s, last_modified=%s', p_list)
diff_time = time.time() - cur_time

print('diff time : ', diff_time)
cur.close()
conn.commit()
conn.close()

10만개 데이터 입력

insert = 7.5s
insert conflict on = 10s
insert update rule = 17s

각각 다른 테이블에 10만건 입력

1 stire : 11s
2 store : 11s
3 store : 11s
4 store : 12s
5 store : 12~15s
6 store : 17~18s
7 store : 19~21s
8 store : 21~23s

※ 같은 테이블에 데이터를 동시에 쓸경우 ACID 보장을 때문에 RowExclusiveLock 걸림

Lock 확인

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation=t.relid order by relation asc;

접속된 사용자 확인

SELECT datname, usename, client_addr, client_port, application_name FROM pg_stat_activity;

1000만건 테스트 데이터 테이블 생성

CREATE TABLE t AS 
    SELECT
        gs as idx,
        '테스트 문자열' || gs AS test_string,
        md5(random()::text) AS random_string
    FROM
        generate_series(1, 1000000) AS gs;

결과 : 1000만건 12초

시간 단위로 테스트 데이터 테이블 생성

CREATE TABLE t2 AS 
    SELECT
        (ROW_NUMBER() OVER()) as idx,
        md5(random()::text) AS random_string,
        gs AS test_date
    FROM
        generate_series('2015-01-01 00:00'::timestamp, '2015-01-01 23:00'::timestamp, '1 hours') AS gs;

파일과 테이블 사이간 데이터를 복사

COPY t_product to '/home/test/product.csv';
COPY t_product(pr_code, pr_info) TO '/home/test/product.csv' WITH DELIMITER ',' CSV HEADER;

COPY t_product(pr_code, pr_info) FROM '/home/test/product.csv' WITH DELIMITER ',' CSV HEADER;