PostgreSQL中的share_buffer_size [英] Share_buffer_size in postgresql
问题描述
我在RHEl 6.4上有一个Postgres 9.3 DB。我正在从RHel6.4上的服务器获取数据库连接超时。
I have a Postgres 9.3 DB on RHEl 6.4. I am getting DB connection time out from a server which is on RHel6.4.
发生此问题时，以下数据是SAR数据。
The following data is SAR data when this issue occurred.
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的值似乎很高，但是该值不包括 shared buffer的值。
（ kbcached包括共享缓冲区缓存内存。）
The value of "memused" seems to be high, but this value does not include the value of "shared buffer". ("kbcached" include the "shared buffer cache memory".)
当前，要通过db的共享缓冲区导出到服务器的数据立刻。
该数据量巨大。
结果是发生db_timeout。
Currently, data which is to be exported to server via "shared buffer" of db at once. This data size is huge. As the result, db_timeout is occurring.
共享缓冲区：导出数据时使用的内存
Shared buffer: memory used when exporting data
请建议。
- 是否需要增加共享缓冲区的大小。
- 是否可以我可以将数据划分到要发送到服务器的共享缓冲区中。
我分析了来自db函数的查询。
I analyzed query from db function.
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
$ b DB中的
$ b
shared_buffers 设置为1GB
shared_buffers setting in DB is 1GB
我可以做些改善我的事情吗？查询。
Can I do something to improve my query.
推荐答案
我怀疑以下索引会使事情加速3倍或更多：
I suspect that the following index would speed up things by a factor 3 or more:
CREATE INDEX ON charge_history(picked_status, (updated_date::date));
但是您只能在 updated_date 是日期或不带时区的时间戳，因为从带时区的时间戳进行转换到日期不是不可变（取决于 TimeZone 的设置）。
But you can only create that index if updated_date is a date or a timestamp without time zone, because casting from timestamp with time zone to date is not immutable (it depends on the setting of TimeZone).
如果这是一个问题，则可以将查询更改为以下内容：
If that is a problem, you could change the query to something like:
... AND CAST(charge_history.updated_date AT TIME ZONE 'UTC' AS date) = ...
然后可以对该表达式建立索引，因为它
Then that expression can be indexed, because it is immutable.
另一个问题是优化器低估了 charge_history 中将匹配的行数。原因很可能是最近的行趋向于 picked_status ='NOTPICKED'。也许解决方案是更频繁地计算该表的统计信息。
The other problem is that the optimizer underestimates how many rows in charge_history will be matched. The cause could well be that recent rows tend to have picked_status = 'NOTPICKED'. Maybe the solution is to calculate statistics for that table more often.
您可能想尝试使用或减少 autovacuum_analyze_scale_factor 将该表或设置为100并设置较高的 autovacuum_analyze_threshold 。
这可以是用这样的SQL语句完成：
You might want to experiment with either reducing autovacuum_analyze_scale_factor for that table or setting it to 100 and setting a reasonably high autovacuum_analyze_threshold.
This can be done with an SQL statement like this:
ALTER TABLE charge_history SET (
autovacuum_analyze_scale_factor = 100,
autovacuum_analyze_threshold = 100000
);
此示例语句将导致在修改了100000行时计算新的统计信息。
This example statement would cause new statistics to be calculated whenever 100000 rows have been modified.
这篇关于PostgreSQL中的share_buffer_size的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
