ANALYZE crashes randomly

[Olivier Hubaut]

Hi,

On PostgreSQL 7.3.3 on MacOS X 10.2, the ANALYZE command sometimes crash
and I don't know why! I've tried it some binaries copies of the DB, it's
a totally random behaviour.

When it crahes, I get this message:

PANIC: could not open transaction-commit log directory
(/usr/local/pgsql/annot/pg_clog): Too many open files
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Does anyone know how to resolve it?

[Tom Lane]

Post by Olivier Hubaut
PANIC: could not open transaction-commit log directory
(/usr/local/pgsql/annot/pg_clog): Too many open files

Try reducing max_files_per_process (in postgresql.conf) and/or
increasing the kernel's limit on number of open files (I think
you set this via sysctl in /etc/rc, but it may vary depending on
OS X version).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Olivier Hubaut]

Post by Tom Lane

Post by Olivier Hubaut
PANIC: could not open transaction-commit log directory
(/usr/local/pgsql/annot/pg_clog): Too many open files

Try reducing max_files_per_process (in postgresql.conf) and/or
increasing the kernel's limit on number of open files (I think
you set this via sysctl in /etc/rc, but it may vary depending on
OS X version).
regards, tom lane

Thank you, it works fine by reducing the max_files_per_process to 200
(instead of the default '1000' value).

But it's amazing to me that i had to reduce it so much as the postmaster
is almost the only application running on this server. There only 3 or 4
simultaneous connections and the kernel max files is set to 12500!

Is that comportement normal or not?

[Tom Lane]

Post by Olivier Hubaut
But it's amazing to me that i had to reduce it so much as the postmaster
is almost the only application running on this server. There only 3 or 4
simultaneous connections and the kernel max files is set to 12500!

But how many open files are needed by the rest of an OS X system?
(I have no idea, but you could probably find out using lsof or a
similar tool.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Post by Olivier Hubaut

Post by Olivier Hubaut
PANIC: could not open transaction-commit log directory
(/usr/local/pgsql/annot/pg_clog): Too many open files

But it's amazing to me that i had to reduce it so much as the postmaster
is almost the only application running on this server.

Oh, wait, I bet you are running into the per-process open file limit not
the kernel limit. The per-process limit is usually pretty low on
Darwin, and checking the code I see

xldir = opendir(XLogDir);
if (xldir == NULL)
ereport(PANIC,
(errcode_for_file_access(),
errmsg("could not open transaction log directory \"%s\": %m",
XLogDir)));

That is, we don't have a fallback path to recover when the error is
ENFILE or EMFILE. Looks to me like all the opendir() calls in the
backend ought to be handled by fd.c with code to release other open
files at need.

In the meantime, though, it seems fishy that Postgres wouldn't have
detected and allowed for the per-process file limit. Which version of
OS X did you say you were using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Olivier Hubaut]

Post by Tom Lane

Post by Olivier Hubaut

Post by Olivier Hubaut
PANIC: could not open transaction-commit log directory
(/usr/local/pgsql/annot/pg_clog): Too many open files

But it's amazing to me that i had to reduce it so much as the postmaster
is almost the only application running on this server.

Oh, wait, I bet you are running into the per-process open file limit not
the kernel limit. The per-process limit is usually pretty low on
Darwin, and checking the code I see
xldir = opendir(XLogDir);
if (xldir == NULL)
ereport(PANIC,
(errcode_for_file_access(),
errmsg("could not open transaction log directory \"%s\": %m",
XLogDir)));
That is, we don't have a fallback path to recover when the error is
ENFILE or EMFILE. Looks to me like all the opendir() calls in the
backend ought to be handled by fd.c with code to release other open
files at need.
In the meantime, though, it seems fishy that Postgres wouldn't have
detected and allowed for the per-process file limit. Which version of
OS X did you say you were using?
regards, tom lane

Once again, thanks for your help

For the moment, we are running on OS X 10.2.8 but we'll change soon for
OS X 10.3