PostgreSQL 지표 수집

set MON_HOST=<HOST>
set MON_PORT=5432
set MON_DB=postgres
set MON_USER=<MONITOR_USER>
set MON_PASSWORD=<PASSWORD>
set MON_WATCH_DB=<LOAD_USER>
python -m streamlit run app.py --server.port 8501

ARGS = dict(host=os.getenv("PG_HOST", "<HOST>"), port=int(os.getenv("PG_PORT", "5432")),
            dbname="<LOAD_USER>", user="<LOAD_USER>", password=os.getenv("PG_PASSWORD", "<PASSWORD>"))
stop = threading.Event()

def oltp():
    """인덱스로 1건 조회 + 1건 입력·커밋 반복"""
    with psycopg.connect(application_name="load_oltp", autocommit=True, **ARGS) as c:
        while not stop.is_set():
            c.execute("select amount from big_sales where id = %s", (random.randint(1, 2_000_000),)).fetchone()
            c.execute("insert into commit_log(id) values (%s)", (random.randint(1, 1000),))
            time.sleep(0.01)

def full_scan():
    """인덱스 없는 조건으로 200만 건 풀스캔"""
    with psycopg.connect(application_name="load_scan", autocommit=True, **ARGS) as c:
        while not stop.is_set():
            c.execute("select count(*), avg(amount) from big_sales where filler like %s",
                      (f"%{random.randint(0, 9)}%",)).fetchone()

def main():
    heavy = len(sys.argv) > 1 and sys.argv[1] == "heavy"
    workers = [threading.Thread(target=oltp, daemon=True) for _ in range(4)]
    if heavy:
        workers += [threading.Thread(target=full_scan, daemon=True) for _ in range(2)]
    for w in workers:
        w.start()
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        stop.set()

python load.py
python load.py heavy

rows = mon.execute("""select pid, usename, datname, application_name, state, left(query, 40)
                        from pg_stat_activity
                       where backend_type = 'client backend'
                       order by pid""").fetchall()
name, size = mon.execute("select name, size from pg_ls_logdir() order by modification desc limit 1").fetchone()

\timing on
-- 1 카테시안 곱
select count(*) from big_sales a, big_sales b where a.amount < 50000;
-- 2 상관 서브쿼리 반복
select count(*) from big_sales s
 where s.amount > (select avg(x.amount) from big_sales x where x.cust_id = s.cust_id);
-- 3 인덱스 컬럼 가공(DO 블록 안 1000번 반복)
do $$ declare n int; begin
  for i in 1..1000 loop
    select count(*) into n from big_sales where to_char(sale_date, 'YYYYMMDD') = '20240101';
  end loop; end $$;
-- 4 락 걸고 방치: 창 A에서 begin; update hot_row set cnt = cnt + 1 where id = 1; (커밋 안 함)
--   창 B·C에서 같은 update 실행 → 대기
-- 5 PL/pgSQL 무한 루프
do $$ declare x bigint := 0; begin loop x := x + 1; end loop; end $$;

select pid, usename, state, wait_event_type, wait_event from pg_stat_activity where pid = <PID>;
select pg_terminate_backend(<PID>);
select pg_sleep(1);
select pid from pg_stat_activity where pid = <PID>;