AWR Report in Greenplum Database
Automatic Workload Repository (AWR) in Oracle Database is very common for Oracle DBA. We are working on creating similar report in Greenplum.
Script name: gpdb_awr_rpt.sh
SQL Scripts: gpdb_awr_rpt.sql
Email us at gpdba@greenplumdba.com with subject "Add to AWR" if you have a SQL scripts that you want to add in this. I have around 50 SQL scripts that is used in this SQL scripts. A sample (Partial) report output is given below/
There are different sections in the report to look at. Report is fully customizable.
Note: Some of the information is hardcoded so show the clearity. Sample was ran against 2 node greenplum database on my Linux Server
Report output:
[gpadmin@sachi scripts]$ ./gpdb_awr_report.sh
SCRIPT VARIABLES LIST /home/gpadmin/maintenance/scripts/db_param EXISTS
######################################################################################################################
DCA_ALIAS = DEV_DCA
SCRIPTS_DIR = /home/gpadmin/maintenance/scripts
SCRIPTS_LOG = /home/gpadmin/maintenance/logs
MAIL_CONTACTS = $SCRIPTS_DIR/mail_contacts
*****************************************************************************************************
2014-10-17 23:25:05 EDT Generating AWR Report.
****************************************************************************************************
Report is being generated. Please wait.... It may take few minutes...
**************************************************************************************************************************************
2014-10-17 23:25:07 EDT END AWR Report Generated. Report is available at /home/gpadmin/maintenance/reports
Filename:/home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt
Time spent to generate this report, 00:00:02
**************************************************************************************************************************************
[gpadmin@sachi scripts]$ view /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt
[gpadmin@sachi scripts]$ wc /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt
2266 41834 504385 /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt
[gpadmin@sachi scripts]$
#######################################################################################################################
Greenplum AWR Report
Date created: 2014-10-17_18:09
DCA Hostname: sachi
For bug fixes and enhancements, contact Sachchida (Sachi) N Ojha at gpdba@greenplumdba.com
########################################################################################################################
SYSTEM INFORMATION
DCA VERSION:DCA V1.2.1.1
Hostname:sachi
OS Version:Red Hat Enterprise Linux Server release 6.5 (Santiago)
Greenplum database version:gpstate version 4.2.2.4 build 1 Community Edition
----------------------------------------------------------------------------------------
-- Section 1 - DCA Segments Health Check
----------------------------------------------------------------------------------------
connection_details
-----------------------------------------------------------
You are now connected to database gpadmin as user gpadmin
(1 row)
-- check total no of segments in the DCA and working
total_segments_in_the_database
--------------------------------
3
(1 row)
--check_GP_server_segment status
dbid | content | role | preferred_role | mode | status | port | hostname | address
------+---------+------+----------------+------+--------+-------+----------+---------
1 | -1 | p | p | s | u | 5432 | sachi | sachi
2 | 0 | p | p | s | u | 40000 | sachi | sachi
3 | 1 | p | p | s | u | 40001 | sachi | sachi
(3 rows)
--check storage utilization per segment server
You are now connected to database "gpperfmon" as user "gpadmin".
ctime | hostname | filesystem | total_bytes | used_bytes | avail_bytes | pct_used
---------------------+----------+------------+-------------+------------+-------------+----------
2014-06-20 11:55:00 | sachi | /home | 177 GB | 136 GB | 41 GB | 76.87%
2014-06-20 11:55:00 | sachi | / | 49 GB | 12 GB | 38 GB | 23.76%
2014-06-20 11:55:00 | sachi | /boot | 484 MB | 80 MB | 404 MB | 16.51%
2014-06-20 11:55:00 | sachi | /usr | 49 GB | 5508 MB | 44 GB | 10.93%
2014-06-20 11:55:00 | sachi | /opt | 49 GB | 1101 MB | 48 GB | 2.19%
2014-06-20 11:55:00 | sachi | /software | 197 GB | 2481 MB | 194 GB | 1.23%
2014-06-20 11:55:00 | sachi | /disk2 | 197 GB | 1105 MB | 196 GB | 0.55%
2014-06-20 11:55:00 | sachi | /disk1 | 197 GB | 1104 MB | 196 GB | 0.55%
2014-06-20 11:55:00 | sachi | /app | 197 GB | 377 MB | 196 GB | 0.19%
2014-06-20 11:55:00 | sachi | /vm | 98 GB | 188 MB | 98 GB | 0.19%
2014-06-20 11:55:00 | sachi | /disk3 | 197 GB | 271 MB | 197 GB | 0.13%
2014-06-20 11:55:00 | sachi | /disk4 | 197 GB | 187 MB | 197 GB | 0.09%
2014-06-20 11:55:00 | sachi | /tmp | 197 GB | 188 MB | 197 GB | 0.09%
(13 rows)
Disk Space Availability
total_tb | used_tb | available_tb | pct_used | pct_available
----------+---------+--------------+----------+---------------
0.19 | 0.00 | 0.19 | 0.00 | 100.00
(1 row)
You are now connected to database "sachi" as user "gpadmin".
----------------------------------------------------------------------------------------
-- Section 2 - List Databases, Schemas, Users and Roles
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- Section 3 - Grants and Privileges Report
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- Section 4 - Schema Objects Report
----------------------------------------------------------------------------------------
-- List Schema Objects with type and storage class
---------------------------------------------------------------------------------------------------
-- Section 5 - Space Monitoring - Database, Schema, Table and Index Size
--------------------------------------------------------------------------------------------------
--Database size
datname | size
-----------+---------
gpadmin | 29 MB
postgres | 29 MB
template1 | 28 MB
template0 | 27 MB
himanshu | 28 MB
sachi | 1400 MB
gpperfmon | 284 MB
(7 rows)
-- Schema size
schemaname | table_size_gb | index_size_gb | schema_size_gb
------------+---------------+---------------+----------------
public | 1.30 | 0.02 | 1.32
(1 row)
-- Table and Index Size
schema_name | table_name | table_gb | index_gb | total_gb
-------------+----------------------+----------+----------+----------
public | bloattest | 1.282 | 0.021 | 1.303
public | sales | 0.012 | 0.000 | 0.012
public | cms_qlz | 0.002 | 0.000 | 0.002
public | cms_zlib | 0.002 | 0.000 | 0.002
public | cms_zlib9 | 0.002 | 0.000 | 0.002
public | wwearthquakes_lastwk | 0.001 | 0.000 | 0.001
public | abc | 0.000 | 0.000 | 0.000
public | cms | 0.000 | 0.000 | 0.000
public | cms_part | 0.000 | 0.000 | 0.000
public | err_earthquakes | 0.000 | 0.000 | 0.000
public | foo | 0.000 | 0.000 | 0.000
public | foo1 | 0.000 | 0.000 | 0.000
public | sachi | 0.000 | 0.000 | 0.000
public | sachi3 | 0.000 | 0.000 | 0.000
public | test | 0.000 | 0.000 | 0.000
public | usha | 0.000 | 0.000 | 0.000
public | usha1 | 0.000 | 0.000 | 0.000
public | usha3 | 0.000 | 0.000 | 0.000
(18 rows)
----------------------------------------------------------------------------------------
-- Section 6 - Database Locks and Resource Queues
----------------------------------------------------------------------------------------
--check_waiting_lock
usename | count
---------+-------
(0 rows)
--check_size_of_resource_queue
rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqwaiters | rsqholders
------------+---------------+---------------+--------------+--------------+------------+------------
perf_test | 2 | 0 | 1e+08 | 0 | 0 | 0
myque3 | -1 | | 1e+08 | 0 | 0 | 0
cdr_test | 25 | 0 | -1 | | 0 | 0
myque1 | 20 | 0 | -1 | | 0 | 0
highrq | 20 | 0 | -1 | | 0 | 0
myque2 | 20 | 0 | 1e+08 | 0 | 0 | 0
pg_default | 20 | 0 | -1 | | 0 | 0
(7 rows)
-- check resource queue usage by user activity
----------------------------------------------------------------------------------------
-- Section 7 - Current Database Activity
----------------------------------------------------------------------------------------
--check_current_activity
datid | datname | procpid | sess_id | usesysid | usename | waiting | query_start | backend_start | xact_start | application_name | current_query
-------+-----------+---------+---------+----------+---------+---------+-------------------------------+-------------------------------+-------------------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
16993 | sachi | 25246 | 147 | 10 | gpadmin | f | 2014-10-17 18:09:38.671451-04 | 2014-10-17 18:09:37.345324-04 | 2014-10-17 18:09:38.671451-04 | psql | SELECT datid, datname, procpid, sess_id, usesysid, usename, waiting, query_start, backend_start, xact_start, application_name, current_query FROM pg_stat_activity ;
16992 | gpadmin | 13812 | 105 | 10 | gpadmin | f | | 2014-10-17 17:27:40.062017-04 | | psql | <IDLE>
33553 | gpperfmon | 14131 | 106 | 10 | gpadmin | f | 2014-10-17 18:05:30.369481-04 | 2014-10-17 17:37:46.340393-04 | | psql | <IDLE>
(3 rows)
--check_user_activity_by_database
datname | usename | count
-----------+---------+-------
gpadmin | gpadmin | 1
gpperfmon | gpadmin | 1
sachi | gpadmin | 1
(3 rows)
-- Check sessins started and idle for more than 60 minutes
datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start
-------+---------+---------+---------+----------+---------+---------------+---------+-------------+---------------+-------------+-------------+------------------+------------
(0 rows)
----------------------------------------------------------------------------------------
-- Section 8 - Active Database Monitoring
----------------------------------------------------------------------------------------
-- check missing stats report
smischema | smitable | smisize | smicols | smirecs
-----------+-----------------------------------------------------------------+---------+---------+---------
public | sachitest | f | 2 | 0
public | gp_log_master_ext | f | 30 | 0
public | usha | f | 1 | 0
public | usha1 | f | 1 | 0
public | sachi3 | f | 1 | 0
public | sachi | f | 3 | 3
public | test | f | 3 | 0
public | err_earthquakes | f | 8 | 0
public | cms_part_1_prt_p1 | f | 11 | 0
public | cms_part_1_prt_p2 | f | 11 | 0
public | cms_part_1_prt_p3 | f | 11 | 0
public | cms_part_1_prt_p4 | f | 11 | 0
public | cms_part_1_prt_p5 | f | 11 | 0
public | cms_part_1_prt_p6 | f | 11 | 0
public | cms_part_1_prt_p7 | f | 11 | 0
public | cms_part_1_prt_p8 | f | 11 | 0
public | cms_part_1_prt_p9 | f | 11 | 0
public | cms_part_1_prt_p10 | f | 11 | 0
public | cms_part_1_prt_p11 | f | 11 | 0
public | cms_part_1_prt_p12 | f | 11 | 0
public | cms_part_1_prt_p13 | f | 11 | 0
public | cms_part_1_prt_p14 | f | 11 | 0
public | cms_part_1_prt_p15 | f | 11 | 0
public | cms_part_1_prt_p16 | f | 11 | 0
public | cms_part_1_prt_other | f | 11 | 0
public | cms_part | f | 11 | 0
public | cms | f | 11 | 0
public | cms_qlz_1_prt_p1 | f | 11 | 0
public | cms_qlz_1_prt_p2 | f | 11 | 0
public | cms_qlz_1_prt_p3 | f | 11 | 0
public | cms_qlz_1_prt_p4 | f | 11 | 0
....
public | sales_1_prt_2_2_prt_3 | f | 5 | 0
public | sales_1_prt_2_2_prt_4 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_other_months | f | 5 | 0
public | sales_1_prt_2_2_prt_5 | f | 5 | 0
public | sales_1_prt_2_2_prt_6 | f | 5 | 0
public | sales_1_prt_2_2_prt_7 | f | 5 | 0
public | sales_1_prt_2_2_prt_8 | f | 5 | 0
public | sales_1_prt_2_2_prt_9 | f | 5 | 0
public | sales_1_prt_2_2_prt_10 | f | 5 | 0
public | sales_1_prt_2_2_prt_11 | f | 5 | 0
public | sales_1_prt_2_2_prt_12 | f | 5 | 0
public | sales_1_prt_2_2_prt_13 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_2 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_3 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_4 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_5 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_6 | f | 5 | 0
public | sales_1_prt_outlying_years_2_prt_7 | f | 5 | 0
(212 rows)
-- check bloat diagnosis report
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+---------------+-------------+-------------+------------------------------------
1259 | pg_catalog | pg_class | 36 | 9 | moderate amount of bloat suspected
2606 | pg_catalog | pg_constraint | 44 | 11 | moderate amount of bloat suspected
(2 rows)
-- check tables with random distribution key
table_owner | table_name | distribution_keys
-------------+--------------------------+----------------------
gp_toolkit | __gp_localid | DISTRIBUTED RANDOMLY
gp_toolkit | __gp_log_segment_ext | DISTRIBUTED RANDOMLY
gp_toolkit | gp_disk_free | DISTRIBUTED RANDOMLY
public | err_earthquakes | DISTRIBUTED RANDOMLY
public | ext_wwearthquakes_lastwk | DISTRIBUTED RANDOMLY
public | foo | DISTRIBUTED RANDOMLY
public | foo1 | DISTRIBUTED RANDOMLY
public | sachitest | DISTRIBUTED RANDOMLY
(8 rows)
-- check tables with high skew
-- check table not analyzed in last 3 days
nspname | relname | statime | ?column?
---------+---------+---------+----------
(0 rows)
-- check worst performing SQL
You are now connected to database "gpperfmon" as user "gpadmin".
ctime | query_text | username | db | rsqname | run_time | month | day | weekday | hour | cnt | avg_cpu_used | min_cpu_used | max_cpu_used | avg_load2 | min_load2 | max_load2 | avg_disk_rb_rate | min_disk_rb_rate | max_disk_rb_rate | avg_disk_wb_rate | min_disk_wb_rate | max_disk_wb_rate | avg_net_rb_rate | min_net_rb_rate | max_net_rb_rate | avg_net_wb_rate | min_net_wb_rate | max_net_wb_rate | avg_mem_actual_used_gb | min_mem_actual_used_gb | max_mem_actual_used_gb | avg_swap_used | min_swap_used | max_swap_used
-------+------------+----------+----+---------+----------+-------+-----+---------+------+-----+--------------+--------------+--------------+-----------+-----------+-----------+------------------+------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+------------------------+------------------------+------------------------+---------------+---------------+---------------
(0 rows)
----------------------------------------------------------------------------------------
--- Section 9 - Partition Information and Monitoring
---------------------------------------------------------------------------------------
Email us at gpdba@greenplumdba.com to get this script or buy our premium email support.
Added a separate section to show resource queue - Oct 22, 2014 11:5:59 PM