postgresql中的Share_buffer_size

Question

我在RHEl 6.4上有一个Postgres 9.3数据库。我正在从RHel6.4上的服务器获取数据库连接时间。

以下数据是发生此问题时的SAR数据。

00:00:01  CPU  %usr  %nice  %sys %iowait %steal  %irq  %soft %guest  %idle 
02:10:01  all  0.05  0.00  0.29  3.06  0.00  0.00  0.05  0.00  96.55 
02:20:01  all  0.07  0.00  0.28  3.84  0.00  0.00  0.05  0.00  95.75 

00:00:01 kbmemfree kbmemused %memused kbbuffers kbcached kbcommit %commit 

02:10:01  781108 65150968  98.82 151576 60250076 5905400  7.17 
02:20:01  245712 65686364  99.63 151664 60778552 5905140  7.17 

“memused”的值似乎很高，但该值不包括“共享缓冲区”的值。 （“kbcached”包括“共享缓冲区高速缓存”）。

当前，要通过db的“共享缓冲区”一次性导出到服务器的数据。 这个数据是巨大的。 因此，正在发生db_timeout。

共享缓冲液：存储器导出数据

请提出时使用。

1. 是否需要增加共享缓冲区的大小。
2. 是否有可能将我的数据划分到要发送到服务器的共享缓冲区中。

我分析了db函数的查询。在DB

kddi=# EXPLAIN (BUFFERS,ANALYZE) 
select * 
from table, user_data 
where user_data.customer_id = charge_history.customer_id 
    and charge_history.updated_date::date = (CURRENT_DATE - integer '1') 
    and charge_history.picked_status = 'NOTPICKED'; 

                     QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Nested Loop (cost=0.85..10873.44 rows=75 width=271) (actual time=0.123..51.515 rows=3982 loops=1) 
    Buffers: shared hit=18475 read=55682 
    -> Index Scan using idx_chrghist_picked_status on charge_history (cost=0.42..10239.13 rows=75 width=255) (actual time=0.092..16.022 rows=3982 loops=1) 
     Index Cond: (picked_status = 'NOTPICKED'::text) 
     Filter: ((updated_date)::date = (('now'::cstring)::date - 1)) 
     Rows Removed by Filter: 10022 
     Buffers: shared hit=2547 read=55682 
    -> Index Scan using "CUSTOMERID" on subscriber_data (cost=0.43..8.45 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=3982) 
     Index Cond: ((customer_id)::text = (charge_history.customer_id)::text) 
     Buffers: shared hit=15928 
Total runtime: 52.053 ms 

shared_buffers设置为1GB

我可以做一些事情来提高我的查询。

Answer 1

我怀疑以下指数将3倍以上的加速事情：

CREATE INDEX ON charge_history(picked_status, (updated_date::date)); 

但是，你只能创建一个索引，如果updated_date是date或timestamp without time zone，因为铸造从timestamp with time zone到date不是immutable（取决于TimeZone的设置）。

如果这是一个问题，你可以在查询更改为类似：

... AND CAST(charge_history.updated_date AT TIME ZONE 'UTC' AS date) = ... 

然后该表达式可以被索引，因为它是不可变的。

另一个问题是优化器低估了charge_history中匹配的行数。原因很可能是最近的行往往有picked_status = 'NOTPICKED'。也许解决方案是更频繁地计算该表的统计数据。

你可能想用要么减少autovacuum_analyze_scale_factor该表或将它设置为100与设定相当高的autovacuum_analyze_threshold实验。
这可以用这样的SQL语句来完成：

ALTER TABLE charge_history SET (
    autovacuum_analyze_scale_factor = 100, 
    autovacuum_analyze_threshold = 100000 
); 

这个例子语句会导致每当100000行已被修改来计算新的统计数据。