Greenplum數(shù)據(jù)庫基礎培訓課件

Greenplum數(shù)據(jù)庫基礎培訓Greenplum數(shù)據(jù)庫基礎培訓1Greenplum數(shù)據(jù)庫海量并行處理(MassivelyParallelProcessing)DBMS

基于PostgreSQL8.2相同的客戶端功能增加支持并行處理的技術增加支持數(shù)據(jù)倉庫和BI的特性外部表(externaltables)/并行加載(parallelloading)資源管理查詢優(yōu)化器增強(queryoptimizerenhancements)Greenplum數(shù)據(jù)庫海量并行處理(Massively2S1002NetworkConfigurationS1002NetworkConfiguration3Greenplum基本體系架構Greenplum基本體系架構4客戶端接口和程序psqlpgAdminIIIODBC/DatadirectJDBCPerlDBIPythonlibpqOLEDB客戶端接口和程序psql5MasterHost訪問系統(tǒng)的入口數(shù)據(jù)庫偵聽進程(postgres)處理所有用戶連接建立查詢計劃協(xié)調工作處理過程管理工具系統(tǒng)目錄表和元數(shù)據(jù)（數(shù)據(jù)字典）不存放任何用戶數(shù)據(jù)MasterHost訪問系統(tǒng)的入口6每段（Segment）存放一部分用戶數(shù)據(jù)一個系統(tǒng)可以有多段用戶不能直接存取訪問所有對段的訪問都經(jīng)過Master數(shù)據(jù)庫監(jiān)聽進程(postgres)監(jiān)聽來自Master的連接Segment每段（Segment）存放一部分用戶數(shù)據(jù)Segment7

Greenplum數(shù)據(jù)庫之間的連接層進程間協(xié)調和管理基于千兆以太網(wǎng)架構屬于系統(tǒng)內部私網(wǎng)配置支持兩種協(xié)議：TCPorUDPInterconnectGreenplum數(shù)據(jù)庫之間的連接層Interconnec8Greenplum高可用性體系架構Greenplum高可用性體系架構9Standby節(jié)點用于當Master節(jié)點損壞時提供Master服務Standby實時與Master節(jié)點的Catalog和事務日志保持同步Master/Standby鏡像保護Standby節(jié)點用于當Master節(jié)點損壞時提供M10每個Segment的數(shù)據(jù)冗余存放在另一個Segment上，數(shù)據(jù)實時同步當PrimarySegment失敗時，MirrorSegment將自動提供服務PrimarySegment恢復正常后，使用gprecoverseg–F同步數(shù)據(jù)。數(shù)據(jù)冗余-Segment鏡像保護每個Segment的數(shù)據(jù)冗余存放在另一個Segment上，數(shù)11

Hash分布CREATETABLE…DISTRIBUTEDBY(column[,…])同樣數(shù)值的內容被分配到同一個Segment上

循環(huán)分布

CREATETABLE…DISTRIBUTEDRANDOMLY具有同樣數(shù)值的行內容并不一定在同一個Segment上表分布的策略-并行計算的基礎表分布的策略-并行計算的基礎Hash分布表分布的策略-并行計算的基礎表分布的策略-并行12查詢命令的執(zhí)行舉例說明：按卡號、客戶號、機構的分布方式優(yōu)劣點分布存儲查詢命令的執(zhí)行舉例說明：按卡號、客戶號、機構的分布方式優(yōu)劣點13查詢命令的執(zhí)行查詢命令的執(zhí)行查詢命令的執(zhí)行查詢命令的執(zhí)行14SQL查詢處理機制SQL查詢處理機制15SELECTcustomer,amountFROMsalesJOINcustomerUSING(cust_id)WHEREdate=04302008;并行查詢計劃SELECTcustomer,amountFROMs16表分區(qū)的概念將一張大表邏輯性地分成多個部分，如按照分區(qū)條件進行查詢，將減少數(shù)據(jù)的掃描范圍，提高系統(tǒng)性能。提高對于特定類型數(shù)據(jù)的查詢速度和性能也可以更方便數(shù)據(jù)庫的維護和更新兩種類型:Range分區(qū)(日期范圍或數(shù)字范圍)/如日期、價格等List分區(qū),例如地區(qū)、產品等Greenplum中的表分區(qū)在使用中具有總表的繼承性，并通過Check參數(shù)指定相應的子表分區(qū)的子表依然根據(jù)分布策略分布在各segment上分區(qū)是一種非常有用的優(yōu)化措施，例如一年的交易按交易日期分區(qū)后，查詢一天的交易性能將提高365倍?。。”矸謪^(qū)的概念將一張大表邏輯性地分成多個部分，如按照分區(qū)條件進17Segment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DJan2005Feb2005Mar2005Apr2005May2005Jun2005Jul2005Aug2005Sep2005Oct2005Nov2005Dec2005每個分區(qū)表的數(shù)據(jù)平均分布到各個節(jié)點表分區(qū)可減少數(shù)據(jù)的搜索范圍，提高查詢性能DataDistribution&PartitioningSegment1ASegment1BSegment1C18Segment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSELECTCOUNT(*)FROMordersWHEREorder_date>=‘Oct202005’ANDorder_date<‘Oct272005’VSHashDistributionHashDistribution+TablePartitioningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DFullTableScanVS.PartitionPruningSegment1ASegment1BSegment1C19表分區(qū)示意圖表分區(qū)示意圖20壓縮存儲壓縮存儲支持ZLIB和QUICKLZ方式的壓縮,壓縮比可到10:1壓縮表只能是AppendOnly方式壓縮數(shù)據(jù)不一定會帶來性能的下降，壓縮表將消耗CPU資源，而減少I/O資源占用語法CREATETABLEfoo(aint,btext)

WITH(appendonly=true,compresstype=zlib,compresslevel=5);壓縮存儲壓縮存儲21行列存儲Greenplum支持行或列存儲模式列模式目前只支持AppendOnly如果常用的查詢只取表中少量字段，則列模式效率更高，如查詢需要取表中的大量字段，行模式效率更高語法：

CREATETABLEsales2(LIKEsales)WITH(appendonly=true,orientation=column);效率比較測試：測試1：需要去表中所有字段，此時行存儲更快。

select*fromdw_ods.s1_sema_scmcaccp_rowwherecrdacct_nbr='4033930000166380411';

41秒

select*fromdw_ods.s1_sema_scmcaccp_colwherecrdacct_nbr='4033930000166380411';

116秒測試2：只取表中少量字段，列存儲更快

selectcrdacct_statusfromdw_ods.s1_sema_scmcaccp_rowwherecrdacct_nbr='4033930000166380411';

35秒

selectcrdacct_statusfromdw_ods.s1_sema_scmcaccp_colwherecrdacct_nbr='4033930000166380411';

3秒行列存儲Greenplum支持行或列存儲模式22外部表加載外部表的特征Read-only數(shù)據(jù)存放在數(shù)據(jù)庫外可執(zhí)行SELECT,JOIN,SORT等命令，類似正規(guī)表的操作外部表的優(yōu)點并行方式加載ETL的靈活性格式錯誤行的容錯處理支持多種數(shù)據(jù)源兩種方式ExternalTables:基于文件WebTables:基于URL或指令外部表加載外部表的特征23基于外部表的高速數(shù)據(jù)加載利用并行數(shù)據(jù)流引擎，Greenplum可以直接用SQL操作外部表數(shù)據(jù)加載完全并行Master主機Segment主機內部互聯(lián)網(wǎng)—千兆以太網(wǎng)交換機gpfdistgpfdistSegment主機Segment主機Segment主機外部表文件外部表文件ETL服務器內部網(wǎng)絡基于外部表的高速數(shù)據(jù)加載利用并行數(shù)據(jù)流引擎，Greenplu24外部表加載的特征并行數(shù)據(jù)加載提供最好的性能能夠處理遠程存儲的文件采用HTTP協(xié)議200MB/sdatadistributionratepergpfdistgpfdist文件分發(fā)守護進程啟動:

gpfdist-d/var/load_files/expenses-p8080-l/home/gpadmin/log&外部表定義:CREATEEXTERNALTABLEext_expenses

(nametext,datedate,

amountfloat4,descriptiontext)LOCATION('gpfdist//etlhost:8081/*','gpfdist//etlhost:8082/*')FORMAT'TEXT'(DELIMITER'|')ENCODING’UTF-8’LOGERRORSINTOext_expenses_loaderrorsSEGMENTREJECTLIMIT10000ROWS;外部表加載的特征并行數(shù)據(jù)加載提供最好的性能25Loadgoodrowsandcatchpoorlyformattedrows,suchas:rowswithmissingorextraattributesrowswithattributesofthewrongdatatyperowswithinvalidclientencodingsequencesDoesnotapplytoconstrainterrors:PRIMARYKEY,NOTNULL,CHECKorUNIQUEconstraintsOptionalerrorhandlingclauseforexternaltables:[LOGERRORSINTOerror_table]SEGMENTREJECTLIMITcount[ROWS|PERCENT](PERCENTbasedongp_reject_percent_thresholdparameter)ExampleCREATEEXTERNALTABLEext_customer(idint,nametext,sponsortext)LOCATION('gpfdist://filehost:8081/*.txt')FORMAT'TEXT'(DELIMITER'|'NULL'')LOGERRORSINTOerr_customerSEGMENTREJECTLIMIT5ROWS;外部表加載異常處理Loadgoodrowsandcatchpoorl26

DataresidesoutsidethedatabaseNodatabasestatisticsforexternaltabledataNotmeantforfrequentorad-hocaccessCanmanuallysetroughstatisticsinpg_class:UPDATEpg_class

SETreltuples=400000,relpages=400

WHERErelname='myexttable';外部表靜態(tài)統(tǒng)計優(yōu)化Dataresidesoutsidethedata27PostgreSQLcommandSupportloadingandunloadingOptimizedforloadingalargenumberofrowsLoadsallrowsinonecommand(notparallel)LoadsdatafromafileorfromstandardinputSupportserrorhandlingasdoesexternaltablesEXAMPLECOPYmytableFROM'/data/myfile.csv'WITHCSVHEADER;（文件生成在Master）\COPYmytableFROM‘/data/myfile.csv’WITHCSVHEADER;（文件生成在本地）COPYcountryFROM'/data/gpdb/country_data'WITHDELIMITER'|'LOGERRORSINTOerr_countrySEGMENTREJECTLIMIT10ROWS;COPYSQL命令PostgreSQLcommandCOPYSQL命令28

DropindexesandrecreateafterloadIncreasemaintenance_work_memparametertospeedupCREATEINDEXoperations

RunANALYZEafterloadRunVACUUMafterloaderrors、delete、upate。。。DonotuseODBCINSERTtoloadlargevolumesofdata數(shù)據(jù)加載性能優(yōu)化提示Dropindexesandrecreateaft29限制查詢隊列的激活數(shù)防止系統(tǒng)過載(CPU,diskI/O,memory)資源負載管理(WorkloadManagement)限制查詢隊列的激活數(shù)資源負載管理(WorkloadMan30資源隊列的兩種典型管理方式ResourceQueueLimitsACTIVETHRESHOLD

EXAMPLE:CREATERESOURCEQUEUEadhocACTIVETHRESHOLD10IGNORETHRESHOLD1000.0;COSTTHRESHOLD

EXAMPLES:CREATERESOURCEQUEUEbatch1COSTTHRESHOLD1000000.0NOOVERCOMMIT;

CREATERESOURCEQUEUEbatch1COSTTHRESHOLD1e+6;資源隊列的兩種典型管理方式ResourceQueueLi31Greenplum性能監(jiān)控器Greenplum性能監(jiān)控器32

Highlyinteractiveweb-basedperformancemonitoringReal-timeandhistoricviewsof:ResourceutilizationQueriesandqueryinternalsDashboardGreenplumPerformanceMonitorHighlyinteractiveweb-based33SystemMetricsGreenplumPerformanceMonitorSystemMetricsGreenplumPerfor34QueryMonitorGreenplumPerformanceMonitorQueryMonitorGreenplumPerform35BackupsandRestores

Parallelbackups(gp_dump)Parallelrestores(gp_restore)

Automatingdump/restores(gpcrondump,gpdbrestore)Non-parallelbackupsandrestores(pg_dump/pg_restore/psql)備份與恢復BackupsandRestoresParallel36用于在同構環(huán)境間遷移數(shù)據(jù)結構、數(shù)據(jù)、function備份恢復并行備份和恢復（gp_dump/gp_restore）用于在同構環(huán)境間遷移數(shù)據(jù)結構、數(shù)據(jù)、function備份恢復37用于在異構環(huán)境間遷移數(shù)據(jù)結構、數(shù)據(jù)、function串行備份和恢復（pg_dump/pg_restore）用于在異構環(huán)境間遷移數(shù)據(jù)結構、數(shù)據(jù)、function串行備份38

EachactivesegmentisdumpedinparallelDumpfilescreatedinsegmentdatadirectorybydefaultSupportscompression(gzip)EnsuresufficientdiskspacefordumpfilesAbackupsetisidentifiedbyatimestampkeyExample:Backupadatabase:gp_dumpgpdbBackupadatabase,andcreatedumpfilesinacentralizedlocationonallhosts:gp_dump--gp-d=/home/gpadmin/backupsgpdbBackupaparticularschemaonly:gp_dump-nmyschemamydatabaseBackupasinglesegmentinstanceonly(bynotingthedbidofthesegmentinstance):gp_dump--gp-s=i[5]gpdbRunningaParallelBackup(gp_dump)Eachactivesegmentisdumped39Onthemasterhost

gp_catalog_1_<dbid>_<timestamp>

gp_cdatabase_1_<dbid>_<timestamp>

gp_dump_1_<dbid>_<timestamp>

gp_dump_status_1_<dbid>_<timestamp>

Onthesegmenthosts

gp_dump_0_<dbid>_<timestamp>gp_dump_status_0_<dbid>_<timestamp>DumpFilesCreatedbygp_dumpOnthemasterhostDumpFiles40

Usegp_restorecommandNeedtimestampkeyfromgp_dumpMakesuredumpfilesareplacedoncorrectsegmenthostsMakesuredatabaseexistsbeforerestoringDatabase-levelserverconfigurationsettingsarenotrestoredExamplesRestoreanGreenplumdatabaseusingbackupfilescreatedbygp_dump:gp_restore--gp-k=2005103112453-dgpdbRestoreasinglesegmentinstanceonly(bynotingthedbidofthesegmentinstance):gp_restore--gp-k=2005103112453-dgpdb--gp-s=i[5]RunningaParallelRestore(gp_restore)Usegp_restorecommandRunning41gpcrondumpCallstogp_dumpCanbecalleddirectlyorcanscheduleusingCRONSendemailnotificationsFlexibledumpoptionsCancopyconfigurationfilesCandumpsystemcatalogsCandumpglobalobjectsCanincludeapost-dumpscriptgpdbrestoreRestoresfromgpcrondumpfilesCanrestorefromanarchivehost–noneedtopre-placedumpfilesonsegmentsSchedulingRoutineBackupsgpcrondumpSchedulingRoutine42

Greenplumalsosupportspg_dumpandpg_restore

Usefulformigratingdatato/fromotherDBMSpg_dumpcreatesasingledumpfileCanbeslowonverylargedatabasesRunatlow-usagetimesSupportscompressionCandumpdataasINSERTorCOPYcommandsgp-syntaxoptionincludesDISTRIBUTEDBYstatementsinDDLNon-ParallelBackupsandRestoresGreenplumalsosupportspg_d43DumpadatabasecalledmydbintoaSQL-scriptfile: pg_dumpmydb>db.sqlToreloadsuchascriptintoa(freshlycreated)databasenamednewdb: psql-dnewdb-fdb.sqlDumpaGreenplumdatabaseintarfileformatandincludedistributionpolicyinformation: pg_dump-Ft--gp-syntaxmydb>db.tarTodumpadatabaseintoacustom-formatarchivefile: pg_dump-Fcmydb>db.dumpToreloadanarchivefileintoa(freshlycreated)databasenamednewdb: pg_restore-dnewdbdb.dumpTodumpasingletablenamedmytab: pg_dump-tmytabmydb>db.sqlTospecifyanupper-caseormixed-casenamein-tandrelatedswitches,youneedtodouble-quotethename;elseitwillbefoldedtolowercase.Butdoublequotesarespecialtotheshell,sointurntheymustbequoted.Thus,todumpasingletablewithamixed-casename,youneedsomethinglike: pg_dump-t'"MixedCaseName"'mydb>mytab.sqlNon-ParallelBackupsandRestoresExampleDumpadatabasecalledmydbi44客戶端工具pgAdmin3圖形化管理和SQL執(zhí)行/分析/監(jiān)控工具psql行命令操作和管理工具客戶端工具pgAdmin345pgAdmin3forGPDBpgAdmin3istheleadinggraphicalOpenSourcemanagement,developmentandadministrationtoolforPostgreSQLGreenplumhascontributedextensiveGPDB-specificenhancementsWithGPDB3.3,GreenplumshipsandsupportsthistoolpgAdmin3forGPDBpgAdmin3ist46pgAdmin3forGPDBpgAdmin3forGPDB47pgAdmin3forGPDB監(jiān)控活動session,同SQL：select*frompg_stat_activity;監(jiān)控鎖，從pg_lock中獲取信息可以停止正在運行的SQLpgAdmin3forGPDB監(jiān)控活動session,48PSQLConnectthroughthemasterConnectioninformationdatabasename(-d|PGDATABASE)masterhostname(-h|PGHOST)masterport(-p|PGPORT)username(-U|PGUSER)Firsttimeconnectionstemplate1databasedefaultsuperuseraccount(gpadmin)PSQLConnectthroughthemaster49IssuingSQLStatementsInteractivemode

psqlmydatabase

mydatabase=#SELECT*FROMfoo;

Non-interactivemode(singlecommand)

psqlmydatabase–ac“SELECT*FROMfoo;”Non-interactivemode(multiplecommands)

psqlmydatabase–af/home/lab1/sql/createdb.sql(Usesemi-colon(;)todenoteendofastatement)IssuingSQLStatementsInteract50CommonPSQLMetaCommands\?(helponpsqlmeta-commands)\h(helponSQLcommandsyntax)\dt(showtables)\dtS(showsystemtables)\dgor\du(showroles)\l(showdatabases)\cdb_name(connecttothisdatabase)\q(quitpsql)\!(Enterintoshellmode)\df(showfunction)\dn(showschema)Setsearch_path=…\timingCommonPSQLMetaCommands\?(h51postgresql.confLocalLocalLocalLocal參數(shù)參考Adminguide重要參數(shù):max_connection,share_buff,work_mem…Local變量的修改，如max_stack_depth需要修改所有segment上的valueLocal,Global,andMaster-Onlypostgresql.confLocalLocalLocal52postgresql.conffileLocatedinmasterorsegmentinstance’sdatadirectoryUsedtosetconfigurationparametersonthesystemlevelParametersthatareusingthedefaultsettingarecommentedout(#)Requiresarestart(orreloadusinggpstop-u)forchangestotakeeffectpostgresql.conffileLocatedin53ViewingParameterSettingsViewaspecificparametersetting

Example:SHOWsearch_path;

Viewallparametersettings

Example:SHOWALL;Setparameter

Example:setsearch_path=public setclient_encoding=gb18030

ViewingParameterSettingsView54ConfiguringHost-BasedAuthentication客戶端授權是否允許從某個客戶端的連接用戶是否可以連接到所請求的數(shù)據(jù)庫pg_hba.conffile基于hostaddress,database,and/orDBuseraccount控制權限位于master和segment實例的數(shù)據(jù)目錄中系統(tǒng)初始化時進行default配置ConfiguringHost-BasedAuthent55DefaultMasterHostpg_hba.confLocalconnectionsallowedforGreenplumsuperuserRemoteconnectionsnotallowedEXAMPLE#TYPEDATABASEUSERCIDR-ADDRESSMETHODlocalallgpadminidentsameuserlocalallallidentsameuserhostallgpadmin/0trust

DefaultMasterHostpg_hba.con56配置pg_hba.confEXAMPLE#TYPEDATABASEUSERCIDR-ADDRESSMETHODlocal all alltrusthost allall /32trusthost allall ::1/128trusthost allall 06/32trust

host carddw etl 2/32 md5

host gpadmin all /24 md5gpstop-u可與在不重啟數(shù)據(jù)庫方式下，讓設置生效配置pg_hba.confEXAMPLE57SQL語法具體參考《Gpsqllanguage》：注意事項：DELETE,UPDATE在兩表關聯(lián)時，兩個表的distribution必須一致。如：deletefromtableausingtablebwheretablea.id=tableb.id

UPDATEtableaasa SETdesc=b.desc FROMtablebasb WHEREa.id=b.id以上操作tablea,tableb必須使用相同的分布，必要時可以使用altertablesetdistribution進行分布修改。SQL語法具體參考《Gpsqllanguage》：58數(shù)據(jù)類型常用數(shù)據(jù)類型CHAR,VARCHAR,TEXTSmallint,integer,bigintNumeric,real,doubleprecisionTimestamp,date,timeBooleanArray類型。如integer[]其它數(shù)據(jù)類型請參考數(shù)據(jù)類型常用數(shù)據(jù)類型59Allsystemcatalogsinpg_catalogschemaStandardPostgreSQLsystemcatalogs(pg_*)Greenplum-specificcatalogs:gp_configurationgp_distribution_policygp_idgp_version_at_initdbpg_resqueuepg_exttablepg_tablespg_classpg_stat_activityTolistallsystemcatalogtablesinpsql:\dtSTolistallsystemviewsinpsql:\dvS其它catalog參考SystemCatalogTablesandViewsAllsystemcatalogsinpg_cata60函數(shù)日期函數(shù)Extract（day|month|year。。。Fromdate);Selectdate+‘1day’::interval,date+‘1month’::intervalSELECTdate_part('day',TIMESTAMP'2001-02-1620:38:40');Result:16SELECTdate_trunc('hour',TIMESTAMP'2001-02-1620:38:40');Result:2001-02-1620:00:00pg_sleep(seconds);系統(tǒng)日期變量Current_dateCurrent_timeCurrent_timestampNow()Timeofday()在事務中發(fā)生變化，以上函數(shù)在事務中不變函數(shù)日期函數(shù)61函數(shù)字符串處理函數(shù)Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char,||(字符串連接)substring(stringfrompattern～/～*，like，simillarto(模式匹配)其它雜類Case。。。When/Coalescenullifgenerate_seriesIn/notin/exists/any/all各類函數(shù)參考：函數(shù)字符串處理函數(shù)各類函數(shù)參考：62存儲過程Greenplum支持SQL/PYTHON/PERL/C語言構建函數(shù)，以下著重介紹SQL存儲過程。一個存儲過程就是一個事務，包括對子過程的調用都在一個事務內存儲過程結構：CREATEFUNCTIONsomefunc()RETURNSintegerAS$$DECLAREquantityinteger:=30;BEGINRETURN;END;$$LANGUAGEplpgsql;賦值給一個變量或行/記錄賦值用下面方法：identifier:=expression例子：user_id:=20;執(zhí)行一個沒有結果的查詢:PERFORMquery;一個例子：PERFORMcreate_mv('cs_session_page_requests_mv',my_query);存儲過程請參考：存儲過程Greenplum支持SQL/PYTHON/PERL63存儲過程動態(tài)SQLEXECUTEcommand-string[INTO[STRICT]target];SELECTINTOExample：SELECTIDINTOVAR_IDFROMTABLEA獲取結果狀態(tài)GETDIAGNOSTICSvariable=item[,...];一個例子： ·GETDIAGNOSTICSinteger_var=ROW_COUNT;SQL返回變量SQLERRM，SQLSTATE控制結構IF...THEN...ELSEIF...THEN...ELSELOOP,EXIT,CONTINUE,WHILE,FOR從函數(shù)返回有兩個命令可以用來從函數(shù)中返回數(shù)據(jù)：RETURN和RETURNNEXT。Syntax：RETURNexpression;設置回調EXECSQLWHENEVERcondition

action;condition可以是下列之一：SQLERROR，SQLWARNING，NOTFOUND存儲過程動態(tài)SQL64存儲過程異常處理EXCEPTIONWHENunique_violationTHEN--donothingEND;忽略錯誤：EXCEPTIONWHENOTHERSTHENRAISENOTICE'anEXCEPTIONisabouttoberaised';RAISEEXCEPTION'NUM:%,DETAILS:%',SQLSTATE,SQLERRM;END;錯誤和消息RAISElevel'format'[,expression[,...]];Level:Info:信息輸入Notice:信息提示Exception:產生一個例外，將退出存儲過程Example:RAISENOTICE'Callingcs_create_job(%)',v_job_id;存儲過程異常處理65PerformanceTuning

IntroductiontoperformancetuningCommonperformanceproblemsTrackingdownaperformanceproblemQueryprofiling(EXPLAIN,EXPLAINANALYZE)Querytuning

我的經(jīng)驗：性能調優(yōu)PerformanceTuningIntroducti66Setperformanceexpectationsacceptableresponsetimes,queriesperminute,etc.BenchmarksKnowyourbaselinehardwareperformancethroughput/capacityKnowyourworkloadheavyusagetimesresourcecontentiondatacontentionFocusyouroptimizationsApproachingaPerformanceTuningInitiativeSetperformanceexpectationsAp67HardwareIssues/FailedSegmentsResourceAllocationContentionBetweenConcurrentWorkloadsInaccurateDatabaseStatisticsUnevenDataDistributionSQLFormulationDatabaseDesignCommonCausesofPerformanceIssuesHardwareIssues/FailedSegme68DiskfailuresHostfailuresNetworkfailuresOSnottunedforGreenplumDiskCapacity70%maximumrecommendedVACUUMafterupdates,deletesandloadsVACUUMconfigurationparametersmax_fsm_relations=tables+indexes+systemtablesmax_fsm_pages=16*max_fsm_relationsHardwareIssuesDiskfailuresHardwareIssues69

GreenplumresourcequeueslimitactivequeriesinthesystemlimitthesizeofaqueryaparticularusercanrunPerformadmintasksatlowusagetimesDataloading,ETLVACUUM,ANALYZEBackupsDesignapplicationstopreventlockconflictsConcurrentsessionsnotupdatingthesamedataatthesametimeResource-relatedConfigurationParameterswork_mem=32MBmaintenance_work_mem=64MBshared_buffers=125MBResourceAllocationandContentionGreenplumresourcequeuesReso70DatabasestatisticsusedbythequeryplannerRunANALYZEafterDataloadsRestoresfrombackupsChangestoschema(addingindexes,etc.)Inserts,Updates,orDeletesCanconfigurestatisticscollectiondefault_statistics_target=25gp_analyze_relative_error=.25onspecifictablecolumnsALTERTABLEnameALTERcolumnSETSTATISTICS#DatabaseStatistics(ANALYZE)Databasestatisticsusedbyth71TableDistributionKeyConsiderationsEvendatadistributionLocalvs.distributedoperationsEvenqueryprocessing

Checkingfordataskewgpskew–tschema.tableUnixsystemutilities(gpssh)：gpssh–fseg_host->sar1100RebalancingaTableCREATETABLEsales_temp(LIKEsales)DISTRIBUTEDBY(date,total,customer);

INSERTINTOsales_tempSELECT*FROMsales;

DROPsales;

ALTERTABLEsales_tempRENAMETOsales;GreenplumDataDistributionTableDistributionKeyConside72

DataTypeSelectionsmallestsizepossibletofitdataINTEGER,notNUMERIC(11,2)forintegerfieldssamedatatypeacrosstablesforjoincolumnsconsiderhashofwidejoinkeys,usingBYTEAinsteadofCHAR(100)varcharortextforcharacterdataDenormalization(starschema)TablePartitioningDatabaseDesignDataTypeSelectionDatabaseD73UsesparinglyinGreenplumDatabaseTryworkloadwithoutindexesfirstCanimproveperformanceofOLTPtypeworkloadsOtherindexconsiderations:AvoidonfrequentlyupdatedcolumnsAvoidoverlappingindexesUsebitmapindexeswhereapplicableinsteadofB-treeDropindexesforloadsConsideraclusteredindexConfiguringIndexUsage:enable_indexscan=on|offDatabaseDesign-IndexesUsesparinglyinGreenplumDat74GeneralConsiderationsKnowyourdataMinimizereturnedrowsAvoidunnecessarycolumns/tablesinresultsetAvoidlargesortsifpossibleMatchdatatypesinjoinsGreenplum-specificConsiderationsJoinoncommondistributionkeycolumnswhenpossibleConsiderdatadistributionpolicyandquerypredicatesSQLFormulationGeneralConsiderationsSQLForm75SystemCatalogTablesandViewspg_stat_activitypg_locks/pg_classDatabaseLogsLocatedinmaster(andsegment)datadirectoriesUNIXsystemutilities(gpssh)TrackingDownaPerformanceProblemSystemCatalogTablesandView76ExaminequeryplanstoidentifytuningopportunitiesWhattolookfor?PlanoperationsthataretakingexceptionallylongAretheplanner’sestimatesclosetoreality?(EXPLAINANALYZE)Istheplannerapplyingselectivepredicatesearly?Istheplannerchoosingthebestjoinorder?Istheplannerselectivelyscanningpartitionedtables?Istheplannerchoosinghashaggregateandhashjoinoperationswhereapplicable?Istheresufficientworkmemory?QueryProfilingExaminequeryplanstoidentif77TheQueryProcessTheQueryProcess78ToseetheplanforaqueryEXPLAIN<query>EXPLAINANALYZE<query>QueryplansarereadfrombottomtotopMotions(Gather,Redistribute,Broadcast)Joins,sorts,aggregationsTablescansThefollowingmetricsaregivenforeachoperationcost(unitsofdiskpagefetches)rows(rowsoutputbythisnode)width(bytesoftherowsproducedbythisnode)ViewingtheQueryPlanToseetheplanforaqueryVie79EXPLAINSELECT*FROMnamesWHEREname='Joelle';

QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)->SeqScanon'names'(cost=0.00..20.88rows=1width=13)Filter:name::text~~'Joelle'::textReadingEXPLAINOutputEXPLAINSELECT*FROMnamesWH80EXPLAINANALYZESELECT*FROMnamesWHEREname='Joelle';QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)

recv:Total1rowswith0.305mstofirstrow,0.537mstoend.->SeqScanon'names'(cost=0.00..20.88rows=1width=13)

Total1rows(seg0)with0.255mstofirstrow,0.486mstoend.Filter:name::text~~'Joelle'::text22.548mselapsed

ReadingEXPLAINANALYZEOutputEXPLAINANALYZESELECT*FROM81MVCC(multi-versionconcurrencycontrol)GreenplumsupportsalltransactionisolationlevelsdefinedintheSQLstandardINSERT/COPYacquirelocksattherow-levelUPDATE/DELETEacquirelocksatthetable-levelCanuseLOCKcommandtoacquirespecificlocksTransactionConcurrencyControlMVCC(multi-versionconcurrenc82ACCESSSHARE(SELECT,ANALYZE)ROWSHARE(SELECTFORUPDATE,SELECTFORSHARE)ROWEXCLUSIVE(INSERT,COPY)SHAREUPDATE

EXCLUSIVE(VACUUM)SHARE(CREATEINDEX)SHAREROWEXCLUSIVEEXCLUSIVE(UPDATE/DELETE)ACCESSEXCLUSIVE(ALTERTABLE,DROPTABLE,REINDEX,CLUSTER,andVACUUMFULL)Table-levelLockModesACCESSSHARE(SELECT,ANALYZE)83Lockconflictscausedby:ConcurrenttransactionsaccessingthesameobjectResourcequeuelocksTransactiondeadlocksbetweensegments(rare)Querypg_lockssystemtabletoseecurrentlocks

EXAMPLE:SELECTlocktype,database,c.relname,l.relation,l.transactionid,l.transaction,l.pid,l.mode,l.granted,a.current_queryFROMpg_locksl,pg_classc,pg_stat_activityaWHEREl.relation=c.oidANDl.pid=cpidORDERBYc.relname;CheckingforLockConflictsLockconflictscausedby:Check84Transactionsbundlemultiplestatementsintoone‘all-or-nothing’operationTransactioncommandsBEGINorSTARTTRANSACTIONENDorCOMMITROLLBACKSAVEPOINTandROLLBACKTOSAVEPOINTAutocommitmodeinpsql\setautocommiton|offTwo-phasecommittransactionsnotsupportedPREPARETRANSACTIONCOMMITPREPAREDROLLBACKPREPAREDAboutTransactionsinGreenplumDatabaseTransactionsbundlemultiples85PhysicalstorageServerprocessesDatabaseInternalsPhysicalstorageDatabaseInte86CollectsinformationaboutdatabaseactivityServerconfigurationparametersstart_stats_collector=onstats_block_level=offstats_row_level=offstats_queue_level=offstats_command_string=onToseestatisticsviewsandtablesincatalog:\dtvSpg_stat*StatisticsCollectorCollectsinformationaboutdat87PhysicalStorage-DataDirectoryFileStructurePhysicalStorage-DataDirect88Linux:psax|greppostgresSolaris:ps–ef|greppostgres

pargs<process_id>GreenplumMasterInstancepostgresdatabaselistenerprocesspostgres:<sub_process_name>postgres:seqserverprocesspostgres:<user><database><con#><host><cmd#><slice#>GreenplumSegmentInstancepostgresdatabaselistenerprocesspostgres:<sub_process_name>ServerProcessesLinux:psax|greppostgresSe89附錄Greenplum數(shù)據(jù)庫基礎培訓課件90AboutWindowFunctionsConstructingaWindowSpecificationOVERclauseWINDOWclauseBuilt-inWindowFunctionsOLAPWindowingExtensionsAboutWindowFunctionsOLAPWin91NewclassoffunctionallowedonlyintheSELECTlistReturnsavalueperrow(unlikeaggregatefunctions)Resultsinterpretedintermsofthecurrentrowanditscorrespondingwindowpartitionorframe

CharacterizedbytheuseoftheOVERclauseDefinesthewindowpartitions(groupsofrows)toapplythefunctionDefinesorderingofdatawithinawindowDefinesthepositionalorlogicalframingofarowinrespecttoitswindowAboutWindowFunctionsNewclassoffunctionallowed92AllwindowfunctionshaveanOVER()clauseSpecifiesthe‘window’ofdatatowhichthefunctionappliesDefines:

Windowpartitions(PARTITIONBYclause)Orderingwithinawindowpartition(ORDERBYclause)Framingwithinawindowpartition(ROWS/RANGEclauses)DefiningWindowSpecifications(OVERClause)AllwindowfunctionshaveanO93SELECT*,row_number()OVER()FROMsaleORDERBYcn;SELECT*,row_number()OVER(PARTITIONBYcn)FROMsaleORDERBYcn;row_number|cn|vn|pn|dt|qty|prc++++++1|1|10|200|1401-03-01|1|02|1|30|300|14