본 게시글은
서울대학교 데이터사이언스 대학원 이상원 교수님의
데이터사이언스 응용을 위한 빅데이터 및 지식관리시스템 수업을
학습을 목적으로 재구성하였습니다
이번 시간은 실습 + 과제 시간이었다
benchbase를 이용하여 TPC-C라는 유명 benchmark 예제를 활용해
postgreSQL을 실습하는 수업이었다
본격적으로 실습 시작 전에
benchmark는?
benchbase는?
TPC-C는?
어떤 개념인지 간단하게 살펴보자
Benchmark라는 말은 정말 많이 들어봤을 것이다
나도 이전회사에서 자연어 모델을 다룰 때
처음 들어봤던 용어였던 것 같다
benchmark는 특정 시스템, 제품, 프로세스의 성능을
측정하고 평가하는 과정을 말한다
어떤 주어진 workload scenario 아래에서
성능을 측정한다
따라서 다양한 프로세스(?)에는 benchmark가 있는데
DB에서는 크게
OLTP, OLAP, HTAP의 성능을 측정하는 benchmark가 있고
그 중에서도 transaction의 성능을 측정하는
OLTP benchmark에서는
TPC-B, TPC-C가 있다
TPC-B는 은행의 사례로 simulation 한 것이고
TPC-C는 온라인 쇼핑몰과 같은 사례를 simulation 한 것이다
본 수업에서 우리가 실습용으로 사용할 것은
TPC-C이다
OLTP의 성능을 측정하는 metrics들에 대해서
대표적인 것들만 소개를 하고 넘어가겠다
TPS(Transactions Per Second)라고 해서
초당 처리할 수 있는 transaction의 개수이다
CPU Utilization은 DB가
CPU 자원을 얼마나 사용하는지를 평가하는 항목이다
OLTP에서는 CPU가 transaction에 매우 중요한 역할을 하고
CPU에 병목이 없어야 높은 TPS를 달성할 수 있다
Memory Usage는 transaction 수행 시
RAM, 즉 main memory의 사용량을 평가하는 항목이다
Cache hit ratio는 DB가 SSD가 아니고
캐시에서 데이터를 찾는 비율이다
캐시 적중율이 높으면 속도가 빨라진다
I/O Latency
디스크에서 데이터 I/O가 발생할 때
걸리는 시간을 측정하는 항목이다
마지막으로는 Read/Write speed로
초당 데이터 읽기 및 쓰기 속도를 말한다
OLTP benchmark는 각각의 항목을 이용하여
해당 DB의 OLTP 성능을 측정한다
우리 수업에서는 해당 실습과 과제를
OLTP benchmark의 TPC-C를 활용하여 수행하는데
이걸 편하게 해주는 오픈소스가 바로 benchbase이다
Benchbase는 다양한 데이터베이스를
benchmark 해줄 수 있게하는 오픈소스 툴인데
원래는 OLTP-bench라는 프로젝트에서 파생되었다
computer science로 매우 유명한 cmu에서도
개발에 참여했다고한다
https://github.com/cmu-db/benchbase
https://db.cs.cmu.edu/projects/benchbase/
https://github.com/cmu-db/benchbase/wiki/TPC-C
더 자세한 내용이 궁금하다면
위 링크들을 참고하면 될 듯 하다
따라서 우리 수업에서는
benchmark와 TPC-C workload를 이해하고
TPC-C의 데이터를 PostgreSQL에 넣어서
해당 TPC-C table을 이용해서 실습을 수행한다
본 실습을 위해서
postgreSQL + benchbase가 설치되어있는
docker image를 제공해주었다
따라서 해당 image를 build하고 docker를 실행해서
docker 환경내부에서 PostgreSQL로 작업을 진행할 수 있었다
이렇게 docker를 실행해서 postgreSQL 환경을 설정한 다음
benchbase를 통해 TPC-C table을
postgreSQL에 넣어줘야한다
이걸 하기 위해서는 config file을 조금 수정한 다음
jar파일을 실행시켜주면 benchbase가 실행이 되고
TPC-C table이 우리의 postgreSQL에 들어갈 수 있었다
본 실습에서 수정해야하는 config는
scalefactor와 time이었다
sample_tpcc_config.xml이라는 파일을
경로에 들어가 찾아서
scalefactor와 time으로 태깅되어있는 부분을 찾아 수정해준다
그런다음 benchbase.jar을 우리가 수정한
sampel_tpcc_config.xml을 반영해서 실행시켜줘야한다
benchbase.jar 파일의 경로를 찾은 다음
위와 같은 방식으로 명령어를 수행시켜주면 된다
뒤에 --create=true --load=true --execute=true는 옵션들인데
table을 만들고, load하고, 쿼리 수행도 가능하게 한다는 옵션이라고한다
java -jar benchbase.jar -b tpcc -c config /root/benchbase/target/benchbase-
postgres/config/postgres/sample_tpcc_config.xml--create=true --load=true --execute=true
우리 실습에서 제공한 benchbase.jar 실습 명령어는 위와 같다
해당 명령어를 제대로 실행했다면
성공했다는 output이 뜰 것이다
그런 다음 postgreSQL에 다시 접속해서
table과 사이즈를 보여주는 /d+를 입력한다
원래 아무것도 없었던 postgreSQL에
이렇게 TPC-C table이 예쁘게 들어간 것을 확인할 수 있다
TPC-C는 앞서 말했듯이
온라인 쇼핑몰을 simulation 한 것이다
해당 스키마에 대한 자세한 설명은
아래 항목들을 참고하면 좋을 것 같다
TPC-C Query
TPC-C Requirements
− https://tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf
TPC-C description
https://sp.ts.fujitsu.com/dmsp/publications/public/benchmark_overview_tpc-c.pdf
간단하게 TPC-C scenario에 대해 설명하겠다
크게 transaction으로는
New-Order, Payment, Order-Status, Delivery, Stock-Level
5가지가 존재한다
concurrency는 동시성에 관한 내용인데
여러 명의 사용자들이 동시에 주문을 생성하고
배송을 처리하는 환경을 시뮬레이션한다
또, TPC-C는 실제 온라인 쇼핑몰을 반영하여
WAREHOUSE, DISTRICT, CUSTOMER, ORDERS, ITEM과 같은
table들을 갖고있다
TPC-C Table에 대해 잠깐 살펴보자
https://github.com/cmu-db/benchbase/wiki/TPC-C
TPC C
Multi-DBMS SQL Benchmarking Framework via JDBC. Contribute to cmu-db/benchbase development by creating an account on GitHub.
github.com
위 url에 접속하면 자세한 schema를 더 볼 수 있다
oorder이라는 테이블이 있는데
각 order들에 대한 기본적인 정보를 담은 table이다
누가, 언제, 얼마나 많은 물건을 주문했는지가 담겨있다고한다
한개의 row는 한개의 order를 의미한다
new_order은 주문이 완료되었지만
아직 배송이 되지 않은 주문들을 담은 테이블이다
new order transaction이 완료되면 생성된다
그러고 delivery에 선택되면 해당 테이블에서
해당 order를 담은 row는 삭제된다
order_line은 각 order에서 주문된
item에 대한 자세한 내용을 담은 테이블이다
무엇이 주문되었는지, 양, 가격 등이 담겨있다
각 order의 item 정보를 담은 테이블이므로
각 row는 item을 의미한다
이제 해당 배경지식을 바탕으로
본 수업에서 SQL 쿼리 실습을 진행하였다
총 질문은 10개이며
해당 질문을 보고 우리가 SQL을 작성하면 된다
Q1) I_DATA 컬럼에 "original"이라는 단어가 포함된 상품의 개수를 구하세요.
1번부터 5번까지는 조교님이 정답을 제시해주셨지만
답을 보지않고 처음에는 진행해보았다
질문 자체가 어떤 특징을 가진 "상품"의 개수를 구하는 것이므로
상품 정보를 담은 테이블을 우선 체크해줬다
SELECT * FROM item;
I_DATA라는 column이 있는지 확인해보려고
item table 전체를 스캔했다
전체 개수가 몇 개인지는 모르겠지만
(많아보임)
아무튼 이렇게 item들에 관한 table이 있고
i_data라는 column이 있는 것을 확인했다
확인해보니 i_data는 varchar로 구성된 column같으므로
LIKE를 이용해서 original이라는 단어가
포함되었는지를 확인하면 될 것 같았다
대충 table을 확인했을 때
ORIGINAL이라고 대문자가 포함되어있는 것을 확인할 수 있었다
그래도 소문자도 포함되어있을 수 있으니
대소문자 구분하지않고 찾게하는
ILIKE를 사용하여 쿼리하였다
SELECT * FROM item WHERE i_data ILIKE '%original%';
결과
굉장히 많이 출력되었다
이제 문제에서 "개수"를 구하라고 했으므로
앞에 *대신에 COUNT(*)로 수행해준다
예전에 회사에서 일할 땐 귀찮아서 뭐 확인할 때 그냥
SELECT * 를 해줬는데
이번 DB수업 배우면서 쓸데없는 column까지 가져오는걸
최소화하라는 말을 들어서
i_data column만 가지고 COUNT를 수행해줬다
(성능에 크게 영향을 미치는지는 모르겠다)
SELECT COUNT(i_data) FROM item WHERE i_data ILIKE '%original%';
총 10016개가 나오는 것을 확인할 수 있었다
ppt에 올라와있는 정답 쿼리이다
COUNT까지는 기밀이라 알려주고싶지 않으셨나보다
Q2) 재고 수량이 20 미만인 품목의 정보(I_ID, I_NAME, S_QUANTITY)를 10개만 조회하세요.
이번에는 재고 수량이 20개 미만인 품목을 조회하는 문제이다
table이름을 보다가 stock 테이블에
재고 수량과 같은 column이 있나 싶어 조회를 해보았다
SELECT * FROM stock;
테이블이 위와같이 출력되는 것을 확인했고
s_i_id, s_quantity와 같은 정보들이 있는 것을 확인했다
stock 테이블을 위에서 봤던 item 테이블과 join하는데
s_i_id = i_id로 해주면 될 것 같았다
거기에다가 s_quantity < 20을 한 다음 LIMIT 10을 걸어줘야겠다고 생각했다
SELECT i.i_id, i.i_name, s.s_quantity
FROM stock s
JOIN item i ON i.i_id = s.s_i_id
WHERE s.s_quantity < 20
LIMIT 10;
이쁘게 우리가 원하는 결과가 나오는 것을 확인했다
Q3) 각 지역별로 배송되지 않은 주문(new_order) 수를 구하세요. (GROUP BY)
이번에는 지역별로 배송되지 않은 주문의 수를 구하는 문제이다
GROUP BY를 쓰라는 알음다운 힌트까지있다
우선은 new_order라는 table 정보부터 조회하였다
d_id가 district_id인 것으로 추측했다
위에서 new_order는 아직 배송이 되지 않은 주문목록이라 했으므로
해당 테이블에서 GROUPBY를 한 다음
no_o_id의 COUNT를 구해주면 될 것 같았다
SELECT no_d_id, COUNT(no_o_id)
FROM new_order
GROUP BY no_d_id;
이렇게 각 district_id와 함께 조회해주니
어떤 district_id가 몇개가 있는지 알음답게 조회가 되었다
ppt에서 정답을 보니 마지막에 ORDER BY도 있었는데
뭐 사실 d_id를 기준으로 정렬한건지
count를 기준으로 정렬한건지는 잘 모르겠다
하지만 문제에 뭐 정렬을 하라는 말은 없었으므로
귀찮아서 생략해줬다
Q4) 각 지역(DISTRICT)별로 고객들의 평균 지불 금액(C_YTD_PAYMENT)을 구하세요. (GROUP BY + AVG)
각 지역별로 고객들의 평균 지불 금액을 구하는 문제이다
문제에 또 친절하게 GROUP BY와 AVG를 사용하라고 힌트가 주어졌다
사실 TPC-C 스키마 설명을 정확하게 안읽어서
customer가 회원가입한 회원인지?
아니면 주문을 한 번이라도 한 회원인지?
(주문을 한 번이라도 한 회원을 customer라고 한다면
해당 테이블에 누적 주문금액같은게 있다고 생각)
그게 궁금했는데 일단 customer 테이블을 조회해봤다
(백문이불여일견)
이런 구조로 생긴 table인데
attribute로 c_ytd_payment가 존재하고
어떤 금액을 나타내는 것 같았다
그래서 그냥 이 테이블에서
c_d_id로 GROUP BY를 걸어서
c_ytd_payment에 AVG 걸어주면 된다고 생각했다
SELECT c_d_id, AVG(c_ytd_payment)
FROM customer
GROUP BY c_d_id;
평균의 결과가 아름답게 나왔다
이번에도 ppt 정답에는 ORDER BY가 있는데 생략...해줬다
Q5) 가장 많이 주문된 상품 5개를 수량 기준으로 조회하세요. (GROUP BY + 집계)
가장 많이 주문된 상품을 조회하기 위해서
우선 order_line이라는 테이블을 조회해봤다
그냥 조회했더니 row가 너무 많아서
10개만 끊어서 조회했다
구조를 간단하게 살펴보니 order_line table은
한 개의 order_id에 주문된 item들이 각 row였고
몇 개를 주문했는지 quantity column이 들어가있었다
그래서 이걸로 item_id로 GROUPBY를 한 다음
quantity를 모두 더하고 ORDER BY로 가장 높은 것부터 나열한 후
5개를 잘라야겠다고 생각했다
SELECT ol_i_id AS item_id, SUM(ol_quantity) AS total_amout
FROM order_line
GROUP BY ol_i_id
ORDER BY SUM(ol_quantity) DESC
LIMIT 5;
이렇게 결과가 나오는 것을 확인할 수 있었다
Q6) 최근 30일 이내 주문된 건 중 배송 여부(배송일 존재 여부)를 함께 조회하세요. (LEFT OUTER JOIN)
여기서부터는 ppt 정답이 없어서
내가 짠 쿼리가 정답이라고는 확신을 못한다
최근 30일 이내에 주문된 건 중에서
배송여부를 "함께" 조회하는 쿼리이다
배송여부=배송일존재여부인데
배송일이 존재하는 것만 조회하라는게 아니고
배송일 존재 여부를 함께 조회하라고 했기 때문에
NULL이더라도 조회가 되어야하는 LEFT OUTER JOIN을 이용해야한다
우선 모든 order를 담은 oorder table을 조회해줬다
o_id와 o_entry_d로 오더 날짜가 있는 것을 확인할 수 있었다
30일 이내라는 기간을 설정해야했으므로
o_entry_d의 데이터 타입을 확인해줬다
확인해보니 timestamp로 저장이 되어있는 것을 확인할 수 있었다
그래서 30일 이내로 쿼리를 하려면
WHERE o_entry_d >= NOW() - INTERVALS '30 days'
와 같이 조건을 걸어야했다
사실 배송일여부? 배송일을 담은 테이블이있나?를 많이 고민했는데
이것저것 조회하다보니 order_line에 delivery_d가 있는 것을 확인할 수 있었다
그래서 oorder에 order_line을 LEFT JOIN해서
ol_delivery_d가 있으면 표기해주고 없으면 NULL인걸로 해주기로했다
(정답인지는 몰루)
처음에는 그냥
SELECT oo.o_id AS order_id, ol.ol_delivery_d AS delivery_date
FROM oorder oo
LEFT OUTER JOIN order_line ol ON oo.o_id = ol.ol_i_id
WHERE oo.o_entry_d >= NOW() - INTERVAL '30 days';
이렇게 해줬는데
중복된 order가 반환되는 결과가 발생했다
기준이 되는 table인 oorder에는 중복된 order row가 없지만
join을 하는 테이블인 order_line에 order_id가 중복된 row가 있어서 이렇게 뜨는 것이었다
따라서 이것저것 구글링하다가
DISTINCT ON을 사용하면 해당 column에 대해서 중복된게 있으면
첫 번째의 행만 반환한다고 한다
따라서 아래와 같이 쿼리를 수정해서 작성해줬다
SELECT DISTINCT ON(oo.o_id)
oo.o_id AS order_id, ol.ol_delivery_d AS delivery_date
FROM oorder oo
LEFT OUTER JOIN order_line ol ON oo.o_id = ol.ol_o_id
WHERE oo.o_entry_d >= NOW() - INTERVAL '30 days';
그런데 왜 delivery_date가 없는 row가 없지?
내가 뭘 잘못했나..?
다른 delivery 정보가 있는 테이블이 있나싶어서 찾아봤는데
delivery_date가 있는 table은 order_line 밖에 없었다,,
아무튼,,,
Q7) 최근 10일 내에 주문된 상품별 총 수량을 구하세요.
이번엔 배송여부와는 상관없이 주문된 상품별 총 수량이다
처음에는 order_line만 써줄 생각을 했지만
order_line은 배송이 완료된 경우만 담는 table같았다
그런데 .. 뭐 위 경우를 봐도
oorder에는 있는데 order_line에 없는 그런 row는 없는 듯 했고
oorder에는 주문 날짜가
order_line에는 item_quantity 정보가 있기 때문에
이 두 가지를 join 해주는게 맞다고 생각했다
LEFT OUTER JOIN이라서 무엇을 기준으로 해주지 하다가
사실상 delivery_date가 NULL인게 없었기때문에
어느 테이블을 기준으로 하나 상관이 없다고 생각했지만
(INNER JOIN해도 상관없ㅇ)
혹시나 NULL이 생길수도 있는 경우를 대비해서
정석적으로 기준 테이블을 잡자고 생각했다
그래서 정석으로 해주는건 oorder 테이블을 기준으로 잡는게
맞다고 생각해서 oorder 테이블을 기준으로
LEFT JOIN을 때려줬다
SELECT ol.ol_i_id AS item_id, SUM(ol.ol_quantity) AS item_amount
FROM oorder oo
LEFT OUTER JOIN order_line ol ON oo.o_id = ol.ol_o_id
WHERE oo.o_entry_d >= NOW() - INTERVAL '10 days'
GROUP BY ol.ol_i_id
ORDER BY ol.ol_i_id;
item id로 ORDER BY를 해주면
더 한눈에 결과를 확인할 수 있어서 추가로 해주었다
위와같이 결과가 나오는 것을 확인할 수 있었다
Q8) 고객 ID = 100 인 고객이 지금까지 주문한 모든 상품의 ID와 총 수량을 조회하세요.
위 문제를 해결하기 위해서 우선 customer 테이블에서
c_id = 100으로 조회를 했다
그런데..
row가 한개만..나와야하는거 아닌가...?
왜 10개가 나오지?
그냥 c_id가 100인 고객이 주문할 때 마다
customer row에 담아놓은건가 싶었다
그래서 TPC-C schema 홈페이지 들어가서 확인해보니
customer 테이블에서는
c_w_id, c_d_id, c_id
이 세개가 pk라던데
warehouse id랑 district id랑 customer id가 pk...?
한 고객이 뭐 이사가면 달라지는건가...?
그냥 warehouse별로 고객이 같더라도 관리체계가 달라지는건가..?
난 이제 진짜 모르겠다 ㅋㅋㅋ
아무튼 customer id가 100인 고객의 주문 기록을 봐야하므로
oorder에서 o_c_id = 100으로 조회해봤다
모든 상품의 id와 customer 100이 주문한 상품의 총 수량을 알아야하므로
order_line table이랑 join을 해주면 되겠다고 생각했다
ID=100이 구매한 모든 상품의 id와 그 상품의 총 수량이 나와야하므로
여러 개의 row를 어떻게 한 row에 표현할지 고민하다가
구글링을 해보니 row to array인
ARRAY_AGG, ARRAY_TO_STRING을 찾았다
이 중에서 DISTINCT를 써주고 싶어서
ARRAY_AGG를 쓰기로 했다
처음에는
SELECT ARRAY_AGG(DISTINCT ol.ol_i_id) AS item_id,
SUM(ol.ol_quantity) AS total_quantity
FROM oorder oo
JOIN order_line ol
ON oo.o_id = ol.ol_o_id
WHERE oo.o_c_id = 100;
이렇게 해주었는데
자꾸 아무런 결과도 뜨지 않는 것이었다
그래서 TPC-C schema에 대해서 좀 알아보니
o_id와 w_id, d_id를 함께 조인해줘야 정확한 결과가 나온다한다
customer table에서 w_id랑 d_id랑 c_id가 pk이기 때문이다
그래서 수정사항을 반영해서 다시 쿼리를 날려줬다
SELECT ARRAY_AGG(DISTINCT ol.ol_i_id) AS item_id,
SUM(ol.ol_quantity) AS total_quantity
FROM oorder oo
JOIN order_line ol
ON oo.o_id = ol.ol_o_id
AND oo.o_w_id = ol.ol_w_id
AND oo.o_d_id = ol.ol_d_id
WHERE oo.o_c_id = 100;
결과가 나오는 것을 확인할 수 있었다
Q9) 가장 최근 배송일 기준으로 최근 배송된 주문 5건의 주문 ID와 배송일을 조회하세요.
배송일을 기준으로 하므로
order_line 테이블만 사용해주면 될 것 같았다
그래서 처음에는
SELECT ol_o_id AS order_id, MAX(ol_delivery_d) AS delivery_date
FROM order_line
GROUP BY ol_o_id
ORDER BY delivery_date DESC
LIMIT 5;
이렇게 해줬는데
결과가 이렇게 나오는 것이다..
ol_delivery_d에 NULL값이 포함되어있었고
postgreSQL에서는 DESC하면 NULL이 가장 위에 온다고한다
그래서 WHERE로 IS NOT NULL을 추가해줬다
SELECT ol_o_id AS order_id, MAX(ol_delivery_d) AS delivery_date
FROM order_line
WHERE ol_delivery_d IS NOT NULL
GROUP BY ol_o_id
ORDER BY delivery_date DESC
LIMIT 5;
이렇게 해줬더니
이렇게 출력이 되는 것을 확인할 수 있었다
Q10) 각 고객의 가장 최근 주문 번호와 주문일을 조회하세요. (JOIN + 서브쿼리)
JOIN과 서브쿼리를 활용하는 문제다
어떻게 할지 고민을 가장 많이했는데
우선 oorder table에 o_c_id와 o_entry_date가 있으므로
o_c_id로 GROUP BY를 한 후 MAX(o_entry_date)를 띄워주는 것까진 알겠으나..
이걸 뭐.. JOIN + 서브쿼리로 어떻게..?
하다가 그냥 oorder table이랑
또 oorder table에서 subquery로 select한거를 join시켜줘야겠다 생각했다
SELECT oo.o_c_id AS customer_id, oo.o_id AS order_id, oo.o_entry_d AS order_date
FROM oorder oo
JOIN (
SELECT o_c_id, MAX(o_entry_d) AS order_date
FROM oorder
GROUP BY o_c_id
) co
ON oo.o_c_id = co.o_c_id
AND oo.o_entry_d = co.order_date;
그래서 위 쿼리처럼 그냥
subquery로
o_c_id로 GROUP BY를 한 후 MAX(o_entry_date)를 해주고
이걸 oorder table이랑 c_id랑 order_date로 join해서
결과만 받아오는걸로 해주었다
쿼리 결과는 위와같이 나온다
이렇게 수업시간에 한 실습 + 과제를 포함해서
postgreSQL + benchbase TPC-C 스키마를 이용해
10개의 쿼리 실습을 해보았다
내가 정답인지는 모르겠다
(사실 스키마를 제대로 안읽고해서 틀린게 많을 것 같다)
아무튼 이것저것 많이 해보니 재미있었다
평소에 잘 쓰지않는 subquery같은 것도
잘 쓸 수 있도록 습관을 들이면 좋을 것 같았다
그럼 실습내용 정리는 여기까지-!