Use the plprofiler plugin

Updated at:

You can use the plprofiler plugin to perform performance analysis on your PolarDB for PostgreSQL (Compatible with Oracle) cluster.

Background information

When you use the PostgreSQL server for programming, you may find that PL/pgSQL in PostgreSQL is a black-box environment, and any internal issue may cause a performance bottleneck. Common exceptions include:

  • A problem statement runs fast at first but slows down after multiple calls.

  • Random performance bottlenecks.

  • Performance issues on production systems.

Typically, the preceding performance issues can only be troubleshooted through manual analysis (analyzing schemas, statistics, and SQL statements) or breakpoints (pldebugger). Troubleshooting is time-consuming and unintuitive, and performance issues may come and go (for the first issue mentioned above, the root cause may not even be identifiable). Therefore, you need a better troubleshooting method to help you identify performance bottlenecks.

plprofiler provides a concise way to collect PL/pgSQL performance data for identifying PL/pgSQL performance bottlenecks, allowing you to optimize the performance of functions, stored procedures, and schemas in a targeted manner. For more information about the plugin, see plprofiler.

Prerequisites

Note

The following operation examples apply only to Linux and macOS.

  1. Obtain the plprofiler source code from GitHub.

  2. Export 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 string information in the example with your actual connection string information. For information about how to view the connection address, see View or apply for an endpoint.

  3. Go to the source code directory and install the client software. The following example shows the operations.

    cd $PLPROFILER_PATH/python-plprofiler
    python setup.py install #sudo python setup.py install, or using 'pip install plprofiler'
  4. Log on to the PolarDB for PostgreSQL (Compatible with Oracle) database, and create a database and the plugin. The following example shows the operations.

    > CREATE DATABASE pgbench_plprofiler;
    > \c pgbench_plprofiler
    > CREATE EXTENSION plprofiler;
  5. Prepare the tables, data, and functions for testing.

    cd $PLPROFILER_PATH/examples
    bash prepdb.sh

Performance analysis

  1. Run the plprofiler command to perform performance analysis. The command is as follows:

    plprofiler run --command "SELECT tpcb(1, 2, 3, -42)" --output tpcb-test1.html
    Note

    tpcb-test1.html is the URL of the output performance analysis page.

  2. After the command is complete, an editor opens where you can edit the title, width, height, description, and other information of the output web page. You do not need to modify anything and can exit directly.

    [current]
    title = PL Profiler Report for current
    tabstop = 8
    svg_width = 1200
    table_width = 80%
    desc = <h1>PL Profiler Report for current</h1>
            <p>
            <!-- description here -->
            </p>
  3. Open tpcb-test1.html in a browser.

    The flame graph shows that the function that most affects performance is tpcb_fetch_abalance. Analysis shows that the poor performance is caused by the absence of an index. Although tpcb_upd_accounts also shows poor performance, this function may be affected by its child functions. You need to optimize the child functions first and then check whether the performance is normal.

  4. Create an index to complete this optimization. The following example shows the operations.

    psql
    > CREATE INDEX pgbench_accounts_aid_idx ON pgbench_accounts (aid);
  5. Run the plprofiler command again to perform performance analysis.

    The flame graph shows that tpcb_fetch_abalance is no longer a performance bottleneck. Because tpcb_fetch_abalance is optimized, the execution time of tpcb_upd_accounts is also reduced.

    If the result meets your expectations, the optimization is complete. If the result does not meet your expectations, you can continue to identify performance bottlenecks from the new flame graph and optimize them.