Re: Generate call graphs in run-time - Mailing list pgsql-hackers

On 01/09/2012 10:00 PM, Joel Jacobson wrote:
> Because of this I decided to sample data in run-time to get a real-life
> picture of the system.
> Any functions not being called in productions are not that important to
> include in the documentation anyway.

FWIW I have a similar problem - with a similar solution. I'll try to
describe it, in case there is some synergy to be leveraged :)

> In pgstat_init_function_usage(), each call to a function will add the
> caller->called oid pair unless already set in the hash.
> When pgstat_end_function_usage() is called, the current function oid is
> removed from the List of parent oids.
> In AtEOXact_PgStat(), called upon commit/rollback, the call graph is
> sorted and written to the log unless already seen in the session.
> The variables are resetted in pgstat_clear_snapshot().
> 

My approach was to add parent oid to the per-backend function stats
structure - PgStat_BackendFunctionEntry. Also, I changed the hash key
for that structure to (oid, parent) pair. This means that within the
backend the function usage is always tracked with the context of
calling function. This has the nice property that you get the per-parent
usage stats as well. Also the additional lists for parent tracking are
avoided.

During pgstat_report_stat() the call graph (with stats) is output
to logs and the statistics uploaded to collector -- with the parent oid
removed.

> This functionality is probably something one would like to enable only
> temporarily in the production environment.
> A new configuration parameter would therefore be good, just like
> track_functions. Perhaps track_callgraphs?
> 

I opted to always track the function parents -- this is very cheap. The
logging on the other hand is quite heavy and needs to be explicitly
configured (I used log_usage_stats GUC).

There is a patch for this and we do use it in production for occasional
troubleshooting and dependency analysis. Can't attach immediately
though -- it has some extra cruft in it that needs to be cleaned up.

> Instead of writing the call graphs to the postgres log, it would ne more
> useful to let the statistics collector keep track of the call graphs, to
> allow easier access than having to parse through the log file.

Indeed. Something like a pg_stat_user_function_details view would be
very useful. Something along the lines of:
  Column     |  Type  |
--------------+--------+funcid       | oid    |parentfuncid | oid    | <-- newschemaname   | name   |funcname     |
name  |calls        | bigint |total_time   | bigint |self_time    | bigint |
 

And then rewrite pg_stat_user_functions by aggregating the detailed
view. That'd make the individual pg_stat_get_function* functions a
bit slower, but that is probably a non-issue - at least not if the
pg_stat_user_functions view is rewritten to use a SRF.

regards,
Martin