Greenplum Administration DBA FAQ Part II

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article:

• Pertains to all versions of Greenplum Database (GPDB).
• Covers some frequently asked Admin DBA related questions.
• Provides guidelines on how to troubleshoot, maintain and configure a Greenplum cluster

Resolution

1. How to investigate a gpstart failure

Check gpstart logfile using the following command: (Note: replace YYYMMDD with the date of the failure)

less ~gpadmin/gpAdminLogs/gpstart_YYYYMMDD.log

The file will indicate what failed to start and why. It may be due to an issue with the coordinator or a segment that failed.

If the coordinator failed to start view the last updated file in the $COORDINATOR_DATA_DIRECTORY/pg_log (for GPDB 6.x) or $COORDINATOR_DATA_DIRECTORY/log (for GPDB 7.x) directory. For example:

# Change directory to the log or pg_log directory
cd $COORDINATOR_DATA_DIRECTORY/log

# list the files sorted by "last updated time"
ls -ltr

# view the last updated file
less startup.log
# or
less gpdb-YYYY-MM-DD_HHMMSS.csv

If the segments failed to start, connect to the segment host running the segment and check the same log files as above:

# Connect to the segment host, (Change to the correct host)
ssh sdw1

# Change directory to the log or pg_log directory (Change to the correct segment data directory).
cd /data1/primary/gpseg0/log

# list the files sorted by "last updated time"
ls -ltr

# view the last updated file
less startup.log
# or (Provide the correct filename)
less gpdb-YYYY-MM-DD_HHMMSS.csv

 

2. What is "gpstop -m" and "gpstart -m" used for?

The gpstart -m command allows a user to start the master only and none of the data segments. It is used primarily by support to get system level information/configuration. An end user would not regularly or normally use it.

 

3. How to find the uptime of the Greenplum database?

To find the uptime of the GPDB use the following command:

select now() - pg_postmaster_start_time() ;

 

4. What is the procedure to get rid of mirror segments?

There is no utilities available to remove mirrors from Greenplum. Contact Greenplum support for assistance in removing mirrors.

 

5. How to run gpcheckcat.

The gpcheckcat tool is used to check catalog inconsistencies between the master and segments. It can be found in the $GPHOME/bin/ directory:

$ gpcheckcat --help

Usage: gpcheckcat [<option>] [dbname]

    -? | --help              : help menu
    -B parallel              : number of worker threads
    -g dir                   : generate SQL to rectify catalog corruption, put it in dir
    -p port                  : DB port number
    -P passwd                : DB password
    -U uname                 : DB User Name
    -v                       : verbose
    -A                       : all databases
    -S option                : shared table options (none, only)
    -O                       : Online
    -l                       : list all tests
    -R test | 'test1, test2' : run this particular test(s) (quoted, comma seperated list for multiple tests)
    -s test | 'test1, test2' : skip this particular test(s) (quoted, comma seperated list for multiple tests)
    -C catname               : run cross consistency, FK and ACL tests for this catalog table
    -x                       : set session level GUCs
    --skip-leaked-temp-schema: skip temporary leaked schema, temp and toast tables cleanup

    Test subset options are mutually exclusive, use only one of '-R', '-s', or '-C'.

Example:

gpcheckcat -O mydb > gpcheckcat_mydb_$(date +%Y%m%d).log 2>&1

 

6. How to delete a coordinator standby.

To remove the currently configured coordinator standby from a Greenplum Database cluster, run:

 gpinitstandby -r

 

7. How to re-sync a standby.

Use this command only if a standby coordinator is already configured, but is no longer in-sync. Use this method when attempting to resynchronize the data between the coordinator and standby coordinator. The Greenplum system catalog tables will not be updated.

gpinitstandby -n

 

8. How to recover a failed segment mirror.

Use the gprecoverseg tool, which recognizes which segments need recovery and will initialize recovery.

Note: Without the "-F" option, the change tracking log will be sent and applied to the mirror. With the "-F" option, the entire data directory will be resynced.

9. How to add mirrors to the array.

The gpaddmirrors utility configures mirror segment instances for an existing Greenplum Database system that was initially configured with primary segment instances only.

Refer to gpaddmirrors documentation for more details.

 

10. How to view primary to mirror mapping.

Use the following query to list the configuration, the "role" is either "p" for primary or "m" for mirror and "content" is shows the segment.

SELECT * FROM gp_segment_configuration order by content, role desc;

 

11. How to start/stop Greenplum in special modes.

• Restricted mode - gpstart -R -Starts Greenplum Database in restricted mode with resource management turned off (only database superusers are allowed to connect).
• Master Only mode - gpstart -m - Starts the coordinator instance only, which may be useful for maintenance tasks. This mode only allows connections to the coordinator in utility mode.

Refer to gpstart documentation for further details.

 

12. How to run gpcheckperf IO/netperf?

Create a directory where space is available and common in all hosts.

• For network I/O test for each nic card:

gpcheckperf -f seg_host_file_nic-1 -r N -d /data/gpcheckperf > seg_host_file_nic_1.out
gpcheckperf -f seg_host_file_nic-2 -r N -d /data/gpcheckperf > seg_host_file_nic_2.out 

•  For disk I/O:

gpcheckperf -f seg_host_file_nic-1 -r ds -D -d /data/gpdb_p1 -d  /data/gpdb_p2 -d /data/gpdb_m1 -d  /data/gpdb_m2 

13. How to update postgresql.conf and reload it?

In GP 4.0 version, check gpconfig utility to change postgres.conf parameters.

 

14. How to manage pg_hba.conf?

The pg_hba.conf file of the coordinator controls client access and authentication to the Greenplum system. 

15. How to add a new user to the database?

Use "createuser" utility to create users. See "createuser --help" for more details.

Another option is to use SQL commands in the psql prompt to create users.

CREATE USER or ROLE <ROLE_NAME> .... 

 

16. How to create a password-free trusted env between all the segment hosts?

Use gpssh-exkeys:

gpssh-exkeys -h hostname1 -h hostname2 ..  -h hostnameN 

 

17. How to check db version and version at init db?

To check the version:

psql# select version(); 

or

postgres --gp-version 

To check the gp version at install:

psql# select * from gp_version_at_initdb; 

 

18. How to see the value of GUC?

Connect the GPDB database using psql query catalog or use the show parameter command.

gpdb# select name,setting from pg_settings where name='GUC_NAME'; 

or

gpdb# show <GUC_NAME>; 

 

19. What is the location of pg_hba, logfile and data directory?

The location of the coordinator data directory is defined by the environment variable $MASTER_DATA_DIRECTORY. The pg_hba.conf and postgresql.conf files are in that directory

The log files are located in $COORDINATOR_DATA_DIRECTORY/pg_log (for GPDB 6.x) or $COORDINATOR_DATA_DIRECTORY/log (for GPDB 7.x and above).

 

20. How to find errors/fatal from log files?

grep for ERRORS, FATAL, SIGSEGV in the files in the $COORDINATOR_DATA_DIRECTORY/pg_log (for GPDB 6.x) or $COORDINATOR_DATA_DIRECTORY/log (for GPDB 7.x and above) directory.

21. What is a vacuum and when should I run this?

VACUUM reclaims storage occupied by deleted tuples. In normal GPDB operation, tuples that are deleted or obsoleted by an update are not physically removed from their table. They remain present on disk until a VACUUM is done. Therefore, it is necessary to do VACUUM periodically, especially on the frequently-updated table.

22. What is the difference between vacuum and vacuum full?

Unless you need to return space to the OS so that other tables or other parts of the system can use that space, you should use VACUUM instead of VACUUM FULL.

VACUUM FULL is only needed when a table that is mostly dead rows. That is, the vast majority of the contents of the table have been deleted. Even then, VACUUM FULL should be used when disk space is urgently needed or the table will never again grow to its past size. Do not use it for table optimization or periodic maintenance as it is counterproductive.

Check the guide for more information.

 

23. What is Analyze and how frequently should I run this?

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the system table pg_statistic. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

It is good practic to run ANALYZE periodically, or after making major changes in the contents of a table. Accurate statistics will help the query planner to choose the most appropriate query plan, thereby improving the speed of query processing. A common strategy is to run VACUUM and ANALYZE once a day during a low-load time of day.

 

24. What is gp_toolkit?

The gp_toolkit is a database schema. It has many tables, views, and functions to better manage Greenplum Database when the database is up.

Additional Information

For more detailed information please refer to the Greenplum Administrator's Guide. 

Related articles:

• FAQ - Greenplum Administration for DBA Part I (General)
• FAQ - Greenplum Administration for DBA Part III (Backup)
• FAQ - Greenplum Administration for DBA Part IV (Performance)