Re: postgresql-server-8.4.4-1PGDG.el5 - ERROR: could not write block 503414 of temporary file: No space le,ft on device

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(at)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(at)noao(dot)edu> 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(at)noao(dot)edu
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
---------------------------------------------------------------------