<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      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

      Versions: from 9.5

      Architecture: X86, S390x

      Operating System: Windows, Linux

      It takes a long time to create the indexes. How can I speed this up?KB5008

      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

      Read our latest blogs

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