Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> [...] Can you correlate the performance hit with any specific part
> of autovacuum? In particular, I'm wondering if it matters whether
> vacuum is cleaning tables or indexes [...]

In case it helps, this systemtap run will report on the top few
time-sampled call graphs of post* processes:

# debuginfo-install -y postgresql
# stap -V [...Fedora16...]
Systemtap translator/driver (version 1.7/0.153 non-git sources)
# cat post-prof.stp
probe timer.profile {
if (substr(execname(),0,4) != "post") next
bt[sprint_ustack(ubacktrace())] <<< 1
}
probe end {
foreach ([s] in bt- limit 50) { println("\n",@count(bt[s]),":",s) }
}
global bt[20000]

# stap post-prof.stp -v -d /usr/bin/postgres -d /usr/bin/postmaster --ldd --all-modules --suppress-handler-errors
[wait awhile during workload]
^C

1390:index_getnext+0x1f9 [postgres]
IndexNext+0x56 [postgres]
ExecScan+0x14e [postgres]
ExecProcNode+0x228 [postgres]
ExecLimit+0xb8 [postgres]
ExecProcNode+0xd8 [postgres]
standard_ExecutorRun+0x14a [postgres]
PortalRunSelect+0x287 [postgres]
PortalRun+0x248 [postgres]
PostgresMain+0x754 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

935:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
get_relation_info+0x5e2 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]

721:__send+0x22 [libc-2.14.90.so]
internal_flush+0x3b [postgres]
pq_flush+0x22 [postgres]
ReadyForQuery+0x29 [postgres]
PostgresMain+0x49e [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

431:recv+0x22 [libc-2.14.90.so]
secure_read+0x1c6 [postgres]
pq_recvbuf+0x5f [postgres]
pq_getbyte+0x15 [postgres]
PostgresMain+0x4bf [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

380:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
estimate_rel_size+0x7c [postgres]
get_relation_info+0x121 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
[...]