Fujitsu Logo
ENQUIRE

    Knowledge articles - Performance/Tuning

    How do I monitor and collect VACUUM processing statistics?KB5001

    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:

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Long-term operations cause fragmentation of tables and indexes, which result in performance degradation. Are there cases when I should run something other than AUTOVACUUM (e.g., VACUUM FULL)? What indicators should I use to judge? Are there any other factors causing performance degradation in long-term operations?KB5002

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    What data should I collect when the performance of the application is delayed?KB5003

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Why is searching for a remote server with postgres_fdw taking longer than searching on the remote server?KB5004

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Database performance has degraged due to increased disk space usage caused by repeated updates and deletes.KB5005

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    How can I speed up index searches?KB5006

    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    How do I trace slow SQL?KB5007
    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

    Architecture: X86, S390x

    Operating System: Windows, Linux

    Versions: from 9.5

    Read our latest blogs

    Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.