Pivotal Knowledge Base

Follow

How to track Analyze progress during gpdbrestore utility

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS All versions
Others  

Purpose

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

Cause

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

Procedure

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

For large databases, system will take time to do analyze during gpdbrestore if the tables were never had statistics collected or database is restored to new cluster. To identify progress during analyze step in such scenarios, following steps should to 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 session level and for this SQL only.

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