gpcheckcat - Part 2

  1. What is gpchectkcat?
  2. When should gpcheckcat be run?
  • In-Short: The gpcheckcat script is used to test tables for inconsistencies.
  • Better Explanation :
  1. This script executes multiple tests to check database catalog inconsistencies. 
  2. Few tests are recommended to be run only when the system is not available to users. 
  3. Such tests are called has Off-line tests. 
  4. These cannot be run concurrently with other workload or the results wil not be usable. So how to run such tests? its always better to start the database in restricted mode when running gpcheckcat, restricted mode helps in preventing users connections reaching database. 
  5. There is a trick to run gpcheckcat without putting database in restricted mode, just set database connection to 0. 
  6. Then terminate all existing users connections. When we run this script, it checks for and removes orphaned, temporary database schemas in the specified database. 
  7. The same will also be reported in command line and logs.
  8. Catalog inconsistencies are inconsistencies that occur between database system tables. 
  9. There are 3 types of inconsistencies:
  • Inconsistencies in system tables at the segment level. 
  • Inconsistencies between same system table across segments.
  • Persistent table inconsistencies are inconsistencies in persistence object state and file system objects on a segments.
  1. It is always better to run Online gpcheckcat on daily bases. (-O option). This is nowhere related to database size. 
  2. It is a best partice to run Offline gpchechcat on all databases at least once in a Month. Most preferred method is to run once in a week (depending on maintenance window).

Comments

Post a Comment

Popular posts from this blog

GP - SQL to fetch table(s) owned by given user

SQL SELECT n.nspname||'.'||c.relname FROM pg_catalog.pg_class As c INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid AND n.nspname = '<schemaname>' AND  c.relkind IN ('r','v') AND c.relstorage != 'x' INNER JOIN pg_catalog.pg_roles AS r ON c.relowner = r.oid AND r.rolname = '<userid>' EXCEPT SELECT partitionschemaname||'.'||partitiontablename FROM pg_catalog.pg_partitions ORDER BY 1; Here <schemaname> and  <userid> can be supplied accordingly to fetch details.
Read more

The Segment - Brief

Image
The Segments In Greenplum Database, the segments are where data is stored and where most query processing occurs. User-defined tables and their indexes are distributed across the available segments in the Greenplum Database system; each segment contains a distinct portion of the data. Segment instances are the database server processes that serve segments. Users do not interact directly with the segments in a Greenplum Database system, but do so through the master. In the reference Greenplum Database hardware configurations, the number of segment instances per segment host is determined by the number of effective CPUs or CPU core. For example, if your segment hosts have two dual-core processors, you may have two or four primary segments per host. If your segment hosts have three quad-core processors, you may have three, six or twelve segments per host. Performance testing will help decide the best number of segments for a chosen hardware platform. Segment Redundancy When ...
Read more

Greenplum Architecture - Brief

Image
Pivotal Greenplum Database is a massively parallel processing (MPP) database server with an architecture specially designed to manage large-scale analytic data warehouses and business intelligence workloads. MPP (also known as a shared nothing architecture) refers to systems with two or more processors that cooperate to carry out an operation, each processor with its own memory, operating system and disks. Greenplum uses this high-performance system architecture to distribute the load of multi-terabyte data warehouses, and can use all of a system's resources in parallel to process a query. Greenplum Database is based on PostgreSQL open-source technology. It is essentially several PostgreSQL database instances acting together as one cohesive database management system (DBMS). It is based on PostgreSQL 8.2.15, and in most cases is very similar to PostgreSQL with regard to SQL support, features, configuration options, and end-user functionality. Database users interact with ...
Read more