Re: BUG #13672: What is the purpose of the temp_buffers setting?

On Friday, October 9, 2015 11:34 PM, "jkoceniak@mediamath.com"
<jkoceniak@mediamath.com> wrote:

> I set temp_buffers to a high value (8GB). My understanding was
> that temporary tables stay in memory if they do not go over
> temp_buffers limit.

Since temp_buffers is a limit on how much process-local memory each
connection can reserve for temporary table buffers, 8GB seems like
an insanely high value.  Keep in mind that once a connection
allocates memory for this purpose, it doesn't release it until the
connection is closed, so with this setting, if you had 100
connections open which had all used temporary tables, you might
have 800GB set aside for this purpose even if all of the
connections were idle and had dropped all of their temporary
tables.

> However, I noticed that any temporary tables that I create are
> written to disk. (i.e. I see t181_869525834 filenames in the data
> directory)

Are you sure that those temporary files are related to temporary
tables?  temp_buffers is only for pages in temporary tables, not
for the temporary work files created by sorts, hash joins, CTEs,
etc.  Those spill to disk if work_mem is not adequate to hold them.

> Also, I don't see any query performance improvements when
> querying temporary tables using a higher temp_buffers value.

Keep in mind that even if a temporary table doesn't fit into
temp_buffers, the OS cache will tend to cache it for some period of
time, so you are likely only dealing with difference between
accessing the pages in process-local memory versus bringing them in
from the OS RAM cache.  That might be hard to measure.

> What is the purpose of this setting and what performance
> improvements would you expect to see by adjusting the
> temp_buffers value?

Personally, I would tend to think that the cases in which
increasing this setting would show any improvement would be few and
far between.  I think that the default setting is probably too
high, especially for anyone who increases max_connections from the
default of 100.  The setting you have, combined with
max_connections of 1000 could tie up 8TB in this type of cache, for
very dubious benefit.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company