Find the slowest DBI queries with dbiprof

You will turn a DBI::ProfileDumper file into a short, useful report: first by total runtime, then by query count or a filtered method such as execute. The examples use the installed libdbi-perl package, version 1.643-4ubuntu0.1, whose dbiprof command reports version 1.000000. Allow about ten minutes if a profile file already exists, or longer if you also need to collect a profile.

1. Check the installed command

Confirm which executable your shell will run and record its version; this is an ordinary, unprivileged check:

$ command -v dbiprof
/usr/bin/dbiprof
$ dbiprof --version
dbiprof 1.000000

The local help output uses both short option spellings such as -sort=count and the long forms documented by the manpage, such as --sort count. Use the long form in scripts because its purpose is easier to recognise during review:

$ dbiprof --help
dbiprof [options] [files]

Reads and merges DBI profile data from files and prints a summary.

Checkpoint: You have confirmed the executable and version before interpreting a report.

2. Locate a profile file

dbiprof reads dbi.prof when you do not supply a file name. A DBI::ProfileDumper setup can also write to a named file, so look for the actual output rather than guessing:

$ ls -l dbi.prof /path/to/profile.prof

Replace /path/to/profile.prof with a real path, or inspect the application configuration that set the DBI profile output. You only need read access to analyse the file. Do not use sudo to make a missing or unreadable profile appear; first check the path and the account that created it.

If no profile exists yet, collect one through the application or its normal wrapper. The companion DBI::ProfileDumper manual documents the DBI_PROFILE environment variable and the dbi.prof default. Profiling changes what the application records and may expose SQL text or other sensitive values, so keep the output permissions and retention period appropriate for the environment.

3. Produce the default slowest-query report

Pass the profile file explicitly when it is not named dbi.prof:

$ dbiprof /path/to/profile.prof

The report's exact rows and formatting depend on the profile data. It contains ten items by default and sorts by total runtime across all runs. A high total may mean one very slow execution, many moderate executions, or both. Check the count and longest-run values before deciding what to optimise.

With the default file name, the equivalent command is:

$ dbiprof

Checkpoint: You have a report from the intended profile file and know that its default ranking is total time, not the single slowest run.

4. Change the ranking and report size

Use --sort count to find frequently executed paths. Add --number when ten rows are too many or too few:

$ dbiprof --sort count --number 15 /path/to/profile.prof

The available ranking fields are total, longest, count, first, shortest, key1, key2 and key3. The key fields sort values from the profile path, so their usefulness depends on how the application configured DBI profiling. Sorting by more than one field is not supported by this installed command.

To see the lowest values first, reverse the selected sort:

$ dbiprof --sort total --reverse /path/to/profile.prof

--number all requests every result. That can produce a large report, so redirect it only when you have chosen a destination deliberately.

5. Filter by a profile key

Filtering is useful when the profile path records both a statement and a DBI method. Keys are numbered from one. For a path such as [ DBIprofile_Statement, DBIprofile_Methodname ], key 2 is the method name:

$ dbiprof --match key2=execute --sort total /path/to/profile.prof

This keeps only entries whose selected key matches execute. Matching is case-insensitive by default. Add --case-sensitive when the difference matters:

$ dbiprof --case-sensitive --match key2=execute /path/to/profile.prof

A value enclosed by slashes is treated as a regular expression. To keep statements beginning with SELECT, use:

$ dbiprof --match 'key1=/^SELECT/' /path/to/profile.prof

Shell quoting keeps the regular-expression punctuation together. Use --exclude for the inverse, such as removing prepare entries before reviewing execution work:

$ dbiprof --exclude key2=prepare /path/to/profile.prof

Checkpoint: The filter matches the profile path you actually used. If the report becomes empty, verify the key number and spelling before changing the application.

6. Inspect nodes without ranking them

--dumpnodes prints the profile nodes as a Perl data structure. This helps when you need to discover what the configured path contains before choosing a key filter:

$ dbiprof --dumpnodes /path/to/profile.prof

The exact structure follows the profile data. Add --sort if you want the dumped list ordered. Do not treat this output as a stable interchange format for another program; it is a diagnostic Perl representation.

7. Treat deletion as a separate, destructive operation

Warning: --delete tells DBI::ProfileData to remove profile files after reading them. It is not needed for an ordinary report and the deletion is not reversible through dbiprof. Do not combine it with a first inspection, a shared profile directory, or a command whose file list you have not checked.

If retention policy genuinely requires cleanup, make a copy or archive first, confirm the exact input paths, and run the command as the account that owns the files. Without --delete, the normal report leaves the profile data in place. If you accidentally delete a file, recover it from the application's backup or filesystem recovery process; there is no dbiprof undo command.

8. Diagnose a failed report

A missing file is reported as a read error and the command exits non-zero. Check the path and permissions without changing anything:

$ dbiprof /tmp/does-not-exist-dbiprof.prof
Unable to load profile data: Unable to read profile file '/tmp/does-not-exist-dbiprof.prof': No such file or directory at /usr/bin/dbiprof line 63.
$ printf 'exit status: %s\n' "$?"
exit status: 2
$ test -r /path/to/profile.prof && echo readable
readable

The Perl source location in the diagnostic is version-specific. If the file is readable but the data is rejected, preserve a copy and check that it was written by DBI::ProfileDumper or another compatible DBI::ProfileData producer. Do not edit a profile by hand while diagnosing it.

Done means