We have migrated to new Google sites!
Incremental Analyze is much awaited features of the Greenplum database. Analyzing large (partitioned) tables are the most time consuming operation during the regular database maintenance activity. Lets explore this feature a little further.
Incremental Analyze (4.3.5.0)
Greenplum database 4.3.5.0 introduced a new utility called analyzedb. As of
[gpadmin@localhost gpsne-1]$ analyzedb --help
Usage: analyzedb [options]
Analyze a database incrementally. 'Incremental' means if a table or partition has not been modified by DML or DDL commands since the last analyzedb run, it will be automatically skipped since its statistics must be up to date. Some restrictions apply:
1. The incremental semantics only applies to append-only tables or partitions.
2. All heap tables are regarded as having stale stats every time analyzedb is run. This is because we use AO metadata to check for DML or DDL events, which is not available to heap tables.
3. Catalog tables, views and external tables are automatically skipped.
Options:
--version show program's version number and exit
-d <database name> Database name. Required.
-s <schema name> Specify a schema to analyze. All tables in the schema
will be analyzed.
-t <schema name>.<table name>
Analyze a single table. Table name needs to be
qualified with schema name.
-i <column1>,<column2>,...
Columns to include to be analyzed, separated by comma.
All columns will be analyzed if not specified.
-x <column1>,<column2>,...
Columns to exclude to be analyzed, separated by comma.
All columns will be analyzed if not specified.
-f <config_file>, --file=<config_file>
Config file that includes a list of tables to be
analyzed. Table names must be qualified with schema
name. Optionally a list of columns (separated by
comma) can be specified using -i or -x.
-l, --list List the tables to be analyzed without actually
running analyze (dry run).
-p <parallel level> Parallel level, i.e. the number of tables to be
analyzed in parallel. Valid numbers are between 1 and
10. Default value is 5.
--skip_root_stats Skip refreshing root partition stats if any of the
leaf partitions is analyzed.
--full Analyze without using incremental. All tables
requested by the user will be analyzed.
--clean_last Clean the state files generated by last analyzedb run.
All other options except -d will be ignored.
--clean_all Clean all the state files generated by analyzedb. All
other options except -d will be ignored.
-h, -?, --help Show this help message and exit.
-v, --verbose Print debug messages.
-a Quiet mode. Do not prompt for user confirmation.
[gpadmin@localhost gpsne-1]$
While performing the ANALYZE operations, analyzedb creates a snapshot of table metadata snapshot and stores it on disk on the master host. If you run analyzedb to ANALYZE an append-optimized table, the utility checks the metadata taken during a previous analyzedb operation to determine if the table has been modified. An ANALYZE operation is performed only if the table has been modified. If a table or partition has not been modified since the last time it was analyzed, analyzedb automatically skips the table or partition because it already contains up-to-date statistics.
For a partitioned, append-optimized table, analyzedb checks the partitioned table root partition and leaf partitions. If needed, the utility updates statistics for partitions that have changed and, if necessary, the root partition.
To perform the ANALYZE operations, analyzedb creates concurrent sessions to analyze tables in parallel. For each session,analyzedb issues an ANALYZE command to the database and specifies different table names.
This example command specifies a file that contains a list of tables. The command collects statistics on the tables listed in the file analyze-tables in the database named mytest.
$ analyzedb -d sachi -f analyze-table_list