Обсуждение: ANALYZE crashes randomly

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?

--
Signature en cours de maintenance,
Veuillez patienter...

Olivier Hubaut <olivier@scmbb.ulb.ac.be> writes:
> 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

Tom Lane wrote:

> Olivier Hubaut <olivier@scmbb.ulb.ac.be> writes:
>
>>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?

--
Signature en cours de maintenance,
Veuillez patienter...

Olivier Hubaut <olivier@scmbb.ulb.ac.be> writes:
> 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

Olivier Hubaut <olivier@scmbb.ulb.ac.be> writes:
>>> 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

Tom Lane wrote:

> Olivier Hubaut <olivier@scmbb.ulb.ac.be> writes:
>
>>>>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

--
Signature en cours de maintenance,
Veuillez patienter...