Pivotal Knowledge Base

Follow

How to Track Analyze Progress during gpdbrestore Utility

Environment

  • Pivotal Greenplum Database (GPDB) 4.3.x
  • Operating System (OS)- Red Hat Enterprise Linux (RHEL) 6.x

Purpose

This article helps users to track the progress of "analyze" during gpdbrestore utility.

Cause

Long-running "analyze" during gpdbrestore left customer in a dilemma as there is no way to track the progress.

Procedure

As part of gpdbrestore, the system will perform "analyze" on the entire database or list of tables unless --noanalyze option is specified.

For large databases, the system will take time to do analyze during gpdbrestore if the tables were never had statistics collected or database is restored to a new cluster. To identify progress during analyze step in such scenarios, following steps should be performed:

a. Connect to Database
psql

b. Enable GUC to get invisible rows
set gp_select_invisible=on

c. Run following SQL
select distinct Table_Analyzed from (select c.nspname ||'.'|| b.relname Table_Analyzed
from pg_stat_last_operation a, pg_class b ,pg_namespace c
where a.objid=b.oid and b.relnamespace = c.oid
and a.statime >= (select max(query_start) from pg_stat_activity where current_query='analyze;') and a.staactionname='ANALYZE'
and nspname not in ('gp_toolkit','pg_toast','pg_bitmapindex','pg_catalog','information_schema','pg_aoseg') order by a.statime desc) result;

Note:

  1. This SQL will work only when analyze process is still in progress during gpdbrestore 
  2. Data will be visible only when GUC gp_select_invisible is on. Please enable this GUC at the session level and for this SQL only.

The SQL can be modified according to user requirement for additional attribute visibility.

Comments

  • Avatar
    Gurupreet Singh Bhatia

    Below is output of above query, What it mean?
    table_analyzed
    ----------------
    (0 rows)

    Time: 22.949 ms

Powered by Zendesk