Skip site navigation (1) Skip section navigation (2)

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

From: Irene Barg <ibarg(at)noao(dot)edu>
To: Alex Hunsaker <badalex(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: postgresql-server-8.4.4-1PGDG.el5 - ERROR: could not write block 503414 of temporary file: No space le,ft on device
Date: 2010-07-28 21:43:31
Message-ID: 4C50A483.2010100@noao.edu (view raw or flat)
Thread:
Lists: pgsql-admin
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
---------------------------------------------------------------------

In response to

Responses

pgsql-admin by date

Next:From: Alex HunsakerDate: 2010-07-28 21:53:43
Subject: Re: postgresql-server-8.4.4-1PGDG.el5 - ERROR: could not write block 503414 of temporary file: No space le,ft on device
Previous:From: Walter WillmertingerDate: 2010-07-28 21:02:47
Subject: Re: psql shell with no password prompt

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group