Re: VACUUM FULL performance issues with pg_largeobject table - Mailing list pgsql-general

Hi Tom,

Unfortunately, I've tried your advice, and I think that we're still in a CPU-bound situation even after following the re-indexing and re-vacuuming.

Fortunately, though, I've just learned about a poor-man's profiler under Linux named pstack, and it's telling me that the vacuum process is spending most of its time in the enough_space() function in vacuum.c (somewhere around line 2574 in the most recent version in CVS I believe).

When I run pstack, I almost always see the execution stack like this:

% pstack 12158
#0  0x0000000000527478 in enough_space ()
#1  0x0000000000528b60 in repair_frag ()
#2  0x000000000052b130 in full_vacuum_rel ()
#3  0x000000000052bab8 in vacuum_rel ()
#4  0x000000000052bf12 in vacuum ()
#5  0x00000000005d69b9 in PortalRunUtility ()
#6  0x00000000005d79d5 in PortalRunMulti ()
#7  0x00000000005d80e5 in PortalRun ()
#8  0x00000000005d3e0b in exec_simple_query ()
#9  0x00000000005d4a6b in PostgresMain ()
#10 0x00000000005abe0b in ServerLoop ()
#11 0x00000000005ac9bd in PostmasterMain ()
#12 0x000000000055ae7e in main ()

Every now and then, I can see a call to ReadBufferExtended from repair_frag(), so the CPU-intensive part does eventually find what it's looking for, but I'm hopeful that this could somehow be done more efficiently and this process could be limited by I/O only.

So...I guess my question is this: is it a known issue that this code path can get very cpu-bound for large numbers of blob garbage?  Looking at the code for enough_space(), it is almost hard to believe that a lot of time is being spent here, but perhaps it's just getting called a LOT from repair_frag().  I'm no stranger to hacking code if I have to....thanks!

Sam