pivotal greenplum command centergreenplum command center 4.4.0 is a minor release that adds features...
TRANSCRIPT
PivotalGreenplum®
CommandCenter
Version4.4.0
UserGuide
Rev:01
©2018PivotalSoftware,Inc.
245
101314162223242526272930313334363840414345474954555860636466676870747778808487909293949597
TableofContents
TableofContentsPivotalGreenplumCommandCenter4.4.0DocumentationPivotalGreenplum®CommandCenter4.4.0ReleaseNotesAboutPivotalGreenplumCommandCenterInstallingtheGreenplumCommandCenterSoftwareCreatingthegpperfmonDatabaseInstallingPivotalGreenplumCommandCenterUpgradingGreenplumCommandCenterUninstallingGreenplumCommandCenterAbouttheCommandCenterInstallationSettingtheGreenplumCommandCenterEnvironmentConnectingtotheGreenplumCommandCenterConsoleAdministeringtheCommandCenterWebServerAdministeringCommandCenterAgentsAdministeringthegpperfmonDatabasegpccConfigurationFileReferenceCommandCenterAgentParametersCommandCenterConsoleParametersSetupConfigurationFileGreenplumDatabaseServerConfigurationParametersSecuringGreenplumCommandCenterManagingGreenplumCommandCenterAuthenticationManagingGreenplumCommandCenterPermissionsSecuringthegpmonDatabaseUserEnablingAuthenticationwithKerberosMonitoringtheGreenplumDatabaseSystemDashboardGreenplumDatabaseClusterStateSegmentStatusClusterMetricsHostMetricsStorageStatusMonitoringandManagingGreenplumDatabaseQueriesQueryMonitorQueryDetailsHistoryManagingGreenplumDatabaseWorkloadsAboutGreenplumCommandCenterWorkloadManagementWorkloadManagementImportingResourceQueuestoResourceGroupsAccessingtheWorkloadConfigurationProgrammaticallyTroubleshootingEnablingResourceGroupsPivotalCommandCenterOnlineHelpTopicsCPUCPUSkewLocksMemory
©CopyrightPivotalSoftwareInc,2013-2018 2 4.4.0
99QueryPlanExecution
©CopyrightPivotalSoftwareInc,2013-2018 3 4.4.0
PivotalGreenplumCommandCenter4.4.0DocumentationPDF
GreenplumCommandCenter4.4.0ReleaseNotes
AboutGreenplumCommandCenter
InstallingGreenplumCommandCenter
InstallingGreenplumCommandCenter
AbouttheCommandCenterInstallation
SettingtheGreenplumCommandCenterEnvironment
Administration
AdministeringtheCommandCenterWebServer
AdministeringCommandCenterAgents
AdministeringthegpperfmonDatabase
gpccCommandReference
ConfigurationFileReference
SecuringGreenplumCommandCenter
UsingGreenplumCommandCenter
MonitoringtheGreenplumDatabaseSystem
OverallClusterStateSegmentStatusClusterMetricsHostMetricsStorageStatus
MonitoringandManagingGreenplumDatabaseQueries
QueryMonitorQueryDetailsQueryHistory
ManagingWorkloads
AboutWorkloadsManagingWorkloadswithCommandCenterTroubleshootingCommandCenterWorkloadManagement
CommandCenterHelpTopics
CPU
CPUSkew
Locks
Memory
QueryOptimization
SpillFiles
©CopyrightPivotalSoftwareInc,2013-2018 4 4.4.0
PivotalGreenplum®CommandCenter4.4.0ReleaseNotes
AboutThisReleaseThisdocumentcontainspertinentreleaseinformationaboutPivotalGreenplumCommandCenter4.4.0.GreenplumCommandCenter4.4.0isaminorreleasethataddsfeaturesandresolvessomeissues.
SeeEnhancementsandChangesinGreenplumCommandCenter4.4.0forinformationaboutnewfeaturesandchangesinthisCommandCenterrelease.
SupportedPlatformsGreenplumCommandCenter4.4.0iscompatiblewiththefollowingplatforms.
PivotalGreenplumDatabase5.9.0andhigher
RedHatEnterpriseLinux6.xand7.x
CentOS6.xand7.x
SUSEEnterpriseLinux11SP4
IfyouuseresourcegroupsandworkloadmanagementonRedHatorCentOS6.x,upgradeyourkernelto2.6.32-696orhighertobenefitfromimprovementsintheLinuxcgroupsservice.
GreenplumCommandCenterworkloadmanagementfeaturesarenotsupportedonSUSEEnterpriseLinux11.
EnhancementsandChangesinGreenplumCommandCenter4.4.0GreenplumCommandCenter4.4.0containsthefollowingfeaturesandenhancements.
WorkloadManagementCommandCenterhasanewuserinterfacetoassistadministratorsinenablingresourcegroupsinGreenplumDatabase,importingexistingresourcequeuestoresourcegroups,andenablingworkloadmanagementwithCommandCenter.Theoptiontoimportresourcequeuestoresourcegroupsispresentedifnoresourcegroupshavebeencreated(otherthan default_group and admin_group )andGreenplumDatabasehasresourcequeuestoconvert(otherthan pg_default ).Oncetheadministratorhasimportedresourcequeues,orchosentoskipimportingresourcequeues,theoptiontoimportqueuesisnolongerpresented.
TheresourcegrouplistontheWorkloadManagementviewhasanewcolumntoshowtheminimum(fixed)amountofmemorythatwillbeallocatedtoaqueryforeachresourcegroup.Thisvalueisrecalculatedwhenyouenternewvalueswhileeditingresourcegroups.
Administratorscannowdefineresourcegroupassignmentrulesandidlesessionkillruleswithaninteractiveinterface.ItisnolongernecessarytoedittheJSONdocumentforworkloadmanagementrules.TheJSONtextfieldisremoved.
TheWorkloadManagementviewchangedtoalighttheme.
PermissionsCommandCenteruserswiththeSelfOnlypermissionlevelcan:
seeallqueriesonthequerymonitor,includingqueriesownedbyotheruserscanceltheirownqueriesaccessthequerydetailsviewfortheirownquerieshoveronaquerytoseequerytextfortheirownquerieshoveronlocking/blockingqueriesandaccessdetailsoflocking/blockingqueriesthatdonotbelongtootherusers
BeginningwithGreenplumDatabase5.11.0,youcanusehostnamesintheaddressfieldofthe pg_hba.conf configurationfile.GreenplumCommandCenterdoesnotyetsupportenteringhostnamesintheAdmin>Authorizationview.IfyouusehostnamesinGreenplumDatabase,donotusetheAdmin>AuthorizationviewinGreenplumCommandCenter4.4.0orearliertoeditthe pg_hba.conf file.
©CopyrightPivotalSoftwareInc,2013-2018 5 4.4.0
UserswithSelfOnlypermissionlevelcannotseequerytextoraccessthedetailsviewsofothers’queries.
TheGreenplumDatabaseroles gpcc_basic , gpcc_operator ,and gpcc_operator_basic arecreatedduringCommandCenterinstallationiftheydonotalreadyexist.
QueryMonitorFixedabugwherethedatabasenameandrolenameweremissingfromquerydetailswhenqueriesareexecutedinasessionafterrestartingCommandCenter.
EnhancementsandChangesinGreenplumCommandCenter4.3.0GreenplumCommandCenter4.3.0containsthefollowingenhancements.
ResourceGroupManagementTheAdmin>WorkloadMgmtviewhasanewuserinterfaceyoucanusetoaddandremoveresourcegroupsandtochangetheConcurrency,CPU%,andMemory%attributesofresourcegroups.
ResourceGroupRoleAssignmentsTheAdmin>WorkloadMgmtviewhasanewuserinterfaceyoucanusetoviewandchangeGreenplumDatabaseroles’defaultresourcegroups.
DetailsAddedtoVisualQueryPlanStepsThemetricscollectorextensioninGreenplumDatabaserelease5.9isupdatedtosubmitadditionalinformationabouteachstepinthequeryplantotheCommandCenterbackend.CommandCenterdisplaysthisinformationinthevisualqueryplanwhenyouexpandastepinthequeryplan.Thenewinformationdisplayeddependsontheoperationthestepperformsandincludesdetailssuchashashkey,mergekey,joincondition,orfiltercondition.Previously,youcouldonlyseethisinformationbygeneratingthetextualqueryplan.
EnhancementsandChangesinGreenplumCommandCenter4.2.0GreenplumCommandCenter4.2.0containsthefollowingenhancements.
VisualQueryPlanTheCommandCenterQueryDetailsviewnowincludesavisualqueryplan.
IdleSessionKillRulesIdlesessionkillrulescanincludeoptional exemptedRoles and message parameters.
Thevalueofthe exemptedRoles parameterisalistofGreenplumDatabaserolesthatareexemptedfromtherule.ThelistcanincludePosixregularexpressionstomatchGreenplumDatabaserolenames.
Thevalueofthe message parameterisastringtoincludeinthemessagethatisdisplayedwhenasessioniskilled.
CommandCenterCanRunontheMasterHostorStandbyMasterHostTheGreenplumCommandCenterwebserverandbackendmaynowbeexecutedonthemasterhostoronthestandbymasterhost.RunningGPCConthestandbymasterhostisrecommendedtoavoidaddingloadtothemasterserver,butitisnolongerarequirement.AftertheGPCCsoftwareisinstalled,log
©CopyrightPivotalSoftwareInc,2013-2018 6 4.4.0
intothehostwhereyouwanttorunGPCC,sourcethe gpcc_path.sh fileintheGPCCinstallationdirectory,andrunthe gpccstart
command.
EnhancementsandChangesinGreenplumCommandCenter4.1.0GreenplumCommandCenter4.1.0containsthefollowingenhancements.
CommandCenteradministratorscansetpermissionlevelsforCommandCenterusers.Permissionsareenforcedasdescribedinthedocumentation.
OntheQueryDetailview,clickingCopyinthequerytextorqueryplanpanelcopiesthetextinthepaneltotheclipboard.
Ahelpiconandin-apphelphavebeenaddedontheQueryMonitorandQueryDetailviews.
Thefollowingworkloadmanagementfeatures,improvements,andbugfixeshavebeenaddedintheworkloadmanagementextensionincludedwithGreenplumDatabase5.8.0.
OntheAdmin>WorkloadMgmtview,youcanaddidlesessionkillrulesforeachresourcegroup.Whenyouaddtheserules,theGreenplumDatabaseworkloadmanagementextensionkillsasessionafterithasbeenidleforthenumberofsecondsyouspecify.SeeWorkloadManagementforsyntaxandexamples.
OptimizationshavebeenimplementedtoreducetheimpactonGreenplumDatabasewhentheworkloadmanagementextensionisdisabled.
Theworkloadmanagementextensiontakesadvantageofresourcegroupname-to-idcachingaddedinGreenplumDatabase.
Fixedabugintheworkloadmanagementextensionthatcausederrorstoprinttothe psql prompt,evenwhentheextensionwasdisabled.
GreenplumCommandCenter4.1.0containsthefollowingbugfixes.
IntheHistoryDetailview,wheneitheroftheDiskRorDiskWmetricsis0,botharereportedtobe0.Thisisfixed.
WhenusingtheKerberosgpmon-onlyauthenticationmode,generatinganexplainplanfailed.Thisisfixed.
TheCommandCenteragentfailedwiththemessage“Error:can’tfindgpcc.query_metrics_port,metrics_collectorisnotcorrectlyinstalled.”ThisoccurswhenrunningCommandCenteronaGreenplumDatabasesystemthatwasupgradedfromanearlierGreenplumDatabase5.xrelease.Themetrics_collector and gp_wlm extensionsareinstalledwiththeupgrade,buttheupgradeprocessdoesnotperformtherequiredconfigurationchangesinthe postgresql.conf configurationfile.TheCommandCenterinstallationinstructionsnowincludestepstomanuallyconfigureandrestartanupgradedGreenplumDatabasesystem.
WhenCommandCenterstarts,anerrormessageiswrittenintheGreenplumDatabaselogfile:“functiongpcc_schema.read_pghba(unknown)doesnotexist.”Thisisfixed.
AchangetotimezonehandlinginGreenplumDatabase5.7cancauseGreenplumCommandCenter4.0.0todisplayanincorrecttimeifthemasterhostoperatingsystemtimeandGreenplumDatabaseusedifferenttimezones.NowCommandCentertimesaredisplayedusingthetimezoneoftheGreenplumDatabasemasterhostoperatingsystem.Thecurrenttime,lastsynctime,andtimestampsinalertlogs,clustermetrics,andqueryhistoryarealldisplayedusingthemasterhost’ssystemtimezone.
OntheAdmin>Authorizationview,whentheauthorizationmethodis gss andtheoptionsfieldcontainstext,changingthemethodto trust doesnotcleartheoptionsfieldanditisnotpossibletosavechangestothe pg_hba.conf file.
TheCommandCenterwebserver, gpccws ,spawnssshprocessesbutdoesnotreaptheminatimelymanner,leadingtomanyzombieprocesses.Thisisfixed.
OntheAdmin>Authenticationview,ifthenumberofuserslistedintheusercolumnislongerthancanbedisplayed,CommandCentertruncatesthelistandaddsellipsis( ... )totheend.Onlyadministratorscanviewthecompletevalue,byeditingthefield.Nowanyuserwithaccesstotheviewcanseethefulllistofusersbyhoveringoverthefield.
EnhancementsandChangesinGreenplumCommandCenter4.0.0
CommandCenterInstallationChangesGreenplumCommandCenter4.xsoftware,unlikepreviousreleases,isinstalledoneveryhostintheGreenplumDatabasecluster.TheCommandCenterwebserverandbackendrunonthestandbymaster,ifyourGreenplumDatabaseclusterhasastandbymaster.Ifthereisnostandbymaster,CommandCenterrunsonthemasterhostinstead.
TomodifytheCommandCenterinstallation—forexampletoenableordisableSSLorinstallCommandCenteronneworreplacedhosts—justre-execute
©CopyrightPivotalSoftwareInc,2013-2018 7 4.4.0
theinstallerandrestartCommandCenter.ItisnotnecessarytouninstallCommandCenterbeforereinstalling.
ThereisoneCommandCenterinstallationperGreenplumDatabasecluster.ItisnolongernecessarytocreateCommandCenterinstancesafterinstallingthesoftware.
The gpcmdr command-lineutilityisreplacedwiththenew gpcc utility.Usethe gpcc utilitytostartandstopCommandCenterandmetricscollectionagents,checkCommandCenterstatus,andenableordisableKerberosauthentication.
Inpreviousreleases,thegpmonrolerequiredonlylocalconnectionstodatabasesontheGreenplummasterhost.InGreenplumCommandCenter4.x,thegpmonusermustbeabletoconnecttodatabasesfromthehostrunningtheCommandCenterwebserverandbackend,whichrequiresaddingahostentrytothe pg_hba.conf authenticationconfigurationfile.
Real-timeQueryMetricsGreenplumCommandCenter4.0introducesreal-timequerymetricsforPivotalGreenplumDatabase5.7andabove.ThisnewfeaturecombinesthefollowingnewfeaturesinGreenplumDatabaseandGreenplumCommandCenter:
GreenplumDatabasesavesqueryexecutionmetricsinsharedmemorywhilequeriesexecute.
AnewGreenplumDatabasemetricscollectionextension,includedwithPivotalGreenplumDatabase,emitsthesavedmetricsasUDPdatagrams.
AnewGreenplumCommandCentermetricscollectionagentrunningoneachGreenplumDatabasehostreceivesthedatagramsandpostsmetricstotheGreenplumCommandCenterbackend.TheCommandCenterbackendstartsandmanagesthemetricscollectionagents.
TheCommandCenterQuerymonitorviewupdatesinrealtimesoyoucanseequeriesthatarewaitingtoexecuteandthecurrentstatusandresourceusageforqueriesthatarerunning.
Metricscollectionnowincludeslockandspillfileinformation.OntheQueryMonitor,youcanseewhichqueriesareblockedandwhichqueriesholdthelocksblockingthem.TheQueryMonitorshowsthetotalsizeofspillfilescreatedonallsegmentsforeachquery.
InstallingthegpperfmondatabaseremainsaprerequisiteforCommandCenter.ThegpperfmondatabaseisthesourceforqueryhistorydisplayedintheCommandCenteruserinterface.Thenewreal-timemetricsarenotpersistedandarenotdirectlyrelatedtothemetricscollectedandpersistedinthegpperfmondatabase.
WorkloadManagementWorkloadmanagementisnowanintegratedCommandCenterfeatureratherthanaseparateproduct.WorkloadmanagementisavailableinCommandCenteronlyafterresourcegroupshavebeenenabledinGreenplumDatabasebychangingthe gp_resource_manager serverconfigurationparameterfrom'queue' to 'group' andenablingLinuxcontrolgroups(cgroups).
InCommandCenter4.0,workloadmanagementallowsyoutoassigntransactionstoGreenplumDatabaseresourcegroupsatexecutiontimebyevaluatingthecurrentdatabaseroleandquerytagsagainstworkloadassignmentfiltersyoudefineinCommandCenter.Querytagsareuser-definedname=value parametersthatyoudefineinthe gpcc.query_tags databasesessionparameter.Youcandefinemultiplequerytagsseparatedbysemicolons.SetquerytagsinaGreenplumDatabasesessioneitherasaparameterinthedatabaseconnectionURLorbyexecutingSETgpcc.query_tagsTO'<tag1>=<val1>;<tag2>=<val2>;...'
inthedatabasesession.
Whenatransactionisabouttoexecute,thecurrentdatabaseroleandquerytagsarecomparedtotheworkloadassignmentfiltersthatyouhavecreatedinCommandCenter.Ifamatchisfound,thetransactionisassignedtoaresourcegroupaccordingtotheworkloadmanagementfilter.Otherwise,thetransactionisassignedtothedatabaseuser’sresourcegroup,whichisthedefaultbehaviorwhenCommandCenterworkloadmanagementisnotenabled.
Workloadmanagementusesthe gp_wlm databaseextensionincludedwithPivotalGreenplumDatabase.
UnimplementedFeaturesSomefeaturesavailableinpreviousGreenplumCommandCenterreleaseshavebeenremovedorarenotyetimplementedinCommandCenter4.x.
TheabilityforaCommandCenteradmintopostamessagetotheQueryMonitorviewisnotyetimplemented.
Themulti-clusterviewhasbeenremoved.
©CopyrightPivotalSoftwareInc,2013-2018 8 4.4.0
KnownIssuesThefollowingareknownissuesinthecurrentGreenplumCommandCenterrelease.
UnabletoViewRealTimeQueriesAfterUpgradingFromaPreviousCommandCenterReleaseIfyouinstallanewversionofGreenplumCommandCenterusingthesameportnumberasthepreviousversion,andyouusetheChromewebbrowser,youmaybeunabletoviewreal-timequeriesuntilafteryouclearthebrowser’scache.SeethenoteinConnectingtotheCommandCenterConsoleforstepstoclearthebrowsercache.
©CopyrightPivotalSoftwareInc,2013-2018 9 4.4.0
AboutPivotalGreenplumCommandCenterPivotalGreenplumCommandCenterisamanagementtoolforthePivotalGreenplumDatabaseBigDataPlatform.ThistopicintroduceskeyconceptsaboutGreenplumCommandCenteranditscomponents.
GreenplumCommandCenterFeaturesGreenplumCommandCentermonitorssystemperformancemetrics,analyzesclusterhealth,andenablesdatabaseadministratorstoperformmanagementtasksinaGreenplumDatabaseenvironment.
GreenplumCommandCenterprovidesabrowser-nativeHTML5graphicalconsoleforviewingGreenplumDatabasesystemmetricsandperformingcertaindatabaseadministrativetasks.TheCommandCenterapplicationprovidesthefollowingfunctionality:
Interactiveoverviewofrealtimesystemmetrics.Drilldowntoseedetailsforindividualclusterhostsandsegments.
Detailedrealtimestatisticsfortheclusterandbyserver.
QueryMonitorviewlistsqueriesexecuting,waitingtoexecute,andblockedbylocksheldbyotherqueries.
QueryDetailviewshowsquerymetrics,querytext,andtheexecutionplanforthequery.
WorkloadManagementviewallowsadministratorsto:
CreateandmanageworkloadstomanageconcurrencyandallocateCPUandmemoryresources.ChangedefaultresourcegroupsforGreenplumDatabaseroles.Createassignmentrulestoassigntransactionstoresourcegroups.Createidlesessiontimeoutrulestosettheamountoftimebeforeanidlesessioniskilled.
Fourpermissionlevelsallowuserstovieworcanceltheirownorothers’queries,andtoviewormanageadministrativeinformation.
ClusterMetricsviewshowssynchronizedchartsofhistoricalsystemmetrics.
Historyviewlistscompletedqueriesandsystemmetricsplottedoveraselectedtimeperiod.
PermissionsviewtoseeormanageCommandCenterpermissionlevels.
Authenticationviewtoseeoreditthe pg_hba.conf host-basedauthenticationconfigurationfile.
SegmentStatusviewwithsummariesanddetailsbysegment.
StorageStatusviewwithsummariesanddetailsbysegmentdatadirectory.
GreenplumCommandCenterArchitectureThefollowingfigureillustratestheGreenplumCommandCenterarchitecture.
©CopyrightPivotalSoftwareInc,2013-2018 10 4.4.0
GreenplumCommandCenterWebServerandWebApplicationTheGreenplumCommandCenterwebserverandbackendapplicationcanrunonthemaster,standbymaster,oranysegmenthostintheGreenplumDatabasecluster—thestandbymasterhostisrecommended.Thewebserver,gpccws,isacustomHTTPserverdesignedforCommandCenter.ThewebapplicationisanHTML5andGolanguageapplication.
TheCommandCenterwebserverauthenticatesuserswiththeGreenplumDatabaseauthenticationsystem.AdministratorscanedittheGreenplumDatabasehost-basedauthenticationfile, pg_hba.conf ,intheCommandCenterConsole.CommandCentercanalsobeconfiguredtoauthenticateusersinaKerberosenvironment.
CommandCenterdefinesfouruserauthorizationlevelstomanageusers’accesstotheQueryMonitor,andtoadministrativeinformationandoperations.UserauthorizationismanagedintheAdministrativeareaoftheCommandCenteruserinterface.
GreenplumCommandCenterdisplaysinformationderivedfromseveralsources:
GreenplumDatabaseperformancemonitoringdatabase(gpperfmon)
Operatingsystemprocessaccounting
GreenplumDatabasesystemcatalogtables
Real-timequerymetricscollectionextension
Workloadmanagementextension
GreenplumDatabaseisinstrumentedtoenablecapturingperformancemetricsandtrackingqueryexecution.Theperformancemonitoringdatabaseandthequerymetricscollectionextensiondeployagents—processesrunningoneachhostlisteningforUDPpackets—tocollectmetrics.ThegpperfmonagentsforwardcollecteddatatoanagentontheGreenplumDatabasemaster.Thereal-timequerymetricsagentssubmitcollecteddatadirectlytotheCommandCenterrpcport.TheagentsalsocollectdatafromthehostoperatingsystemsothatqueryperformancecanbecorrelatedwithCPUandmemoryutilizationanddiskspacecanbemonitoredinCommandCenter.
GreenplumDatabasePerformanceMonitoringDatabaseThegpperfmonperformancemonitoringdatabasestorescurrentandhistoricalquerystatusandsysteminformationcollectedfromagentsrunningonthemasterandsegmenthosts.GreenplumCommandCenterusesgpperfmonforhistoricaldataonly;itusesthereal-timequerymetricstomonitoractiveandqueuedqueries.GreenplumDatabasesendsUDPpacketsatvariouspointsduringqueryexecution.The gpsmon processoneachsegmenthostcollectsthedata.Periodically,every15secondsbydefault,a gpmmon agentonthemasterhostsignalsthe gpsmon processtoforwardthecollecteddata.Theagentonthemasterhostreceivesthedataandaddsittothegpperfmondatabase.
TheCommandCenterdatabaseconsistsofthreesetsoftables:
nowtablesstoredataoncurrentsystemmetricssuchasactivequeries
©CopyrightPivotalSoftwareInc,2013-2018 11 4.4.0
historytablesstoredataonhistoricalmetrics
tailtablesarefordataintransition.Tailtablesareforinternaluseonlyandshouldnotbequeriedbyusers.
Thenowandtaildataarestoredastextfilesonthemasterhostfilesystem,andtheCommandCenterdatabaseaccessesthemviaexternaltables.Thehistorytablesareregulardatabasetablesstoredwithinthegpperfmondatabase.
YoucanrunSQLqueriesonthedatastoredinthegpperfmondatabase.GreenplumCommandCenterrunsqueriesonthedatabaseforinformationpresentedintheCommandCenterConsole.TheGreenplumDatabaseReferenceGuidecontainsreferencesforthetablesinthegpperfmondatabase.
GreenplumDatabaseprovidesamanagementutility, gpperfmon_install ,tocreatethegpperfmondatabaseandenablethegpperfmonagentsonthemasterandsegmenthosts.CreatingthegpperfmondatabaseisaprerequisiteforinstallingGreenplumCommandCenter.SeetheGreenplumDatabaseUtilityGuidefordetailsofrunningthe gpperfmon_install managementutility.
Real-TimeQueryMetricsCollectionThedatacollectedbyreal-timequerymetricscollectionismoredetailedandmorecurrentthanstatisticsrecordedinthegpperfmondatabase.CommandCenteruserscanobservequeriesastheyexecuteand,withsufficientpermissions,cancelproblemqueriestoallowotherqueriestocomplete.
TheGreenplumDatabasequerymetricsextensionandthemetricscollectionagentworktogethertocollectreal-timemetricsandupdatetheCommandCenterapplication.
GreenplumDatabasecallsthequerymetricsextensionwhenaqueryisfirstsubmitted,whenaquery’sstatuschanges,andwhenanodeinthequeryexecutionplaninitializes,starts,orfinishes.ThequerymetricsextensionsendsmetricsinUDPpacketstothemetricscollectionagentrunningoneachsegmenthost.Theextensionalsocollectsinformationaboutthelocksqueriesholdsothatyoucanseewhichqueriesholdlocksthatblockotherqueries.TheagentpoststhemetricstotheGreenplumCommandCenterrpcport.
The metrics_collection extensionisincludedwithPivotalGreenplumDatabase.Theextensionisenabledbysettingthe gp_enable_query_metrics serverconfigurationparametertoonandrestartingtheGreenplumDatabasecluster.ThemetricscollectionagentisinstalledoneachhostwhenyouinstallGreenplumCommandCenter.TheCommandCenterapplicationmonitorstheagentandrestartsitifneeded.
CommandCenterWorkloadManagementWorkloadssetconcurrency,memory,andCPUresourcelimitsfordatabasetransactionstheymanage.AGreenplumCommandCenterworkloadcorrespondstoaGreenplumDatabaseresourcegroup,butaddsadditionalcapabilitiesthatarenotavailablewithresourcegroups.
CommandCenterallowsadministratorsgreaterflexibilityinassigningtransactionstoworkloads.EveryGreenplumDatabaseroleisassignedtoasingleresourcegroupand,bydefault,transactionsaremanagedbytherole’sresourcegroup.WithCommandCenterworkloadmanagement,administratorscandefinecriteriatoassigntransactionstoworkloadsbasedonattributesotherthantherolesubmittingthetransaction.Currently,assignmentcriteriacanevaluatequerytagsandrolesincombinationwithquerytags.
Aquerytagisakey-valuepairdefinedinthe gpcc.query_tags parameterofadatabasesession.Theparameterhastheformat<tag1>=<value1>;<tag2>=<value2> ,wheretagsandvaluesareuser-definedvalues.Forexample,ifyouwanttorunETLoperationsinaworkloadnamed“etl”,youcoulddefineatagnamed“xact-type”andsetitto“etl”: xact-type=etl .The gpcc.query_tags parametercanbesetasaconnectionparameteronGreenplumDatabaseclientsthatallowit,orwitha SET commandinsidethesessionaftertheconnectionhasbeenestablished,forexampleSETgpcc.query_tags='xact-type=etl'
.
The gp_wlm extensioninPivotalGreenplumDatabaseprovidessupportforCommandCenterworkloads.TheextensionisincludedwithPivotalGreenplumDatabase,butisnotenabledbydefault.Initially,GreenplumDatabaseusesresourcequeuestomanageresources.UsingCommandCenterworkloadsrequiresenablingresourcegroupsinGreenplumDatabase.ResourcegroupsarebasedontheLinuxcontrolgroups(cgroups)service,whichmustfirstbeenabledintheoperatingsystem.
©CopyrightPivotalSoftwareInc,2013-2018 12 4.4.0
InstallingtheGreenplumCommandCenterSoftwareThissectioncontainsthefollowingtopics:
CreatingthegpperfmonDatabase
InstallingGreenplumCommandCenter
UpgradingGreenplumCommandCenter
UninstallingGreenplumCommandCenter
SettingtheCommandCenterEnvironment
©CopyrightPivotalSoftwareInc,2013-2018 13 4.4.0
CreatingthegpperfmonDatabaseThistopicdescribeshowtocreatetheCommandCentergpperfmondatabaseandenablethegpperfmondatacollectionagents.ThistaskmustbecompletedonetimefortheGreenplumDatabasesystem,beforeyouinstallGreenplumCommandCenter.
Whenthedatacollectionagentsareenabled,theirprocessesarestartedandstopped(using gpstart and gpstop )ontheGreenplumsegmenthostsalongwiththeGreenplumDatabaseserverprocesses.
GreenplumDatabaseincludesa gpperfmon_install utilitythatperformsthefollowingtasks:
CreatestheCommandCenterdatabase(gpperfmon).
CreatestheCommandCentersuperuserrole( gpmon ).
ConfiguresGreenplumDatabaseservertoacceptconnectionsfromthe gpmon role(editsthe pg_hba.conf and .pgpass files).
SetstheCommandCenterserverconfigurationparametersintheGreenplumDatabaseserver postgresql.conf files.
The gpperfmon_install utilityandtheagentsarepartoftheGreenplumDatabasesoftware.ThetasksinthistopiccanbeperformedbeforeoraftertheCommandCentersoftwareisinstalled.
EnablingtheCollectionAgents1. LogintotheGreenplummasterhostasthe gpadmin user.
$su-gpadmin
2. SourcethepathfilefromtheGreenplumDatabaseinstallationdirectory:
#source/usr/local/greenplum-db/greenplum_path.sh
3. Runthe gpperfmon_install utilitywiththe --enable option.YoumustsupplytheconnectionportoftheGreenplumDatabasemasterserverprocess,andsetthepasswordforthe gpmon superuserthatwillbecreated.Forexample:
$gpperfmon_install--enable--passwordchangeme--port5432
Note:The gpperfmon_install utilitycreatesentriesforthe gpmon userinthe $MASTER_DATABASE/pg_hba.conf file.SeegpmonUserAuthenticationfornotesaboutrestrictingthegpmonuser’saccesstodatabases.Thepasswordyouspecifyissavedina .pgpass fileinthe gpadmin user’shomedirectory.SeeChangingthegpmonPassword forstepstochangethe gpmon password.
4. Whentheutilitycompletes,restartGreenplumDatabaseserver.Thedatacollectionagentsdonotstartuntilthedatabaseisrestarted.
$gpstop-r
5. Usingthe ps command,verifythatthedatacollectionprocessisrunningontheGreenplummaster.Forexample:
$ps-ef|grepgpmmon
6. Runthefollowingcommandtoverifythatthe gpmon usercanauthenticateandthatthedatacollectionprocessesarewritingtotheCommandCenterdatabase.Ifallofthesegmentdatacollectionagentsarerunning,youshouldseeonerowpersegmenthost.
$psql-Ugpmongpperfmon-c'SELECT*FROMsystem_now;'
Thedatacollectionagentsarenowrunning,andyourGreenplumsystemnowhasagpperfmondatabaseinstalled.ThisisthedatabasewherehistoricalCommandCenterdataisstored.Youcanconnecttoitasfollows:
$psqlgpperfmon
©CopyrightPivotalSoftwareInc,2013-2018 14 4.4.0
ConfiguringaStandbyMasterHost(ifenabled)1. Copythe $MASTER_DATA_DIRECTORY/pg_hba.conf filefromyourprimarymasterhosttoyourstandbymasterhost.Thisensuresthattherequired
connectionoptionsarealsosetonthestandbymaster.
2. Copyyour ~/.pgpass filefromyourprimarymasterhosttoyourstandbymasterhost.Thisfileusuallyresidesinthe gpadmin user’shomedirectory.Notethatthepermissionson .pgpass mustbesetto600(forexample: chmod 0600 ~/.pgpass ).
gpmonUserAuthenticationThe gpperfmon_install utilityaddsentriesforthe gpmon usertothe pg_hba.conf file,whichallowsthe gpmon usertomakealocalconnectiontoanydatabaseintheGreenplumcluster.
localgpperfmongpmonmd5hostallgpmon127.0.0.1/28md5hostallgpmon::1/128md5
ForGreenplumCommandCenter,the gpmon usermustalsohavehostaccessfromthehostwheretheCommandCenterwebserverandbackendrun.ThisrequiresthatyouaddanentryfortheCommandCenterhost.Editthe $MASTER_DATA_DIRECTORY/pg_hba.conf fileandaddalinelikethefollowing.
hostallgpmon<cc-host-ip>/32md5
Sincethe gpmon roleisaGreenplumDatabasesuperuser,youmaywishtorestricttherolefromaccessingotherdatabases.Edittheselines:
ListgpperfmonandthedatabasesyouwanttomonitorwithCommandCenterinthesecondfield:
localgpperfmon,userdb1,userdb2gpmonmd5hostgpperfmon,userdb1,userdb2gpmon127.0.0.1/28md5hostgpperfmon,userdb1,userdb2gpmon::1/128md5hostgpperfmon,userdb1,userdb2gpmon<cc-host-ip>/32md5
SeeChangingthegpmonPassword forstepstochangethe gpmon user’spassword.
©CopyrightPivotalSoftwareInc,2013-2018 15 4.4.0
InstallingPivotalGreenplumCommandCenterThePivotalGreenplumCommandCenterinstallationutilityinstallstheCommandCenterfilesonallhostsintheGreenplumDatabasecluster.
PrerequisitesTheGreenplumCommandCenterinstallerinstallstheCommandCentersoftwareoneveryhostintheGreenplumDatabasecluster.Youcanruntheinstalleronthemasterhost.ThelistofhoststoinstalluponisretrievedfromtheGreenplumDatabase gp_segment_configuration systemtable.
BeforeinstallingGreenplumCommandCenter,ensurethefollowingrequirementsaremet:
GreenplumDatabasemustbeinstalledandrunning.SeethePivotalGreenplumCommandCenterreleasenotesforcompatibleGreenplumDatabaseversions.
TheGreenplumDatabase MASTER_DATA_DIRECTORY environmentvariablemustbeset.
Thegpperfmondatabaseandgpmonrolemustbecreatedandthegpperfmonagentsmustberunning.SeeCreatingthegpperfmonDatabase.
ThedirectorywhereGreenplumCommandCenterwillbeinstalled, /usr/local/ bydefault,mustbewritablebythegpadminuseronallGreenplumDatabasehosts.Alternatively,youcaninstallCommandCenterasrootand,whendone,changeownershipofallfilesintheinstallationdirectorytothegpadminuser.SeeSelectingandPreparinganInstallationDirectoryforCommandCenterforoptions.
PreparetheStandbyMasterHostAftertheCommandCentersoftwareisinstalled,youstarttheCommandCenterwebserverandbackendonthestandbymasterhostoronthemasterhost.SomepreparationisrequiredtoenableCommandCentertorunonthestandbymasterhost.
1. Onthemasterhost,editthe $MASTER_DATA_DIRECTORY/pg_hba.conf fileandaddthefollowingentrytoallowthegpmonusertoauthenticatefromanyhostintheGreenplumDatabasecluster.
hostallgpmon<IP_of_host>/32md5
Run gpstop-u tohaveGreenplumDatabasereloadthefile.
2. Copythe .pgpass filefromthemasterhosttothestandbymasterhost.Runthesecommandsfromthemaster:
$sshgpadmin@<standby_host>$scpgpadmin@<master_host>:~/.pgpass~$chmod600~/.pgpass
3. IftheGreenplumCommandCenterwebserveristosupportTLS/SSL,aservercertificatemustbeobtainedandinstalledontheCommandCenterhostinalocationreadablebythegpadminuser.Thedefaultpathis /etc/ssl/certs/cert.pem .
4. IfGreenplumCommandCenteristosupportKerberosauthentication,GreenplumDatabasemusthaveKerberosauthenticationsetupandtherequiredprincipalsandkeytabsmustbeinstalledontheCommandCenterhost.SeeEnablingAuthenticationwithKerberosforKerberossetupinstructions.YoucaninstallCommandCenterwithoutKerberosauthenticationinitiallyandthenenableKerberoslaterbyrunningthe gpcckrbenable
Important!IfyouupgradedtoPivotalGreenplumDatabaserelease5.7orlaterfromanearlierGreenplumDatabase5.xrelease,youmustfollowstepsatSetUptheMetricsCollectionandWorkloadManagementExtensionsbeforeyoustartGreenplumCommandCenter.
NOTETherearetwoalternativemethodsyoucanusetosupplythegpmonuser’spasswordsothatyoudonothavetoputthe .pgpass fileonthehost.CommandCenteronlyrequiresthegpmonpasswordwhenyourun gpccstart , gpccstop ,or gpccstatus .
1. Setthe PGPASSWORD environmentvariablebeforeyourun gpcc commands.Example:
$ PGPASSWORD=changeme gpcc status
2. Addthe -W optionto gpcc commandstohavethecommandpromptyouforthepassword.Example:
$ gpcc start -W
©CopyrightPivotalSoftwareInc,2013-2018 16 4.4.0
command.
SelectingandPreparinganInstallationDirectoryforCommandCenterTheCommandCenterinstallationdirectorymustexistandbewritableoneveryhostintheGreenplumDatabaseclusterbytheLinuxuserwhorunstheCommandCenterinstaller.Thatusermustalsohavepasswordlesssshaccesstoallhostsinthecluster.TheCommandCenterinstallercreatesadirectorynamed greenplum-cc-web-<version> intheinstallationdirectoryoneveryhostintheGreenplumDatabasecluster.WhenCommandCenterinstallationiscompletethe greenplum-cc-web-<version> directoryandallofitscontentsmustbeownedbythe gpadmin user.
ThedefaultinstallationdirectoryforCommandCenteris /usr/local .InastandardLinuxsystem,the /usr/local directoryisownedby root andisonlywritableby root .Ifyouchoosethedefaultinstallationdirectoryoranotherdirectorywhere gpadmin doesnothavewritepermission,youcanuseoneofthefollowingmethodstoinstallCommandCenter.
Temporarilychangepermissionsontheinstallationdirectorytoallowthe gpadmin usertoinstallCommandCenter.
As root ,createthe greenplum-cc-web-<version> directoryintheinstallationdirectoryonallhostsintheGreenplumDatabaseclusterandchangethedirectory’sownershipto gpadmin .
RuntheCommandCenterinstalleras root andthenchangetheownershipofthe greenplum-cc-web-<version> directoryanditscontentstogpadmin .
Alloftheseoptionsrequire root toexecuteshellcommandsoneveryGreenplumDatabasehost.TheGreenplumDatabaseutilities gpssh-exkeys andgpssh makethismucheasier.
SettingupPasswordlesssshfortherootUserTherootusermusthavepasswordlesssshaccesstoallGreenplumDatabasehostsinordertoruntheCommandCenterinstallerortoexecuteshellcommandsonGreenplumhostsusingthe gpssh utility.Followthisproceduretosetuppasswordlesssshaccesswiththe gpssh-exkeys utility.
1. Logintothemasterhostasthe root user.
2. Createatextfile,forexample allhosts.txt ,containingalistofallGreenplumDatabaseclusterhosts,includingthemasterandstandbymasterhost,onenameperline.
3. SourcetheGreenplumDatabaseenvironmentsetupscriptintheGreenplumDatabaseinstallationdirectory.
#source/usr/local/greenplum-db-<version>/greenplum-db_path.sh
4. Runthe gpssh-exkeys utility,specifyingthehostsfilewiththe -f option.
#gpssh-exkeys-fallhosts.txt
CreatetheInstallationDirectoryandInstallCommandCenterSoftwareCreatetheCommandCenterinstallationdirectoryonallGreenplumDatabasehostsandassignpermissionssothatyoucanruntheCommandCenterinstallerasthe gpadmin user.
1. Logintothemasterhostasthe root user.
2. SourcetheGreenplumDatabaseenvironmentsetupscriptintheGreenplumDatabaseinstallationdirectory.
#source/usr/local/greenplum-db-<version>/greenplum-db_path.sh
3. Usethe gpssh utilitytocreatethedirectoryandsetownershiponallhosts.
#gpssh-fallhosts.txt=>mkdir/opt/greenplum-cc-web-4.4.0=>chowngpadmin:gpadmin/opt/greenplum-cc-web-4.4.0=>exit
4. Asthe gpadmin user,runtheCommandCenterinstallerasdescribedinInstallationSteps.Whenpromptedfortheinstallationdirectory,enter /opt .
©CopyrightPivotalSoftwareInc,2013-2018 17 4.4.0
ChangeOwnershipoftheCommandCenterDirectoryPost-InstallationIfyouinsteadinstalledCommandCenterinstallerasthe root user,thenyoumustfollowthesestepstochangetheownershipoftheinstallationdirectoryanditscontentstothe gpadmin user.ThisexampleassumesyouhaveinstalledCommandCenteras root inthe opt directory,followingthestepsinInstallationSteps.Thefilesinthe greenplum-cc-web-<version> directoryanditscontentsarecurrentlyownedby root .
1. Logintothemasterhostas root .
2. SourcetheGreenplumDatabaseenvironmentsetupscriptintheGreenplumDatabaseinstallationdirectory.
#source/usr/local/greenplum-db-<version>/greenplum-db_path.sh
3. Runthefollowingcommands,providingthepathtoyourCommandCenterinstallation.
#gpssh-fallhosts.txt=>chown-Rgpadmin:gpadmin/opt/greenplum-cc-web-<version>exit
Nowyoucanlogintothestandbymasterormasterhostas gpadmin andstartCommandCenter.
InstallationStepsYoucanruntheGreenplumCommandCenterinstallerontheGreenplumDatabasemasterhost.Thesoftwarewillbecopiedtoallotherhostsinthecluster.
Performthesestepsasthe gpadmin user,if gpadmin haswritepermissionintheinstallationdirectory,oras root .
IfyouinstallCommandCenteras root ,makesure root haspasswordlesssshaccesstoallhostsintheGreenplumDatabasecluster.(SeeSettingupPasswordlesssshfortherootUser.)Wheninstallationiscomplete,besuretochangetheownershipoftheGreenplumCommandCenterdirectoryandcontentsto gpadmin .(SeeChangeOwnershipoftheCommandCenterDirectoryPost-Installation.)
1. DownloadGreenplumCommandCenter4.xfromPivotalNetwork .
2. Extracttheinstallerfromthezipfile.
$unzipgreenplum-cc-web-<version>-LINUX-x86_64.zip
Extractingtheinstallercreatesa greenplum-cc-web-<version> directorycontainingthe gpccinstall-<version> installationutility.
3. Sourcethe greenplum_path.sh scriptintheGreenplumDatabaseinstallationdirectorytoensurethe GPHOME environmentvariableisset.
$source/usr/local/greenplum-db/greenplum_path.sh
4. RuntheGreenplumCommandCenterinstaller.
$cdgreenplum-cc-web-<version>$./gpccinstall-<version>-W
Theinstallerhasthreeoptions.
The -c <config-file> optionspecifiesthepathtoaninstallationconfigurationfiletouseforanon-interactiveinstallation.SeeInstallwithaConfigurationFile.The W optioninstructstheinstallertopromptforthegpmonuser’spassword.TheinstallerattemptstoretrievethegpmonpasswordfromthePGPASSWORD environmentvariable,ifset,orfromthe ~gpadmin/.pgpass file,ifpresent.Ifneitheroftheseoptionsisavailable,includethe-W optiontoinstructtheinstallertopromptforthepassword.The --ssh_path optionallowsyoutospecifythefullpathtoacustomsshprogram.Ifyoudonotsupplythisoption,theinstallerusesthesshcommandonthepath.
$./gpccinstall-<version>--ssh_path/usr/local/bin/ssh-W
5. Readthroughthelicenseagreementandenter y toaccept.
6. WherewouldyouliketoinstallGreenplumCommandCenter?
©CopyrightPivotalSoftwareInc,2013-2018 18 4.4.0
Thedefaultinstallationdirectoryis /usr/local .PressEntertoacceptthedefaultorenterthedesiredpath.ThedirectorymustexistonallhostsintheGreenplumDatabaseclusterandmustbewritablebygpadmin.
7. WhatwouldyouliketonamethisinstallationofGreenplumCommandCenter?EnteranametodisplayonCommandCenterwebpagestoidentifythisGreenplumDatabasesystem.
8. OnwhichportwouldyouliketoinstalltheGreenplumCommandCenterwebserver?ThedefaultCommandCenterlistenportis28080.PressEntertoacceptthedefaultorenteranotherportnumber.
9. WouldyouliketoenableSSL?Enter y ifyouwanttoenableSSL/TLS(HTTPS)encryptionforclientconnectionstotheCommandCenterwebserver.TheinstallationutilitypromptsforthelocationoftheSSLcertificate.ProvidethefilepathfortheSSLcertificateEnterthepathtothecertificateinstalledontheCommandCenterhost.Thedefaultis /etc/ssl/certs/cert.pem .Thecertificatemustbereadablebythegpadminuser.
10. WouldyouliketoenableKerberos?Enter y ifyouwanttoenableclientauthenticationwithKerberos.KerberosmustalreadybeenabledforGreenplumDatabase.(Ifyouenter n ,youcansetupKerberosauthenticationlaterusingthe gpcckrbenable command.)TheinstallerpromptsforinformationaboutyourKerberosinstallation.ChooseKerberosmode(1.normal/2.strict/3.gpmon_only)GreenplumCommandCentersupportsthreedifferentKerberosauthenticationschemes.
1-normalmode(default)–TheCommandCenterKerberoskeytabfilecontainstheCommandCenterprincipalandmaycontainprincipalsforCommandCenterusers.Iftheprincipalintheclient’sconnectionrequestisintheCommandCenter’skeytabfile,CommandCenterusestheclient’sprincipalfordatabaseconnections.Otherwise,CommandCenterusesthe gpmon userfordatabaseconnections.2-strictmode–CommandCenterhasaKerberoskeytabfilecontainingtheCommandCenterserviceprincipalandaprincipalforeveryCommandCenteruser.Iftheprincipalintheclient’sconnectionrequestisinthekeytabfile,thewebservergrantstheclientaccessandthewebserverconnectstoGreenplumDatabaseusingtheclient’sprincipalname.Iftheprincipalisnotinthekeytabfile,theconnectionrequestfails.3-gpmon_onlymode–CommandCenterusesthe gpmon databaseroleforallGreenplumDatabaseconnections.NoclientprincipalsareneededintheCommandCenter’skeytabfile.
ProvidethepathtothekeytabfileEnterthepathtothekeytabfilecontainingtheKerberosprincipalfortheCommandCenterwebserverand,optionally,CommandCenteruserprincipals.WhatisthenameoftheGPDBKerberosservice?ThedefaultservicenameforGreenplumDatabaseis postgres .YoucancheckthevalueoftheservicenameforyourGreenplumDatabaseclusterwiththe gpconfig utility:
$gpconfig-skrb_srvname
WhatistheURLoftheCommandCenterwebserver?TheKerberoskeytabfilemustcontainaprincipalfortheCommandCenterwebserver.Theprincipalnameisoftheformat HTTP/<gpcc-host>@<realm> ,where <gpcc-host> isthehostnameclientsuseinURLswhenconnectingtotheCommandCenterwebserver.
Ifyourantheinstalleras root ,changetheownershipoftheGreenplumCommandCenterdirectoryandcontentsto gpadmin .SeeChangeOwnershipoftheCommandCenterDirectoryPost-installation.
InstallWithaConfigurationFileYoucanprovideaconfigurationfiletotheGreenplumCommandCenterinstallertoperformanon-interactiveCommandCenterinstallation.Notethatyoumuststillviewandacceptthelicenseagreement.
$cdgreenplum-cc-web-<version>$./gpccinstall-<version>-c<config-file>-W
Thefollowingtablecontainsthenamesoftheparameterscorrespondingtotheinteractiveinstallationpromptsandtheirdefaultvalues.Defineparametersinaconfigurationfileforanyparametersthathavenodefaultvalueortooverridedefaultvalues.
InstallerPrompt Default Parameter
WherewouldyouliketoinstallGreenplumCommandCenter? /usr/local path
WhatwouldyouliketonamethisinstallationofGreenplumCommandCenter? gpcc display_name
©CopyrightPivotalSoftwareInc,2013-2018 19 4.4.0
OnwhichportwouldyouliketoinstalltheGreenplumCommandCenterwebserver? 28080 web_portWouldyouliketoenableSSL? false enable_ssl
PleaseprovidethefilepathfortheSSLcertificate: /etc/ssl/certs/cert.pem ssl_cert_file
WouldyouliketoenableKerberos? false enable_kerberos
ChooseKerberosmode(1.normal/2.strict/3.gpmon_only): 1 krb_mode
Pleaseprovidethepathtothekeytabfile: keytab
WhatisthenameoftheGPDBKerberosservice? postgres krb_service_name
WhatistheURLoftheCommandCenterwebserver? gpcc webserver_url
InstallerPrompt Default Parameter
Ifthe enable_ssl parameteristrue,the ssl_cert_file parameterisrequired.
Ifthe enable_kerberos parameteristrue,the keytab parameterisrequiredandthe webserver_url , krb_mode ,and krb_service_name mustbesettovaluesfromyourKerberosinstallation.
Thefollowinginstallationconfigurationfileexamplesetsallparameterstotheirdefaultvalues.
path=/usr/local#Setthedisplay_nameparamtothestringtodisplayintheGPCCUI.#ThedefaultisthehostnameoftheGreenplummasterhost#display_name=localhost
master_port=5432web_port=28080rpc_port=8899enable_ssl=false#Uncommentandsetthessl_cert_fileifyousetenable_ssltotrue.#ssl_cert_file=/etc/certs/mycertenable_kerberos=false#Uncommentandsetthefollowingparametersifyousetenable_kerberostotrue.#webserver_url=<webserver_service_url>#krb_mode=1#keytab=<path_to_keytab>#krb_service_name=postgres
SetUpCommandCenterandWorkloadManagementExtensionsYoumustfollowthestepsinthissectiononlyifyouhaveupgradedyourPivotalGreenplumDatabasesystemfroma5.xreleaseearlierthan5.7.0.
TheGreenplumDatabasemetricscollectionandworkloadmanagementextensionsareinstalledwhenyouupgradetoPivotalGreenplumDatabase5.7.0orlater.However,theupgradeprocedurepreservesyourprevious postgresql.conf configurationfile,soyoumustmanuallysettheserverconfigurationparametersthatenabletheextensions.YoumustrestartGreenplumDatabaseifyouchangeanyconfigurationparameters.
TosetuptheCommandCenterandworkloadmanagementextensions,logintothemasterhostasgpadminandfollowthesesteps.
1. Addthemetricscollectorandworkloadmanagementsharedlibrariestothe shared_preload_libraries configurationparameter.Checkthecurrentvalueofthe shared_preload_libraries configurationparameter.
$gpconfig-sshared_preload_librariesValuesonallsegmentsareconsistentGUC:shared_preload_librariesMastervalue:Segmentvalue:
AddtheCommandCenterandworkloadmanagementlibrariestotheparameter.(Iftherewereexistinglibrariesintheparameter,appendthenewlibraries,separatedwithacomma.)
$gpconfig-cshared_preload_libraries-v'\$libdir/metrics_collector,\$libdir/gp_wlm'
2. Makesurethe gp_enable_query_metrics configurationparameterison.
gpconfig-sgp_enable_query_metricsgpconfig-cgp_enable_query_metrics-von
3. Ifyouchangedanyconfigurationparameters,restartGreenplumDatabase.
©CopyrightPivotalSoftwareInc,2013-2018 20 4.4.0
gpstop-r
NextStepsSettingtheGreenplumCommandCenterEnvironment
StartingandStoppingGreenplumCommandCenter
ConnectingtoGreenplumCommandCenter
©CopyrightPivotalSoftwareInc,2013-2018 21 4.4.0
UpgradingGreenplumCommandCenterToupgradeGreenplumCommandCenter,youinstallthenewCommandCentersoftwarerelease,stoptheoldversion,andstartthenewversion.YoucanthenremovetheolderCommandCenterreleasefromyourGreenplumDatabasehosts.
ToupgradetoanewreleaseofGreenplumCommandCenter4.x:
1. DownloadandinstallthenewCommandCenterreleasebyfollowingtheinstructionsinInstallingGreenplumCommandCenter.
2. StopthecurrentCommandCenterrelease.CommandCenter3.x:
$gpcmdr--stop<instance_name>
CommandCenter4.x:
$gpccstop
3. Sourcethe gpcc_path.sh scriptinthenewCommandCenterinstallationdirectory.
$source/usr/local/greenplum-cc-web-<version>/gpcc_path.sh
Note:Alsoupdatethesourcecommandinyourshellstart-upscript,forexample ~/.bashrc.sh or ~/.bash_profile.sh .
4. StartthenewCommandCenterrelease.
$gpccstart
5. UninstalltheolderCommandCenterrelease.See“UninstallingGreenplumCommandCenter”intheGreenplumCommandCenterdocumentationforthereleaseyouareuninstalling.
UpgradingFromGreenplumCommandCenter3.xto4.x
TheGreenplumCommandCenterarchitecturechangedbetweenCommandCenter3.xand4.x.
WithCommandCenter3.x,youinstalledtheGreenplumCommandCentersoftwareonetimeontheCommandCenterhost.YouthencreatedaCommandCenterinstanceforeachGreenplumDatabaseclusteryoumonitoredwithCommandCenter.
CommandCenter4.xdoesnothaveinstances;youinstalltheCommandCentersoftwareonthemasterorstandbymasteroftheGreenplumDatabaseclusteryouwanttomonitorwithCommandCenter.TheinstallercopiesthesoftwaretoeveryhostintheGreenplumDatabasecluster.TomonitoradditionalGreenplumDatabaseclustersyoumustinstalltheCommandCentersoftwareagain,onadifferentmasterorstandbyhost.MonitoringmultipleGreenplumDatabaseclustersrunningonthesamehardwareclusterisnotsupported.
©CopyrightPivotalSoftwareInc,2013-2018 22 4.4.0
UninstallingGreenplumCommandCenterTouninstallGreenplumCommandCenter,youmuststopboththeCommandCenterConsoleanddisablethedatacollectionagents.Optionally,youmayalsoremoveanydataassociatedwithGreenplumCommandCenterbyremovingyourCommandCenterConsoleinstallationandthegpperfmondatabase.
1. StopCommandCenterConsoleifitiscurrentlyrunning.Forexample:
$gpcc--stop
2. RemovetheCommandCenterinstallationdirectoryfromallhosts.Forexample:
$rm-rf/usr/local/greenplum-cc-web-version
3. Disablethedatacollectionagents.
a. LogintothemasterhostastheGreenplumadministrativeuser( gpadmin ):
$su-gpadmin
b. Disablethedatacollecitonagentsbysettingthe gp_enable_gpperfmon serverconfigurationparameteroff:
$gpconfig-cgp_enable_gpperfmon-voff
c. Removeorcommentoutthegpmonentriesin pg_hba.conf .Forexample:
#localgpperfmongpmonmd5#hostgpperfmongpmon0.0.0.0/0md5
d. DroptheCommandCentersuperuserrolefromthedatabase.Forexample:
$psqltemplate1-c'DROPROLEgpmon;'
e. RestartGreenplumDatabase:
$gpstop-r
f. CleanupanyuncommittedCommandCenterdataandlogfilesthatresideonthemasterfilesystem:
$rm-rf$MASTER_DATA_DIRECTORY/gpperfmon/data/*$rm-rf$MASTER_DATA_DIRECTORY/gpperfmon/logs/*
g. IfyoudonotwanttokeepyourhistoricalCommandCenterdata,dropthegpperfmondatabase:
$dropdbgpperfmon
©CopyrightPivotalSoftwareInc,2013-2018 23 4.4.0
AbouttheCommandCenterInstallationTheinstallationprocedurecreatesasoftwareinstallationdirectoryforGreenplumCommandCenter.ThisdirectoryiscopiedtoallhostsintheGreenplumCluster.VersionsofGreenplumDatabasethatarecompatiblewithGreenplumCommandCenterincludepre-packagedfilesthatsupporttheCommandCenterreal-timemetricsandworkloadmanagementfeatures.
SoftwareInstallationDirectoryThefollowingfilesandfirst-levelsubdirectoriesarecopiedintotheinstallationdirectoryyouspecifywhenyouinstallGreenplumCommandCenter.Thislocationcanbereferencedwiththe $GPCC_HOME environmentvariablewhenyouhavesettheCommandCenterenvironment.
gpcc_path.sh –filecontainingenvironmentvariablesforGreenplumCommandCenter
bin/ –programfilesforGreenplumCommandCenter
gpcc-agent -real-timequerymetricscollectionagentgpccws -theGreenplumCommandCenterwebserverstatic/ -staticfilesfortheCommandCenterapplication
conf/
app.conf -configurationfilefortheCommandCenterwebserver
logs/ -webserveraccessanderrorlogfiles
open_source_licenses_GPCC.txt –licensesforopensourcecomponentsusedbyGreenplumCommandCenter
GreenplumDatabaseArtifactsTheCommandCenterreal-timemetricsandworkloadmanagementfeaturesdependonthe gp_wlm and metrics_collection GreenplumDatabaseextensions.TheseextensionsareincludedwithcompatibleversionsofGreenplumDatabase.
©CopyrightPivotalSoftwareInc,2013-2018 24 4.4.0
SettingtheGreenplumCommandCenterEnvironmentToenablethe gpadmin usertoexecuteCommandCenterutilitiessuchas gpcc atthecommandline,sourcethe gpcc_path.sh fileintheGreenplumCommandCenterinstallationdirectory.Forexample:
$source/usr/local/greenplum-cc-web-<version>/gpcc_path.sh
The gpcc_path.sh scriptsetsthe GPCC_HOME environmentvariabletotheCommandCenterinstallationdirectoryandaddsthe $GPCC_HOME/bindirectorytothepath.
Toautomaticallysourcethe gpcc_path.sh eachtimeyoulogin,addtheabovesourcecommandtoyourstart-upscript,forexample ~/.bashrc or~/.bash_profile .
©CopyrightPivotalSoftwareInc,2013-2018 25 4.4.0
ConnectingtotheGreenplumCommandCenterConsoleSignintotheCommandCenterConsolewithanameandpassword.IftheGuestAccesstoQueryMonitorfeatureisenabled,youcansigninanonymouslytoseejusttheQueryMonitorview.
OpentheCommandCenterConsoleinasupportedbrowserusingthehostnameandportconfiguredfortheCommandCenterwebserver.Forexample,toopenasecureCommandCenterconnectiononahostnamed smdw atport28080,enterthisURLintoyourbrowser:
https://smdw:28080
IftheViewQueryMonitorlinkispresent,youcanclickittoviewtheQueryMonitorpagewithoutsigningin.ThistakesyouimmediatelytotheQueryMonitorview.ToaccessadditionalCommandCenterfeatures,clickSignInontheQueryMonitorviewandsigninwithavalidCommandCenterusernameandpassword.Ifthelinkisnotpresentonthesign-inpage,aCommandCenteradministratorhasdisabledtheanonymousquerymonitorfeature.
TosigninasaCommandCenteruser,entertheusernameandpasswordofaGreenplumrolethathasbeenconfiguredtoallowauthenticationtoGreenplumCommandCenter,thenclickSignIn.ThisopenstheDashboardpageoftheCommandCenterConsole,whichprovidesagraphicalsystemsnapshotandasummaryviewofactivequeries.SeetheDashboardforinformationabouttheDashboardview.
NotetoChromeBrowserUsers
IfyouinstallanewversionofGreenplumCommandCenterusingthesameportnumberasthepreviousversion,andyouusetheChromewebbrowser,youmaybeunabletoviewreal-timequeriesuntilafteryouclearthebrowser’scache.Followthesesteps.
1. ChooseSettingsfromtheChromemenu.
2. ScrolltothebottomandclickAdvanced.
3. UnderPrivacyandsecurity,clickClearbrowsingdata.
4. ClicktheBasictabandselectCachedimagesandfiles.YoudonothavetoclearBrowsinghistoryorCookiesandothersitedata.
5. ClickCLEARDATAandthenlogintoCommandCenter.
©CopyrightPivotalSoftwareInc,2013-2018 26 4.4.0
AdministeringtheCommandCenterWebServerThegpccwswebserverbinaryandwebapplicationfilesareinstalledinthe bin directoryofyourGreenplumCommandCenterinstallation.
StartingandStoppingtheWebServerStartingtheCommandCenterWebServerrunsthegpccwswebserver,startsthemetricscollectionagentsonthesegmentservers,andstartsalistenerontheCommandCenterrpcport.
Youcanrunthe gpcc commandasthegpadminuseronthestandbymasterhost(recommended)oronthemasterhost.
Toensurethe gpcc commandisonyourpath,sourcethe gpcc_path.sh fileintheCommandCenterinstallationdirectoryoraddittothestartupscriptforyourcommandshell.SeeSettingtheGreenplumCommandCenterEnvironmentforinstructions.
TostartGreenplumCommandCenter
Logontothestandbymasterhostorthemasterhost.
Tologontothestandbyfromthemasterhost:
$ssh<standby-host>
SourcetheCommandCenterenvironmentvariables.
$source/usr/local/greenplum-cc-<version>/gpcc_path.
StarttheCommandCenterwebserverandthemetricscollectionagents.
$gpccstartStartingthegpccagentsandwebserver…2018/03/2217:35:06Agentsuccessfullystartedon7/8hosts2018/03/2217:35:06ViewGreenplumCommandCenterathttp://smdw:28080
TostopGreenplumCommandCenter
$gpccstop2018/03/2217:36:23Gpccwebserverandmetricscollectionagentshavebeenstopped.Usegpccstarttostartthemagain
TochecktheGreenplumCommandCenterstatus
$gpccstatusStartingthegpccagentsandwebserver…2018/03/2217:36:55Agentsuccessfullystartedon7/8hosts2018/03/2217:36:55ViewGreenplumCommandCenterathttp://smdw:28080
Seethe gpcc referencepageforfullsyntaxforthe gpcc command.
ConfiguringtheCommandCenterWebServerThewebserverconfigurationfileisstoredin $GPCC_HOME/conf/app.conf .TheparametersinthisconfigurationfilearesetwhenyouinstallGreenplumCommandCenter.TheinstallercopiestheCommandCenterinstallationdirectory,includingthisconfigurationfile,toeveryGreenplumDatabasehost.
SeetheWebServerParameterssectionofConfigurationFileReferenceforadescriptionoftheparametersinthisfile.
NOTEThe gpcc commandusesthegpmonroletoconnecttoGreenplumDatabase.Itlooksforthegpmonpasswordinthe PGPASSWORD environmentvariableorinthe .pgpass fileinthegpadminuser’shomedirectory.Youcaninsteadappendthe -W flagtothe gpcc commandsbelowtohavegpcc promptyoutoenterthepassword.
©CopyrightPivotalSoftwareInc,2013-2018 27 4.4.0
Youcanseeasummaryofthecurrentconfigurationusingthe gpcc--settings
command.
$gpcc--settingsInstallpath:/usr/localDisplayName:gpccGPCCport:28080Kerberos:disabledSSL:disabled
Ifyoumodifythefileononehostyoushouldcopyittoeveryotherhost.Besuretorestartthewebserverafteryouchangetheconfiguration.Ratherthanmodifyingtheconfigurationdirectly,youcanjuststopCommandCenterandre-runthe gpccinstall-<version> installationcommand.Thisensurestheconfigurationisconsistentonallhosts.
Youcanusethe gpcckrbenable commandtoaddKerberosauthenticationtotheCommandCenterconfiguration.SeeEnablingAuthenticationwithKerberosfordetailsaboutsettingupKerberosontheCommandCenterhost.The gpcckrbenable commandpromptsfortheKerberosprincipalnamesandartifactsandupdatestheconfiguration.
The gpcckrbdisable commandremovesKerberosparametersfromtheCommandCenterconfiguration.
ViewingandMaintainingWebServerLogFilesWebserveraccessanderrorlogmessagesarewrittento $GPCC_HOME/logs/gpccws.log .
IfyouexperienceerrorsviewingtheGreenplumCommandCenterConsole,refertothisfileformoreinformation.
Topreventthewebserverlogfromgrowingtoexcessivesize,youcansetuplogfilerotationusing logrotate or cronolog .
©CopyrightPivotalSoftwareInc,2013-2018 28 4.4.0
AdministeringCommandCenterAgentsTheCommandCentermetricscollectionagent, cc_agent ,runsonsegmenthostsandlistensonaUDPportforreal-timemetricsemittedbythemetricscollectiondatabaseextension.StartingGreenplumCommandCenterwiththe gpcc
startcommandstartstheCommandCenteragentoneachsegment
host.StoppingCommandCenterwith gpccstop endstheseprocesses.TheCommandCenterbackendmonitorstheseagentsandrestartsthemwhennecessary.
InstallingthegpperfmondatabaseconfiguresGreenplumDatabasetorunagentsonthemasterhostandeachsegmenthost.Theagentsarestartedautomaticallywhenthedatabasesystemstartsup.TheGreenplumDatabasepostmasterprocessmonitorstheagentsandrestartsthemwhennecessary.
gpmmon
The gpmmon agentrunsontheGreenplumDatabasemasterhost.Itcollectsinitialqueryinformationfromthemaster.Every15seconds,bydefault,itpromptsthe gpsmon agentstosendtheiraccumulateddata.The gpmmon processsavesthemetricsdataandlogstoCSVtextfilesthatfeedtheexternaltablesinthegpperfmondatabase.
gpsmon
The gpsmon agentrunsoneachGreenplumDatabasesegmenthost.ItlistensonaUDPportformetricsemittedbyGreenplumDatabase,gathersadditionalmetricsfromtheoperatingsystem,andforwardsdatatothe gpmmon agentwhenrequested.
Thistopicdescribesbasicagentadministrationtasks,includingaddinghostsandviewingagentlogfiles.
AddingandRemovingHostsWhenyouaddorreplaceGreenplumDatabasehosts,youmustreinstalltheGreenplumCommandCentersoftwaretoensurethesoftwareisinstalledonthenewhosts.ItisnotnecessarytouninstallCommandCenterbeforereinstalling.StopCommandCenterandrestartittostartagentsonthenewhosts.
Thegpperfmon gpsmon agentsonnewhostsaredetectedautomaticallybythemasteragent, gpmmon .Wheneverthe gp_enable_gpperfmon serverconfigurationparameterisenabledonthemaster,the gpmmon agentautomaticallydetects,starts,andbeginsharvestingdatafromnewsegmentagents.
ViewingandMaintainingAgentLogFilesLogmessagesfortheCommandCentermetricscollectoragentsaresavedinthe logs directoryoftheCommandCenterinstallationdirectory.
Logmessagesforthegpperfmonmasteragentarewrittentothefollowingfilebydefault:
$MASTER_DATA_DIRECTORY/gpperfmon/logs/gpmmon.log
Tochangethegpperfmonlogfilelocation,editthe log_location parameterin gpperfmon.conf .
Onthesegmenthosts,gpperfmonagentlogmessagesarewrittentoa gpsmon.log fileinthesegmentinstance’sdatadirectory.Forahostwithmultiplesegments,theagentlogfileislocatedinthedatadirectoryofthefirstsegment,aslistedinthe gp_configuration tablebydbid.Ifthesegmentagentisunabletologintothisdirectory,itwilllogmessagestothehomedirectoryoftheuserrunningCommandCenter(typically gpadmin ).
ConfiguringgpperfmonLogFileRolloverAthigherlogginglevels,thesizeofthegpperfmonlogfilesmaygrowdramatically.Topreventthelogfilesfromgrowingtoexcessivesize,youcanaddanoptionallogrolloverparameterto gpperfmon.conf .Thevalueofthisparameterismeasuredinbytes.Forexample:
max_log_size=10485760
Withthissetting,thelogfileswillgrowto10MBbeforethesystemrollsoverthelogfile.Thetimestampisaddedtothelogfilenamewhenitisrolledover.Administratorsmustperiodicallycleanoutoldlogfilesthatarenolongerneeded.
©CopyrightPivotalSoftwareInc,2013-2018 29 4.4.0
AdministeringthegpperfmonDatabaseDatacollectedbygpmmonandgpsmonagentsisstoredinadedicateddatabasecalledgpperfmon.Thisdatabaserequiresthetypicaldatabasemaintenancetasks,suchascleanupofoldhistoricaldataandperiodic ANALYZE .
SeethegpperfmonDatabaseReference sectionforareferenceofthetablesandviewsinthegpperfmondatabase.
ConnectingtothegpperfmonDatabaseDatabaseadministratorscanconnectdirectlytothegpperfmondatabaseusinganyGreenplumDatabase-compatibleclientprogram(suchas psql ).Forexample:
$psql-dgpperfmon-hmaster_host-p5432-Ugpadmin
BackingUpandRestoringthegpperfmonDatabaseThehistorytablesofthegpperfmondatabasecanbebackedupandrestoredusingtheGreenplumDatabasebackupandrestoreutilities.SeetheGreenplumDatabaseUtilityGuideformoreinformation.
MaintainingtheHistoricalDataTablesAllofthe *_history tablesstoredinthegpperfmondatabasearepartitionedintomonthlypartitions.AJanuary2010partitioniscreatedatinstallationtimeasatemplatepartition.Itcanbedeletedoncesomecurrentpartitionsarecreated.TheCommandCenteragentsautomaticallycreatenewpartitionsintwomonthincrementsasneeded.Administratorsmustperiodicallydroppartitionsforthemonthsthatarenolongerneededinordertomaintainthesizeofthedatabase.
SeetheGreenplumDatabaseAdministratorGuideformoreinformationondroppingpartitionsofapartitionedtable.
©CopyrightPivotalSoftwareInc,2013-2018 30 4.4.0
gpccManagestheGreenplumCommandCenterwebserviceandmetricscollectionagents.
gpcc<action>[-W]
gpcc[--version|-v]
gpcc[--help|-h]
gpcc[--settings]
Actionsstart
StartstheCommandCenterwebserviceandmetricscollectionagents.Addthe -W flagtoforceapromptforthegpmonuserpassword.
stop
StopstheCommandCenterwebserviceandmetricscollectionagents.Addthe -W flagtoforceapromptforthegpmonuserpassword.
status
Displaysthestatus,either Running or Stopped ,ofthewebserverandmetricscollectionagents.Addthe -W flagtoforceapromptforthegpmonuserpassword.
krbenable
EnablesKerberosauthenticationforCommandCenter.
Usethe gpcckrbenable commandtosetupKerberosauthenticationforCommandCenterusersifCommandCenterwasinitiallyinstalledwithoutenablingKerberos.Whenyourun gpcckrbenable , gpcc promptsfor:
thewebservername
thenameoftheGreenplumDatabaseKerberosservice
theCommandCenterKerberosauthenticationmode
thepathtothekeytabfileontheCommandCenterhost.
Beforeyourun gpcckrbenable ,seeEnablingAuthenticationwithKerberostocheckprerequisitesandforhelppreparingtheCommandCenterhosttoallowKerberosauthentication.
krbdisable
DisablesKerberosauthenticationforCommandCenter.
help
Displayssyntaxandhelptextforthe gpcc command.
Options--settings
DisplaysthecurrentvaluesoftheCommandCenterconfigurationparameters.SeeSetupConfigurationFileforalistoftheconfigurationparameters.
--version or -vDisplaystheGreenplumCommandCenterversion.
-W <password>
Theoptional -W optionspecifiesthepasswordforthegpmonuser.The gpcc commandnormallygetsthepasswordfromthe $PGPASSWDenvironmentvariableorthe .pgpass fileinthegpadminuser’shomedirectory.Ifthepasswordisnotavailablewitheitherofthesemethods,the-W optionmustbeincludedtospecifythepasswordwheneveryourun gpcc .
©CopyrightPivotalSoftwareInc,2013-2018 31 4.4.0
DescriptionOncestarted,theCommandCenterbackendmonitorsthemetricsagentswithaheartbeat.Ifafailedagentisdetected,thebackendspawnsanewagentprocess.
ExamplesStartCommandCenterandthemetricsagents,promptingforthegpmonpassword.
$gpccstart-WPasswordforGPDBusergpmon:Startingthegpccagentsandwebserver…2018/03/2217:51:51Agentsuccessfullystartedon7/8hosts2018/03/2217:51:51ViewGreenplumCommandCenterathttp://smdw:28080
©CopyrightPivotalSoftwareInc,2013-2018 32 4.4.0
ConfigurationFileReferenceConfigurationparametersforGreenplumCommandCenterarestoredinthefollowingfiles.
$MASTER_DATA_DIRECTORY/gpperfmon/conf/gpperfmon.conf ,onGreenplumDatabasemasterhostStoresconfigurationparametersfortheGreenplumCommandCenteragents.
$GPCC_HOME/conf/app.conf ,onCommandCenterhost.StoresconfigurationparametersfortheCommandCenterwebapplicationandwebserver.
$MASTER_DATA_DIRECTORY/postgresql.conf
StoresconfigurationparameterstoenabletheGreenplumCommandCenterfeaturesforGreenplumDatabaseserver.Theseparametersarenormallysetusingthe gpconfig GreneplumDatabasemanagementutility.
$GPCC_HOME/bin/ssh-wrapper
GreenplumCommandCenternormallyfindsthe ssh commandonthepath.Ifyourenvironmenthasanincompatibleimplementationofthiscommandonthepath,youcanprovidetheabsolutepathtoyourversioninthe ssh-wrapper script,locatedat $GPCC_HOME/bin/ssh-wrapper .
Forexample:
ssh="/opt/bin/myssh"
©CopyrightPivotalSoftwareInc,2013-2018 33 4.4.0
CommandCenterAgentParametersThe $MASTER_DATA_DIRECTORY/gpperfmon/conf/gpperfmon.conf fileontheGreenplumDatabasemasterhoststoresconfigurationparametersfortheCommandCenteragents.Forconfigurationchangestotheseoptionstotakeeffect,youmustsave gpperfmon.conf andthenrestartGreenplumDatabaseserver( gpstop-
r).
ToenabletheCommandCenteragentswithinGreenplumDatabaseserver,youmustalsosettheGreenplumDatabaseServerConfigurationParameters.Seethe gpperfmon_install referenceintheGreenplumDatabaseUtilityGuidefordetails.
log_locationSpecifiesadirectorylocationforCommandCenterlogfiles.Defaultis $MASTER_DATA_DIRECTORY/gpperfmon/logs .
min_query_timeSpecifiestheminimumqueryruntimeinsecondsforstatisticscollection.CommandCenterlogsallqueriesthatrunlongerthanthisvalueinthequeries_historytable.Forquerieswithshorterruntimes,nohistoricaldataiscollected.Defaultsto20seconds.
Ifyouknowthatyouwanttocollectdataforallqueries,youcansetthisparametertoalowvalue.Settingtheminimumqueryruntimetozero,however,collectsdataevenforthenumerousqueriesrunbyCommandCenteritself,creatingalargeamountofdatathatmaynotbeuseful.
min_detailed_query_timeSpecifiestheminimumiteratorruntimeinsecondsforstatisticscollection.CommandCenterlogsalliteratorsthatrunlongerthanthisvalueintheiterators_historytable.Foriteratorswithshorterruntimes,nodataiscollected.Minimumvalueis10seconds.
Thisparameter’svaluemustalwaysbeequalto,orgreaterthan,thevalueof min_query_time .Setting min_detailed_query_time higherthanmin_query_time allowsyoutologdetailedqueryplaniteratordataonlyforespeciallycomplex,long-runningqueries,whilestillloggingbasicquerydataforshorterqueries.
Giventhecomplexityandsizeofiteratordata,youmaywanttoadjustthisparameteraccordingtothesizeofdatacollected.Iftheiterators_* tablesaregrowingtoexcessivesizewithoutprovidingusefulinformation,youcanraisethevalueofthisparametertologiteratordetailforfewerqueries.
max_log_sizeThisparameterisnotincludedingpperfmon.conf,butitmaybeaddedtothisfileforusewithGreenplumCommandCenter.
Topreventthelogfilesfromgrowingtoexcessivesize,youcanaddthe max_log_size parameterto gpperfmon.conf .Thevalueofthisparameterismeasuredinbytes.Forexample:
max_log_size=10485760
Withthissetting,thelogfileswillgrowto10MBbeforethesystemrollsovertoanewlogfile.
partition_ageThenumberofmonthsthatGreenplumCommandCenterstatisticsdatawillberetained.Thedefaultitis0,whichmeanswewon’tdropanydata.
quantumSpecifiesthetimeinsecondsbetweenupdatesfromCommandCenteragentsonallsegments.Validvaluesare10,15,20,30,and60.Defaultsto15seconds.
Ifyoupreferalessgranularviewofperformance,orwanttocollectandanalyzeminimalamountsofdataforsystemmetrics,chooseahigherquantum.Tocollectdatamorefrequently,choosealowervalue.
ignore_qexec_packetWhensettotrue,GreenplumCommandCenteragentsdonotcollectperformancedatainthegpperfmondatabase queries_* tables: rows_out ,cpu_elapsed , cpu_currpct , skew_cpu ,and skew_rows .Thedefaultsetting,true,reducestheamountofmemoryconsumedbythegpmmonprocess.Setthisparametertofalseifyourequirethisadditionalperformancedata.
smdw_aliasesThisparameterallowsyoutospecifyadditionalhostnamesforthestandbymaster.Forexample,ifthestandbymasterhastwoNICs,youcanenter:
smdw_aliases=smdw-1,smdw-2
ThisoptionalfaulttoleranceparameterisusefuliftheGreenplumCommandCenterlosesconnectivitywiththestandbymaster.Insteadofcontinuouslyretryingtoconnecttohostsmdw,itwilltrytoconnecttotheNIC-basedaliasesof smdw-1 and/or smdw-2 .ThisensuresthattheCommandCenterConsolecancontinuouslypollandmonitorthestandbymaster.
©CopyrightPivotalSoftwareInc,2013-2018 34 4.4.0
©CopyrightPivotalSoftwareInc,2013-2018 35 4.4.0
CommandCenterConsoleParametersTheCommandCenterConsoleconfigurationfileisontheCommandCenterhostat $GPCC_HOME/webserver/conf/app.conf .
Aftereditingthisfile,reloadtheconfigurationbyrestartingtheCommandCenterConsole.
$gpcc--stop$gpcc--start
appname = gpccws
Thewebserverbinaryfile.Donotchange.
listentcp4 = [true | false]
When true ,theaddresstypeistcp4.Thedefaultis true .
runmode = [prod | dev | test]
Theapplicationmode,whichcanbe dev , prod or test .Thedefaultis dev .In dev modeCommandCentershowsuserfriendlyerrorpages.Userfriendlyerrorpagesarenotrenderedin prod mode.
session = [true | false]
Usesessionstomanageuserexperience.Thedefaultis true .Sessionsarestoredinmemory.
enablexsrf = [true | false]
EnableCSRFprotection.
xsrfexpire = <seconds>
CSRFexpiretime.Thedefaultis 2592000 seconds.
xsrfkey = <token_string>
TheCSRFtoken.
rendertype = json
Therendertypeofwebserver.Donotchange.
printallsqls = [true | false]
PrintallbackendgpperfmonSQLtothewebserverconsole.Thedefaultis false .
path = /usr/local
PathtothedirectorywhereGreenplumCommandCenterisinstalled.
display_name = <display_name>
Thedisplaynameforconsole.
enable-kerberos = [true | false]
TrueifKerberosauthenticationisenabledforCommandCenter.Thedefaultis false .
HTTPSCertFile = </path/to/cert.pem>
Thefullpathtotheserver’sSSLcertificate,ifSSLisenabled.
HTTPSKeyFile = </path/to/cert.pem>
Theserver’sprivatekeyfileifSSLisenabled.
EnableHTTPS = [true | false]
EnablelisteningonthesecureSSLport.Thedefaultis true .
EnableHTTP = [true | false]
EnablelisteningontheHTTPport.Defaultis false .
httpport = <port>
Thewebserverport.Thedefaultis28080.
rpcport = <port>
TheportonwhichtheCommandCenterbackendreceivesdatafrommetricscollectoragents.Thedefaultis8899.
master_host = <hostname>
©CopyrightPivotalSoftwareInc,2013-2018 36 4.4.0
TheGreenplumDatabasehostname.Thedefaultis localhost .
master_port = <port>
TheGreenplumDatabasemasterport.Thedefaultis 5432 .
©CopyrightPivotalSoftwareInc,2013-2018 37 4.4.0
SetupConfigurationFileAsetupconfigurationfilecontainspropertiesusedtoconfigureGreenplumCommandCenterwhenyouperformanon-interactiveCommandCenterinstallation.Thefileispassedtothe gpccinstall commandwiththe -c option:
$./gpccinstall-<version>-c<config_file>
Theconfigurationfilecontains name:value or name=value entries,oneperline.Commentsbeginwitha # or ; characterandcontinuethroughtheendoftheline.
SeeInstallingPivotalGreenplumCommandCenterformoreinformationaboutinstallingCommandCenterwithaconfigurationfile.
Parameterspath
ThepathtothedirectorywhereGreenplumCommandCentersoftwarewillbeinstalled.ThedirectorymustbewritablebythegpadminuseronallhostsintheGreenplumCluster.
display_name
ThenametodisplayintheCommandCenteruserinterface.Thedefaultdisplaynameis gpcc .
master_port
TheGreenplumDatabasemasterport.Default: 5432 .
web_port
ThelistenportfortheCommandCenterwebserver.Thedefaultis 28080 .
enable_ssl
True ifclientconnectionstotheCommandCenterwebserveraretobesecuredwithSSL.Thedefaultis false .If true the ssl_cert_fileparametermustbesetandtheSSLcertificatemustbeinstalledonthehostwhereyourunCommandCenter.
ssl_cert_file
If enable_ssl is true ,setthisparametertothefullpathtoavalidcertificateinPEMfileformat.ThecertificatemustbeinstalledonthehostwhereyourunCommandCenter.
enable_kerberos
Setto true toenableKerberosauthentication.
krb_mode
TheKerberosauthenticationschemetouse.Thedefaultis 1 .
1-normalmode(default)-TheCommandCenterKerberoskeytabfilecontainstheCommandCenterprincipalandmaycontainprincipalsforCommandCenterusers.Iftheprincipalintheclient’sconnectionrequestisintheCommandCenter’skeytabfile,CommandCenterusestheclient’sprincipalfordatabaseconnections.Otherwise,CommandCenterusesthegpmonuserfordatabaseconnections.
2-strictmode-CommandCenterhasaKerberoskeytabfilecontainingtheCommandCenterserviceprincipalandaprincipalforeveryCommandCenteruser.Iftheprincipalintheclient’sconnectionrequestisinthekeytabfile,thewebservergrantstheclientaccessandthewebserverconnectstoGreenplumDatabaseusingtheclient’sprincipalname.Iftheprincipalisnotinthekeytabfile,theconnectionrequestfails.
3-gpmon_onlymode-CommandCenterusesthegpmondatabaseroleforallGreenplumDatabaseconnections.NoclientprincipalsareneededintheCommandCenter’skeytabfile.
webserver_url
Thewebserverhostname,fromtheKerberosHTTPserviceprincipal.
keytab
PathtothekeytabfilecontainingKerberosprincipalsfortheCommandCenterwebserverandusers.
Examples
©CopyrightPivotalSoftwareInc,2013-2018 38 4.4.0
######GPCC4.0setupconfigurationfile#####path=/optdisplay_name=GreenplumDatabaseProductionClustermaster_port=5432webserver_port=28081EnableHTTP=true;allowbothHTTPandHTTPSEnableHTTPS=truessl_cert_file=/etc/ssl/cert.pemenable_kerberos=false
©CopyrightPivotalSoftwareInc,2013-2018 39 4.4.0
GreenplumDatabaseServerConfigurationParameters
GreenplumDatabasegpperfmonDatabaseThefollowingGreenplumDatabaseconfigurationparametersmustbeuncommentedandsetintheserverconfigurationfile( postgresql.conf )toenabletheCommandCenterdatacollectionagents:
gp_enable_gpperfmon and gpperfmon_port mustbesetinboththemasterandsegment postgresql.conf files.
gp_enable_gpperfmon and gp_enable_gpperfmon onlyneedtobesetinthemaster postgresql.conf file.
Afterchangingthesesettings,theGreenplumDatabaseinstancemustberestartedforthechangestotakeeffect.
gp_enable_gpperfmonTurnsontheCommandCenterdatacollectionagentforasegment.Mustbesetinall postgresql.conf files(masterandallsegments).
gpperfmon_portThedefaultportfortheCommandCenteragentsis8888,butyoucansetthisparametertoadifferentportifrequired(masterandallsegments).
gp_gpperfmon_send_intervalSetsthefrequencyinsecondsthattheGreenplumDatabaseserverprocessessendqueryexecutionupdatestotheCommandCenteragentprocesses.
gp_external_enable_execThisparameterisenabledbydefaultandmustremainenabled.ItallowstheuseofexternaltablesthatexecuteOScommandsorscriptsonthesegmenthosts.TheCommandCenteragentsusethistypeofexternaltablestocollectcurrentsystemmetricsfromthesegments.
gpperfmon_log_alert_levelControlswhichmessagelevelsarewrittentothegpperfmonlog.Eachlevelincludesallthelevelsthatfollowit.Thelaterthelevel,thefewermessagesaresenttothelog.Thedefaultvalueiswarning.
shared_preload_librariesAcomma-separatedlistofsharedlibrariesthataretobepreloadedwhenGreenplumDatabasestarts.TheworkloadmanagementandquerymetricsextensionlibrariesmustbeincludedinthisconfigurationparametertouseGreenplumCommandCenter.
gp_enable_query_metricsWhenon,enablesquerymetricscollection.Thedefaultisoff.Aftersettingthisconfigurationparameter,GreenplumDatabasemustberestartedforthechangetotakeeffect.
gp_instrument_shmem_sizeTheamountofsharedmemory,inkilobytes,allocatedforquerymetrics.Thedefaultis5120andthemaximumis131072.Atstartup,ifgp_enable_query_metrics issettoon,GreenplumDatabaseallocatesspaceinsharedmemorytosavequerymetrics.Thismemoryisorganizedasaheaderandalistofslots.Thenumberofslotsneededdependsonthenumberofconcurrentqueriesandthenumberofexecutionplannodesperquery.Thedefaultvalue,5120,isbasedonaGreenplumDatabasesystemthatexecutesamaximumofabout250concurrentquerieswith120nodesperquery.Ifthe gp_enable_query_metrics configurationparameterisoff,oriftheslotsareexhausted,themetricsaremaintainedinlocalmemoryinsteadofinsharedmemory.
©CopyrightPivotalSoftwareInc,2013-2018 40 4.4.0
SecuringGreenplumCommandCenterGreenplumCommandCenterConsolecanbesecuredbyencryptingnetworktrafficbetweenthewebserverandusers’browsers,authenticatingCommandCenterusers,andmanagingusers’permissionstoaccessCommandCenterfeatures.
SSL/TLSEncryptionGreenplumCommandCentersupportsSSL/TLSencryptiontosecureconnectionsbetweenbrowsersandtheCommandCenterwebserver.WhenSSLisenabled,CommandCenterusestheWebSocketsAPI,enablinglong-lived,full-duplexconnections,inadditiontoencryption.
ToenableSSL,youshouldhaveasignedcertificatefortheCommandCenterwebserverinplacewhenyouinstallCommandCenter.PlaceyourcertificateontheserverwhereCommandCenterisinstalled,forexampleinthe /etc/ssl/certs directoryoftheGreenplummasterhost.YouimportthecertificatewhenyouinstallCommandCenter.Thelocationsofthecertificateandprivatekeyfilesaresavedinthe $GPCC_HOME/conf/app.conf configurationfilefortheCommandCenter.SeeCommandCenterConsoleParametersfordetails.
Youcanrequestacertificatefromyourorganization’sinternalcertificateauthorityoracommercialcertificateauthority,oryoucanuseaself-signedcertificateyoucreateyourselfwithacryptographysuitesuchasOpenSSL.Ifyoucreateaself-signedcertificate,notethatclientswillhavetooverrideasecuritywarningwhentheyfirstconnecttotheCommandCenterwebserver.
AuthenticationOptionsUsersloggingintoGreenplumCommandCenterareauthenticatedwiththeGreenplumDatabasehost-basedauthenticationsystem.Userscanentercredentialsasausernameandpasswordor,ifKerberosauthenticationisconfigured,byauthenticatingwithKerberosontheirworkstationbeforebrowsingtotheCommandCenterwebserver.
Note:GreenplumCommandCenterdoesnotacceptloginsfromthegpadminuser,orfromusersconfiguredwithtrustauthenticationinthe pg_hba.conffile.
DatabaseusersmustfirstbeaddedtotheGreenplumDatabasebyusingcommandssuchas CREATEROLE or CREATEUSER .The LOGIN privilegeisrequired.Thisexamplecreatesaloginuserwithanencryptedpassword:
CREATEROLEcc_userWITHLOGINENCRYPTEDPASSWORD'changeme';
The pg_hba.conf configurationfiledetermineshowauthenticationwillproceed.Thisfilecontainsalistofentriesthatarecomparedtoattributesoftheuser’sconnectionrequest,includingthetypeofconnection,networklocationoftheoriginatinghost,databasename,andloginusername.Whenamatchisfound,theauthenticationmethodspecifiedintheentryisapplied.
The pg_hba.conf filecanbeviewedbyOperatorsandeditedbyAdminsintheCommandCenterconsoleontheAdmin>Authenticationpage.
The md5 and password authenticationmethodsauthenticatetheusernameandpasswordwiththeGreenplumDatabase pg_roles systemtable.Themd5 methodrequiresthepasswordtobeMD5-encodedwhensentoverthenetwork,soitispreferredoverthe password method,whichsendsthepasswordincleartext.
The ldap authenticationmethodauthenticatestheusernameandpasswordwithanLDAPserver.TheLDAPserverandparametersarespecifiedintheoptionsfieldofthe pg_hba.conf entry.SeethePostgreSQLLDAPauthentication documentationfortheformatoftheLDAPoptions.
The gss authenticationmethodisusedforKerberosauthentication.TouseKerberoswithCommandCenter,KerberosauthenticationmustbeenabledfortheGreenplumDatabasesystemandCommandCentermustalsobeconfigured.UsersauthenticatewiththeKerberosKDContheirworkstations(using kinit ,forexample)beforeconnectingtotheCommandCenterwebserver.TherolenameinCommandCenteristheuser’sKerberosprincipalname.
FordetailsaboutsettingupKerberosauthentication,seeEnablingKerberosAuthenticationwithGreenplumCommandCenter.
SeethePostgreSQLAuthenticationmethods documentationforadditionaldetailsoftheauthenticationoptions.
Authorization
Note:ThefunctionalitydescribedinthissectionhasnotbeenfullyimplementedinGreenplumCommandCenter4.0.0.OnlyAdminandSelfOnly
©CopyrightPivotalSoftwareInc,2013-2018 41 4.4.0
CommandCentermanagespermissionlevelsusingGreenplumDatabaserolesandgroups.TheBasic,OperatorBasic,andOperatorpermissionlevelscorrespondtothe gpcc_basic , gpcc_operator_basic ,and gpcc_operator grouprolesinthedatabase.TheAdminpermissionlevelisconferredtorolesthathavethe SUPERUSER privilege.Auserwhohasnotbeenaddedtoanyofthegroupsanddoesnothave SUPERUSER privilegehasthemostrestrictivepermissionlevel,SelfOnly.
GreenplumDatabasesuperuserscanmanagepermissionlevelsontheCommandCenterAdmin>Permissionspage.Superuserscanalsodirectlyassignusersrolesinthedatabasebyusingthe ALTERUSER , ALTERGROUP ,andrelatedcommandstoaddorremoveusersfromgroupsandaddorremovetheSUPERUSER privilege.Ifaroleisconfiguredformorethanonepermissionlevel,CommandCenterusesthehighestpermissionlevel.
CommandCenterusershavethefollowingcapabilities,accordingtotheirpermissionlevels:
SelfOnlyUserscanviewmetricsandviewandcanceltheirownqueries.
AnyGreenplumDatabaseusersuccessfullyauthenticatedthroughtheGreenplumDatabaseauthenticationsystemcanaccessGreenplumCommandCenterwithSelfOnlypermission.Higherpermissionlevelsarerequiredtoviewandcancelother’squeriesandtoaccesstheSystemandAdminControlCenterscreens.
BasicAllowsuserstoviewmetrics,viewallqueries,andcanceltheirownqueries.
UserswithBasicpermissionaremembersoftheGreenplumDatabase gpcc_basic group.
OperatorBasicAllowsuserstoviewmetrics,viewtheirownandothers’queries,canceltheirownqueries,andviewtheSystemandAdminscreens.
UserswithOperatorRead-onlypermissionaremembersoftheGreenplumDatabase gpcc_operator_basic group.
OperatorAllowsuserstoviewtheirownandothers’queries,canceltheirownandother’squeries,andviewtheSystemandAdminscreens.
UserswithOperatorpermissionaremembersoftheGreenplumDatabase gpcc_operator group.
AdminAllowsusersaccesstoallviewsandcapabilitiesintheCommandCenter.
GreenplumDatabaseuserswiththe SUPERUSER privilegeinGreenplumDatabasehaveSuperuserpermissionsinCommandCenter.
permissionlevelsareavailable.
©CopyrightPivotalSoftwareInc,2013-2018 42 4.4.0
ManagingGreenplumCommandCenterAuthenticationTheSystem>AuthenticationscreenallowsuserswithOperatorBasic,Operator,andAdminpermissiontoviewtheGreenplumDatabasehost-basedauthenticationfile, pg_hba.conf .
UserswithAdminpermissioncanadd,remove,change,andmoveentriesinthefile.TheCommandCenterUIvalidatesentriestoensurecorrectsyntax.Previousversionsofthefilearearchivedsothatyoucanrestoreanearlierversionorauditchanges.
SeeAuthenticationOptionsforanoverviewofuserauthenticationoptionsforGreenplumDatabaseandGreenplumCommandServer.
Seepg_hba.conffile inthePostgreSQLdocumentationforadetaileddescriptionofthecontentsofthe pg_hba.conf file.
ViewingtheHost-BasedAuthenticationFileChooseAdmin>AuthenticationtodisplaythecontentoftheGreenplumDatabase pg_hba.conf file.
The pg_hba.conf filecontainsalistofentriesthatspecifythecharacteristicsofdatabaseconnectionrequestsandauthenticationmethods.WhenGreenplumDatabasereceivesaconnectionrequestfromaclient,itcomparestherequesttoeachentryinthe pg_hba.conf entryinturnuntilamatchisfound.Therequestisauthenticatedusingthespecifiedauthenticationmethodand,ifsuccessful,theconnectionisaccepted.
EditingtheHost-BasedAuthenticationFileCommandCenteruserswiththeAdminpermissioncaneditthe pg_hba.conf file.Notethatanychangesyoumakearelostifyoumovetoanotherscreenbeforeyousavethem.
Tochangeanexistingentry,clickanywhereontheentry.EditthefieldsandclickSavetosaveyourchanges,orCanceltorevertchanges.
Tomoveanentryupordowninthelist,clickonthe symbol,dragthelinetothedesiredlocation,andrelease.
Toaddanewentrytotheendofthefile,clickAddNewEntryatthebottomofthescreen.EditthefieldsandclickSavetosaveyourchanges,orCanceltoabandonthenewentry.
Toaddanewentryafteranexistingentry,highlighttheexistingentryandclick .EditthefieldsandclickSavetosaveyourchanges,orCanceltoabandonthenewentry.
Tocopyanentry,selecttheentryandclick .Acopyoftheselectedentryisaddedbelowtheselectedentryanddisplayedforediting.EditthefieldsandclickSavetosaveyourchanges,orCanceltoabandonthecopy.
Toaddacommenttothefile,addanentrybyclickingAddNewEntry andthenchoose # fromthe Type list.or
©CopyrightPivotalSoftwareInc,2013-2018 43 4.4.0
Totoggleanentrybetweenactiveandinactive,selectthelineandclicktheactive/inactivetogglecontroltotheright.Thisactionaddsorremovesacommentcharacter( # )atthebeginningoftheentry.
Toremoveanentry,highlightthelineandclick .Theentryisdisplayedwithstrikethroughtext.Youcanrestoretheentrybyhighlightingitandclickingundelete.TheentryispermanentlyremovedwhenyouclickSaveconfigandupdateGPDB.
Tofinishediting,clickSaveconfigandupdateGPDB.ThenclickSaveandUpdatetosaveyourchangesorclickCanceltoreturnwithyoureditsintact.
WhenyouselectSaveandUpdate,the pg_hba.conf fileissavedandrefreshedinGreenplumDatabase.Notethatexistingclientconnectionsareunaffected.
LoadingaPreviousVersionoftheHost-BasedAuthenticationFileWhenyousaveanewversionofthe pg_hba.conf file,acopyissavedintheGreenplumDatabase $MASTER_DATA_DIRECTORY/pg_hba_archive directoryaspg_hba.conf-<timestamp> .
Toviewanarchivedversionofthe pg_hba.conf file,clickLoadversions…andclickthetimestampfortheversiontodisplay.
Toreverttoapreviousversionofthefile,loadthepreviousversionandthenclickSaveconfigandupdateGPDB.TheconfigurationisrefreshedinGreenplumDatabaseandsavedasanewversioninthearchivedirectory.
©CopyrightPivotalSoftwareInc,2013-2018 44 4.4.0
ManagingGreenplumCommandCenterPermissionsThePermissionsLevelsforGPCCAccessscreenallowsuserswithOperatorBasic,Operator,orAdminpermissiontoviewCommandCenteruserpermissionsforGreenplumDatabaseusers.UserswithAdminpermissioncansetpermissionsforanyuser.
UserswithOperatorBasic,Operator,andAdminpermissioncanalsoseeiftheGuestAccesstoQueryMonitorfeatureisenabledordisabled,andAdminuserscantogglethisfeatureonandoff.
ViewingUserPermissionsInitially,allGreenplumDatabaseloginusersareincludedinthelistofroleswiththeircurrentpermissionlevels.
Tofilterbyrolename,enterallorpartoftheuser’sdatabaserolenameintheRoleNamefield.Thefilterperformsasimplesubstringsearchanddisplaysuserswithmatchingrolenames.ClicktheRoleNamelabeltoreversethesearchorder.
Tofilterforuserswithaspecificpermissionlevel,choosethepermissionlevelfromthePermissionLevellist.
RoleNameandPermissionLevelfilterscanbeusedtogether.
Toresetthefilters,removealltextfromtheRoleNamefieldandchooseFilterby…fromthePermissionLevellist.
ChangingUserPermissionLevelsUserswithAdminpermissioncanchangepermissionlevels.
1. UsetheRoleNameandPermissionLevelfilterstodisplaytherolesyouwanttochange.
2. Checktheboxnexttoarolenametoselecttheuser,orchecktheboxintheheadingtoselectalldisplayedusers.
3. SelectthenewpermissionslevelforeachuserfromthelistinthePermissionLevelcolumn,orselectanewpermissionlevelforallselectedusers
©CopyrightPivotalSoftwareInc,2013-2018 45 4.4.0
fromtheChangeSelectedto…list.
EnablingorDisablingGuestAccesstoQueryMonitorWhenenabled,theGuestAccesstoQueryMonitorfeatureallowsanyonewithaccesstotheGreenplumCommandCenterwebservertoclickViewQueryMonitorontheCommandCentersign-inscreenandseetheQueryMonitorpagewithoutloggingin.TheseanonymoususerscannotcancelqueriesandcannotaccessanyotherCommandCenterfeatures.
Whenthisfeatureisoff,theViewQueryMonitorlinkdoesnotappearonthesign-inscreenandanonymoususerscannotseetheQueryMonitorpage.
CommandCenteruserswithAdminpermissioncantogglethefeatureonanoffwithamouseclick.UserswithOperatororOperatorBasicpermissionseeamessagereportingifthefeatureisonoroff.
©CopyrightPivotalSoftwareInc,2013-2018 46 4.4.0
SecuringthegpmonDatabaseUserTheGreenplumDatabase gpmon userisasuperuserroleusedtomanagethegpperfmondatabase.The gpperfmon_install utility,whichmustberunbeforeyouinstallGreenplumCommandCenterConsole,createsthe gpmon role.
GreenplumDatabaseusesthe gpmon roletoupdatethegpperfmondatabasewithdatacollectedbyagentsrunningonthesegmenthosts.TheCommandCenterwebserverusesthe gpmon roletoconnecttothegpperfmondatabaseaswellasdatabasesmonitoredbytheCommandCenter.
When gpperfmon_install createsthe gpmon role,itpromptsforapassword,whichitthenaddstothe .pgpass fileinthe gpadmin user’shomedirectory.Theentryinthe .pgpass fileissimilartothefollowing:
*:5432:gpperfmon:gpmon:changeme
SeeThePasswordFile inthePostgreSQLdocumentationfordetailsaboutthe .pgpass file.
The .pgpass fileisrequiredontheGreenplumDatabasemasterhosttostartthegpperfmondatacollectionagents.IfyourunGreenplumCommandCenteronadifferenthost,youcancopythe .pgpass filetothathost,oryoucanruntheCommandCenter gpcc managementutilitywiththe -W optiontorequestpasswordentryeachtimeyoustartorstopCommandCenterorrequeststatus.
Inthe $MASTER_DATA_DIRECTORY/pg_hba.conf authenticationfile, gpperfmon_install createstheseentries:
localgpperfmongpmonmd5hostallgpmon127.0.0.1/28md5hostallgpmon::1/128md5
IfyouauthenticateuserswithKerberos,youcanalsosetupKerberosauthenticationforthe gpmon roleontheGreenplummasterandstandbyhosts.KerberosauthenticationissupportedwithTCPconnectionsonly; local entriesuseLinuxsocketsandauthenticatewiththe .pgpass filepassword,evenifyouhaveenabledKerberosfor host entries.
ChangingthegpmonPasswordTochangethe gpmon password,followthesesteps:
1. LogintoGreenplumDatabaseasasuperuserandchangethe gpmon passwordwiththe ALTERROLE command:
#ALTERROLEgpmonWITHENCRYPTEDPASSWORD'new_password';
2. OntheGreenplummasterhost,updatethepasswordinthe .pgpass fileinthe gpadmin homedirectory( ~/.pgpass ).Replacetheexistingpasswordinthelineorlinesfor gpmon withthenewpassword.
*:5432:gpperfmon:gpmon:new_password
3. Ensurethatthe .pgpass fileisownedby gpadmin andRW-accessibleby gpadmin only.
$chowngpadmin:gpadmin~/.pgpass$chmod600~/.pgpass
4. RestartGreenplumCommandCenterwiththe gpcc utility.
$gpccstop$gpccstart
AuthenticatinggpmonwithKerberosIfyouauthenticateGreenplumDatabaseandCommandCenteruserswithKerberos,youcanalsoauthenticatethe gpmon userwithKerberos.
Besuretoalsoupdatethe .pgpass fileonthestandbymasterhost.
©CopyrightPivotalSoftwareInc,2013-2018 47 4.4.0
ToprepareforinstallingCommandCenterwithKerberosauthentication,followthesesteps:
1. CreatethegpperfmondatabaseusingtheGreenplumDatabase gpperfmon-install managementutility.SeeCreatingthegpperfmonDatabase.
2. OntheKDC,createakeytabfilecontainingtheKerberosprincipalforthe gpmon user,justasyouwouldforanyKerberos-authenticatedclient.InstallthefileontheGreenplummasterandstandbyhosts.
3. Updatetheentriesfor gpmon inthe $MASTER_DATA_DIRECTORY/pg_hba.conf filetousethe gss authenticationmethod.
hostallgpmon0.0.0.0/0gssinclude_realm=0krb_realm=GPDB.EXAMPLE.COM
Notethat local entriesin pg_hba.conf cannotbeauthenticatedwithKerberos.Ifthereisa local entryforthe gpmon user,itwillusethe .pgpass filetoauthenticatewiththedatabase.SeeThepg_hba.conffile inthePostgreSQLdocumentationforcomplete pg_hba.conf filedocumentation.
4. Logintothemasterhostas gpadmin andauthenticatethe gpmon user.
$kinitgpmon
5. InstallGreenplumCommandCentertosetuptheKerberos-enabledCommandCenter.
©CopyrightPivotalSoftwareInc,2013-2018 48 4.4.0
EnablingAuthenticationwithKerberosIfyouhaveenabledKerberosauthenticationforGreenplumDatabase,youcansetupGreenplumCommandCentertoacceptconnectionsfromKerberos-authenticatedusers.
GreenplumDatabaseandCommandCenterincludesupportfortheGenericSecurityServiceApplicationsProgramInterface(GSS-API)standard.Arelatedstandard,SimpleandProtectedGSS-APINegotiationMechanism(SPNEGO),describestheprotocolGSS-APIclientsandserversusetoagreeonthemethodofauthentication.
WithaSPNEGO-compliantwebapplicationsuchasCommandCenter,theclientandserveragreeontheauthenticationmethodontheclient’sinitialHTTPrequest.IfKerberosauthenticationisnotsupportedonbothendsoftheconnectiontheserverfallsbacktobasicauthentication,anddisplaysaloginformrequestingausernameandpassword.IfauserhasauthenticatedontheworkstationwithKerberosandhasavalidticketgrantingticket,thewebbrowserofferstheuser’scredentialtotheCommandCenterwebserver.AKerberos-enabledCommandCenterwebserverisconfiguredtohandletheauthenticateduser’sconnectionrequestinoneofthreemodes,calledstrict,normal,orgpmon-only.
StrictCommandCenterhasaKerberoskeytabfilecontainingtheCommandCenterserviceprincipalandaprincipalforeveryCommandCenteruser.Iftheprincipalintheclient’sconnectionrequestisinthekeytabfile,thewebservergrantstheclientaccessandthewebserverconnectstoGreenplumDatabaseusingtheclient’sprincipalname.Iftheprincipalisnotinthekeytabfile,theconnectionrequestfails.
NormalTheCommandCenterKerberoskeytabfilecontainstheCommandCenterprincipalandmaycontainprincipalsforCommandCenterusers.Iftheprincipalintheclient’sconnectionrequestisinCommandCenter’skeytabfile,itusestheclient’sprincipalfordatabaseconnections.Otherwise,CommandCenterusesthe gpmon userfordatabaseconnections.
gpmon-onlyTheCommandCenterusesthe gpmon databaseroleforallGreenplumDatabaseconnections.NoclientprincipalsareneededintheCommandCenter’skeytabfile.
IfyouhavesetupKerberosauthenticationforGreenplumDatabase,mostoftheconfigurationrequiredtoenableCommandCenterKerberosauthenticationhasbeendone.TheCommandCenterKerberosconfigurationbuildsupontheGreenplumDatabaseKerberossetup.
KerberosauthenticationcanbeenabledbyrespondingtopromptswhenyouinstallCommandCenter,oryoucanusethe gpcc--krbenable
commandto
enableKerberosafterCommandCenterhasbeeninstalled.
BeforeYouBeginKerberosauthenticationmustbeenabledforGreenplumDatabase.SeeUsingKerberosAuthentication forinstructions.Makesurethefollowingprerequisitesaremetbeforeyoucontinue:
The krb5-workstation packageandassociatedlibraries( libkrb5* )mustbeinstalledontheGreenplummasterhostandeachclientworkstation.
ThedateandtimeontheGreenplummasterhostandallclientworkstationsmustbesynchronizedwiththeKDC.
The krb5.conf configurationfilemustbethesameontheKDChost,theGreenplumDatabasemasterhost,andclientworkstations.
TheKDCdatabasemusthaveaserviceprincipalforGreenplumDatabase.ThedefaultservicenameforGreenplumDatabaseispostgres/<master-host>@<realm> .Youcanchooseaservicenameotherthan postgres ,butitmustmatchthevalueofthe krb_srvnameparameterinthe $MASTER_DATA_DIRECTORY/postgresql.conf file.
AkeytabfilewiththeGreenplumDatabaseprincipalmustbeinstalledontheGreenplummasterhostandidentifiedbythe krb_server_keyfileparameterinthe $MASTER_DATA_DIRECTORY/postgresql.conf file.
EachclientworkstationcanhaveakeytabfilecontainingtheirKerberosprincipal, <username>@<realm> .
AddCommandCenterPrincipalstotheKDCDatabaseBeforeyouconfigureCommandCenterforKerberosauthentication,youmustcreatetherequiredKerberosprincipals.AlloftheprincipalsusedwithCommandCenterarecreatedintheGreenplumDatabaseKerberosrealm.CommandCenteruserscanusethesameKerberosprincipaltoauthenticatewithCommandCenterandGreenplumDatabase.
CommandCenterServicePrincipal
AserviceprincipalisneededfortheCommandCenterwebserver.Thisprincipalhastheformat HTTP/<host>@<realm> .Forexample,ifusersaccess
©CopyrightPivotalSoftwareInc,2013-2018 49 4.4.0
CommandCenterattheURL http://mdw.example.com:28080 ,the <host> partoftheservicekeyis mdw.example.com andthe <realm> partistheGreenplumDatabaseKerberosrealm,forexample GPDB.KRB .
NotethatKerberosauthenticationonlyworksifCommandCenterusersenterthehostinthesameformatspecifiedintheKerberosserviceprincipal.IftheprincipalspecifiestheFQDN,forexample,usingthehost’sIPaddressinthebrowserURLwillnotwork;thewebserverwillfallbacktobasicauthentication.
GreenplumDatabasegpmonUser
CommandCenterusesthe gpmon Greenplumroletoaccessthegpperfmondatabase,whichcontainsdatapresentedintheCommandCenterUI.
Youcanchoosetoauthenticatethe gpmon userwithKerberosorwithbasicauthentication.TouseKerberos,youmustcreateaprincipalforthegpmon user.
IfyouchoosetousebasicauthenticationyoudonotneedaKerberosprincipalforthe gpmon user.The gpmon userwillauthenticatewithGreenplumDatabaseusingthepasswordsavedinthe ~gpadmin/.pgpass fileonthehostrunningCommandCenter.IfyourunCommandCenteronahostotherthantheGreenplumDatabasemasterhost,youmustcopythe ~gpadmin/.pgpass filefromthemasterhosttotheCommandCenterhost.SeeChangingthegpmonPassword forinstructionstomanagethe gpmon password.
CommandCenterUsers
AddKerberosprincipalsforanyCommandCenteruserswhodonotalreadyhaveprincipalsintheKDCforGreenplumDatabase.
AddingKerberosPrincipalsToaddtherequiredprincipals,performthefollowingstepsasrootontheKDCserver.
1. AddaprincipalfortheCommandCenterwebservice.Besuretospecifythe <gpcc-host> inthesameformatthatusersshouldenterthehostintheirbrowsers.
#kadmin.local-q"addprinc-randkeyHTTP/<gpcc-host>@<realm>"
2. Ifyouwantthe gpmon databaseusertouseKerberosauthentication,adda gpmon principal.
#kadmin.local-q"addprincgpmon@<realm>"
3. AddprincipalsforanynewCommandCenterusers.
#kadmin.local-q"addprinccc_user1@<realm>"
RepeatforeachnewCommandCenteruser.
SetUpKeytabFilesAfteryouhavecreatedalloftheKerberosprincipalsneeded,youcreateanddistributekeytabfiles.KeytabfilescontainKerberosprincipalsandencryptedkeysbasedontheprincipals’Kerberospasswords.KeytabfilesareneededfortheGreenplumDatabasemasterandstandbyhostsandtheCommandCenterhost.
YoucanalsocreateakeytabfileforeachGreenplumDatabaseorCommandCenterusercontainingjusttheuser’sprincipal.Thiskeytabfileisinstalledontheuser’sworkstationtoenabletheusertoauthenticatetoKerberos.Notethatallkeytabfilesmustcontainthemostrecentversionsoftheprincipals’keys.
CommandCenterRunningontheGreenplumMasterHostIftheGreenplumCommandCenterwebserverisrunningontheGreenplumDatabasemasterhost,CommandCentercansharetheGreenplumDatabasekeytabfile.Youneedtocreateakeytabfilethatcontainsthefollowingprincipals:
Servicekeyforthe postgres processontheGreenplumDatabasemasterhost,forexample postgres/[email protected] .
ServicekeycreatedforCommandCenterintheprevioussection,forexample HTTP/[email protected].
AprincipalforeveryKerberos-authenticatedGreenplumDatabaseorCommandCenteruser.
©CopyrightPivotalSoftwareInc,2013-2018 50 4.4.0
AllservicekeysandprincipalsshouldbeintheGreenplumDatabaserealm.
TocreateakeytabfileforGreenplumDatabaseandCommandCenter,performthefollowingstepsasrootontheKDCserver.
1. CreateakeytabfilecontainingtheGreeplumDatabaseservicekey,thecommandcenterservicekey,andalldatabaseandCommandCenterusers.
kadmin.local-q"ktadd-kgpdb-kerberos.keytabpostgres/[email protected]/[email protected]"
Youcanenteroneormoreprincipalswitheach ktadd command.Youcanspecifyawildcardusingthe -glob option.Forexamplethiscommandaddsallprincipalsinthe GPDB.KRB realm,includingserviceprincipalsandadminusers.
kadmin.local-q"ktadd-kgpdb-kerberos.keytab-glob*@GPDB.KRB"
2. CopythekeytabyoucreatedtotheGreenplumDatabasemasterhost,replacingtheoldkeytabfile.Thelocationofthefileisgivenbythekrb_server_keyfile parameterinthe $MASTER_DATA_FILE/postgresql.conf file.Setthepermissionsonthefilesothatitcanbereadonlybythe gpadmin
user.
3. UpdateanyentriesrequiredfornewGreenplumDatabaseprincipalsinthe pg_hba.conf fileand pg_ident.conf files.SeeUpdatetheGreenplumDatabase pg_hba.conf Filefordetails.
CommandCenterRunningontheStandbyMasterIftheCommandCenterwebserverisonadifferenthostthantheGreenplumDatabasemaster,youneedseparatekeytabfilesforGreenplumDatabaseandCommandCenter.ThekeytabfileforGreenplumDatabasemaynotrequireanyupdates,butyouwillneedtocreateakeytabfileforCommandCenter.
TheGreenplumDatabasekeytabfilemustcontaintheGreenplumDatabaseservicekeyandallprincipalsforuserswithdatabaseaccess.
TheCommandCenterkeytabfilecontainstheCommandCenterservicekeyandprincipalsforusersthathaveCommandCenteraccess.UserswithCommandCenteraccessmustalsohaveGreenplumDatabaseaccess,souserprincipalsintheCommandCenterkeytabfilemustalsobeintheGreenplumDatabasekeytabfile.
UpdatetheGreenplumDatabasekeytabifyoucreatednewdatabaserolesandprincipalsforCommandCenter.Forexample,ifyouwanttouseKerberosauthenticationforthe gpmon user,youmustcreateaprincipalandaddittoboththeGreenplumDatabaseandCommandCenterkeytabfiles.
TocreatethekeytabfileforCommandCenter,performthefollowingstepsasrootontheKDChost.
```
1. CreateakeytabfileandaddtheCommandCenterservicekey.
kadmin.local-q"ktadd-kgpcc-kerberos.keytabHTTP/[email protected]"
2. Ifyouwanttoauthenticatethe gpmon userwithKerberos,addthe gpmon principal.
kadmin.local-q"[email protected]"
3. AddprincipalsforallCommandCenterusers:
kadmin.local-q"[email protected][email protected]"
Youcanenteroneormoreprincipalswitheach ktadd command.
4. Enter quit toexit kadmin.local .
5. CopythekeytabyoucreatedtothethehostrunningCommandCenter,forexample:
$scpgpcc-kerberos.keytabgpadmin@<host-name>:/home/gpadmin
6. Updateanyentriesrequiredfornewprincipalsinthe pg_hba.conf fileand pg_ident.conf filesontheGreenplummaster.SeeUpdatetheGreenplumDatabasepg_hba.confFile.
©CopyrightPivotalSoftwareInc,2013-2018 51 4.4.0
UpdatetheGreenplumDatabasepg_hba.confFileTheGreenplumDatabase $MASTER_DATA_DIRECTORY/pg_hba.conf configurationfiledetermineswhichauthenticationmethodstousetoallowdatabaseaccess.
IfyoucreatednewCommandCenterusers,youmayneedtoaddanentrytoallowaccessviaCommandCenter.Theentryforanindividualuserhasthisformat:
hostdatabase<user-name><gpccCIDR>gss[options]
Authenticationforthe gpmon userneedstobesetupinthe pg_hba.conf fileinoneofthefollowingways.
Basicauthentication
The /home/gpadmin/.pgpass filecontainsthepasswordfor gpmon touse.SeeChangingthegpmonPassword fordetails.Anentryinthe pg_hba.conffilespecifiesthemd5authenticationmethodfor gpmon :
localallgpmonmd5
Trustauthentication
OntheGreenplumDatabasemasterhostonly,the gpmon usercanaccessdatabaseswithoutauthentication:
localallgpmontrust
The /home/gpadmin/.pgpass fileisnotneeded.
Kerberosauthentication
AKerberosprincipalhasbeencreatedforthe gpmon userandaddedtotheGreenplumDatabaseandCommandCenterkeytabfiles.
hostallgpmon<gpccCIDR>gss[options]
Removeanyexistingrejectrulesfor gpmon :
hostallgpmon<auth-method>reject
SeeUsingKerberosAuthentication formoreinformationaboutthe pg_hba.conf file.
EnableKerberosforCommandCenterSetupCommandCentertousetheCommandCenterkeytabfileyoucreated.
IfyouareaddingKerberosauthenticationtoanexistingCommandCenter,usethe gpcc command.Forexample:
$gpcc--krbenable
EntertheCommandCenterhostnameandpathtothekeytabfileattheprompts.SeethegpccReferenceformoreinformation.
AuthenticatingWithKerberosontheClientWorkstationTouseKerberosCommandCenterauthentication,theusermusthaveauthenticatedwithKerberosusingthe kinit command-linetool.
TheuserthenaccessestheCommandCenterwebserverwithaURLcontainingthehostnameintheformatspecifiedintheCommandCenterserviceprincipalandtheportnumber,forexample http://mdw.example.com:28080 .
Theuser’swebbrowsermustbeconfiguredtousetheSPNEGOprotocolsothatitofferstheuser’sKerberosprincipaltothewebbrowser.Themethodforconfiguringwebbrowsersvarieswithdifferentbrowsersandoperatingsystems.SearchonlinetofindinstructionstoenableSPNEGOwithyourbrowserandOS.
©CopyrightPivotalSoftwareInc,2013-2018 52 4.4.0
©CopyrightPivotalSoftwareInc,2013-2018 53 4.4.0
MonitoringtheGreenplumDatabaseSystemDashboard
ClusterState
SegmentStatus
ClusterMetrics
HostMetrics
StorageStatus
©CopyrightPivotalSoftwareInc,2013-2018 54 4.4.0
DashboardTheDashboarddisplayswhenyoufirstsignintoPivotalGreenplumCommandCenter.TheDashboardprovidesaquickviewofthecurrentsystemstatus,SegmentHealth,Queries,CPU,Memory,andDiskusage.
Clickingonapanelprovidesmoredetailedinformationaboutthemetric.TheAlertspanelshowsthemostrecentmessagesfromtheGreenplumDatabaselogfile.SomeinformationisavailableonlytoCommandCenteruserswithAdminorOperatorpermissionlevel.
SystemInformationThefollowingsysteminformationisdisplayedatthetopofthepage.
UptimeTheelapsedtimesincetheGreenplumDatabasesystemwaslaststarted.
GPDBVersionTheversionoftheGreenplumDatabasesoftwarethemonitoredclusterisrunning.
GPCCVersionTheversionoftheGreenplumCommandCentersoftware.
ConnectionsThenumberofactiveGreenplumDatabasesessions(clientconnections).
ServerThedisplaynameforthisGreenplumCommandCenter.
LastSyncDateandtimethedatawaslastsynchronized.TheCommandCenteruserinterfaceupdatesviewswithlivedataevery15seconds.
SegmentHealthTheSegmentHealthsectionoftheDashboardprovidesaquickoverviewofthestatusofthedatabasesystemandsegmentsthisCommandCentermonitors.
DatabaseStateDatabaseStateisthecurrentstateoftheGreenplumDatabasesystem.Thestatecanbeoneofthefollowing:
©CopyrightPivotalSoftwareInc,2013-2018 55 4.4.0
Normal:Thedatabaseisfunctioningwithnomajorerrorsorperformanceissues.
Segment(s)Down:Thedatabaseisinchange-trackingmodeorresyncmode.Overallperformanceandsystemreliabilityisgreatlyreduced.SeethePivotalGreenplumDatabaseSystemAdministratorGuideforinformationaboutresolvingthiscondition.
DatabaseUnreachable:TheGreenplumPerformanceMonitoragentcannotconnecttothedatabase.Thedatabaseislikelydown.SeethePivotalGreenplumDatabaseSystemAdministratorGuidefortroubleshootinginformation.
Unbalanced:Somesegmentsarenotrunningintheirpreferredroles.Thatis,primariesarerunningasmirrorsandmirrorsarerunningasprimaries,resultinginunbalancedprocessing.
Resyncing:Thedatabaseisperformingarecoveryorrebalanceoperation.
SegmentStatusThebargraphintheSegmentStatussectionshowstheupordownstatusofalldatabasesegmentsinyourPivotalGreenplumDatabasesystem.Acolorindicatorandassociatednumberindicatethenumberofdatabasesegmentsthatarecurrentlyinthatparticularstate.Segmentscanhavethefollowingstates:
Up(Green)
Down(Red)
ClickingtheSegmentStatuspaneldisplaystheSegmentStatusCommandCenterpage.
DiskUsageSummaryThischartdisplaystotaldiskusageanddiskavailablefortheGreenplummasterhostandsegmenthostsatthelastsynchronization.Hoveroverthecharttoseetheamountofdiskused,free,andtotal.
QueriesThisgraphdisplaysasummaryviewofactiveandqueuedqueriesforthelast60minutes.ClickonthecoloreddotnexttotheRunningorQueuedlabeltotogglethelineonoroff.Atleastonelinemustbevisibleatalltimes.Hoveroverthegraphtodisplaythenumberofqueriesforeachvisiblelineatthatpointintime.
CPUThisgraphdisplaysaverageCPUusageacrosstheentirecluster,forthelast60minutes.Thegraphdisplaysseparatelinesforsystemprocessesanduserprocesses.TheuserCPUusageincludestheGreenplumdatabasemaster,standby,andsegmentprocesses.ClickonthecoloreddotnexttotheSystemorUserlabeltotogglethatlineonoroff.Atleastonelinemustbevisibleatalltimes.
HoveringthecursoroveralineinthegraphdisplaysasmallwindowwiththepercentageofCPUusedatthatpointintimeforthevisiblelinesandthetotalifboththesystemanduserlinesarevisible.
MemoryThisgraphdisplaystheaveragepercentofmemoryusedacrosstheentireclusteroverthelast60minutes.Hoveroverthelinetodisplaythepercentofmemoryusedatthatpointintime.
AlertsAdminandOperatorpermissionlevelsonly
TheAlertspaneldisplaysrecentmessagesfromtheGreenplumDatabase pg_log logfile.Thepanelisupdatedateachsynchronization.Filterthemessagesbyseveritylevelusingthecontrolsatthetoprightofthepanel.
©CopyrightPivotalSoftwareInc,2013-2018 56 4.4.0
©CopyrightPivotalSoftwareInc,2013-2018 57 4.4.0
GreenplumDatabaseClusterStateTheGreenplumCommandCenterDashboardprovidesaquickviewofthecurrentsystemstatus,SegmentHealth,Queries,CPU,Memory,andDiskusage.Clickingonapanelprovidesmoredetailedinformationaboutthatmetric.TheAlertspanelshowsthemostrecentmessagesfromtheGreenplumDatabaselogfile.SomeinformationisavailableonlytoCommandCenteruserswithAdminorOperatorpermissionlevel.
SystemInformationThefollowingsysteminformationisdisplayedatthetopofthepage.
UptimeTheelapsedtimesincetheGreenplumDatabasesystemwaslaststarted.
GPDBVersionTheversionoftheGreenplumDatabasesoftwarethemonitoredclusterisrunning.
GPCCVersionTheversionoftheGreenplumCommandCentersoftware.
ConnectionsThenumberofactiveGreenplumDatabasesessions(clientconnections).
ServerThedisplaynameforthisGreenplumCommandCenter.
LastSyncDateandtimethedatawaslastsynchronized.TheCommandCenteruserinterfaceupdatesviewswithlivedataevery15seconds.
SegmentHealthTheSegmentHealthsectionoftheDashboardprovidesaquickoverviewofthestatusofthedatabasesystemandsegmentsthisCommandCentermonitors.
DatabaseStateDatabaseStateisthecurrentstateoftheGreenplumDatabasesystem.Thestatecanbeoneofthefollowing:
Normal:Thedatabaseisfunctioningwithnomajorerrorsorperformanceissues.
©CopyrightPivotalSoftwareInc,2013-2018 58 4.4.0
Segment(s)Down:Thedatabaseisinchange-trackingmodeorresyncmode.Overallperformanceandsystemreliabilityisgreatlyreduced.SeethePivotalGreenplumDatabaseSystemAdministratorGuideforinformationaboutresolvingthiscondition.
DatabaseUnreachable:TheGreenplumPerformanceMonitoragentcannotconnecttothedatabase.Thedatabaseislikelydown.SeethePivotalGreenplumDatabaseSystemAdministratorGuidefortroubleshootinginformation.
Unbalanced:Somesegmentsarenotrunningintheirpreferredroles.Thatis,primariesarerunningasmirrorsandmirrorsarerunningasprimaries,resultinginunbalancedprocessing.
Resyncing:Thedatabaseisperformingarecoveryorrebalanceoperation.
SegmentStatusThebargraphintheSegmentStatussectionshowstheupordownstatusofalldatabasesegmentsinyourPivotalGreenplumDatabasesystem.Acolorindicatorandassociatednumberindicatethenumberofdatabasesegmentsthatarecurrentlyinthatparticularstate.Segmentscanhavethefollowingstates:
Up(Green)
Down(Red)
ClickingtheSegmentStatuspaneldisplaystheSegmentStatusCommandCenterpage.
DiskUsageSummaryThischartdisplaystotaldiskusageanddiskavailablefortheGreenplummasterhostandsegmenthostsatthelastsynchronization.Hoveroverthecharttoseetheamountofdiskused,free,andtotal.
QueriesThisgraphdisplaysasummaryviewofactiveandqueuedqueriesforthelast60minutes.ClickonthecoloreddotnexttotheRunningorQueuedlabeltotogglethelineonoroff.Atleastonelinemustbevisibleatalltimes.Hoveroverthegraphtodisplaythenumberofqueriesforeachvisiblelineatthatpointintime.
CPUThisgraphdisplaysaverageCPUusageacrosstheentirecluster,forthelast60minutes.Thegraphdisplaysseparatelinesforsystemprocessesanduserprocesses.TheuserCPUusageincludestheGreenplumdatabasemaster,standby,andsegmentprocesses.ClickonthecoloreddotnexttotheSystemorUserlabeltotogglethatlineonoroff.Atleastonelinemustbevisibleatalltimes.
HoveringthecursoroveralineinthegraphdisplaysasmallwindowwiththepercentageofCPUusedatthatpointintimeforthevisiblelinesandthetotalifboththesystemanduserlinesarevisible.
MemoryThisgraphdisplaystheaveragepercentofmemoryusedacrosstheentireclusteroverthelast60minutes.Hoveroverthelinetodisplaythepercentofmemoryusedatthatpointintime.
AlertsAdminandOperatorpermissionlevelsonly
TheAlertspaneldisplaysrecentmessagesfromtheGreenplumDatabase pg_log logfile.Thepanelisupdatedateachsynchronization.Filterthemessagesbyseveritylevelusingthecontrolsatthetoprightofthepanel.
©CopyrightPivotalSoftwareInc,2013-2018 59 4.4.0
SegmentStatusTheSegmentStatuspageprovidesahealthoverviewfortheGreenplumDatabasesegmentsanddetailsforeachprimaryandmirrorsegment.
SegmentSummaryGreenplumDatabaseismostefficientwhenallsegmentsareoperatingintheirpreferredroles.TheSegmentSummarypaneltellsyoutheoverallsegmentstatusandifanymirrorsareactingasprimaries.
TheSegmentSummarypanelprovidesthefollowinginformation:
DatabaseStateThedatabasestatecanbeoneofthefollowing:
Normal:Thedatabaseisfunctioningwithnomajorerrorsorperformanceissues.
Segment(s)Down:Thedatabaseisinchange-trackingmodeorresyncmode.Overallperformanceandsystemreliabilityisgreatlyreduced.SeethePivotalGreenplumDatabaseSystemAdministratorGuideforinformationaboutresolvingthiscondition.
DatabaseUnreachable:TheGreenplumPerformanceMonitoragentcannotconnecttothedatabase.Thedatabaseislikelydown.SeethePivotalGreenplumDatabaseSystemAdministratorGuidefortroubleshootinginformation.
Unbalanced:Somesegmentsarenotrunningintheirpreferredroles.Thatis,primariesarerunningasmirrorsandmirrorsarerunningasprimaries,resultinginunbalancedprocessing.
Resyncing:Thedatabaseisperformingarecovertyorrebalanceoperation.
MirrorsActingasPrimaryThenumberofmirrorsegmentsactingasprimarysegments.
RecommendedActionsSuggestsactionstoperformtorestoretheclustertobalance.Theseinclude:
RecoverandRebalance
Rebalance
Theseactionsareexecutedfromthecommandlineusingthe gprecoverseg Greenplummanagementutility.See gprecoverseg inthePivotalGreenplumDatabaseUtilityReferenceformoreinformation.
TotalSegments
©CopyrightPivotalSoftwareInc,2013-2018 60 4.4.0
ThetotalnumberofprimaryandmirrorsegmentsintheGreenplumcluster.
SegmentHostsThetotalnumberofsegmenthostsintheGreenplumcluster.
SegmentHealthTheSegmentHealthpanelcontainschartsforGreenplumDatabasesegments’status,replicationmode,andpreferredroles.
StatusNumbersofsegmentsthataredownandup.
ReplicationModeAchartthatshowsthenumberofsegmentsineachofthepossiblereplicationmodes.
NotSyncing:Theprimarysegmentandmirrorsegmentareactiveandallchangestotheprimarysegmenthavebeencopiedtothemirrorusingafileblockreplicationprocess.
ChangeTracking:Ifaprimarysegmentisunabletocopychangestoitsmirrorsegmentusingthefilereplicationprocess,itlogstheunsentchangeslocallysotheycanbereplicatedwhenthemirroragainbecomesavailable.Thiscanhappenifamirrorsegmentgoesdownorifaprimarysegmentgoesdownanditsmirrorsegmentautomaticallyassumestheprimaryrole.
Resyncing:Whenadownsegmentisbroughtbackup,administratorsinitiatearecoveryprocesstoreturnittooperation.Therecoveryprocesssynchronizesthesegmentwiththeactiveprimaryandcopiesthechangesmissedwhilethesegmentwasdown.
Synced:Onceallmirrorsandtheirprimariesaresynchronized,thesystemstatebecomessynchronized.
PreferredRolesTheredportionofthePreferredRolechartshowsthenumbersofsegmentsthatnotoperatingintheirpreferredprimaryormirrorroles.Ifthechartisnotsolidgreen,theperformanceoftheGreenplumclusterisnotoptimal.
Primaryandmirrorsegmentsaredistributedevenlyamongthesegmenthoststoensurethateachhostperformsanequivalentshareoftheworkandprimarysegmentsandtheirmirrorsegmentsresideondifferentsegmenthosts.Whenaprimarysegmentgoesdown,itsmirroronanotherhostintheclusterautomaticallyassumestheprimaryrole,increasingthenumberofprimarysegmentsrunningonthathost.Thisunevendistributionoftheworkloadwillaffectqueryperformanceuntilthedownsegmentisrestoredandthesegmentsarereturnedtotheiroriginal,preferred,roles.
SegmentTableThetableatthebottomoftheSegmentStatuspagecontainsadetailedrowforeveryprimaryandmirrorsegmentintheGreenplumCluster.Thetablehasthefollowingcolumnsforeachsegment:
HostnameThenameofthesegmenthostwherethesegmentisrunning.
AddressThenetworkinterfaceonthesegmenthostforthesegment.
PortTheportnumberassignedtothesegment.
DBIDTheuniqueidentifierforthesegmentinstance.
ContentIDThecontentidentifierforthesegment,from0tothenumberofsegmentsminus1.AprimarysegmentanditsmirrorhavethesameContentID.Themasterandstandbymaster,whichhaveContentID−1,areexcludedfromthetable.
Status“UP”ifthesegmentisrunning,“DOWN”ifthesegmenthasfailedorisunreachable.
RoleThesegment’scurrentrole,either“primary”or“mirror”.
©CopyrightPivotalSoftwareInc,2013-2018 61 4.4.0
PreferredRoleThesegment’sintendedrole,either“primary”or“mirror”.
ReplicationModeThereplicationstatusforthesegment.SeeSegmentHealthforpossiblevalues.
LastEvent|[Total]Thedateandtimeoflastsegmenthealth-relatedactivity.Clicktodisplayalistofrecentevents.
©CopyrightPivotalSoftwareInc,2013-2018 62 4.4.0
ClusterMetricsTheClusterMetricspageshowsconsolidatedstatisticsforallsegmenthostsintheGreenplumcluster.Masterandstandbymasterhostsareexcludedfromthemetrics.
Thechartsdisplaymetricsforthelasttimeperiodsetbythecontrolinthetoprightcornerofthescreen.
UsetheShow/hideChartscontroltochoosewhichmetricstodisplay.
Hoveroveranyofthechartstoseevaluesforthemetricsatapointintimeinpop-upboxes.Thechartsaresynchronizedsothathoveringoveranychartshowsthesamepointintimeinallcharts.
Thecurrentvalueofametricisshownintheupperrightcornerofitschart.
Onchartswithmultiplemetrics,togglethedisplayforalineonoroffbyclickingtheline’slabelinthelegendatthetoprightofthechart.Atleastonelinemustbedisplayed.Alllinesareredisplayedatthenextquantuminterval.
Thepagehaschartsforthefollowingmetrics:
QueriesThenumberofqueriesrunningandthenumberofqueriesqueuedtorun.
CPUThepercentageCPUusedbysystemprocessesandthepercentageCPUusedbyuserprocesses.
MemoryPercentageofmemoryinuse.
Memoryiscalculatedasfollows:
Total=MemTotalFree=MemFree+Buffers+CachedUsed=MemTotal-Free
DiskI/ODiskreadandwriteratesinmegabytespersecond.
NetworkNetworkI/Oreadandwriteratesinmegabytespersecond.NetworkmetricsincludetrafficoverallNICs(networkinterfacecards),includinginternalinterconnectandadministrativetraffic.
LoadSystemloadaveragefor1-minute,5-minute,and15-minuteperiods.
SwapPercentageofswapspaceused.
©CopyrightPivotalSoftwareInc,2013-2018 63 4.4.0
HostMetricsTheHostMetricspagedisplaysatableofthehostsintheclusterwithstatisticscollectedatthemostrecentquantuminterval.
Atthetop,LastSyncdisplaysthetimethestatisticswerelastupdated.
Clickacolumnheadertosortthetablebythatcolumn.Clickagaintotogglebetweenascendinganddescendingsort.Masterandstandbyhostsarenotincludedinthesortandarealwaysdisplayedfollowingthesortedlistofsegmenthosts.
Foreachserver,thefollowingcolumnsaredisplayed:
HostnameThehostnamenameoftheserver.
CPUTotal/Sys/User(%)ThetotalpercentageofCPUinuseisdisplayednexttoagraphillustratingtheCPUusedforsystemanduserprocesses.HoveroverthetablecelltoshowthepercentagesusedforsystemanduserprocessesandthepercentageCPUidle.
MemoryInUse(%)Thepercentageofhostmemoryinuseisdisplayednexttoagraphillustratingthememoryinuseandavailable.Hoveroverthetablecelltoseememoryusedandavailableingigabytes.
Memoryiscalculatedasfollows:
Total=MemTotalFree=MemFree+Buffers+CachedUsed=Total-Free
DiskR(MB/s)|SkewDiskreadrateinmegabytespersecondisdisplayednexttoagraphofcalculateddiskreadskew.HoveroverthetablecelltoseeaLow/Medium/Highratingfordiskskew.
DiskW(MB/s)|SkewDiskwriterateinmegabytespersecondisdisplayednexttoagraphofcalculateddiskwriteskew.HoveroverthetablecelltoseeaLow/Medium/Highratingfordiskwriteskew.
NetR(MB/s)|SkewNetworkreadrateinmegabytespersecondisdisplayednexttoagraphofcalculatednetworkreadskew.HoveroverthetablecelltoseeaLow/Medium/Highratingfornetworkreadskew.
NetW(MB/s)|SkewNetworkwriterateinmegabytespersecondisdisplayednexttoagraphofcalculatednetworkwriteskew.HoveroverthetablecelltoseeaLow/Medium/Highratingfornetworkwriteskew.
AboutSkewCalculationsDiskandNetworkskewratingsarecalculatedaseachserver’sstandarddeviationfromthemeancalculatedfromallsegmenthosts.
©CopyrightPivotalSoftwareInc,2013-2018 64 4.4.0
LowValueiswithin1standarddeviationfromthemean.(Note:ifthevarianceofthesetislessthan3,skewisconsideredlowregardlessofdeviationfrommean.)
ModerateValueisbetween1and2standarddeviationsfromthemean.
VeryHighValueisgreaterthan3standarddeviationsfromthemean.
©CopyrightPivotalSoftwareInc,2013-2018 65 4.4.0
StorageStatusTheStorageStatuspageshowscurrenthistoricaldiskusageforGreenplummasterandsegmenthosts.
DiskUsageSummaryYoucanseecurrentdiskspaceinuse,spacefree,andtotalspaceintheDiskUsageSummarypanel.Diskspacemetricsforthesegmenthosts(GPSegments)andthemaster(GPMaster)areshowninseparatebarcharts.
TheGPSegmentsbarchartshowscombineddiskspaceforallsegments.
TheGPMastersbarchartshowscombineddiskspaceformasterandstandbymaster.
Hoverovereitherofthechartstoseethespaceused,free,andtotalingigabytesandasapercentageofthetotal.
GPSegmentsUsageHistoryTheGPSegmentsUsageHistorypanelpresentsachartofpercentageofdiskspaceinuseforthetimeperiodsetbythecontrolinthepanelheader.
Hoveroverthecharttoseethepercentagediskinuseonanygivenpoint.
StorageStatusTableTheStorageStatustableprovidescurrentdiskspaceusagemetricsforeachhostandbydatadirectorywithinhosts.
©CopyrightPivotalSoftwareInc,2013-2018 66 4.4.0
MonitoringandManagingGreenplumDatabaseQueriesQueryMonitor
QueryDetails
QueryHistory
©CopyrightPivotalSoftwareInc,2013-2018 67 4.4.0
QueryMonitorTheQueryMonitorviewallowsyoutoviewinformationforallGreenplumDatabaseserverqueries,includingdetailsaboutqueriesrunning,queuedtorun,andblockedbyotherqueries.UserswithAdminorOperatorpermissioncanseeandcancelallusers’queries.
IfaCommandCenteradministratorhasenabledQueryMonitorGuestAccess,anyoneabletoaccesstheCommandCenterwebservercanviewthesystemstatusandquerylistonthispagewithoutsigningintoCommandCenter.Anonymoususers,however,cannotcancelqueriesoraccessanyotherCommandCenterfeatures.
Withtheinformationavailableinthisview,GreenplumDatabaseadministratorscaneasily:
Understandhowthesystemisbeingused—bothinreal-timeandtrendingovertime.
Identifyanddiagnoseproblemquerieswhiletheyarerunning,detectskew,findrunawayqueries,andsoon.
Reviewandbalancethequeryloadonthesystembybetteroptimizingandschedulingthequeryload.
Cancelqueriesthatdisruptsystemperformance.
QueryMetricsTheQueryMonitortabledisplaysthefollowingcolumnsforqueries.
QueryIDAnidentificationstringforthequery.Ifthecolumnisblank,noqueryIDhasbeenassignedyet.IntheConsole,thislookslike“1295397846-56415-2”.CommandCentergeneratesthisIDbycombiningthequeryrecord’s tmid , ssid ,and ccnt fields.
tmid isatimeidentifierforthequery.
ssid isthesessionid.
ccnt isthenumberofthecommandwithinthesession.
StatusThestatusofthequery.Thiscanbeoneofthefollowing:
©CopyrightPivotalSoftwareInc,2013-2018 68 4.4.0
Queued:thequeryhasnotyetstartedtoexecute
Running:executionhasstarted,butisnotyetcomplete
Blocked:thequeryiswaitingforoneormoreotherqueriestoreleaselocks
Done:completedsuccessfully
Cancelling:cancelrequestsent,cancelpending
Cancelled:terminated,nolongerrunning
IdleTransaction:thetransactionisopen,butidle,forexample,waitingwhileauserinaninteractivesessionentersastatement
UserTheGreenplumDatabaserolethatsubmittedthequery.
DatabaseThenameofthedatabasethatwasqueried.
WorkloadTheresourcegrouporresourcequeuethatismanagingthequery.
SubmittedThetimethequerywassubmittedtothequeryplanner.
QueuedTimeTheamountoftimethequeryhasbeen(orwas)inqueueawaitingexecution.
RunTimeTheamountoftimesincequeryexecutionbegan.
SpillFilesThetotalsizeofspillfilescreatedforthequery.GreenplumDatabasecreatesspillfileswhenthereisinsufficientmemorytotoexecutethequeryinmemory.SeeManagingSpillFilesGeneratedbyQueries forinformationaboutspillfiles.
BlockedbyShowsthenumberoflocksblockingthequery.Hoveroverthecolumntodisplaydetailsofthelocks.Thetipshowsthetypeofeachlock,theIDofthetransactionorquerythatholdsthelock,theGreenplumDatabaseroleholdingthelock,andtheamountoftimethequeryhasbeenblocked.
UsingtheQueryMonitorControlsClickacolumnheadingtosorttherowsonthatcolumninascendingordescendingorder.
Clickthecheckboxattheleftofarowtochooseaquerytocancelorexport.Clickthecheckboxintheheadingrowtochooseallqueries.
ClickCancelQuerytocancelselectedqueries.
ClickExporttodownloadacomma-separatedvalues(CSV)textfilecontainingrowsfortheselectedqueries.Whennoqueriesareselected,allrowsareexported.Thedefaultfilenameis spreadsheet.csv .
ClickanyqueryIDtoseetheQueryDetails,includingmetrics,thetextofthequery,andthequeryplan.
©CopyrightPivotalSoftwareInc,2013-2018 69 4.4.0
QueryDetailsTheQueryDetailsviewdisplaysquerymetrics,thetextofthequery,andthequeryplanandprogressforasinglequeryselectedfromtheQueryMonitorview.
QueryExecutionMetricsTheQueryID,executionstatus,andruntimeofthequeryaredisplayedatthetop.
Thefollowingmetricsaredisplayedforthequery.
UserTheGreenplumDatabaserolethatsubmittedthequery.
DatabaseThenameofthedatabasethatwasqueried.
WorkloadThenameoftheresourcegrouporresourcequeuethatismanagingthequery.
SubmittedThetimethequerywassubmittedtothequeryplanner.
QueuedTimeTheamountoftimethequeryhasbeen(orwas)inqueueawaitingexecution.
RunTimeTheamountoftimesincequeryexecutionbegan.
CPUMasterCurrentCPUpercentontheGreenplumDatabasemasterhostforthisquery.
©CopyrightPivotalSoftwareInc,2013-2018 70 4.4.0
CPUSegments(Activequeriesonly.)CurrentCPUpercentaverageforallsegmentprocessesexecutingthisquery.Thepercentagesforallprocessesrunningoneachsegmentareaveraged,andthentheaverageofallthosevaluesiscalculatedtorenderthismetric.CurrentCPUpercentaverageisalwayszeroinhistoricalandtaildata.Themasterandstandbymasterareexcludedfromthecalculation.
CPUTimeTotalCPUtimeconsumedbyallprocessesonallsegmentsexecutingthisquery.
CPUSkewTheamountofCPUskew.CPUskewoccurswhenqueryexecutorprocessesforonesegmentuseadisproportionateamountofCPUcomparedtoprocessesforothersegmentsexecutingthequery.Thisvalueiscalculatedas
1−(average_segment_CPU/maximum_segment_CPU)
MemoryMemoryconsumedbyallsegmentprocessesexecutingthequery.
SpillFilesThetotalsizeofspillfilescreatedforthequery.GreenplumDatabasecreatesspillfileswhenthereisinsufficientmemorytoexecutethequeryinmemory.SeeManagingSpillFilesGeneratedbyQueries forinformationaboutspillfiles.
DiskRThecurrentaveragediskreadrateforallsegmenthosts.
DiskWThecurrentaveragediskwriterateforallsegmenthosts.
LocksandBlocksContainstwolistsoflockscurrentlyblockingtransactions.Clickalisttoexpandandviewthecontents.
Alistoflocksheldbythisquery,includingthetypeofeachlockandthequeriesblockedbythatlock.
Alistofqueriesthatholdlocksthatblockthisqueryandthelocktype.
QueryTextandExecutionPlanThequerytextandthequery’splanandexecutionprogressareshowninthelowerpanelsoftheQueryDetailsview.Thetextofthequeryisdisplayedintheleftpanel,andtheplanandprogressisdisplayedintherightpanel.
QueryPlanandProgressThePlan&Progresstabinthelowerrightpanelisagraphicalrepresentationofthequeryplanwithanimationandreal-timemetricstoshowexecutionprogress.Eachboxinthetreerepresentsastepinthequeryexecutionplan.TheboxesarelabeledwiththeoperationtheyrepresentandhaveaCPUusagemetric.Queryexecutionbeginsatthebottomofthetreeandendsatthetop.
©CopyrightPivotalSoftwareInc,2013-2018 71 4.4.0
Beforeastepbeginstoexecute,theboxhasasolidwhitefill.Whenthestepbecomesactive,theboxisanimatedwithagreenandwhitefilltoindicatethattheoperatorisworking.Whenthestephascompleted,theboxhasasolidgreenfill.
Queryexecutionplansareexecutedin“slices,”portionsofthequeryplanthatsegmentscanworkonindependentlyinparallel.Theplanisslicedwhereveradatamotionoperatoroccurs.ThetimedisplayedintheupperrightcornerofeachboxistheamountofCPUtimeusedforallstepsintheslicetowhichthestepbelongs.Thevisualqueryplandoesnotillustrateslices,butyoucanfindinformationaboutslicesinthetextualplan.
Ifyouclickastep,theboxexpandstoshowadditionaldetails.
Theexpandedplanboxcontainsthefollowingmetrics.
Thetypeofoperator.Whentheoperatorisatablescan,thenameofthescannedtableisincluded.SeeQueryPlanExecutionfordescriptionsoftheoperators.
Informationrelatedtothecurrentoperation,suchasthehashkey,mergekey,joincondition,orfiltercondition.
RowSkew-theamountofrowskewforthecurrentoperator,avaluefrom0%to100%.Rowskewoccurswhensomesegmentsprocessmorerowsthanothersegments.Thepercentageiscalculatedas (1 − (average_segment_rows / maximum_segment_rows)) * 100 .
EstimatedCompletion-thecurrentpercentageofactualrowstoestimatedrowsforthisplanstep.Thepercentagecanexceed100%iftheoperationproducesmorerowsthantheoptimizer’sestimate.Thepercentagechangesto“Completed”whentheoperationcompletes.
ActualRows-Thecurrentnumberofrowsproducedbythisstep.Notethatfornestedjoinoperators,theActualRowsisestimatedsincetheactualrowcountsarenotavailablewhilethejoinisexecuting.
EstimatedRows-Theestimatednumberofrowstheoperatorwillproduce.
TextualPlanSelecttheTextualPlantabandclickRUNEXPLAINtogeneratethetextrepresentationoftheexplainplan.
TheRUNEXPLAINbuttonisdimmedifCommandCenterisunabletogeneratetheexplainplan.CommandCenterisunabletogeneratetheexplainplanifthesizeofthequerytextisgreaterthan100Korifthequerytextcontainsmultiplestatements.
©CopyrightPivotalSoftwareInc,2013-2018 72 4.4.0
ThetextualplanistheoutputoftheGreenplumDatabase EXPLAIN commandforthequery.Thequeryplanstepsarelabeledwitharrows( -> )andthestructureofthequeryplantreeisindicatedwithindentation.
The Optimizerstatus: lineatthebottomofthetextualplanreportswhethertheexplainplanwasgeneratedusingtheGPORCAoptimizer(PQO)orthelegacyqueryoptimizer.
Forhelpreadingthetextualexplainplanseethe EXPLAIN commandintheGreenplumDatabaseReferenceGuideandQueryProfiling intheGreenplumDatabaseAdministratorGuide.SeeQueryExecutionfordescriptionsofthequeryoperators.
©CopyrightPivotalSoftwareInc,2013-2018 73 4.4.0
HistoryTheHistorypageallowsyoutodisplaysystemmetricsandqueriesexecutedduringaspecifiedtimeperiod.Queriesmayalsobefilteredbydatabaseand/oruser.
SetthetimeperiodtodisplaybyenteringdatesandtimesintheFromandTodateandtimefields.Youcanenterdatesbytypingthemintothedatefieldorbychoosingfromthepop-upcalendar.Enter24-hourtimesinHH:MMformat.
Torestrictqueriesthatdisplayinthequerytableatthebottomofthepage,enteraGreenplumdatabasenameintheDatabasefield,ausernameintheUserfield,orboth.Filteringbydatabaseanduseronlyaffectsthequeriesdisplayedinthetable.Themetricsdisplayedinchartsincludeallactivityduringtheselectedtimeperiod.
ClickSearchtodisplayresultsthatmatchyourcriteria.
Note:TheHistorydataisnotderivedfromthereal-timemetricsdisplayedbytheQueryMonitorandQueryDetailview.Historyiscollectedandsavedinthegpperfmondatabasebythe gpmmon and gpsmon agents.Variationsshouldbeexpectedduetothedifferentdatacollectionmethods.
©CopyrightPivotalSoftwareInc,2013-2018 74 4.4.0
Youcanclickanddragonacharttozoominonatimerange.ClickSearchtoupdatethequerylistandchartstotheselectedrange.
Scrollchartsleftorrightbyhoveringovertheedgeofthechartandclickinganarrow.Click‹or›tomoveinhalfsteps.Click«or»tomoveinfullsteps.
Inthequerylist,selectorhoveroveraquerytohighlightitsqueuedandruntimeinthecharts.
Chartsofthefollowingmetricsareavailable.Showorhidethematanytimewiththechecklistattheupperrightoftheview.
QueriesThenumberofqueriesrunningandthenumberofqueriesqueuedtorun.
CPUThepercentageofCPUusedbysystemprocessesandthepercentageofCPUusedbyuserprocesses.
MemoryPercentageofmemoryinuse.
DiskI/ODiskreadandwriteratesinmegabytespersecond.
NetworkNetworkI/Oreadandwriteratesinmegabytespersecond.NetworkmetricsincludetrafficoverallNICs(networkinterfacecards),includinginternalinterconnectandadministrativetraffic.
LoadSystemloadaveragefor1-minute,5-minute,and15-minuteperiods.
SwapPercentageofswapspaceused.
QueryMetricsTheQuerytabledisplaysqueriesthatwereactiveduringthespecifiedtimeperiod,includingqueriesthatstartedbeforeorfinishedafterthespecifiedtime.However,queriesthatarestillactivearenotincludedinthetable;thesequeriescanbeviewedontheQueryMonitorpage.
Thequerytablehasthefollowingcolumns:
QueryIDAnidentificationstringforthequery.IntheConsole,thislookslike“1295397846-56415-2”.
StatusThefinalstatusofthequery.Thiscanbeoneofthefollowing:
Done
Cancelled
UserTheGreenplumDatabaseuserwhosubmittedthequery.
DatabaseThenameofthedatabasethatwasqueried.
SubmitTimeThetimethequerywassubmittedtothequeryplanner.
QueuedTimeTheamountoftimeaqueryspentinthequeuebeforeitwasexecuted.
RunTimeTheamountoftimethequeryrequiredtoproducearesult.
EndTimeThetimethequerycompletedorwascancelled.
CPUSkewTheamountofCPUskew.CPUskewoccurswhenqueryexecutorprocessesforonesegmentuseadisproportionateamountofCPUcomparedtoprocessesforothersegmentsexecutingthequery.ThisvalueisthecoefficientofvariationfortheCPUusedbyprocessesrunningthisqueryoneachsegment,multipliedby100.Forexample,avalueof.95isshownas95.
RowSkew
©CopyrightPivotalSoftwareInc,2013-2018 75 4.4.0
Ameasureofrowskewinthesystem.Rowskewoccurswhenonesegmentproducesadisproportionatenumberofrowsforaquery.ThisvalueisthecoefficientofvariationfortheRowsOutmetricofalliteratorsacrossallsegmentsforthisquery,multipliedby100.Forexample,avalueof.95isshownas95.
QueueThenameoftheresourcequeueforthequery.
PriorityEachqueryinheritsthepriorityassignedtoitsresourcequeue.
FormoreinformationaboutResourceQueuesandQueryPlans,refertotheGreenplumDatabaseAdministratorGuide.
©CopyrightPivotalSoftwareInc,2013-2018 76 4.4.0
ManagingGreenplumDatabaseWorkloadsAboutWorkloads
ManagingGreenplumDatabaseResourceGroups
ImportingResourceQueuestoResourceGroups
AccessingtheWorkloadManagementConfigurationProgrammatically
TroubleshootingEnablingResourceGroups
©CopyrightPivotalSoftwareInc,2013-2018 77 4.4.0
AboutGreenplumCommandCenterWorkloadManagementGreenplumDatabasesegmenthostshaveasetamountofmemory,CPU,I/O,andnetworkresources.Iftheseresourcesstarttorunshortduetoheavydatabaseload,queriescanfailortheentiredatabasesystemcanbecomeunacceptablyslow.Forthisreason,itisimportanttomanagedatabaserequeststopreventresourcedepletion.
SeeUsingResourceGroups intheGreenplumDatabaseAdministratorGuideforafulldescriptionofresourcemanagementfeaturesavailablewithresourcegroupsandinstructionstoenableresourcegroupsinGreenplumDatabase.
OntheCommandCenterAdmin>WorkloadMgmtview,youcanperformthefollowingtasks:
CreatenewresourcegroupsinGreenplumDatabase
Deleteexistingresourcegroups
Changethenumberofconcurrenttransactionseachresourcegroupallows
ChangethepercentagesofavailablesystemCPUandmemoryeachresourcegroupmanages
ChangeGreenplumDatabaseroles’defaultresourcegroups
Writequeryassignmentrulestooverrideauser’sdefaultresourcegroupwhenatransactionexecutes
Writeidlesessiontimeoutrulesforeachresourcegrouptosettheamountoftimeasessioncanbeidlebeforeitiskilled
FormoreinformationaboutLinuxcgroupsandGreenplumDatabaseresourcegroupsseeUsingResourceGroups intheGreenplumDatabaseAdministratorGuide.
QueryassignmentrulesisaGreenplumCommandCenterresourcegroupenhancementthatyoucanusetomoreflexiblyassigntransactionstoresourcegroups.Withidlesessiontimeoutrulesyoucansettheamountoftimeasessionmanagedbyaresourcegroupisidlebeforeitisterminated.ThefollowingsectionsprovidemoreinformationabouttheseCommandCenterfeatures.
AboutQueryAssignmentRulesGreenplumDatabasedeferstotheworkloadmanagementdatabaseextensiontoassigntransactionstoresourcegroups.CommandCenteruserswithAdminpermissionlevelcancreateassignmentrulesinCommandCentertoassigntransactionstoresourcegroups.
Whenatransactionbegins,GreenplumDatabasecallstheworkloadmanagementextensiontodeterminetheresourcegroup.Theextensionevaluatestheassignmentrulesand,ifamatchingruleisfound,returnsthatrule’sresourcegroup.Ifnoassignmentrulematches,GreenplumDatabasefallsbacktothedefaultbehavior,assigningthetransactiontotheresourcegroupspecifiedforthecurrentuserinthe pg_authid systemtable.
AssignmentrulescanredirectatransactiontoaresourcegroupbasedonquerytagsoracombinationofquerytagsandtheGreenplumDatabaseroleexecutingthetransaction.
Aquerytagisauser-definedname=valuepairthatyoucansetinaGreenplumDatabasesessionwhentheworkloadmanagementdatabaseextensionisenabled.Querytagsaredefinedbysettingthe gpcc.query_tags parameterontheconnectstringwhenrequestingadatabaseconnectionorinthesessionwith SETgpcc.query_tagsTO'<query-
tags>'.Multiplequerytagscanbesetbyseparatingthemwithasemicolon.Querytagsaresetbeforeatransactionbegins
andcannotbechangedinsideofatransaction.
SeeWorkloadManagementfordetailsaboutcreatingassignmentrulesandforexamplesthatusequerytags.
AboutTimeoutRules
GreenplumDatabasehastworesourcemanagementsystems:resourcequeuesandresourcegroups.CommandCenterworkloadmanagementisbasedonresourcegroups,theresourcemanagementsystemintroducedinGreenplumDatabaseversion5.ResourcegroupsrequireenablingLinuxcontrolgroups(cgroups),soGreenplumDatabaseinitiallyissettoperformresourcemanagementusingresourcequeues.
WhenCommandCenterstarts,itcheckstheresourcemanagementsystemenabledinyourGreenplumDatabasesystem.IfyouhavenotyetenabledresourcegroupsinyourGreenplumDatabasesystem,theCommandCenterworkloadmanagementviewdisplaysamessageencouragingyoutoenableresourcegroups,includingalinktodocumentationwiththeprocedureforcompletingthetask.WhenyoustartCommandCenterafterenablingresourcegroups,clicktheENABLEWORKLOADSbutton.CommandCenterpresentsaviewtohelpyousettheintialresourcegroupconfigurationbyimportingyourexistingresourcequeuestoresourcegroups.
©CopyrightPivotalSoftwareInc,2013-2018 78 4.4.0
CommandCenteradministratorscancreatesessiontimeoutrulesforresourcegroupsthatspecifythemaximumtimethatasessioncanremainidlebeforeitisterminated.WhenthesessionprocessontheGreenplumDatabasemasterbecomesidleandthespecifiedtimehaselapsed,thesessionterminatesitself.Sessionsthatbecomeidlewhileinsideofatransactionarenotsubjecttotimeoutrules.
Sessiontimeoutrulesareperresourcegroup,allowingyoutodeterminehowlongtowaitfordifferentclassesoftransactions.YoucanincludealistofGreenplumDatabaserolestoexemptfromtheruleandacustommessagetoaddtothesessionterminationerroroutput.
Creatingtimeoutrulesforthe admin_group resourcegroupisnotrecommended.AvoidcreatingrulesthatkillsessionsstartedbyGreenplumDatabaseclientapplicationsthataredesignedtohavealong-lastingorpersistentsession.Forexample,the gpmmon processcreatesasessionasthe gpmon roletoupdatethegpperfmondatabase.Anidlesessiontimeoutrulethatkillsidle gpmon sessionstooquicklycouldcauseGreenplumDatabasetologmanysessionterminationmessagesandunnecessarilycreatenewsessions.SeeWorkloadManagementfordetailsaboutcreatingtimeoutrules.
©CopyrightPivotalSoftwareInc,2013-2018 79 4.4.0
WorkloadManagement
DefiningResourceGroupsandResourceAttributesCommandCenterallowsyoutoviewresourcegroupsthathavebeencreatedinGreenplumDatabase,toaddordeleteresourcegroups,andtoedittheresourcegroupattributesConcurrency,CPU%,andMemory%.
1. ClickEDITtoopentheResourceGroupeditor.
2. Todeletearesourcegroup,selecttheresourcegroup,andclicktheminussignthatappearsattheright.Youcannotdeletethe default_group or admin_group resourcegroups.YoucannotdeletearesourcegroupthatisassignedtoanyGreenplumDatabaserole.
3. Toaddaresourcegroup,clickADDRESOURCEGROUPandenteranamefortheresourcegroupintheNamecolumn.Resourcegroupnamesmustbeuniqueandarecase-sensitive.
4. AdjustthevaluesoftheConcurrency,CPU%,andMemory%resourcegroupattributes.Concurrency
Themaximumnumberofconcurrenttransactions,includingactiveandidletransactions,thatarepermittedintheresourcegroup.Concurrencysetsthe CONCURRENCY attributeoftheresourcegroup.ThetotaloftheConcurrencycolumnscannotexceedthevalueoftheGreenplumDatabase max_connections masterserverconfigurationparameter.
CPU%ThepercentageofCPUresourcesavailabletothisresourcegroup.ThepercentageistheportionofthetotalCPUpercentageallocatedforallresourcegroups(reservedCPUsexcluded),whichissetwiththe gp_resource_group_cpu_limit serverconfigurationparameter.CPU%setsthe CPU_RATE_LIMIT attributeoftheresourcegroup.
Memory%Thepercentageofmemoryresourcesavailabletothisresourcegroup.Thepercentageistheportionofthetotalmemoryallocatedforallresourcegroups,whichissetwiththe gp_resource_group_memory_limit GreenplumDatabaseconfigurationparameter.ChangingtheMemory%valuesetsthe MEMORY_LIMIT attributeoftheresourcegroup.
MinmemoryperqueryTheminimumamountofmemoryallocatedtoaquery.ThiscolumnisrecalculatedasyouadjustConcurrencyandMemory%settings.Thevalueistheresourcegroup’stotalshareofsystemmemory,lesstheresourcegroup’ssharedmemorypool(20%bydefault),dividedbythevalueintheConcurrencycolumn.ThepercentageofmemoryallocatedtothesharedmemorypoolcanbechangedbysettingtheMEMORY_SHARED_QUOTA attributeoftheresourcegroupusingtheALTERRESOURCEGROUP SQLcommand.Eachquerymanagedbytheresourcequeueisallocatedthisamountofmemory.Ifaqueryneedsmorememory,itisallocatedfromtheresourcegroupsharedmemorypoolandtheglobalsharedmemorypool,ifavailable.
ThetotalsoftheCPU%andMemory%columnsmustnotexceed100%.YoushouldnotallowthetotaloftheCPU%columntoexceed90%,becausethiscouldcauseresource-intensivequeriestoconsumenearlyallCPU,starvingotherGreenplumDatabaseprocesses.IfthetotaloftheMemory%columnislessthan100%,theunreservedmemoryispartoftheresourcegroupsharedglobalmemorypool.See“GlobalSharedMemory”inUsingResourceGroups intheGreenplumDatabaseAdministratorGuideforinformationabouttheglobalresourcegroupsharedmemorypool.
5. ClickApplytosaveyourchangesorclickCanceltoabandonyourchanges.
Tochangevaluesofthe MEMORY_AUDITOR , CPUSET , MEMORY_SHARED_QUOTA ,or MEMORY_SPILL_RATIO resourcegroupattributes,usethe ALTERRESOURCEGROUP SQLcommand.
©CopyrightPivotalSoftwareInc,2013-2018 80 4.4.0
AssigningRolestoResourceGroupsEveryGreenplumDatabaseroleisassignedtoasingleresourcegroupinthe pg_roles systemtable.Transactionsexecutedbyarolearemanagedbyitsassignedresourcegroup,unlessyoucreateanassignmentruletooverridethedefault.
Youcanviewthecurrentresourcegroupassignmentsforallrolesandchangearole’sresourcegroupbyaddingittoadifferentresourcegroup.
Tomovearoletoadifferentresourcegroup:
1. Enterallorpartoftherolenameintheaddrolefieldbeneaththenewresourcegroup.
2. ChoosetherolefromthelistthatisdisplayedandpressEnter.
ThechangeisimmediatelyappliedtotheGreenplumDatabase pg_roles systemtable.
DefiningWorkloadManagementRulesWorkloadmanagementrulesincludequeryassignmentrulesandtimeoutrules.
Queryassignmentrulesallowyouassigntransactionstoaresourcegroupbasedonuser-definedquerytagsand,optionally,thecurrentroleinthedatabasesession.Whennorulematches,thetransactionisassignedtotherole’sdefaultresourcegroup.SeeAboutAssignmentRulesformoreinformationaboutassignmentrules.
Timeoutrulessettheamountoftimeasessioncanbeidlebeforeitisterminated.Youcreateatimeoutruleforeachresourcegroup.SeeAboutTimeoutRulesformoreinformationabouttimeoutrules.
SeeAccessingtheWorkloadConfigurationProgrammaticallyforinformationaboutretrievingandsettingrulesprogrammaticallywithdatabasefunctions.
1. ClickEDITtoopentheWorkloadManagementRuleseditor.
2. Todeletearule,selecttheruleandclicktheminussignthatappearsattheright.
3. Toaddanassignmentrule,clickADDASSIGNMENTRULEandfillinthefields.QueryTags
©CopyrightPivotalSoftwareInc,2013-2018 81 4.4.0
Thefirstfieldisalistofquerytagstomatchagainstthe gpcc.query_tags parameterintheGreenplumDatabasesession.Aquerytagisauser-defined<name>=<value>pair.Separatemultiplequerytagswithsemicolons.SeeDefiningandSettingQueryTagsformoreinformationaboutquerytags.
Role(Optional)Ifyouenterarolenameinthisfield,therulematchesonlyifboththequerytagsandrolematchthetagsandcurrentroleinthedatabasesession.
ResourceGroupChoosearesourcegroupfromthelist.
Changetheorderoftheassignmentrulesbydraggingarule’shandle(attheleft)upordown.Assignmentrulesareevaluatedfromtoptobottom.GreenplumDatabaseappliesthefirstrulethatmatches.UsetheActive/Inactivetoggletomakearuleactiveorinactive.
4. Toaddatimeoutrule,clickADDTIMEOUTRULEandfillinthefields.ResourceGroup
Choosearesourcegroupfromthelist.Timebeforeidleconnectionskilled
Theamountoftimebeforeanidlesessionisterminated.Enteranintegervalueforthenumberofsecondsorminutesandchooseminutesorsecondsfromthelist.Avalueof600seconds,forexample,terminatesthesessionafterithasbeenidlefor10minutes.SeeAboutTimeoutRulesforwarningsaboutsettingtimeoutsforthe admin_group resourcegroupandresourcegroupswithrolesthatrequirepersistentsessions.
ExemptedrolesTheruleisnotappliedtorolesaddedtothisfield.Openthelisttochooserolestoexemptfromtherule.Onlyrolesfortheselectedresourcegroupareincludedinthelist.Toremovearolefromthefield,clickthexnexttotherolename.
MessageEnterthemessagetologwhenasessioniskilledbythisrule.
5. ClickAPPLYtosaveyourchanges.
DefiningandSettingQueryTagsAquerytagisauser-defined<name>=<value>pair,setintheGreenplumDatabase gpcc.query_tags parameterintheGreenplumDatabasesession.Thegpcc.query_tags parameterisdefinedwhenthe gp_wlm databaseextensionisenabledinthepostgresdatabase.Ifyoutrytosetquerytagswhenthegp_wlm extensionisnotenabled,yougetanunrecognizedconfigurationparametererror.Toseeiftheextensionisenabled,runthefollowingcommand.
$psqlpostgres-c"\dx"ListofinstalledextensionsName|Version|Schema|Description--------+---------+--------+--------------------------------------gp_wlm|0.1|gpcc|GreenplumWorkloadManagerExtension(1row)
Whenyousubmitatransactionandthe gp_wlm extensionisenabled,GreenplumDatabasecallsthe gp_wlm extensiontodeterminetheresourcegroupforthetransaction.TheextensionevaluatesthecurrentroleandquerytagssetinthesessionagainsttherulesyouhavedefinedinCommandCenter.Ifthereisamatch,theextensionreturnstherule’sresourcegroup.Ifthereisnomatch,GreenplumDatabaseassignsthetransactiontotherole’sdefaultresourcegroup.
Thefollowingcommand,executedintheGreenplumDatabasesession,setsthe appName and appUser querytagsto“tableau”and“bi_sales”,respectively.
=#SETgpcc.query_tagsTO'appName=tableau;appUser=bi_sales';
Tomatcharule,alltagsintherule’squerytagfieldmustbepresentinthe gpcc.query_tags parameterinthedatabasesession.Theorderofthetagsisnotsignificant,andthe gpcc.query_tags parametercanhaveasupersetofthetagsdefinedinthe queryTags value.
Ifyousetthe gpcc.query_tags parameterinsideofatransaction,youmustcommitthetransactionbeforethenewquerytagsareusedtoevaluateassignmentrules.
Youcansetthevalueofthe gpcc.query_tags parameterusingthe SET command,asintheexampleabove,orasaconnectionparameterwithdatabaseclientsthatsupportit,suchas psql .Followingaretwoexamplesthatshowhowtospecifyquerytagsonthe psql commandline.
©CopyrightPivotalSoftwareInc,2013-2018 82 4.4.0
$PGOPTIONS="-cgpcc.query_tags='appName=tableau;appUser=bi_sales'"psql
$psqlpostgresql://mdw:5432/postgres?options="-cgpcc.query_tags%3D'appName%3Dtableau;appUser%3Dbi_sales'"
Inthesecondexample,itisnecessarytocodetheequalssignsas %3D toprevent psql frominterpretingthequerytagsascommand-linearguments.
©CopyrightPivotalSoftwareInc,2013-2018 83 4.4.0
ImportingResourceQueuestoResourceGroupsGreenplumCommandCenterworkloadmanagementworkswithresourcegroups,thenewGreenplumDatabaseresourcemanagementsystem.ThedefaultresourcemanagementsystemforGreenplumDatabaseisresourcequeues.TousetheCommandCenterworkloadmanagementfeatures,youmustfirstenableresourcegroupsinGreenplumDatabase.
CommandCentercanassistyouinenablingresourcegroupsandinimportingexistingresourcequeuestoresourcegroups.
StepOne:EnableResourceGroupsinGreenplumDatabaseIfyourGreenplumDatabasesystemisstillconfiguredtouseresourcequeues,theCommandCenterAdmin>WorkloadMgmtviewdescribesthebenefitsofresourcegroupsandworkloadmanagementwithCommandCenterandprovidesalinktotheGreenplumDatabasedocumentationtohelpyouenableresourcegroups.
ClickVIEWRESOURCEGROUPSETUPGUIDEforinstructionstoenableresourcegroupsinyourGreenplumDatabasesystem.
StepTwo:PreviewandConfigureResourceGroupImportsAfteryouhaveenabledresourcegroupsandrestartedGreenplumDatabase,restartCommandCenter( gpcc
start),login,andchooseAdmin>Workload
Mgmt.
Theworkloadmanagementviewnowdisplaysapreviewofresourcegroupsconvertedfromyourexistingresourcequeues.Youcanusethisone-timeviewtoconvertyourGreenplumDatabaseresourcequeuestoresourcegroups.
©CopyrightPivotalSoftwareInc,2013-2018 84 4.4.0
Theresourcegrouplistincludestherequired admin_group and default_group resourcegroups,andarowforeachofyourexistingresourcequeues.
Rolesareassignedtotheresourcegroupmatchingtheresourcequeuetowhichtheyareassigned.ClickthePreviewroleslinktoseetheroleassignments.
Ifyouwanttosetupresourcegroupslater,youcanclickSKIPIMPORT.Onlythe default_group and admin_group resourcegroupsarecreated.Roleswiththesuperuserattributeareassignedtothe admin_group resourcegroup;roleswithoutsuperuserprivilegeareassignedtothe default_group resourcegroup.
IfyouwantCommandCentertoimportresourcequeuestoresourcegroups,youmustcompletetheresourceallocationfieldsforallresourcegroups.
SettheConcurrency,CPU%,andMemory%resourcegroupattributestoallocateGreenplumDatabaseresourcestotheresourcequeues.TheConcurrencyfieldsmusteachcontainapositiveinteger.TheCPU%andMemory%fieldsmusteachcontainpositiveintegersbetween1and99andthetotalsfortheCPU%andMemory%columnsmustnotexceed100%.SeeDefiningResourceGroupsandResourceAttributesforhelpdeterminingthevaluestoenter.
TheIMPORTRESOURCEGROUPSbuttonisdisableduntilyouhaveenteredvalidvaluesintheallocationfieldsforeveryresourcegroup.
Whenyouarereadytoimporttheresourcegroups,clickIMPORTRESOURCEGROUPStocreatetheresourcegroups.
StepThree:EnableCommandCenterWorkloadManagementAfteryouimport(orskipimporting)resourcequeuestoresourcegroups,youcanenableCommandCenterworkloadmanagement.
©CopyrightPivotalSoftwareInc,2013-2018 85 4.4.0
ClickENABLEWORKLOADMANAGEMENTtoenableworkloadmanagementinGreenplumCommandCenter.GreenplumCommandCentercreatesthegp_wlm extension,the gpcc.workload_config table,andtheassociateduser-definedfunctionsinGreenplumDatabase.
YouarenowabletousetheCommandCenterWorkloadManagementinterfacetoadd,remove,andconfigureresourcegroups;changeroleassignments;anddefineworkloadmanagementrules.
SeeWorkloadManagementforhelpusingtheCommandCenterWorkloadManagementview.
©CopyrightPivotalSoftwareInc,2013-2018 86 4.4.0
AccessingtheWorkloadConfigurationProgrammaticallyTheGreenplumDatabaseworkloadmanagementextension gp_wlm createsatableinthepostgresdatabasetostoretheworkloadmanagementrules,anduser-definedfunctionstogetorsettheworkloadmanagementrules.
The gpcc.workload_config tablestorestheworkloadmanagementrulesasaJSONvalue.Youcanusethe gpcc.get_workload_config() andgpcc.set_workload_config() functionstoreadandwritethisJSONvalue.
ThistopicisareferencefortheworkloadmanagementconfigurationJSONdocumentandthegetandsetfunctions.
WorkloadManagementRulesJSONFormatThissectiondescribestheJSONobjectthatstorestheresourcegroupassignmentandidlesessiontimeoutrules.Theobjecthasthreemembers:
a version key/valuepair
an assignmentRules arraycontainingoneelementforeachassignmentrule
an idleSessionKillRules listcontainingonekey/valuepairforeachresourcegroup
versionpair
versionThe version valueisaninteger.Itisreservedforfutureuse.Itcanbesetto 1 .
assignmentRulesarray
assignmentRulesThe assignmentRules arrayhasoneelementforeachassignmentrule.EachelementmapstheruletoaresourcegroupinGreenplumDatabaseanddefinestheconditionsthatassignatransactiontothatresourcegroup.
GreenplumCommandCenterevaluateseachruleinthe assignmentRules arrayfromtoptobottomandstopsatthefirstmatch,sotheorderisimportant.
Theelementsinthe assignmentRules arraycanhavethefollowingkey/valuepairs.
resourceGroupNameThenameoftheGreenplumDatabaseresourcegroup.TheresourcegroupmustalreadyexistorhavebeencreatedinGreenplumDatabasewiththeCREATE RESOURCE GROUP SQLstatement.
roleNameThenameofaGreenplumDatabaseroletomatchagainstthecurrentroleidentifierintheGreenplumDatabasesession.ThecurrentroleisinitiallythedatabaserolethatauthenticatedwiththeGreenplumDatabasesystemtobeginthesession.Auserwithsufficientprivilegescanchangethecurrentroleinthedatabasesessionusingthe SET ROLE SQLcommand.
Ifnoroleisspecifiedintheassignmentruleandthequerytagsmatch,thetransactionisassignedtothespecifiedresourcegroup.Ifthe roleNamevalueispresent,however,thecurrentdatabaseusermustmatchthespecifiedrole.
queryTagsAlistofquerytagstomatchagainstthe gpcc.query_tags parameterintheGreenplumDatabasesession.Aquerytagisauser-defined<name>=<value>pair.Separatemultiplequerytagswithsemicolons.Forexample,thefollowingstatement,executedintheGreenplumDatabasesession,setsthe appName and appUser querytagsto“tableau”and“bi_sales”,respectively.
Warning!The gpcc.workload_config tableshouldonlybeaccessedbyusingthe gpcc.get_workload_config() and gpcc.set_workload_config() functionsortheCommandCenteruserinterface.Donotdropthetablewhiletheworkloadmanagementextensionisenabled.
The gpcc.set_workload_config() functionrequiresvalidJSONsyntax,butdoesnotvalidatetheworkloadmanagementrules.YoumustensurethattheJSONvaluecontainsa version variable,andcorrectlyspecifiedassignmentandidlesessiontimeoutrules.
©CopyrightPivotalSoftwareInc,2013-2018 87 4.4.0
=#SETgpcc.query_tagsTO'appName=tableau;appUser=bi_sales';
Tomatch,alltagsintheassignmentrule’s queryTags valuemustbepresentinthe gpcc.query_tags parameterinthedatabasesession.Theorderofthetagsisnotsignificant,andthe gpcc.query_tags parametercanbeasupersetofthetagsdefinedinthe queryTags value.
Ifthe queryTags valueisempty,ortheparameteromitted,therulewillmatcheverysessionforthe roleName databaserole.
Ifyousetthe gpcc.query_tags parameterinsideofatransaction,youmustcommitthetransactionbeforethenewquerytagsareusedtoevaluateassignmentrules.
The gpcc.query_tags parametercanbespecifiedusingthe SET command,asabove,orasaconnectionparameterwithdatabaseclientsthatsupportit,suchas psql .Herearetwoexamplesthatshowhowtospecifyquerytagsonthe psql commandline:
$PGOPTIONS="-cgpcc.query_tags='appName=tableau;appUser=bi_sales'"psql
$psqlpostgresql://mdw:5432/postgres?options="-cgpcc.query_tags%3D'appName%3Dtableau;appUser%3Dbi_sales'"
Inthesecondexample,itisnecessarytocodetheequalssignsas %3D toprevent psql frominterpretingthequerytagsascommand-linearguments.
disabledIfsetto true ,theassignmentruleisignoredwhenCommandCenterevaluatesrulestoassigntransactionstoworkloads.Thisparameterisoptionalanditsdefaultvalueis "false" .
gpcc.get_workload_config()RetrievesthecurrentworkloadassignmentrulesasaJSONvalue.
Example
postgres=#SELECTgpcc.get_workload_config();get_workload_config--------------------------------------------------------{"version":1,"assignmentRules":[{"resourceGroupName":"default_group","roleName":"gpadmin","queryTags":"appName=tableau;appUser=bi_sales"},{"resourceGroupName":"admin_group","roleName":"gpadmin","queryTags":"appName=tableau;appUser=bi_acct","disabled":true}],"idleSessionKillRules":{"admin_group":{"timeoutSeconds":600},"default_group":{"timeoutSeconds":3600}}}
(1row)
gpcc.set_workload_config()Setstheworkloadassignmentrules.TheargumentisavalidJSONvaluecontainingtheassignmentrulesan.SeeJSONParametersfordescriptionsoftheparameters.
©CopyrightPivotalSoftwareInc,2013-2018 88 4.4.0
Ifyoucall gpcc.set_workload_config() withinatransaction,youmustcommitthetransactionbeforetheworkloadmanagmentextensionappliesthenewrules.
Example
postgres=#SELECTgpcc.set_workload_config('{"version":1,"assignmentRules":[{"resourceGroupName":"default_group","roleName":"gpadmin","queryTags":"appName=tableau;appUser=bi_sales"},{"resourceGroupName":"admin_group","roleName":"gpadmin","queryTags":"appName=tableau;appUser=bi_acct","disabled":true}],"idleSessionKillRules":{"admin_group":{"timeoutSeconds":600},"default_group":{"timeoutSeconds":3600}}}');set_workload_config---------------------t(1row)
The gpcc.set_workload_config() functionacceptsanyvalidJSONvalue.Youmustensurethatthevaluecontainsa version elementandproperlyspecifiedassignmentandidlesessiontimeoutrules.
©CopyrightPivotalSoftwareInc,2013-2018 89 4.4.0
TroubleshootingEnablingResourceGroupsIfyouexperienceproblemsenablingresourcegroupsinGreenplumCommandCenter,reviewthefollowinglisttoensureprerequisitesaremetandallofthedependenciesareproperlyconfigured.
RedHat6.xand7.xandCentOS6.xand7.xarecurrentlysupported.
YoumustberunningGreenplumDatabaseversion5.7.0orlater.
ConfiguretheLinuxcgroupskernelfeatureonyourhostsbyfollowingtheinstructionsat“Prerequisite”inUsingResourceGroups .
Makesurethe /etc/cgconfig.d/gpdb.conf filecontainstheobjectsperm,cpu,andcpuacct.Ifthedocumentisincorrectandthe gp_resource_manager
configurationparameterissetto "group" ,GreenplumDatabasecanhangatstartup.
groupgpdb{perm{task{uid=gpadmin;gid=gpadmin;}admin{uid=gpadmin;gid=gpadmin;}}cpu{}cpuacct{}}
OnRedHat7,makesureyourun cgconfigparser-L/etc/cgconfig.d toparsechangestothe /etc/cgconfig.d/gpdb.conf file.Thiscommandmustalsobesetuptorunatboottime.
SettheGreenplumDatabase gp_resource_manager serverconfigurationparameterto "group" .
$gpconfig-cgp_resource_manager-v"group"
Verifybyshowingthevalueoftheparameter:
$gpconfig-sgp_resource_managerValuesonallsegmentsareconsistentGUC:gp_resource_managerMastervalue:groupSegmentvalue:group
AfterinstallingaPivotalGreenplumDatabasedistribution,the shared_preload_libraries configurationparametercontainsthemetricscollectorandworkloadmanagerextensionsharedlibraries.Makesuretheselibrariesarestillpresent:
$gpconfig-sshared_preload_librariesValuesonallsegmentsareconsistentGUC:shared_preload_librariesMastervalue:$libdir/metrics_collector,$libdir/gp_wlmSegmentvalue:$libdir/metrics_collector,$libdir/gp_wlm
Checkthatthesharedlibrariesexistat $GPHOME/lib/postgresql/metrics_collector.so and $GPHOME/lib/postgresql/gp_wlm.so .Ifthelibrariesdonotexist,makesureyouhaveinstalledthePivotalGreenplumDatabasedistribution.TheseextensionsarenotavailableintheGreenplumDatabaseOpenSourceversion.Ifthesharedlibraryfilesexistinthe $GPHOME/lib/postgresql directory,butnotinthe shared_preload_libraries parameter,addthemwiththe gpconfig
command:
$gpconfig-cshared_preload_libraries-v'\$libdir/metrics_collector,\$libdir/gp_wlm'
Notethataddingthelibrariestothe shared_preload_libraries parameterdoesnotenablethemetrics_collectororgp_wlmextensions,butisaprerequisiteforenablingthem.
ThegpmonusermustbeabletoconnecttodatabasesfromtheCommandCenterhost.Makesuretoadda host entrylikethefollowinginthe$MASTER_DATA_DIRECTORY/pg_hba.conf file.
hostallgpmon<IP_of_host>/32md5
Checkwhetherthe gp_wlm extensionisinstalled.TheextensionisaddedwhenyouclickEnableWorkloadsintheGreenplumCommandCenter
©CopyrightPivotalSoftwareInc,2013-2018 90 4.4.0
Admin>WorkloadMgmtview.
$psqlpostgres\dxpostgres=#\dxListofinstalledextensionsName|Version|Schema|Description--------+---------+--------+--------------------------------------gp_wlm|0.1|gpcc|GreenplumWorkloadManagerExtension
Makesurethe gpcc.workload_config tableandfunctionsarepresentinthepostgresdatabase:
$psqlpostgrespostgres=#\dgpcc.*Table"gpcc.workload_config"Column|Type|Modifiers----------+---------+-----------dist_col|integer|config|json|Distributedby:(dist_col)postgres=#\dfgpcc.*ListoffunctionsSchema|Name|Resultdatatype|Argumentdatatypes|Type--------+---------------------+------------------+----------------------+-------gpcc|get_workload_config|json||normalgpcc|set_workload_config|boolean|wlm_json_configjson|normal(2rows)
Ifthe gpcc.workload_config tableorthefunctionsarenotpresent,droppingandrecreatingthegp_wlmextensionwillcreatethem.Notethatanyassignmentrulessavedinthe gpcc.workload_config tablewillhavetoberecreatedinCommandCenter.
$psqlpostgrespostgres=#DROPEXTENSIONgp_wlm;DROPEXTENSIONpostgres=#CREATEEXTENSIONgp_wlm;CREATEEXTENSION
©CopyrightPivotalSoftwareInc,2013-2018 91 4.4.0
PivotalCommandCenterOnlineHelpTopics
QueryMonitorHelpTopicsCPU
CPUSkew
Locks
QueryOptimization
Memory
SpillFiles
©CopyrightPivotalSoftwareInc,2013-2018 92 4.4.0
CPUTheCPUpercentmetricistheaveragecurrentCPUpercentageforallbackendprocessesexecutingthisquery.Thepercentagesforallprocessesrunningaqueryoneachsegmentareaveraged,andthentheaverageofallthosevaluesiscalculatedtorenderthismetric.
YoucanmanagethepercentageofCPUthatqueriescanconsumebycreatingworkloadsandspecifyingthemaximumpercentofCPUeachworkloadcanconsume.Thatpercentageisfurtherdividedamongthesegmentsrunningoneachhostandthenamongtheconcurrentqueriestheworkloadcanexecute.
CPUallocatedtoidleworkloadsisreallocatedtoactivequeriesandreclaimedwhentheidleworkloadbecomesactiveagain.ThismeansthattheCPUpercentvalueforaquerycanexceedlimitsdefinedforworkloadsandcanincreaseanddecreaseasotherqueriesstartorfinish.
MemoryanddiskI/OresourcesaremorelikelycausesfordegradedqueryperformancethanlackofCPUcycles.ThewaystoreduceCPUcontentionmirrorthesolutionsforinsufficientmemory:
ReduceconcurrencyofworkloadstomakemoreCPUavailabletoeachquery.
ReducethenumberofworkloadsandreallocateCPUtotheremainingworkloads.
IfCPUisnotconstrainedandthesizeofspillfilesforsomequeriesisverylarge,makesurethatthe gp_workfile_compress_algorithm serverconfigurationparameterissetto zlib andnot none .CompressingspillfilesreducesdiskI/O,butusesCPUcylestocompressanddecompressthedata.
SeeUsingResourceGroups formoreaboutmanagingperformancewithresourcegroups.
IfyourGreenplumDatabasesystemisconfiguredtomanageresourceswithresourcequeues,seeUsingResourceQueues .
©CopyrightPivotalSoftwareInc,2013-2018 93 4.4.0
CPUSkew
WhatisCPUSkew?CPUskewoccurswhentheworktoexecuteaqueryisnotdistributedevenlyamongthesegments.
TheCPUmetricistheaverageoftheCPUpercentagesusedbyeachprocessexecutingthequery.TheCPUskewmetricisavariancestatisticbasedonthedifferencebetweentheaverageandeachsegment’scurrentCPUmetric.ThesmallertheCPUskew,themoreequallytheworkisdistributed.TheCPUskewmetricvariesbetween0.0(noskew)and1.0.Thelowertheskewmetricthemorefullythedatabasecluster’sresourcesareutilized.
CPUskewisusuallyrelatedtothevolumeofdataprocessedbythesegmentswhileexecutingthequeryexecutionplan.Therearetwotypesofskewyoushouldinvestigate:dataskewandcomputationalskew.
DataSkewAhighCPUskewmaybeanindicationofdataskew,wheretablesusedbythequeryaredistributedunevenly,sothatsomesegmentshavemoredatatoprocessthantheirpeers.Youcancheckfordataskewinatablebyrunningaquerylikethisone:
=#SELECTgp_segment_id,COUNT(*)FROM<table-name>GROUPBYgp_segment_id;
Therowcountshouldbeapproximatelyequalforeachsegment.Iftherowsaredistributedunevenly,checkthedistributionkeyforthetable.Agooddistributionkeyisacolumnorlistofcolumnswithuniqueornearlyuniquevalues,suchasthetable’sprimarykey.SettingthedistributionpolicytoDISTRIBUTEDRANDOMLY alsoensuresawell-distributedtable,butprecludestakingadvantageofperformance-enhancingstrategiessuchasco-locationfortableswithequivalentprimarykeys.
ComputationalSkewHighCPUskewcanbetheresultofcomputationalskew,whichoccursduringqueryexecution.Someoftheoperationsinthequeryplancancausesomesegmentstodomoreworkthanothers.Forexample,joins,sorts,oraggregationsoncolumnswithlowcardinalityorunevenlydistributedvaluescancontributetoCPUskewbycausingsomesegmentstoprocessmanymoretuplesthanothers.
SeeDistributionandSkew intheGreenplumDatabaseAdministratorGuideandTuningSQLQueries intheGreenplumDatabaseBestPracticesguideformorehelpfindingthecausesofskew.
©CopyrightPivotalSoftwareInc,2013-2018 94 4.4.0
LocksGreenplumCommandCenterdisplaysthelockscurrentlyheldbyqueriesandqueriesblockedbylocks.
Ablockoccurswhenonequeryneedstoacquirealockthatconflictswithalockheldbyanotherquery.Ifaqueryisblockedforalongperiodoftime,youcaninvestigatetheblockingqueryand,ifnecessary,canceloneofthequeries.
Lockscanbeacquiredusingthe LOCKTABLE SQLstatement.SomeSQLcommandsacquirelocksautomatically.Followingaredescriptionsofthelockmodes,theGreenplumDatabasecommandsthatacquirethem,andwhichlockmodesconflictwiththem.
ACCESSSHAREAcquiredby SELECT and ANALYZE commands.
ConflictswithACCESSEXCLUSIVElocks.
Ingeneral,anyquerythatonlyreadsatableanddoesnotmodifyitacquiresthislockmode.
ROWSHAREAcquiredby SELECT FOR SHARE command.
ConflictswithEXCLUSIVEandACCESSEXCLUSIVElocks.
AROWSHARElockisplacedonthespecifiedtableandanACCESSSHARElockonanyothertablesreferencedinthequery.
ROWEXCLUSIVEAcquiredby INSERT and COPY commands.
ConflictswithSHARE,SHAREROWEXCLUSIVE,EXCLUSIVE,andACCESSEXCLUSIVElocks.
AROWEXCLUSIVElockisplacedonthespecifiedtableandACCESSSHARElocksareplacedonanyotherreferencedtables.
SHAREUPDATEEXCLUSIVEAcquiredby VACUUM and VACUUM FULL .
ConflictswiththeSHAREUPDATEEXCLUSIVE,SHARE,SHAREROWEXCLUSIVE,EXCLUSIVE,andACCESSEXCLUSIVElocks.
SHAREUPDATEEXCLUSIVEprotectsatableagainstconcurrentschemachangesand VACUUM runs.
SHAREAcquiredby CREATE INDEX .
ConflictswithROWEXCLUSIVE,SHAREUPDATEEXCLUSIVE,SHAREROWEXCLUSIVE,EXCLUSIVE,andACCESSEXCLUSIVElocks.
Protectsatableagainstconcurrentdatachanges.
SHAREROWEXCLUSIVEThislockmodeisnotautomaticallyacquiredbyanyGreenplumDatabasecommand.
ConflictswithROWEXCLUSIVE,SHAREUPDATEEXCLUSIVE,SHARE,SHAREROWEXCLUSIVE,EXCLUSIVE,andACCESSEXCLUSIVElocks.
EXCLUSIVEAcquiredby UPDATE , SELECT FOR UPDATE ,and DELETE commandsinGreenplumDatabase.
ConflictswithROWSHARE,ROWEXCLUSIVE,SHAREUPDATEEXCLUSIVE,SHARE,SHAREROWEXCLUSIVE,EXCLUSIVE,andACCESSEXCLUSIVElocks.ThislockmodeallowsonlyconcurrentACCESSSHARElocks-atablecanbereadbyanothertransactionwhilethislockisheld.ThisismorerestrictivelockingthaninregularPostgreSQL.
ACCESSEXCLUSIVEAcquiredbythe ALTER TABLE , DROP TABLE , TRUNCATE , REINDEX , CLUSTER ,and VACUUM FULL commands.DefaultlockmodeforLOCK TABLE statementsthatdonotspecifyalockmode.Alsobrieflyacquiredby VACUUM (without FULL )onappend-optimizedtablesduringprocessing.
Conflictswithalllocks.
Thislockmodeguaranteesthattheholderistheonlytransactionaccessingthetableinanyway.
FormoreonlocksinGreenplumDatabasequeries,seetheLOCK commandReference.SeealsoTuningSQLQueries .
©CopyrightPivotalSoftwareInc,2013-2018 95 4.4.0
©CopyrightPivotalSoftwareInc,2013-2018 96 4.4.0
MemoryTheGreenplumCommandCenterQueryMonitorreportsthecurrenttotalmemoryconsumedbyallprocessesexecutingaquery.Whenthereisinsufficientmemoryavailableforaquerytocomplete,thequeryhasanerrorstatusinthequerymonitorandanoutofmemoryerrorislogged.
IfyouhaveenabledresourcegroupsinGreenplumDatabase,youcanmanagetheamountofmemoryavailabletoqueriesbytuningresourcegroupparameters,andbysettingGreenplumDatabaseconfigurationparametersthataffectresourcegroupmemory.
Foradetaileddescriptionofresourcegroupmemorymanagement,seeUsingResourceGroups intheGreenplumDatabaseAdministratorGuide.
Ifyouareusingresourcequeues,seeMemoryandResourceManagementwithResourceQueues andUsingResourceQueues forwaystotroubleshootmemoryproblemswithresourcequeues.
SeeTuningSQLQueries forhelpwithqueryoptimization.
ThefollowingsummarydescribestheresourcegroupparametersandrelatedGreenplumDatabaseserverconfigurationparametersthatdeterminetheamountofmemoryavailabletodatabasequeriesandhowconfigurationchoicesaffectconcurrency,spillfileusage,andqueryperformance.
ResourceGroupMemoryConfigurationParametersAresourcegrouphasparameters CONCURRENCY , MEMORY_LIMIT , MEMORY_SHARED_QUOTA ,and MEMORY_SPILL_RATIO ,whichdeterminehowmuchmemoryisallocatedtoexecuteaquery.The CPU_LIMIT parameterhasnoeffectonmemoryallocation.Seethe CREATERESOURCEGROUP
SQLreferenceforcommandsyntaxandinformationabouttheseparameters.
MEMORY_LIMIT
Thisparametersetstheamountofmemorytheresourcegroupmanagesasapercentageofthememoryavailabletoresourcegroups.Thesumofallresourcegroups’ MEMORY_LIMIT smustnotexceed100.Ifthesumofallresourcegroups’ MEMORY_LIMIT sislessthan100,theremaining,unallocatedmemoryisglobalresourcegroupsharedmemory,availabletoqueriesfromallresourcegroupsonafirst-come,first-servedbasis.
MEMORY_SHARED_QUOTA
Aresourcegroupdividesthememoryitmanagesintoafixedportionandasharedportion,calledresourcegroupsharedmemory.Thisparameterspecifiesthepercentageofaresourcegroup’smemorythatisshared.Thedefaultis20andthevaluecanrangefrom0to100.
CONCURRENCY
Thisparameterlimitsthenumberofconcurrenttransactionsaresourcegroupallows.Thefixedportionofthememorytheresourcegroupmanagesisdividedequallyamong CONCURRENCY transactionslots.Everytransactionstartswiththisfixedportionofmemoryand,ifneeded,GreenplumDatabaseusesadditionalmemoryfromtheresourcegroupsharedmemoryandglobalresourcegroupsharedmemory.
MEMORY_SPILL_RATIO
Thisparametersetsalimitfortheamountofmemoryaquerycanusebeforeitspillstodisk.Theparametervalueisexpressedasapercentageofthefixedmemoryallocation.Thedefaultis20andthevaluecanrangefrom0to100.Ahighervalueusesmorememory,butcanimprovequeryperformance.Atransactioncanoverridethisvaluebysettingthe memory_spill_ratio configurationparameterinthesession.
Whenaqueryexecutes,GreenplumDatabaseallocatesmemorytoitfromthefixedportionoftheresourcegroup’smemory.Ifthequeryneedsmorememoryandtheresourcegrouphasavailablesharedmemory,GreenplumDatabaseallocatesadditionalmemory.Ifinsufficientsharedmemoryisavailable,GreenplumDatabaseallocatesadditionalmemoryfromglobalsharedmemory,ifavailable.Iftherequiredmemoryisnotavailablethetransactionfails.
GreenplumDatabaseMemoryConfigurationParametersThefollowingGreenplumDatabaseconfigurationparametersaffectresourcegroupmemoryallocationandconcurrency.
gp_resource_group_memory_limit
ThisGreenplumDatabaseserverconfigurationparametersetsthepercentageofeachhost’ssystemmemorytobemanagedbyresourcegroups.Thedefaultis 0.7 (70%).Thismemoryisdividedequallyamongtheprimarysegmentsoneachhost,andfurtherdividedamongresourcegroupswiththe MEMORY_LIMIT resourcegroupparameter.Anymemorynotallocatedtoresourcegroupsbecomesglobalsharedmemoryavailabletoqueriesfromallresourcegroups.See gp_resource_group_memory_limit foracompletereferenceforthisparameter.
gp_resgroup_memory_policy
ThisparameterdetermineshowGreenplumDatabaseallocatesmemorytoqueryoperators.Thedefaultvalue, eager_free ,re-allocatesmemoryfromcompletedoperatorstooperatorslaterinthequeryplan.Thealternativevalueforthisparameter, auto ,allocatesafixedamountofmemorytooperatorsthatarenotmemory-intensiveandtheresttothememory-intensiveoperators.Thedefaultvalueisusuallytheoptimalsetting.Seegp_resgroup_memory_policy foracompletereferenceforthisparameter.
©CopyrightPivotalSoftwareInc,2013-2018 97 4.4.0
memory_spill_ratio
Atransactioncanoverridetheresourcegroup’s MEMORY_SPILL_RATIO valuebysettingthe memory_spill_ratio configurationparameterinthesession.Thevalueisapercentageofthefixedmemoryallocationfortransactionsintheresourcegroup,expressedasanintegerfrom0to100.Theperformanceofquerieswithlowmemoryrequirementscanbeimprovedbysettingthe memory_spill_ratio parameterinthesessiontoalowsetting,forexample0to2.See memory_spill_ratio formoreinformationaboutthisparameter.
©CopyrightPivotalSoftwareInc,2013-2018 98 4.4.0
QueryPlanExecutionTheGreenplumDatabaselegacyandGPORCAqueryoptimizersgenerateexecutionplansthatproducetheresultsrequestedbythequery.Aplanisasequenceofoperators,suchastablescans,joins,sorts,aggregates,anddatamotions.
WhenyouselectaqueryontheCommandCenterQueryMonitorview,aQueryDetailsviewpresentsagraphicalrepresentationoftheexecutionplan.
YoucanswitchbetweenthegraphicalandtexualrepresentationsofthequeryexecutionplanbyselectingthePlan&ProgresstabortheTexualPlantab.Inthetextualformat,eachplannodeisflaggedwithanarrow( -> ).Inthegraphicalview,thenodesarerepresentedbyboxesthatfillastheplanexecutes.
Aqueryexecutionplanexecutesfromthebottomup.Eachnodeintheplanperformsanoperationandpassesresultsuptothenextnodeintheplan.
The Optimizerstatus: lineontheTextualPlantabreportswhethertheexplainplanwasgeneratedusingtheGPORCAoptimizerorthelegacyqueryoptimizer.
SlicesandGangsSegmentscanworkonportionsofaqueryinparallel,eachsegmentexecutingoperatorsindependentlyontheirlocaldata.Whentheplanrequiresexchangingdatabetweensegments,adatamotionoperatorcoordinatesthedatatransferbetweensegments.Theplanisdividedinto“slices”wherethesedatamotionsoccur.
Adatamotionnodeinatextualqueryplanidentifiesthesliceandthenumberofsegmentsparticipatinginthemotion.
Example:
->BroadcastMotion4:4(slice2;segments:4)(cost=0.00..867.15rows=10000width=30)
Inabroadcastmotion,eachsegmentbroadcastsallofitsrowsforatableoverthenetworksothateverysegmenthasacompletecopyofthetable.Inthisexample,thebroadcastmotionmarksthecompletionof slice2 withfoursegmentssendingandfoursegmentsreceiving.
Eachsegmenthasoneormorebackendprocessesworkingonaslice.Backendprocessesworkingonthesameslicearecalleda“gang”.
OperatorsOperatorsareprocessesthattakeasinputdatabasetablesortheoutputfromotheroperators,andperformsomeactiontoproduceatransformedoutput.
ScanOperators
InitplanAquerythatrunsbeforethemainqueryisoptimizedtofindthepartitionstoscan.
SequentialscanTheoptimizermaychooseasequentialtablescanifthereisnoindexontheconditioncolumnorifmostrowsareexpectedtosatisifythecondition.Becauseeachsegmentscansanequalportionofthedatainparallelwithothersegments,atablescaninGreenplumDatabaseisveryefficient.Aqueryonapartitionedtablemaybeabletoeliminatepartitionstomakethescanevenfaster.
Append-onlyscanScansrowsinarow-oriented,append-optimizedtable.
Append-onlycolumnarscanScansrowsinacolumn-oriented,append-optimizedtable.
DynamictablescanScansselectedpartitionsinapartitionedtable.
FunctionscanAFunctionScannodeselectsthepartitionstoscan.Thefunctioncanbeoneofthefollowing:
©CopyrightPivotalSoftwareInc,2013-2018 99 4.4.0
gp_partition_expansion -choosesallnodes
gp_partition_selection -choosesapartitionwithanequalityexpression
gp_partition_inversion -choosespartitionswitharangeexpression
IndexscanScansaB-treeindexonatabletofindrows.Therowsarethenretrievedfromdisk.
BitmapindexscanABitmapIndexScanisanindexscanoptimizedbystoringrowsinabitmapinsteadofretrievingthemfromthetableimmediately.Whenthescaniscomplete,rowsinthebitmapareretrievedwithaBitmapHeapScanoperation.
BitmapAndandBitmapOrGeneratesanewbitmapbyrunninglogicalANDorORonmultiplebitmaps.
BitmapheapscanRetrievesrowsfromheapstorageusingabitmapgeneratedbyaBitmapindexscanorBitmapAndorBitmapOroperation.
NestedloopwithinnersequentialscanjoinForeachrowinthefirsttable,theoperatortestseveryrowinthesecondtablewithasequentialscan.
Onetablemustbebroadcastsothateverysegmentcancompareallrowsofonetablewiththerowsithasfromtheothertable.Thisisexpensiveandisbestusedonlyforsmalltables.
NestedloopwithinnerindexscanForeachrowinthefirsttable,theoperatorsearchesanindexonthesecondtable.
Onetablemustbebroadcastsothateverysegmentcancompareallrowsofonetablewiththerowsithasfromtheothertable.
AppendConcatenatesdatasets.Forexample,combinesrowsscannedfrommultiplepartitions.
FilterSelectsrowsusinga WHERE clause.
LimitLimitsthenumberofrowsreturned.
MaterializeSavesresultsfromasubselectsothatitisnotnecessarytoprocesstheinnertableforeveryrowintheoutertable.
JoinOperators
HashjoinCreatesahashtableonthejoinkeyofthesmallertable.Scansthelargertableandlooksupmatchingrowsinthehashtable.Hashjoinisveryfast.Thehashtableisheldinmemory,soahashjoincanusealotofmemory,dependingonthesizeofthesmallertable.
SortmergejoinThetablestobejoinedaresortedonthejoinattributeandthenscannedinparalleltofindthematchingvalues.Thisisagoodjoinmethodfortablesthataretoolargetouseahashjoin.
ProductjoinJoinseveryqualifyingrowinthefirsttablewitheveryqualifyingrowinthesecondtable.Thistypeofjoincanbeveryexpensiveifspillfilesmustbeused.
SortandAggregateOperators
SortSortsrowstoprepareforoperationssuchasanaggregationormergejoin.
GroupbyGroupsrowsbyoneormorecolumns.
Group/hashaggregate
©CopyrightPivotalSoftwareInc,2013-2018 100 4.4.0
Aggregatesrowsusingahash.
MotionOperatorsBroadcastmotion
Everysegmentsendsitsownlocaldatatoallothersegmentinstancessothateverysegmentinstancehasacompletelocalcopyofthetable.
RedistributionmotionSendsdatafromonetabletoanothersegmentsothatmatchingrowsarelocatedtogether,enablingalocaljoin.
GathermotionAllsegmentssendrowstothemasterwheretheyaregatheredintoasingleresultset.
DMLOperatorsAssert
Performsconstraintschecking.
SplitUsedforupdateoperations.
©CopyrightPivotalSoftwareInc,2013-2018 101 4.4.0