From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Blair <petertblair@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Select hangs and there are lots of files in table and index directories.
Date: Mon, 27 Jan 2014 19:43:35 -0500
Message-ID: <27362.1390869815@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ9LHMVonzD1rjGoDRawC6tccCh3rVULvz02-W45oUFaziykdQ@mail.gmail.com>
References: <CAJ9LHMVonzD1rjGoDRawC6tccCh3rVULvz02-W45oUFaziykdQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Peter Blair <petertblair@gmail.com> writes:
> Have a problem where a stored procedure is taking a week to run. The
> stored procedure should take less than a second to run.
Is that "it's known to terminate if you give it a week", or "we've let
it run for a week and it shows no sign of ever terminating"?
> In researching a
> select hanging problem, three things are suggested; an autovacuum problem,
> a resource is locked, or there is something wrong with the stored procedure.
I'd bet on the last, given that you're apparently working with an immature
port from Oracle. The error recovery semantics, in particular, are enough
different in PL/SQL and PL/pgSQL that it's not too hard to credit having
accidentally written an infinite loop via careless translation.
> Lastly, in the directories used to store the tables and indexes, there are
> 918896 files in the tables directory and 921291 files in the indexes
> directory. All of the file names are just numbers (no extensions). About
> 60 files are added to each directory every second. On our test systems and
> at our other customer site, there are only about 50 files in each directory.
> Why are there so many files?
If the filenames are just numbers, then they must be actual tables or
indexes, not temp files. (You could cross-check that theory by noting
whether the system catalogs, such as pg_class, are bloating at a
proportional rate.) I'm guessing that there's some loop in your procedure
that's creating new temp tables, or maybe even non-temp tables. You would
not be able to see them via "select * from pg_class" in another session
because they're not committed yet, but they'd be taking up filesystem
entries. The loop might or might not be dropping the tables again; IIRC
the filesystem entries wouldn't get cleaned up till end of transaction
even if the tables are nominally dropped.
Not much to go on, but I'd look for a loop that includes a CREATE TABLE
and a BEGIN ... EXCEPT block, and take a close look at the conditions
under which the EXCEPT allows the loop to continue.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, petertblair@gmail.com
Subject: Re: Select hangs and there are lots of files in table and index directories.
In-Reply-To: <27362.1390869815@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox