Symptoms

An error "canceling statement due to statement timeout" occurs in PBA when generating a report/viewing some kind of information in PBA GUI.

Cause

The table being quired is too big, therefore the query takes more time for execution than specified in the DB_LOCK_WAIT_TIMEOUT parameter from the global.conf file (usr/local/bm/etc/ssm.conf/global.conf or .global.conf).

See KB article https://kb.parallels.com/en/11174 for instructions on how to investigate the "canceling statement due to statement timeout" problem in general.

Resolution

I. Database optimization

  1. Connect to the PBA database:

    psql -U $DB_USER -h $DB_HOST $DB_NAME

    Example:

    psql -U pba -h 172.16.32.53 pba

  2. Check how much time does the quiry takes for its execution:

    pba=> \timing
Timing is on.
pba=> quiry
Time: 63.717 ms

  3. Determine how much space the problem database table TblName takes up on the disk. Make sure the disk is large enough:

    SELECT pg_size_pretty(pg_total_relation_size('"TblName"')) As fullTableSize;

    Example:

    pba=> SELECT pg_size_pretty(pg_total_relation_size('"Scheduler"')) As fullTableSize;
fulltablesize
---------------
43 MB
(1 row)

  4. Reindex the problem table TblName:

    pba=> REINDEX TABLE "TblName";

    Example:

    pba=> REINDEX TABLE "Scheduler";
REINDEX

  5. Additionally you may check the other tables which are big:

    pba=> select relname, relpages / 128 || ' Mb' as size from pg_class order by relpages desc limit 10;

    and reindex/cluster them

  6. Check the final size of the table. Make sure it has decreased significantly:

    SELECT pg_size_pretty(pg_total_relation_size('"TblName"')) As fullTableSize;

    Example:

    pba=> SELECT pg_size_pretty(pg_total_relation_size('"Scheduler"')) As fullTableSize;
fulltablesize
---------------
20 MB
(1 row)
  7. Check the problem query performance again. It should be much faster now.

Known issues

Cannot open Event Manager screens - kb #115879

Internal content

Link on internal Article