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

Alex,

It's funny how one cannot see what is right in front of them. I did NOT
notice that 'var' was mounted as '/var/log', so postgres was getting
installed in root '/'. So I moved pgsql to /data1, and created a symlink
in /var/lib/pgsql to /data1/pgsql.

Now I have a pgsql_tmp file that has grown to 1.9Tb!

> -bash-3.2$ du -ks *
> 5556 1
> 5364 11563
> 5436 11564
> 5529348 28523
> 1962946784 pgsql_tmp

I'm afraid I don't understand how turning on 'log_temp_files' will help?
Right now it is set to '-1' which should mean it's disabled, yet it is
creating 1GB files and NOT cleaning them up:

> -bash-3.2$ du -hs pgsql_tmp/*
> 15M pgsql_tmp/pgsql_tmp22192.3070
> 1.1G pgsql_tmp/pgsql_tmp22192.4916
> 168M pgsql_tmp/pgsql_tmp22192.4918
> 1.1G pgsql_tmp/pgsql_tmp22192.4919
....

Thanks you.
--irene

Alex Hunsaker wrote:
> On Mon, Jul 26, 2010 at 16:01, Irene Barg <ibarg(at)noao(dot)edu> wrote:
>>> 2010-07-26 11:33:33 MST system_admin metadataERROR: could not write block
>>> 503414 of temporary file: No space le
>>> ft on device
>>> 2010-07-26 11:33:33 MST system_admin metadataHINT: Perhaps out of disk
>>> space?
>
>>> -bash-3.2$ df -h
>>> Filesystem Size Used Avail Use% Mounted on
>>> /dev/sda3 16G 13G 1.7G 89% /
>>> /dev/sda8 635G 979M 601G 1% /data0
>>> /dev/sda7 2.0G 241M 1.7G 13% /home
>>> /dev/sda5 7.8G 1.5G 5.9G 20% /u1
>>> /dev/sda2 16G 184M 15G 2% /var/log
>>> /dev/sda1 487M 22M 440M 5% /boot
>>> tmpfs 12G 0 12G 0% /dev/shm
>>> /dev/md0 2.7T 202M 2.6T 1% /data1
>>
>> The pg_stat_tmp is hardly growing::
>>
>>> -bash-3.2$ date
>>> Mon Jul 26 14:37:38 MST 2010
>>> -bash-3.2$ pwd
>>> /var/lib/pgsql/data
>
> Ok so it looks like this is on your / (sda3) partition that only has
> 1.7G free. Its entirely possible that you have some query that needs
> to write more than that in tmp files.
>
>>> -bash-3.2$ du -ks pg_stat_tmp/*
>
> No surprise that is not growing, thats not for temp files, its for the
> stats collector.
>
> I would try turning on log_temp_files, that might help pin point the
> problem query.
>

--
---------------------------------------------------------------------
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
---------------------------------------------------------------------