Postgres性能和并发

参考文档&＃xff1a; PostgreSQL 9 Administration Cookbook &＃xff08;第二版&＃xff09;中文版 第十章 性能和并发

寻找执行缓慢的SQL语句&＃xff08;设置有点类似mysql的慢查询&＃xff09;&＃xff0c;将该参数修改为10秒&＃xff0c;日志中会记录查询超过10秒的语句

#log_min_duration_statement &＃61; -1 # -1 is disabled, 0 logs all statements# and their durations, > 0 logs only# statements running at least this number# of millisecondspostgres&＃61;# show log_min_duration_statement;log_min_duration_statement
-----------------------------1
(1 row)postgres&＃61;# log_min_duration_statement &＃61; 10000postgres&＃61;# show log_min_duration_statement;log_min_duration_statement
----------------------------10s
(1 row)postgres&＃61;#

-- 或者通过pg_stat_activity视图来查询

select now()-query_start as running_for, query from pg_stat_activity order by 1 desc limit 5;
select now()-query_start as running_for, current_query from pg_stat_activity order where current_query !&＃61; &＃39;&＃39; by 1 desc limit 5;

-- 或者反复执行以下查询&＃xff0c;如果runing_for保持较大的数值&＃xff0c;则可能耗费了大量的资源的查询.或者使用shell来处理

select now()-query_start as running_for, query from pg_stat_activity where state &＃61; &＃39;active&＃39;;while psql -qt -c "select current_query, now()-query_start as running_for
from pg_stat_activity" >>query_stats.txt ; do sleep 1; done -- 其中q是安静模式&＃xff0c;A未对齐的格式&＃xff0c;t是html。具体可以查看help

--另外可以通过pg_stat_user_tables和pg_statio_user_tables视图来找到可疑的信息

在pg_stat_user_tables中&＃xff0c;seq_tup_read的快速增长表示有很多顺序扫描发生。seq_tup_read的快速增长表示有很多顺序扫描发生&＃xff0c;seq_tup_read与seq_scan
的比率表示每次顺序扫描&＃xff08;seqscan&＃xff09;读取的元组数  
在pg_statio_user_tables中&＃xff0c;heap_blks_hit和idx_blks_read字段&＃xff0c;提供了有多少数据是从共享池被发现的&＃xff08;heap_blks_hit&＃xff09;&＃xff0c;有多少数据是从磁盘读取的&＃xff08;idx_blks_read&＃xff09;. 如果没有足够的数据被填充到共享缓冲区中&＃xff0c;则会发生大量的相同数据被重复读取的情况&＃xff0c;然后引起性能问题。具体表现为在查询的前后&＃xff0c;pg_stat*中的heap_blks_read、idx_blks_read和toast_blks_read有很大的变化。

-- 也可以通过安装扩展pgstatslog&＃xff0c;通过日志来收集一些信息 &＃xff08;pg中默认没有这个扩展&＃xff0c;需要额外安装&＃xff0c;略&＃xff09;

-- 研究是什么样sql变慢
运行带explain analyze的查询&＃xff0c;确认完成这个查询需要多少数据

mydb&＃61;# explain analyze select count(*) from t;QUERY PLAN
-----------------------------------------------------------------------------------------------------Aggregate (cost&＃61;38.25..38.26 rows&＃61;1 width&＃61;8) (actual time&＃61;0.005..0.005 rows&＃61;1 loops&＃61;1)-> Seq Scan on t (cost&＃61;0.00..32.60 rows&＃61;2260 width&＃61;0) (actual time&＃61;0.002..0.002 rows&＃61;0 loops&＃61;1)Planning time: 0.052 msExecution time: 0.060 ms
(4 rows)mydb&＃61;#

通过空闲系统中&＃xff0c;对比该语句被执行前后的输出&＃xff0c;看有多少行被处理&＃xff0c;以及多少数据被访问。

select
s.relid, s.schemaname, s.relname,
seq_scan,seq_tup_read,
idx_scan,idx_tup_fetch,
heap_blks_read,heap_blks_hit,
idx_blks_read,idx_blks_hit,
toast_blks_read,toast_blks_hit
from pg_stat_user_tables s
join pg_statio_user_tables sio on s.relid &＃61; sio.relid
where s.schemaname &＃61; &＃39;public&＃39; and s.relname &＃61; &＃39;t&＃39;

例如&＃xff0c;得到一个百万行表的最后三行记录

SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;

或者&＃xff0c;使用索引&＃xff0c;对比索引前后的情况&＃xff0c;发现执行时间有明显的改善&＃xff0c;

create table events(id serial);
insert into events select generate_series(1,1000000);
explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;
create index events_id_ndx on events(id);
explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;mydb&＃61;# explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------Limit (cost&＃61;30292.79..30292.79 rows&＃61;3 width&＃61;4) (actual time&＃61;187.357..187.360 rows&＃61;3 loops&＃61;1)-> Sort (cost&＃61;30292.79..33113.72 rows&＃61;1128375 width&＃61;4) (actual time&＃61;187.355..187.356 rows&＃61;3 loops&＃61;1)Sort Key: id DESCSort Method: top-N heapsort Memory: 25kB-> Seq Scan on events (cost&＃61;0.00..15708.75 rows&＃61;1128375 width&＃61;4) (actual time&＃61;0.018..89.370 rows&＃61;1000000 loops&＃61;1)Planning time: 0.149 msExecution time: 187.383 ms
(7 rows)mydb&＃61;# mydb&＃61;# explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------
--------------Limit (cost&＃61;0.42..0.56 rows&＃61;3 width&＃61;4) (actual time&＃61;0.054..0.055 rows&＃61;3 loops&＃61;1)-> Index Only Scan Backward using events_id_ndx on events (cost&＃61;0.42..43680.43 rows&＃61;1000000 width&＃61;4) (actual time&＃61;0.053..0.054 ro
ws&＃61;3 loops&＃61;1)Heap Fetches: 3Planning time: 0.391 msExecution time: 0.082 ms
(5 rows)mydb&＃61;#

-- 查看锁的问题
select * from pg_locks where not granted;   -- 如果锁已授予则为真&＃xff0c;如果锁被等待则为假 。如果返回空或者只有1行或者两行。则说明锁没有问题
一个行的granted为真表示一个被指定进程持有的锁。为假表示该进程当前正在等待获取这个
锁&＃xff0c;这意味着至少一个其他进程正持有或等待同一个可锁对象上的一个冲突锁。该等待进程
将一直休眠直到其他锁被释放&＃xff08;或者一个死锁状态被检测到&＃xff09;。单个进程在同一时间只能等
待最多一个锁。

-- 查看哪些查询正在等待其他查询

select
a1.query as blocking_query,
a2.query as waiting_query,
t.schemaname ||&＃39;.&＃39;||t.relname as locked_table
from pg_stat_activity a1
join pg_locks p1 on a1.pid &＃61; p1.pid and p1.granted
join pg_locks p2 on p1.relation &＃61; p2.relation and not p2.granted
join pg_stat_activity a2 on a2.pid &＃61; p2.pid
join pg_stat_all_tables t on p1.relation &＃61; t.relid;

-- 减少返回的行 。使用limit和offset等

SELECT title, ts_rank_cd(body_tsv, query, 20) AS text_rank
FROM articles, plainto_tsquery(&＃39;spicy potatoes&＃39;) AS query
WHERE body_tsv &＃64;&＃64; query
ORDER BY rank DESC
LIMIT 20
;SELECT title, ts_rank_cd(body_tsv, query, 20) AS text_rank
FROM articles, plainto_tsquery(&＃39;spicy potatoes&＃39;) AS query
WHERE body_tsv &＃64;&＃64; query
ORDER BY rank DESC, articles.id
OFFSET 20 LIMIT 20
;

-- 使用临时表

BEGIN;
CREATE TEMPORARY TABLE nlqp_temp ON COMMIT DROP
AS
SELECT
spoint_id,
extract(&＃39;quarter&＃39; from sale_time) as sale_quarter,
sum(sale_price) - sum(cost) AS profit,
count(*) AS nr_of_sales
FROM sale s
JOIN item_in_wh iw ON s.item_in_wh_id&＃61;iw.id
JOIN item i ON iw.item_id &＃61; i.id
JOIN salespoint sp ON s.spoint_id &＃61; sp.id
JOIN location sploc ON sp.loc_id &＃61; sploc.id
JOIN warehouse wh ON iw.whouse_id &＃61; wh.id
JOIN location whloc ON wh.loc_id &＃61; whloc.id
WHERE sale_time >&＃61; &＃39;2009-01-01&＃39;
AND sale_time <&＃39;2010-01-01&＃39;
AND sploc.id !&＃61; whloc.id
GROUP BY 1,2
;SELECT shop.sp_name AS shop_name,
q1_NLP.profit as q1_profit,
q2_NLP.profit as q2_profit,
q3_NLP.profit as q3_profit,
q4_NLP.profit as q4_profit,
year_NLP.profit as year_profit
FROM (SELECT * FROM salespoint ORDER BY sp_name) AS shop
LEFT JOIN nlqp_temp AS q1_NLP
ON shop.id &＃61; Q1_NLP.spoint_id AND q1_NLP.sale_quarter &＃61; 1
LEFT JOIN nlqp_temp AS q2_NLP
ON shop.id &＃61; Q2_NLP.spoint_id AND q2_NLP.sale_quarter &＃61; 2
LEFT JOIN nlqp_temp AS q3_NLP
ON shop.id &＃61; Q3_NLP.spoint_id AND q3_NLP.sale_quarter &＃61; 3
LEFT JOIN nlqp_temp AS q4_NLP
ON shop.id &＃61; Q4_NLP.spoint_id AND q4_NLP.sale_quarter &＃61; 4
LEFT JOIN (
select spoint_id, sum(profit) AS profit FROM nlqp_temp GROUP
BY 1
) AS year_NLP
ON shop.id &＃61; year_NLP.spoint_id
ORDER BY 1
;
COMMIT; -- 到这里&＃xff0c;临时表消失

-- 使用物化视图

create materialized view nlqp_temp as select * from xxx where xxx ;

--加速查询的执行&＃xff0c;而无须重写
第一步&＃xff0c;从为优化器提供更好的信息开始。如果explain analyze表明估计值和实际值相差很大&＃xff0c;则要告诉pg收集更细粒度的统计信息

mydb&＃61;# show default_statistics_target; default_statistics_target
---------------------------100
(1 row)mydb&＃61;#

针对单个db设置

alter database mydb set default_statistics_target &＃61; 200; -- 该参数&＃xff0c;设置后&＃xff0c;需要重新连接数据库才生效
mydb&＃61;# show default_statistics_target;default_statistics_target
---------------------------100
(1 row)mydb&＃61;# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres&＃61;# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb&＃61;# show default_statistics_target;default_statistics_target
---------------------------200
(1 row)mydb&＃61;#

如果设置的过高&＃xff0c;则会影响analyze的速度 &＃xff0c;pg提供了一个更细粒度的&＃xff0c;通过字段来设置

alter table mytable alter col_with_bad_stats set statistics 500;

-- postgres的直接索引扫描&＃xff08;index-only scan&＃xff09;&＃xff0c;这个功能也就是覆盖索引&＃xff08;covering index &＃xff09;

CREATE INDEX t1_a_b_ndx ON t1(a,b); -- 在t1的字段a上查询数据&＃xff0c;然后在字段b上排序。创建索引&＃xff0c;一次性实现
CREATE INDEX t1_proc_ndx ON t1(i1) WHERE needs_processing &＃61; TRUE; -- pg的条件所引
SELECT id, ... WHERE needs_processing;

-- 在频繁更新的情况下&＃xff0c;设置表的填充率
如果只是经常更新某些表&＃xff0c;并且可以通过调整查询语句来使更新操作不会影响索引字段&＃xff0c;则可以设置表的fillfactor(填充率)为一个比默认值100低的值来触发PG启用堆内元组(heap-only tuples,HOT)更新&＃xff0c;这比普通更新快一个数量级。HOT更新不仅可以避免索引项&＃xff0c;还可以在页内执行快速的微型vacuum,从而为新行腾出空间。
ALTER TABLE t1 SET (fillfactor &＃61; 70);  -- 这个命令告诉PG在插入表t1时候&＃xff0c;每个页填充70%&＃xff0c;剩余的30%用于页内&＃xff08;HOT&＃xff09;更新。

-- 为什么一个查询不用索引 &＃xff08;下面的例子中&＃xff0c;pg认为&＃xff0c;使用顺序扫描更好一些。&＃xff09;

create table itable(id int primary key );
insert into itable select generate_series(1,10000);
analyse;
explain analyse select count(*) from itable where id > 500;
set enable_seqscan to false;
explain analyse select count(*) from itable where id > 500;mydb&＃61;# explain analyse select count(*) from itable where id > 500;QUERY PLAN ---------------------------------------------------------------------------------------------------------
-----Aggregate (cost&＃61;193.75..193.76 rows&＃61;1 width&＃61;8) (actual time&＃61;1.719..1.719 rows&＃61;1 loops&＃61;1)-> Seq Scan on itable (cost&＃61;0.00..170.00 rows&＃61;9500 width&＃61;0) (actual time&＃61;0.045..1.085 rows&＃61;9500 loop
s&＃61;1)Filter: (id > 500)Rows Removed by Filter: 500Planning time: 0.382 msExecution time: 1.764 ms
(6 rows)mydb&＃61;# set enable_seqscan to false; -- 允许或禁止查询规划器使用顺序扫描计划类型。它不可能完全禁止顺序扫描&＃xff0c;但是关闭
这个变量将使得规划器尽可能优先使用其他方法。默认值是on。
SET
mydb&＃61;# explain analyse select count(*) from itable where id > 500;QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------
-Aggregate (cost&＃61;352.29..352.30 rows&＃61;1 width&＃61;8) (actual time&＃61;2.210..2.212 rows&＃61;1 loops&＃61;1)-> Index Only Scan using itable_pkey on itable (cost&＃61;0.29..328.54 rows&＃61;9500 width&＃61;0) (actual time&＃61;0.041..1.563 rows&＃61;9500 loops&＃61;1)Index Cond: (id > 500)Heap Fetches: 9500Planning time: 0.085 msExecution time: 2.330 ms
(6 rows)mydb&＃61;#

-- 强制让一个查询使用索引

set enable_seqscan to false;
create table table_with_no_index(id int);
set enable_seqscan to false;
explain select * from table_with_no_index where id > 10;
create index table_with_no_index_now_has_one on table_with_no_index(id);
explain select * from table_with_no_index where id > 10;
set enable_seqscan to true;
explain select * from table_with_no_index where id > 10;--random_page_cost &＃xff0c;将该值设置为较低&＃xff0c;使PG在大部分场景下更加倾向于索引扫描。

-- 使用乐观锁 。通过加锁select... for update转换成乐观锁&＃xff0c;可以获得一些性能提升。乐观锁假定其他人不更新相同的记录。并在更新时检查&＃xff0c;而不是在客户端上需要处理信息时锁定记录。

BEGIN;
SELECT * FROM ACCOUNTS WHERE HOLDER_NAME &＃61;&＃39;BOB&＃39; FOR UPDATE;

UPDATE ACCOUNTS SET BALANCE &＃61; 42.00 WHERE HOLDER_NAME &＃61;&＃39;BOB&＃39;;
COMMIT;

-- 可以重写上面的语句&＃xff0c;为以下的方式

BEGIN;
SELECT A.*, (A.*::text) AS OLDACCINFO
FROM ACCOUNTS A WHERE HOLDER_NAME &＃61;&＃39;BOB&＃39;;

UPDATE ACCOUNTS SET BALANCE &＃61; 42.00
WHERE HOLDER_NAME &＃61;&＃39;BOB&＃39;
AND (A.*::text) &＃61; ;
COMMIT;

-- 将以上事务用数据库中的一个函数来代替

CREATE OR REPLACE FUNCTION consume_balance (
i_username text, i_amount numeric(10,2), max_credit numeric(10,2),
OUT success boolean, OUT remaining_balance numeric(10,2)) AS
$$
BEGIN
UPDATE accounts SET balance &＃61; balance - i_amount
WHERE username &＃61; i_username
AND balance - i_amount > - max_credit
RETURNING balance
INTO remaining_balance;
IF NOT FOUND THEN
success :&＃61; FALSE;
SELECT balance
FROM accounts
WHERE username &＃61; i_username
INTO remaining_balance;
ELSE
success :&＃61; TRUE;
END IF;
END;
$$ LANGUAGE plpgsql;

-- 在客户端调用函数

SELECT * FROM consume_balance (&＃39;bob&＃39;, 7, 0);

END