Home > PostgreSQL > work_mem tuning using log_temp_files

work_mem tuning using log_temp_files

work_mem is used for sorts, aggregates, and a few other operations. This is non-shared memory, which is allocated
per-operation (one to several times per query); the setting is used to put a limit on the amount of RAM any single operation
can grab before being forced to disk through temp files. If it is been forced to the disk that can cause a bad execution time
as that specific operation is then done using disk and not the RAM.

With PostgreSQL 8.3 a new variable got added named as “log_temp_files” that enables/disables whether temporary files are
logged when been used for the database server logs. These temp files can be created for sorts, hashes, and temporary query results.

log_temp_files can in result then be used to better tune the values for work_mem after analyzing the usage of temp files
(from the database server logs) for a certain query and that can help you minimize or nullify the temp file usage by raising the
values for work_mem.

Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: