Hi,
Darcy Buskermolen noticed that when one has many databases, the autovac
launcher starts eating too much CPU.
I tried it here with 200 databases and indeed it does seem to eat its
share. Even with the default naptime, which I wouldn't have thought
that was too high (it does make the launcher wake up about three times a
second though).
I'm looking at a profile and I can't seem to make much sense out of it.
It seems to me like the problem is not autovac itself, but rather the
pgstat code that reads the stat file from disk. Of course, autovac does
need to read the file fairly regularly.
Here is the top lines of gprof output.
Comments? Is there something here that needs fixing?
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)
Flat profile:
Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls ms/call ms/call name
40.00 0.02 0.02 113360 0.00 0.00
hash_search_with_hash_value
20.00 0.03 0.01 72816 0.00 0.00 AllocSetFreeIndex
20.00 0.04 0.01 55517 0.00 0.00 AllocSetAlloc
20.00 0.05 0.01 4247 0.00 0.00 choose_nelem_alloc
0.00 0.05 0.00 396779 0.00 0.00 pg_isblank
0.00 0.05 0.00 119276 0.00 0.00 calc_bucket
0.00 0.05 0.00 113360 0.00 0.00 hash_search
0.00 0.05 0.00 113330 0.00 0.00 hash_uint32
0.00 0.05 0.00 113330 0.00 0.00 oid_hash
0.00 0.05 0.00 105035 0.00 0.00 get_hash_entry
0.00 0.05 0.00 55514 0.00 0.00 MemoryContextAlloc
0.00 0.05 0.00 41798 0.00 0.00 next_token
0.00 0.05 0.00 25512 0.00 0.00 DynaHashAlloc
0.00 0.05 0.00 16187 0.00 0.00 TimestampDifferenceExceeds
0.00 0.05 0.00 8742 0.00 0.00 MemoryContextStrdup
0.00 0.05 0.00 8511 0.00 0.00 seg_alloc
0.00 0.05 0.00 8507 0.00 0.00 my_log2
0.00 0.05 0.00 8379 0.00 0.00 read_pg_database_line
0.00 0.05 0.00 8364 0.00 0.00 check_list_invariants
0.00 0.05 0.00 8353 0.00 0.00 lappend
0.00 0.05 0.00 8322 0.00 0.00 backend_read_statsfile
0.00 0.05 0.00 8322 0.00 0.00 pgstat_fetch_stat_dbentry
index % time self children called name
<spontaneous>
[1] 99.6 0.00 0.05 reaper [1]
0.00 0.05 1/1 StartAutoVacLauncher [3]
0.00 0.00 1/1 load_role [51]
0.00 0.00 2/3 StartChildProcess [238]
0.00 0.00 1/60 errstart [179]
0.00 0.00 1/1 AutoVacuumingActive [289]
-----------------------------------------------
0.00 0.05 1/1 StartAutoVacLauncher [3]
[2] 99.5 0.00 0.05 1 AutoVacLauncherMain [2]
0.00 0.05 56/56 launch_worker [5]
0.00 0.00 1/1 rebuild_database_list [27]
0.00 0.00 2/2 errmsg [78]
0.00 0.00 2/2 errfinish [101]
0.00 0.00 1/1 BaseInit [115]
0.00 0.00 1/4330 AllocSetContextCreate [24]
0.00 0.00 118/118 PostmasterIsAlive [165]
0.00 0.00 59/59 launcher_determine_sleep [181]
0.00 0.00 59/59 pg_usleep [182]
0.00 0.00 59/254 LWLockAcquire [157]
0.00 0.00 59/254 LWLockRelease [158]
0.00 0.00 58/288 GetCurrentTimestamp [156]
0.00 0.00 58/16187 TimestampDifferenceExceeds
[135]
0.00 0.00 10/22 pqsignal [198]
0.00 0.00 2/60 errstart [179]
0.00 0.00 1/1 init_ps_display [383]
0.00 0.00 1/1 InitAuxiliaryProcess [308]
0.00 0.00 1/116 MemoryContextSwitchTo [166]
0.00 0.00 1/1 autovac_balance_cost [368]
0.00 0.00 1/1 proc_exit [403]
-----------------------------------------------
0.00 0.05 1/1 reaper [1]
[3] 99.5 0.00 0.05 1 StartAutoVacLauncher [3]
0.00 0.05 1/1 AutoVacLauncherMain [2]
0.00 0.00 1/4 fork_process [230]
0.00 0.00 1/1 on_exit_reset [389]
0.00 0.00 1/1 ClosePostmasterPorts [296]
-----------------------------------------------
0.00 0.05 56/56 launch_worker [5]
[4] 97.6 0.00 0.05 56 do_start_worker [4]
0.00 0.04 8176/8322 pgstat_fetch_stat_dbentry [6]
0.00 0.01 56/57 get_database_list [18]
0.00 0.00 56/86 MemoryContextDelete <cycle 1>
[31]
0.00 0.00 56/57 autovac_refresh_stats [34]
0.00 0.00 56/4330 AllocSetContextCreate [24]
0.00 0.00 16072/16187 TimestampDifferenceExceeds
[135]
0.00 0.00 8176/8176 TransactionIdPrecedes [139]
0.00 0.00 112/254 LWLockAcquire [157]
0.00 0.00 112/254 LWLockRelease [158]
0.00 0.00 112/116 MemoryContextSwitchTo [166]
0.00 0.00 112/288 GetCurrentTimestamp [156]
0.00 0.00 56/56 ReadNewTransactionId [185]
0.00 0.00 56/57 list_head [183]
0.00 0.00 56/56 SendPostmasterSignal [186]
-----------------------------------------------
0.00 0.05 56/56 AutoVacLauncherMain [2]
[5] 97.6 0.00 0.05 56 launch_worker [5]
0.00 0.05 56/56 do_start_worker [4]
0.00 0.00 56/56 DLMoveToFront [184]
-----------------------------------------------
0.00 0.00 146/8322 rebuild_database_list [27]
0.00 0.04 8176/8322 do_start_worker [4]
[6] 80.6 0.00 0.04 8322 pgstat_fetch_stat_dbentry [6]
0.00 0.04 8322/8322 backend_read_statsfile [7]
0.00 0.00 8322/113360 hash_search [9]
-----------------------------------------------
0.00 0.04 8322/8322 pgstat_fetch_stat_dbentry [6]
[7] 77.5 0.00 0.04 8322 backend_read_statsfile [7]
0.00 0.04 29/29 pgstat_read_statsfile [8]
0.00 0.00 29/30 IsAutoVacuumLauncherProcess
[192]
-----------------------------------------------
0.00 0.04 29/29 backend_read_statsfile [7]
[8] 77.5 0.00 0.04 29 pgstat_read_statsfile [8]
0.00 0.02 104864/113360 hash_search [9]
0.00 0.02 4234/4242 hash_create [13]
0.00 0.00 29/29 pgstat_setup_memcxt [59]
0.00 0.00 29/93 AllocateFile [174]
0.00 0.00 29/93 FreeFile [175]
-----------------------------------------------
0.00 0.00 4/113360 pg_tzset [63]
0.00 0.00 26/113360 ShmemInitStruct [65]
0.00 0.00 144/113360 rebuild_database_list [27]
0.00 0.00 8322/113360 pgstat_fetch_stat_dbentry [6]
0.00 0.02 104864/113360 pgstat_read_statsfile [8]
[9] 43.0 0.00 0.02 113360 hash_search [9]
0.02 0.00 113360/113360 hash_search_with_hash_value
[10]
0.00 0.00 113330/113330 oid_hash [133]
0.00 0.00 30/30 string_hash [195]
-----------------------------------------------
0.02 0.00 113360/113360 hash_search [9]
[10] 43.0 0.02 0.00 113360 hash_search_with_hash_value [10]
0.00 0.00 105035/105035 get_hash_entry [26]
0.00 0.00 113360/119276 calc_bucket [131]
0.00 0.00 3770/3770 has_seq_scans [148]
0.00 0.00 3770/3770 expand_table [147]
0.00 0.00 27/38 strlcpy [189]
0.00 0.00 2/2 string_compare [285]
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
