Message-ID: <4C4F5696.6090403@noao.edu>
Date: Tue, 27 Jul 2010 14:58:46 -0700
From: Irene Barg <ibarg@noao.edu>
Reply-To: ibarg@noao.edu
Organization: NOAO/AURA, Inc.
User-Agent: Thunderbird 2.0.0.24 (Macintosh/20100228)
MIME-Version: 1.0
To: Alex Hunsaker <badalex@gmail.com>
CC: pgsql-admin@postgresql.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
References: <4C4E05C8.9090702@noao.edu>
	<AANLkTinH9+s1WFAu2k3NnbYpd3Zh=DdyTb5ZduTsxZh-@mail.gmail.com>
In-Reply-To: <AANLkTinH9+s1WFAu2k3NnbYpd3Zh=DdyTb5ZduTsxZh-@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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