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@noao.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@noao.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
---------------------------------------------------------------------