Search Engine: Elastic

Article ID: 122917, created on Sep 17, 2014, last review on Feb 4, 2016

  • Applies to:
  • Business Automation

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

198398b282069eaf2d94a6af87dcb3ff caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07

Email subscription for changes to this article
Save as PDF