Re: Performance Woes

Ralph Mason wrote:

> I have several databases. They are each about 35gb in size and have about
> 10.5K relations (count from pg_stat_all_tables) in them. Pg_class is about
> 26k rows and the data directory contains about 70k files. These are busy
> machines, they run about 50 xactions per second, ( aproxx insert / update /
> delete about 500 rows per second).

Is it always the same trigger the problematic one? Is it just PL/pgSQL,
or do you have something else? Something that may be trying to open
additional files for example? Something that may be trying to open
files behind your back? PL/Perl with funky operators or temp files?

Also, what PG version is this?

> So we decreased the max_files_per_process to 800. This took care of the
> error *BUT* about quadrupled the IO wait that is happening on the machine.
> It went from a peek of about 50% to peeks of over 200% (4 processor
> machines, 4 gigs ram, raid). The load on the machine remained constant.

The max_files_per_process settings controls how many actual file
descriptors each process is allowed to have. Postgres uses internally a
"virtual file descriptor", which normally have one file descriptor open
each. However, if your transactions need to access lots of files, the
VFDs will close the kernel FDs to allow other VFDs to open theirs.

So it sounds like your transaction has more than 800 files open. The
extra IO wait could be caused by the additional system calls to open and
close those files as needed. I would actually expect it to cause extra
"system" load (as opposed to "user") rather than IO, but I'm not sure.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.