On Nov 8, 2010, at 16:03 , Tom Lane wrote:
> Michael Glaesemann <michael.glaesemann@myyearbook.com> writes:
>> We've got over 250GB of files in a pgsql_tmp directory, some with modification timestamps going back to August 2010
whenthe server was last restarted.
>
> That's very peculiar. Do you keep query logs? It would be useful to
> try to correlate the temp files' PIDs and timestamps with the specific
> queries that must have created them.
We don't log all of them, but I checked those we did. It looks like it's happening when queries are timing out. I'm
seeingthis pattern pretty consistently:
temporary file + query
canceling statement due to statement timeout
second temp file
Here's a sample:
pid | 877
sess_id | 4ccf7257.36d
sess_line | 16
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.412-04
error | LOG
sql_state | 00000
message | temporary file: path "pg_tblspc/16384/pgsql_tmp/pgsql_tmp877.0", size 87184416
pid | 877
sess_id | 4ccf7257.36d
sess_line | 17
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.412-04
error | ERROR
sql_state | 57014
message | canceling statement due to statement timeout
pid | 877
sess_id | 4ccf7257.36d
sess_line | 18
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.434-04
error | LOG
sql_state | 00000
message | temporary file: path "pg_tblspc/16384/pgsql_tmp/pgsql_tmp877.1", size 5480448
query |
It looks like the pgsql_tmp877.1 file is cleaned up as it doesn't appear in the pgsql_tmp directory.
> Personally, I'd not risk trying to match on PID; it should be sufficient
> to delete anything with a timestamp older than the oldest active
> backend. (Unless you've got some really long-lived sessions in
> there...)
That's easily-enough determined from pg_stat_activity.
> What PG version is this?
select version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 row)
Michael Glaesemann
michael.glaesemann@myyearbook.com