Skip to main content
Skip table of contents

Slow login for end users in Digital Access

This article describes troubleshooting steps that can be used if end users in Smart ID Digital Access component experiences slow logon.

Problem

If the login for end users is slow, and this cannot be explained by other system circumstances, then the reason could be that the auto maintenance function in Postgres fails and does not clean up the database. This results in bloated indexes and also ghost rows which are rows that have been deleted but not yet garbage collected. This can result in slow login for end users.

Solution

Find out if auto maintenance is not functioning
  1. Enter these commands:
    ssh into the appliance

    sudo bash

    cd /opt/postgres/9.1/bin

    sudo -u postgres ./psql

    \c hag

  2. Copy and execute this function/statement:

    SELECT
      current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
      ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
      CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
      iname, /*ituples::bigint, ipages::bigint, iotta,*/
      ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
      CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
    FROM (
      SELECT
        schemaname, tablename, cc.reltuples, cc.relpages, bs,
        CEIL((cc.reltuples*((datahdr+ma-
          (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
        COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
        COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
      FROM (
        SELECT
          ma,bs,schemaname,tablename,
          (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
          (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
        FROM (
          SELECT
            schemaname, tablename, hdr, ma, bs,
            SUM((1-null_frac)*avg_width) AS datawidth,
            MAX(null_frac) AS maxfracsum,
            hdr+(
              SELECT 1+COUNT(*)/8
              FROM pg_stats s2
              WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
            ) AS nullhdr
          FROM pg_stats s, (
            SELECT
              (SELECT current_setting('block_size')::NUMERIC) AS bs,
              CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
              CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
            FROM (SELECT version() AS v) AS foo
          ) AS constants
          GROUP BY 1,2,3,4,5
        ) AS foo
      ) AS rs
      JOIN pg_class cc ON cc.relname = rs.tablename
      JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
      LEFT JOIN pg_index i ON indrelid = cc.oid
      LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
    ) AS sml
    ORDER BY wastedbytes DESC;

  3. If the output shows a lot of (several MB) unused space in the tables, auto maintenance is not functioning. Continue with the next step: to free up space and restart the database.
Free up space and restart database
  1. If If auto maintenance is not functioning (see instruction above), run this command (after creating a backup):

    VACUUM FULL;

  2. If this command does not complete within 30 seconds press CTRL+C and exit to bash with:

    \q

  3. Enter this command:

    /etc/init.d/postgres-9.1-openscg restart

  4. And then enter these commands:
    sudo -u postgres ./psql

    \c hag

  5. Run this command again:
    VACUUM FULL;

  6. Run the function/statement regarding auto maintenance again (see instruction above) to confirm that Vacuum has functioned properly.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.