Alex,
Thanks for the explanation. That helps.
After looking at some of the pgsql_tmp files, It was clear we needed to
increase work_mem. So I switched back to the pgtune suggestions with one
change:
-bash-3.2$ diff postgresql.conf postgresql.conf.local | grep "< "
< log_temp_files = 0 # log temporary files equal or larger
< default_statistics_target = 50 # pgtune wizard 2010-07-26
< maintenance_work_mem = 1GB # pgtune wizard 2010-07-26
< constraint_exclusion = on # pgtune wizard 2010-07-26
< checkpoint_completion_target = 0.9 # pgtune wizard 2010-07-26
< effective_cache_size = 16GB # pgtune wizard 2010-07-26
< work_mem = 144MB # pgtune wizard 2010-07-26
< wal_buffers = 8MB # pgtune wizard 2010-07-26
< checkpoint_segments = 16 # pgtune wizard 2010-07-26
< #shared_buffers = 5632MB # pgtune wizard 2010-07-26
< shared_buffers = 2048MB
< max_connections = 80 # pgtune wizard 2010-07-26
The postmaster wouldn't start with shared_buffers = 5632MB, so we
reduced it to 2GB. The system's shared memory is:
[dppops@pipen18 ~]$ sudo /sbin/sysctl -a | grep kernel | grep shmmax
kernel.shmmax = 4182863872
We have 24GB RAM total.
The system is behaving normal now, it does create intermittant
'pgsql_tmp' files, but removes them after the SQL statement completes.
We will monitor the logs and try to make informed tweaks to the
postgresql settings as needed.
Thanks to you and Tom Lane for your responses. This group is one of the
best!
--irene barg
Alex Hunsaker wrote:
On Tue, Jul 27, 2010 at 15:58, Irene Barg <ibarg@xxxxxxxx> wrote:
I'm afraid I don't understand how turning on 'log_temp_files' will help?
Well, postgres is probably doing exactly what its told. You just need
to figure out what that is and why. ( and if it is some kind of bug,
we wont be able to do anything about it unless you give us a test case
:-) )
The idea behind log_temp_files is that you should be able to see whats
causing the creation of all those temp files (and so potentially 'fix'
the query/problem). The main problem with log_temp_files is it only
logs when the command is done executing. Of course if it really is
creating tons of files it should take long enough that you should be
able to select * from pg_stat_activity; and pinpoint the problem query
that way. If all else fails you might have better luck with turning
on log_statements and then lurking through the logs when you start to
get lots of tmp files.
Right now it is set to '-1' which should mean it's disabled, yet it is
creating 1GB files and NOT cleaning them up:
It wont clean them up until its done using them. If a backend
crashes, it looks like it might orphan some tmp files, at least until
postgres is restarted.
--
---------------------------------------------------------------------
Irene Barg Email: ibarg@xxxxxxxx
NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg
950 N. Cherry Ave. Voice: 520-318-8273
Tucson, AZ 85726 USA FAX: 520-318-8360
---------------------------------------------------------------------
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
