Use the plprofiler plug-in - User Guide| Alibaba Cloud Documentation Center

This topic describes how to use the plprofiler plug-in to analyze the performance of a PolarDB for PostgreSQL cluster.

Background information

When you use a PostgreSQL server for programming, you can find that a function or a stored procedure written in Procedural Language/PostgreSQL (PL/pgSQL) is a black box. This means that PostgreSQL rarely knows what operations are performed inside a function or a stored procedure. Therefore, each SQL statement error in PL/pgSQL may cause performance bottlenecks. The following common PL/pgSQL issues may occur:

A faulty statement is executed at a fast speed first. However, after the statement is called many times, the statement is executed at a low speed.
Performance bottlenecks randomly occur.
Performance issues occur in the production environment.

To troubleshoot these performance issues, you can only manually analyze the schemas, statistics, and SQL statements or use the pldebugger plug-in. These troubleshooting methods are time-consuming and the results are not displayed clearly. Performance issues randomly occur, which complicates the troubleshooting process. You cannot even identify the cause of the first issue by using these methods. A better troubleshooting method is required to help you identify the performance bottlenecks.

The plprofiler plug-in provides an easy method for you to collect PL/pgSQL performance data and identify the performance issues of PL/pgSQL. This helps you optimize the performance of specific functions, stored procedures, and schemas. For more information about the plug-in, see plprofiler.

Before you begin

Note The following example is used only for Linux and macOS operating systems.

Obtain the source code of plprofiler from GitHub.

Export the environment variables.

export PGHOST=<polardb_host>
export PGPORT=<polardb_port>
export PGUSER=<polardb_user>
export PGPASSWORD=<polardb_password>
export PGDATABASE=pgbench_plprofiler
export PLPROFILER_PATH=<path-to-plprofiler>
export USE_PGXS=1
export PATH=<path-to-plprofiler>/bin:$PATH

Note Replace the connection strings in this example with the actual connection strings. For more information about how to view connection endpoints, see View connection endpoints.

Run the following commands to enter the directory of the source code and install the client:

cd $PLPROFILER_PATH/python-plprofiler
python setup.py install #sudo python setup.py install, or using 'pip install plprofiler'

Log on to the PolarDB for PostgreSQL cluster. Then, execute the following statements to create a PolarDB for PostgreSQL database and the plprofiler plug-in:
```
> CREATE DATABASE pgbench_plprofiler;
> \c pgbench_plprofiler
> CREATE EXTENSION plprofiler;
```
Prepare the tables, data, and functions for testing.
```
cd $PLPROFILER_PATH/examples
bash prepdb.sh
```

Performance analysis

Run the following plprofiler command to analyze the PL/pgSQL performance:
```
plprofiler run --command "SELECT tpcb(1, 2, 3, -42)" --output tpcb-test1.html
```
Note tpcb-test1.html in the preceding command specifies the address of the page that displays the performance analysis result.
After the command is run, the editing page appears. You can edit the title, length, width, and description of the output web page based on your needs and then exit the editing page.
Open the tpcb-test1.html page in your browser, as shown in the following figure.
The flame graph in the preceding figure shows that the function that most affects the PL/pgSQL performance is tpcb_fetch_abalance. You can infer from the performance analysis result that the performance is degraded because no index is created. The performance of the tpcb_upd_accounts function is also unsatisfactory because of its subfunction. To resolve this issue, optimize the subfunction and then check whether the tpcb_upd_accounts function works in a proper way.

Execute the following statement to create an index:

psql
> CREATE INDEX pgbench_accounts_aid_idx ON pgbench_accounts (aid);

Run the plprofiler command to analyze the PL/pgSQL performance again. The following figure shows the performance analysis result.
The flame graph in the preceding figure shows that the tpcb_fetch_abalance function does not affect the PL/pgSQL performance. After the tpcb_fetch_abalance function is optimized, the execution duration of the tpcb_upd_accounts function is shortened.

If the optimization result meets your expectation, the optimization is complete. If the optimization result does not meet your expectation, you can use the new flame graph to identify performance bottlenecks for further optimization.