NAME

PostgreSQL::PLPerl::NYTProf - Profile PostgreSQL PL/Perl functions with Devel::NYTProf

VERSION

version 1.001

SYNOPSIS

Load via a line in your plperlinit.pl file:

use PostgreSQL::PLPerl::NYTProf;

Load via the PERL5OPT environment variable:

$ PERL5OPT='-MPostgreSQL::PLPerl::NYTProf' pg_ctl ...

DESCRIPTION

Profile PL/Perl functions inside PostgreSQL database with Devel::NYTProf.

ENABLING

In order to use this module you need to arrange for it to be loaded when PostgreSQL initializes a Perl interpreter.

Create a plperlinit.pl file in the same directory as your postgres.conf file, if it doesn't exist already.

In the plperlinit.pl file write the code to load this module:

use PostgreSQL::PLPerl::NYTProf;

When it's no longer needed just comment it out by prefixing with a #.

PostgreSQL 8.x

Set the PERL5OPT before starting postgres, to something like this:

PERL5OPT='-e "require q{plperlinit.pl}"'

The code in the plperlinit.pl should also include delete $ENV{PERL5OPT}; to avoid any problems with nested invocations of perl, e.g., via a plperlu function.

PostgreSQL 9.0

For PostgreSQL 9.0 you can still use the PERL5OPT method described above. Alternatively, and preferably, you can use the plperl.on_init configuration variable in the postgres.conf file.

plperl.on_init='require q{plperlinit.pl};'

Alternative Method

It you're not already using the PERL5OPT environment variable to load a plperlinit.pl file, as described above, then you can use it as a quick way to load the module for ad-hoc use:

$ PERL5OPT='-MPostgreSQL::PLPerl::NYTProf' pg_ctl ...

USAGE

By default the NYTProf profile data files will be written into the database directory, alongside your postgres.conf, with the process id of the backend appended to the name. For example nytprof.out.54321.

You'll get one profile data file for each database connection. You can use the nytprofmerge utility to merge multiple data files.

To generate a remort from a data file, use a command like:

nytprofhtml --file=$PGDATA/nytprof.out.54321 --open

LIMITATIONS

XXX Currently only PostgreSQL 9.0 is fully supported

XXX Needs a not-yet-released version of Sub::Name to get the right details about the subs.

XXX Needs a not-yet-developed version of NYTProf to see the source code of the subs (because they are defined by evals).

PL/Perl Function Names Are Missing

The names of functions defined using CREATE FUNCTION don't show up in NYTProf because they're compiled as anonymous subs using a string eval. There's no easy way to determine the PL/Perl function name because it's only known to the postgres internals.

XXX a workaround is being developed.

For PostgreSQL 8 an explicit call to DB::finish_profile is needed

Postgres 8 doesn't execute END blocks when it shuts down, so NYTProf doesn't get a chance to terminate the profile cleanly. To get a usable profile you need to explicitly call finish_profile() in your plperl code.

Can't use plperl and plperlu at the same time

Postgres uses separate Perl interpreters for the plperl and plperlu languages. NYTProf is not multiplicity safe (as of version 3.02). It should just profile whichever language was used first and ignore the second but at the moment the initialization of the second interpreter fails.

SEE ALSO

Devel::NYTProf

AUTHOR

Tim Bunce, http://www.tim.bunce.name and http://blog.timbunce.org

COPYRIGHT AND LICENSE

Copyright (C) 2009 by Tim Bunce.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.