NAME

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

VERSION

version 1.002

SYNOPSIS

Load via the PERL5OPT environment variable:

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

or load via your postgres.conf file:

    custom_variable_classes = 'plperl'
    plperl.on_init = 'use PostgreSQL::PLPerl::NYTProf;'

and restart the server.

Then run some PL/Perl code:

    $ psql -c "do 'sub w { } w() for 1..100_000' language plperl" template1

which will create a nytprof.out.PID file in the $PGDATA directory, where PID is the process id of the postgres backend.

Finally, run nytprofhtml to generate a report, for example:

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

DESCRIPTION

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

PostgreSQL 9.0 or later is required.

ENABLING

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

Quick Occasional Use

The PERL5OPT environment variable can be used like this:

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

This will be effective for any pg_ctl command that restarts the postmaster process, so restart will work but reload won't.

The profiler will remain enabled until the the postmaster process is restarted.

Via postgres.conf

You can simply add a use statement to your postgres.conf file:

    plperl.on_init='use PostgreSQL::PLPerl::NYTProf;'

though I'd recommend arranging for PostgreSQL to load a separate plperloninit.pl file from same directory as your postgres.conf file:

    plperl.on_init='require "plperloninit.pl";'

then you can put whatever Perl statements you want in that file:

    use PostgreSQL::PLPerl::NYTProf;

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

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 if needed.

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

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

INTERPRETING REPORTS

PL/Perl functions are given names in perl that include the OID of the PL/Perl function. So a function created by CREATE FUNCTION foo () ... would appear in the reports as something like main::foo__3762.

PROFILE ON DEMAND

The instructions above enable profiling for all database sessions that use PL/Perl. Instead of profiling all sessions it can be useful to have the profiler loaded into the server but only enable it for particular sessions.

You can do this by loading setting the NYTPROF environment variable to include the "start=no" option. Then, to enable profiling for a particular session you just need to call the DB::enable_profile function. For example:

    do 'DB::enable_profile' language plperl;

See "RUN-TIME CONTROL OF PROFILING" in Devel::NYTProf.

The performance impact of loading but not enabling NYTProf should be very low (though I've not tried measuring it). So, while I wouldn't recommend doing that on a production instance, it would be fine on a development instance.

LIMITATIONS

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 4.05). It should just profile whichever language was used first and ignore the other, but there may still be problems in this situation. Let me know if you encounter any odd behaviour.

PL/Perl functions with unusual names are __ANON__

PL/Perl functions are created as anonymous subroutines in Perl. PostgreSQL::PLPerl::NYTProf arranges for them to be given names. The logic currently only works for names that match /^\w+$/.

SEE ALSO

Devel::NYTProf

AUTHOR

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

COPYRIGHT AND LICENSE

  Copyright (C) 2009-2010 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.