Knowledge articles
Performance/Tuning

You can refer to the pg_stat_all_tables view to check when the latest VACUUM ran for a database object. Monitoring should be enabled to capture the details and check for how long the VACUUM process ran.

You can capture the details using any of the following methods:

• Enable logging and update both log_statement and log_duration parameters in postgresql.conf.
  For details, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server Configuration > 19.8 - Error Reporting and Logging.
• Enable the pg_stat_statement extension.
  For details, refer to PostgreSQL documentation > Part VIII - Appendixes > Appendix F - Additional Supplied Modules > F.29 - pg_stat_statements.
• Use open source or commercial monitoring tools

You can also run VACUUM manually to see how much time the VACUUM process takes.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, Fujitsu Enterprise Postgres for Kubernetes, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

In most installations, it is sufficient to run AUTOVACCUM. If you are experiencing fragmentation, you might have to adjust the autovacuuming parameters to obtain the best results.

For more information, refer to PostgreSQL documentation > Part III - Server administration > Chapter 24 - Routine database maintenance tasks > 24.1 - Routine vacuuming.

Because long-term operations can degrade database access performance, consider periodically running the REINDEX command to reorganize indexes. There is no formula available to estimate the execution time for REINDEX, so please refer to the actual measurement in your environment.

For more information, refer to Fujitsu Enterprise Postgres Operation Guide > Chapter 7 - Periodic Operations > 7.5 - Reorganizing Indexes [Linux version] [Windows version].

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, Fujitsu Enterprise Postgres for Kubernetes, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

You should obtain database statistics.

Information about server process, transactions, and locks can be very important to analyze performance issues or delay. However, these may be wiped out when the database is restarted or after a significant amount of time ellpases. Hence, such information should be collected as soon as possible.

Collect the information below to investigate the issue:

• Server process information - Statistics about client connections to server processes:

  SELECT * FROM pg_stat_activity;

• Transaction information - Information about duration of the transaction (query)

  SELECT pid, wait_event_type, wait_event, state, 
        (current_timestamp - xact_start): interval (3) AS duration, query 
  FROM pg_stat_activity WHERE pid <> pg_backend_pid ();

• Lock information - List of tables waiting for a lock

  SELECT l.locktype, c.relname, l.pid, l.mode, a.query,
        (current_timestamp - xact_start) AS duration
  FROM pg_locks l
       LEFT OUTER JOIN pg_stat_activity a
         ON l.pid = a.pid
       LEFT OUTER JOIN pg_class c
         ON l.relation = c.oid
  WHERE NOT l.granted ORDER BY l.pid;

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

This may be happening because postgres_fdw is configured to retrieve a small number of rows in a single fetch, causing it to communicate with the remote server more often, and thus taking longer.

Resolution

It is possible to reduce the amount of communication with the remote server by increasing the value of the fetch_size option of postgres_fdw.

For more information, refer to PostgreSQL documentation > Part VIII - Appendixes > Appendix F - Additional supplied modules > F.33 - postgres_fdw > F.33.1 - FDW options of postgres_fdw > F.33.1.4 - Remote execution options

For an example on how to use postgres_fdw, refer to our PostgreSQL Insider article Linking to foreign data using foreign data wrappers.

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

Database performance can degraged when it has a large amount of old version of updated and deleted data. PostgreSQL uses a write-once architecture for writing table data, to minimize lock contention. Therefore, when you execute UPDATE and DELETE statements, the table keeps the old version of the updated or deleted data.

Resolution

Enable AUTOVACUUM to collect the old version of updated and deleted data, or run the VACUUM and ANALYZE commands manually at regular intervals.

But keep in mind that the VACUUM command may result in heavy I/O traffic and exclusive table lock, which can degrade the performance of other running sessions. When running the VACUUM command, set the relevant parameters to reduce the performance impact.

For more information, refer to PostgreSQL documentation > Part III - Server administration > Chapter 24 - Routine database maintenance tasks > 24.1 - Routine vacuuming

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

Using the VACCUM command

Before changing any configuration parameters, you can check if there is a need to clean unused tuples (tables) from hard disk. If so, run the command as follows:

VACUUM FULL <tablename>

Increasing the value of work_mem

You can also speed up index lookups by tuning the work_mem parameter, which sets the amount of memory for two operations:

• Sorting data in memory
• Hash table operation

Increasing this parameter reduces disk swapping and speeds up index lookups.

The parameter setting can also be changed on a per-session basis by editing postgresql.conf or by using the SET statement.

However, be aware of large memory consumption because the sort and hash operations are performed at the same time, and the work_mem value is applied to each operation.

For more information, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.4 - Resource consumption > 19.4.1 - Memory

Adjusting the OS readahead setting (for Linux system)

You can also speed up extensive searches in Linux system by adjusting the amount of disk readahead in the OS, so that the OS will understand that PostgreSQL is doing a sequential read, and consequently load the page into the cache first, which speeds up index lookups.

To check the current readahead setting:

# blockdev -getra <device-name-for-the-database-area>

To change the current readahead setting:

# blockdev -setra <value-in-512-byte-sectors> <device-name-for-the-database-area>

However, be aware that the effect of the setting will be small if the value exceeds 16 MB (32768 in 512-byte sectors).

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

You can log SQL statements that take longer to execute than a threshold value (SQL statement execution time) by setting the log_min_duration_statement parameter in postgresql.conf.

For more information on the log_min_duration_statement parameter, refer to PostgreSQL documentation > Part III - Server administration > Chapter 19 - Server configuration > 19.8 - Error reporting and logging > 19.8.2 - When to log > log_min_duration_statement

If you want to log execution plans in addition to SQL statements, use the contrib module auto_explain. By setting a threshold value (SQL statement execution time) for the auto_explain.log_min_duration parameter, execution plans for SQL statements that took longer than the threshold value can be output to the log.

For more information on the auto_explain module, refer to PostgreSQL documentation > Part VIII - Appendix > Appendix F - Additional supplied modules > F.4 - auto_explain

Applicable to

Product: Fujitsu Enterprise Postgres SE, Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux

Indexes are created using memory set aside by the maintenance_work_mem parameter, so this value needs to be big enough for the table. If index creation is taking too long in your environment, then you need to increase its value, to around 3 times as much as required during tests.
It is also recommended to create or rebuild the indexes during the maintenance window or off-peak hours to avoid any transaction blocking.

Applicable to

Product: Fujitsu Enterprise Postgres AE, PostgreSQL

Versions: from 9.5

Architecture: X86, S390x

Operating System: Windows, Linux