Routine Maintenance
Jack Yu 2015.10.15
日常的維護 Σ( ° △ °|||)
MVCC 介紹
Vacuum 介紹
Query Log 分析
還沒 COMMIT 前另一個 client 看不到
Client 1
Client 2
DELETE 不會生效,就算 UPDATE 之前或之後有 hits == 10
BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session: DELETE FROM website WHERE hits = 10;
COMMIT;
ERROR: could not serialize access due to concurrent updates
SELECT schemaname,relname,last_autovacuum,last_autoanalyze FROM pg_stat_
all_tables;
autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold
alter table t SET (autovacuum_enabled=false);
SELECT procpid,current_timestamp - xact_start AS xact_runtime,current_
query FROM pg_stat_activity ORDER BY xact_start;
UPDATE pgbench_accounts SET abalance = abalance + 1631 WHERE aid =
5829858;
UPDATE pgbench_accounts SET abalance = abalance + 4172 WHERE aid =
567923;
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
pgbench=# SELECT round(total_time*1000)/1000 AS total_time,query FROM pg_
stat_statements ORDER BY total_time DESC;
total_time | query
78.104 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE
aid = $2;
1.826 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE
bid = $2;
0.619 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE
tid = $2;
$ pgbench -T 60 -c 4 pgbench
$ pgfouine -logtype stderr -file $PGDATA/pg_log/postgresql-2010-03-28_
210540.log -top 10 -format text
##### Overall statistics #####
Number of unique normalized queries: 8
Number of queries: 27,993
Total query duration: 3m52s
##### Queries by type #####
SELECT: 4666 16.7%
INSERT: 4665 16.7%
UPDATE: 13995 50.0%
##### Slowest queries #####
1) 5.37 s - END;
2) 5.26 s - END;
3) 5.26 s - END;
4) 5.25 s - END;
5) 4.97 s - UPDATE pgbench_accounts SET abalance = abalance + 1631 WHERE
aid = 5829858;
6) 4.96 s - END;
7) 4.58 s - END;
8) 3.93 s - UPDATE pgbench_accounts SET abalance = abalance + 4172 WHERE
aid = 567923;
9) 3.92 s - END;
10) 3.78 s - UPDATE pgbench_accounts SET abalance = abalance + -379 WHERE
aid = 12950248;
##### Queries that took up the most time (N) #####
1) 2m14s - 4,665 - END;
2) 1m26s - 4,665 - UPDATE pgbench_accounts SET abalance = abalance + 0
WHERE aid = 0;
3) 5.3s - 4,665 - UPDATE pgbench_tellers SET tbalance = tbalance + 0
WHERE tid = 0;
4) 2.4s - 4,665 - UPDATE pgbench_branches SET bbalance = bbalance + 0
WHERE bid = 0;
5) 1.8s - 4,665 - INSERT INTO pgbench_history (tid, bid, aid, delta,
mtime) VALUES (0, 0, 0, 0, CURRENT_TIMESTAMP);
6) 1.5s - 4,665 - SELECT abalance FROM pgbench_accounts WHERE aid = 0;
7) 0.1s - 1 - select count(*) from pgbench_branches
8) 0.0s - 1 - truncate pgbench_history
然後原來這本書有簡中,請支持正版 (據說翻得不好)