optimizing CleanupTempFiles

Hi,

We've been profiling a large system (8 CPUs, 64 GB of memory, some
dozens of disks) which seems rather more swamped than it should. Part
of the problem seems to come from CleanupTempFiles, the second entry in
oprofile output.

This is the top 3 symbol report for a 2 minute oprofile run:

$ opreport '*postgres' -l
CPU: AMD64 processors, speed 2600 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Cycles outside of halt state) with a unit mask of 0x00 (No unit mask) count 7000
samples % symbol name
1963666 5.4862 AllocSetAlloc
1629477 4.5525 CleanupTempFiles
1560543 4.3599 hash_search_with_hash_value

Annotating the function results in this:

:static void
:CleanupTempFiles(bool isProcExit)
1334 0.0037 :{ /* CleanupTempFiles total: 1629477 4.5525 */
: Index i;
:
90 2.5e-04 : if (SizeVfdCache > 0)
: {
: Assert(FileIsNotOpen(0)); /* Make sure ring not corrupted */
470706 1.3151 : for (i = 1; i < SizeVfdCache; i++)
: {
281998 0.7879 : unsigned short fdstate = VfdCache[i].fdstate;
:
872073 2.4365 : if ((fdstate & FD_TEMPORARY) && VfdCache[i].fileName != NULL)
: {
: /*
: * If we're in the process of exiting a backend process, close
: * all temporary files. Otherwise, only close temporary files
: * local to the current transaction.
: */
: if (isProcExit || (fdstate & FD_XACT_TEMPORARY))
: FileClose(i);
: }
: }
: }
:
3198 0.0089 : while (numAllocatedDescs > 0)
: FreeDesc(&allocatedDescs[0]);
78 2.2e-04 :}

So we're scanning the array lots of times (there are about 1300
transactions per second), and do no useful work on each scan.

There are about 8900 files in the database, and since this is using a
connection pooler, it's quite likely that each session opens a large
subset of them at least once at some point, and so the VfdCache gets
very large.

I can see two simple ways to solve this problem. One is to create a
second array that keeps pointers to the temp files in VfdCache. Then,
on CleanupTempFiles we scan that array instead of VfdCache directly.

The second one is to use a separate VfdCache for temp files, but this
seems much more involved, requiring touch almost all of fd.c.

Of course, perhaps there's another solution which involves rethinking
fd.c in a more thorough fashion, but I'm not sure how right offhand.

Thoughts?

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