Script- Database and Cluster Level Information

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article provides a simple script to help the administrator identify database and cluster level information.

Note: Please verify the script on a test cluster before running it on production.

 

Resolution

Pivotal Greenplum Database Information

-- Database version

select version() ;

-- Database uptime

select now() - pg_postmaster_start_time() as "Database Uptime";

-- List of Database

psql -l
- OR - 
\l   -- Run after connecting to psql

-- Encoding of Database

SELECT d.datname as Name,
       pg_catalog.pg_encoding_to_char(d.encoding) as Encoding
FROM pg_catalog.pg_database d
ORDER BY 1;

-- Size of the database

SELECT 
    datname "Database Name", 
    pg_size_pretty(pg_database_size(datname)) "Size"
FROM 
    pg_database 
order by 1 ;

Pivotal Greenplum Cluster Information

-- Cluster configurations.

SELECT * 
FROM gp_segment_configuration ;

-- Segments which is currently marked down

SELECT * 
FROM gp_segment_configuration 
WHERE status='d';

-- Segments that are not in its preferred role ( like mirror role is switched to primary or vice versa).

SELECT * 
FROM gp_segment_configuration 
WHERE role<>preferred_role;

-- History of changes in the segments that were marked down.

SELECT * 
FROM gp_configuration_history 
WHERE dbid in (SELECT dbid 
	       FROM gp_segment_configuration 
	       WHERE status='d') 
ORDER BY time desc;

-- List of the tablespace and the location w.r.t to segments

SELECT 
    a.oid as "Tablespace Oid",
    a.spcname as "Tablespace name",
    b.fsname as "Filespace name",
    'Hostname: '|| d.hostname ||' Content: '|| d.content || ' Port: ' || port as "Segments Info",
    c.fselocation as "Tablespace Location" 
FROM
    pg_tablespace a,
    pg_filespace b,
    pg_filespace_entry c,
    gp_segment_configuration d
WHERE
    a.spcfsoid=b.oid
AND b.oid=c.fsefsoid
AND c.fsedbid=d.dbid
ORDER BY 2,4;

-- The amount of free space on the disk used by the pivotal Greenplum cluster.

SELECT 
   dfsegment as "Segment",
   dfhostname as "Hostname",
   dfdevice as "Location",
   dfspace/1024 as "Free Space (MB)"
FROM gp_toolkit.gp_disk_free 
ORDER BY 4 ;

Pivotal Greenplum Database Event Collection

-- List of operation on the database during the specific time.

SELECT 
    logtime as Queryruntime, 
    logsessiontime as Sessloggedintodbtime, 
    loguser as User, 
    logdatabase as Db, 
    loghost as FROMhost,
    logport as FROMport, 
    logseverity as Serverity, 
    logmessage as Queryran 
FROM 
    gp_toolkit.gp_log_database 
WHERE
    logtime > '<FROM_timestamp>' AND  logtime < '<to_timestamp>'
ORDER BY 1;

-- Top 10 FATAL Error message in the last 5 days

SELECT logseverity "Severity",
	   substring(logmessage FROM 1 for 80) "Message",
	   count(*) "# of occurance"  
FROM gp_toolkit.gp_log_database 
WHERE logseverity='FATAL' 
AND logtime between now() - interval '5 days' and now()
GROUP BY logseverity,logmessage 
ORDER BY 3 desc 
LIMIT 10;

-- Top 10 PANIC Error message in the last 5 days

SELECT logseverity "Severity",
	   substring(logmessage FROM 1 for 80) "Message",
	   count(*) "# of occurance"  
FROM gp_toolkit.gp_log_database 
WHERE logseverity='PANIC' 
AND logtime between now() - interval '5 days' and now()
GROUP BY logseverity,logmessage 
ORDER BY 3 desc 
LIMIT 10;

-- Top 10 ERROR Error message in the last 5 days

SELECT logseverity "Severity",
       substring(logmessage FROM 1 for 80) "Message",
       count(*) "# of occurance"  
FROM gp_toolkit.gp_log_database 
WHERE logseverity='ERROR' 
AND logtime between now() - interval '5 days' and now()
GROUP BY logseverity,logmessage 
ORDER BY 3 desc 
LIMIT 10;

-- Information of when the master lost connection to the segments aka segment crashed in the last 5 days

Note: The below-shown information is not the start time of the issue, they might be a symptom of other segments triggering it

Hint: go to the directory "$MASTER_DATA_DIRECTORY/pg_log" and check for start using the keywords like below (message column) once you find the segments that first disconnected FROM master, navigate to that segment log directory for the potential cause:

SELECT logtime "Time",
	   substring(logmessage FROM 1 for 100) "Message"
FROM gp_toolkit.gp_log_database 
WHERE (logmessage like '%Lost connection to one or more segments - fault detector checking for segment failures%' 
OR logmessage like '%Dispatcher encountered connection error%')
AND logtime between now() - interval '5 days' and now()
AND logmessage not like '%gp_toolkit.gp_log_database%'
ORDER BY logtime desc 
LIMIT 10;

Listing all database parameters

SELECT name "Parameter Name",
	substring(setting from 1 for 30) "Parameter Value",
	substring(short_desc from 1 for 100) "Parameter Desc"
FROM pg_settings
ORDER BY 1;

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions