How to Track Analyze Progress during gpdbrestore Utility

Products

• VMware Tanzu Greenplum

Issue/Introduction

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

 

Environment

Cause

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

 

Resolution

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 the 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.

Additional Information

+ Environment:

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