Greenplum数据库基础培训 (2)ppt课件

1、Greenplum 数据库根底培训Wei.LiSenior Data ArchitectureAlpine Solution2021/05Greenplum数据库海量并行处置 (Massively Parallel Processing) DBMS 基于 PostgreSQL 8.2一样的客户端功能添加支持并行处置的技术添加支持数据仓库和BI的特性外部表(external tables)/并行加载(parallel loading)资源管理查询优化器加强(query optimizer enhancements)S1002 Network ConfigurationGreenplum 根本体系

2、架构客户端接口和程序 psql pgAdmin III ODBC/Datadirect JDBC Perl DBI Python libpqOLE DBMaster Host 访问系统的入口 数据库侦听进程 (postgres) 处置一切用户衔接 建立查询方案 协调任务处置过程 管理工具 系统目录表和元数据数据字典 不存放任何用户数据 每段Segment存放一部分用户数据 一个系统可以有多段 用户不能直接存取访问 一切对段的访问都经过Master 数据库监听进程(postgres)监听来自Master的衔接Segment Greenplum数据库之间的衔接层 进程间协调和管理 基于千兆以太网架

3、构 属于系统内部私网配置 支持两种协议：TCP or UDPInterconnectGreenplum 高可用性体系架构Standby 节点用于当 Master 节点损坏时提供 Master 效力Standby 实时与 Master 节点的 Catalog 和事务日志坚持同步Master/Standby 镜像维护每个Segment的数据冗余存放在另一个Segment上，数据实时同步当Primary Segment失败时，Mirror Segment将自动提供效力Primary Segment恢复正常后，运用gprecoverseg F 同步数据。数据冗余-Segment 镜像维护 Hash分布

4、 CREATE TABLE DISTRIBUTED BY (column ,) 同样数值的内容被分配到同一个Segment上 循环分布 CREATE TABLE DISTRIBUTED RANDOMLY 具有同样数值的行内容并不一定在同一个Segment上表分布的战略-并行计算的根底表分布的战略-并行计算的根底查询命令的执行举例阐明：按卡号、客户号、机构的分布方式优劣点分布存储查询命令的执行查询命令的执行SQL查询处置机制SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date=04302021;并行

5、查询方案表分区的概念将一张大表逻辑性地分成多个部分，如按照分区条件进展查询，将减少数据的扫描范围，提高系统性能。提高对于特定类型数据的查询速度和性能也可以更方便数据库的维护和更新两种类型:Range分区 (日期范围或数字范围)/如日期、价钱等List 分区,例如地域、产品等Greenplum中的表分区在运用中具有总表的承继性，并经过Check参数指定相应的子表分区的子表依然根据分布战略分布在各segment上分区是一种非常有用的优化措施，例如一年的买卖按买卖日期分区后，查询一天的买卖性能将提高365倍！Segment 1ASegment 1BSegment 1CSegment 1DSegmen

6、t 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DJan 2005Feb 2005Mar 2005Apr 2005May 2005Jun 2005Jul 2005Aug 2005Sep 2005Oct 2005Nov 2005Dec 2005每个分区表的数据平均分布到各个节点表分区可减少数据的搜索范围，提高查询性能Data Distribution & PartitioningSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BS

7、egment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DS

8、ELECT COUNT(*) FROM orders WHERE order_date= Oct 20 2005 AND order_date Oct 27 2005VSHash DistributionHash Distribution+ Table PartitioningSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DFull Table Scan VS. Partition Pruning表分区表

9、示图紧缩存储紧缩存储支持ZLIB和QUICKLZ方式的紧缩,紧缩比可到10:1紧缩表只能是Append Only方式紧缩数据不一定会带来性能的下降，紧缩表将耗费CPU资源，而减少I/O资源占用语法CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); 行列存储Greenplum支持行或列存储方式列方式目前只支持Append Only 假设常用的查询只取表中少量字段，那么列方式效率更高，如查询需求取表中的大量字段，行方式效率更高语法： CREATE TABLE sal

10、es2 (LIKE sales) WITH (appendonly=true, orientation=column);效率比较测试：测试1：需求去表中一切字段，此时行存储更快。select * from dw_ods.s1_sema_scmcaccp_row where crdacct_nbr= 4033930000166380411;41秒select * from dw_ods.s1_sema_scmcaccp_col where crdacct_nbr= 4033930000166380411;116秒测试2：只取表中少量字段，列存储更快select crdacct_status fr

11、om dw_ods.s1_sema_scmcaccp_row where crdacct_nbr= 4033930000166380411;35秒select crdacct_status from dw_ods.s1_sema_scmcaccp_col where crdacct_nbr= 4033930000166380411;3秒外部表加载外部表的特征Read-only数据存放在数据库外可执行SELECT, JOIN, SORT等命令，类似正规表的操作外部表的优点并行方式加载ETL的灵敏性格式错误行的容错处置支持多种数据源两种方式External Tables: 基于文件Web Tabl

12、es: 基于URL或指令基于外部表的高速数据加载利用并行数据流引擎，Greenplum可以直接用SQL操作外部表数据加载完全并行Master主机Segment主机内部互联网千兆以太网交换机gpfdistgpfdistSegment主机Segment主机Segment主机外部表文件外部表文件ETL效力器内部网络外部表加载的特征并行数据加载提供最好的性能可以处置远程存储的文件采用HTTP协议200 MB/s data distribution rate per gpfdistgpfdist文件分发守护进程启动:gpfdist -d /var/load_files/expenses -p 8080

13、-l /home/gpadmin/log &外部表定义:CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, description text )LOCATION (gpfdist/etlhost:8081/*,gpfdist/etlhost:8082/*)FORMAT TEXT (DELIMITER |)ENCODING UTF-8LOG ERRORS INTO ext_expenses_loaderrors SEGMENT REJECT LIMIT 10000 ROWS ;Load good ro

14、ws and catch poorly formatted rows, such as:rows with missing or extra attributesrows with attributes of the wrong data typerows with invalid client encoding sequencesDoes not apply to constraint errors:PRIMARY KEY, NOT NULL, CHECK or UNIQUE constraintsOptional error handling clause for external tab

15、les:LOG ERRORS INTO error_table SEGMENT REJECT LIMIT count ROWS | PERCENT( PERCENT based on gp_reject_percent_threshold parameter )ExampleCREATE EXTERNAL TABLE ext_customer (id int, name text, sponsor text) LOCATION ( gpfdistfilehost:8081/*.txt ) FORMAT TEXT ( DELIMITER | NULL ) LOG ERRORS INTO err_

16、customer SEGMENT REJECT LIMIT 5 ROWS; 外部表加载异常处置 Data resides outside the database No database statistics for external table data Not meant for frequent or ad-hoc access Can manually set rough statistics in pg_class:UPDATE pg_class SET reltuples=400000, relpages=400 WHERE relname=myexttable;外部表静态统计优化

17、 PostgreSQL commandSupport loading and unloadingOptimized for loading a large number of rowsLoads all rows in one command (not parallel)Loads data from a file or from standard inputSupports error handling as does external tables EXAMPLECOPY mytable FROM /data/myfile.csv WITH CSV HEADER; 文件生成在MasterC

18、OPY mytable FROM /data/myfile.csv WITH CSV HEADER;文件生成在本地COPY country FROM /data/gpdb/country_data WITH DELIMITER | LOG ERRORS INTO err_country SEGMENT REJECT LIMIT 10 ROWS;COPY SQL 命令 Drop indexes and recreate after load Increase maintenance_work_mem parameter to speed up CREATE INDEX operations Ru

19、n ANALYZE after load Run VACUUM after load errors、delete、upate。 Do not use ODBC INSERT to load large volumes of data数据加载性能优化提示 限制查询队列的激活数 防止系统过载(CPU, disk I/O, memory)资源负载管理(Workload Management)资源队列的两种典型管理方式Resource Queue LimitsACTIVE THRESHOLDEXAMPLE: CREATE RESOURCE QUEUE adhoc ACTIVE THRESHOLD 10

20、 IGNORE THRESHOLD 1000.0;COST THRESHOLDEXAMPLES: CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1000000.0 NOOVERCOMMIT;CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1e+6;Greenplum 性能监控器 Highly interactive web-based performance monitoring Real-time and historic views of:Resource utilizationQueries and que

21、ry internalsDashboardGreenplum Performance MonitorSystem MetricsGreenplum Performance MonitorQuery MonitorGreenplum Performance MonitorBackups and Restores Parallel backups (gp_dump) Parallel restores (gp_restore) Automating dump/restores (gpcrondump, gpdbrestore) Non-parallel backups and restores(p

22、g_dump/pg_restore/psql)备份与恢复用于在同构环境间迁移数据构造、数据、function备份恢复并行备份和恢复gp_dump/gp_restore用于在异构环境间迁移数据构造、数据、function串行备份和恢复pg_dump/pg_restore Each active segment is dumped in parallel Dump files created in segment data directory by default Supports compression (gzip) Ensure sufficient disk space for dump f

23、iles A backup set is identified by a timestamp keyExample:Back up a database: gp_dump gpdbBack up a database, and create dump files in a centralized location on all hosts: gp_dump -gp-d=/home/gpadmin/backups gpdbBack up a particular schema only: gp_dump -n myschema mydatabaseBack up a single segment

24、 instance only (by noting the dbid of the segment instance): gp_dump -gp-s=i5 gpdbRunning a Parallel Backup (gp_dump)On the master host gp_catalog_1_ gp_cdatabase_1_ gp_dump_1_ gp_dump_status_1_ On the segment hosts gp_dump_0_gp_dump_status_0_Dump Files Created by gp_dump Use gp_restore command Need

25、 timestamp key from gp_dump Make sure dump files are placed on correct segment hosts Make sure database exists before restoring Database-level server configuration settings are not restored ExamplesRestore an Greenplum database using backup files created by gp_dump:gp_restore -gp-k=2005103112453 -d

26、gpdbRestore a single segment instance only (by noting the dbid of the segment instance):gp_restore -gp-k=2005103112453 -d gpdb -gp-s=i5Running a Parallel Restore (gp_restore) gpcrondumpCalls to gp_dumpCan be called directly or can schedule using CRONSend notificationsFlexible dump optionsCan copy co

27、nfiguration filesCan dump system catalogsCan dump global objectsCan include a post-dump script gpdbrestoreRestores from gpcrondump filesCan restore from an archive host no need to pre-place dump files on segmentsScheduling Routine Backups Green plum also supports pg_dump and pg_restore Useful for mi

28、grating data to/from other DBMS pg_dump creates a single dump fileCan be slow on very large databasesRun at low-usage timesSupports compressionCan dump data as INSERT or COPY commandsgp-syntax option includes DISTRIBUTED BY statements in DDLNon-Parallel Backups and Restores Dump a database called my

29、db into a SQL-script file:pg_dump mydb db.sql To reload such a script into a (freshly created) database named newdb:psql -d newdb -f db.sql Dump a Greenplum database in tar file format and include distribution policy information:pg_dump -Ft -gp-syntax mydb db.tar To dump a database into a custom-for

30、mat archive file:pg_dump -Fc mydb db.dump To reload an archive file into a (freshly created) database named newdb:pg_restore -d newdb db.dump To dump a single table named mytab:pg_dump -t mytab mydb db.sql To specify an upper-case or mixed-case name in -t and related switches, you need to double-quo

31、te the name; else it will be folded to lower case. But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like:pg_dump -t MixedCaseName mydb mytab.sqlNon-Parallel Backups and Restores Example客户端工具pgAdmin3图形化

32、管理和SQL执行/分析/监控工具psql 行命令操作和管理工具pgAdmin3 for GPDBpgAdmin3 is the leading graphical Open Source management, development and administration tool for PostgreSQLGreenplum has contributed extensive GPDB-specific enhancementsWith GPDB 3.3, Greenplum ships and supports this toolpgAdmin3 for GPDBpgAdmin3 for

33、 GPDB监控活动session ,同SQL：select * from pg_stat_activity;监控锁，从pg_lock中获取信息可以停顿正在运转的SQLPSQLConnect through the masterConnection informationdatabase name (-d | PGDATABASE)master host name (-h | PGHOST)master port (-p | PGPORT)user name (-U | PGUSER)First time connectionstemplate1 databasedefault superuse

34、r account (gpadmin)Issuing SQL StatementsInteractive modepsql mydatabasemydatabase=# SELECT * FROM foo; Non-interactive mode (single command) psql mydatabase ac “SELECT * FROM foo;Non-interactive mode (multiple commands) psql mydatabase af /home/lab1/sql/createdb.sql(Use semi-colon (;) to denote end

35、 of a statement)Common PSQL Meta Commands? (help on psql meta-commands) h (help on SQL command syntax)dt (show tables) dtS (show system tables) dg or du (show roles) l (show databases)c db_name (connect to this database) q (quit psql) ! (Enter into shell mode)df (show function)dn(show schema)Set sea

36、rch_path=timingpostgresql.confLocalLocalLocalLocal参数参考Adminguide重要参数:max_connection,share_buff,work_memLocal 变量的修正，如max_stack_depth需求修正一切segment上的valueLocal, Global, and Master-Onlypostgresql.conf fileLocated in master or segment instances data directoryUsed to set configuration parameters on the sy

37、stem levelParameters that are using the default setting are commented out (#)Requires a restart (or reload using gpstop -u) for changes to take effectViewing Parameter SettingsView a specific parameter setting Example: SHOW search_path;View all parameter settings Example: SHOW ALL;Set parameter Exam

38、ple: set search_path=public set client_encoding=gb18030 Configuring Host-Based Authentication客户端授权能否允许从某个客户端的衔接用户能否可以衔接到所恳求的数据库pg_hba.conf file基于host address, database, and/or DB user account控制权限位于master和segment实例的数据目录中系统初始化时进展default配置Default Master Host pg_hba.confLocal connections allowed for Gre

39、enplum superuserRemote connections not allowedEXAMPLE# TYPE DATABASE USER CIDR-ADDRESS METHOD local all gpadmin ident sameuser local all all ident sameuser host all gpadmin /0 trust 配置 pg_hba.confEXAMPLE# TYPE DATABASE USER CIDR-ADDRESS METHOD localallall trust hostall all/32 trust hostall all:1/128

40、 trust hostall all06/32 trust hostcarddwetl21.104.12/32md5 hostgpadminall21.104.0/24md5gpstop -u 可与在不重启数据库方式下，让设置生效SQL语法详细参考：本卷须知：DELETE ,UPDATE在两表关联时，两个表的distribution必需一致。 如：delete from tablea using tableb where tablea.id=tableb.idUPDATE table a as a SET desc= b.desc FROM tableb as b WHERE a.id=b.i

41、d以上操作 table a,table b 必需运用一样的分布，必要时可以运用 alter table set distribution 进展分布修正。数据类型常用数据类型CHAR,VARCHAR,TEXTSmallint ,integer,bigintNumeric, real,double precisionTimestamp,date,timeBooleanArray 类型。如 integer其它数据类型请参考All system catalogs in pg_catalog schemaStandard PostgreSQL system catalogs (pg_*)Greenplu

42、m-specific catalogs: gp_configuration gp_distribution_policy gp_id gp_version_at_initdb pg_resqueue pg_exttable pg_tables pg_class pg_stat_activityTo list all system catalog tables in psql: dtSTo list all system views in psql: dvS其它 catalog 参考 System Catalog Tables and Views函数日期函数Extractday|month|ye

43、ar。 From date);Select date + 1 day:interval,date+ 1 month:intervalSELECT date_part(day, TIMESTAMP 2001-02-16 20:38:40); Result: 16SELECT date_trunc(hour, TIMESTAMP 2001-02-16 20:38:40); Result: 2001-02-16 20:00:00 pg_sleep(seconds);系统日期变量Current_dateCurrent_timeCurrent_timestampNow()Timeofday() 在 事务

44、中发生变化，以上函数在事务中不变函数字符串处置函数Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char, | (字符串衔接)substring(string from pattern/ * ，like，simillar to (方式匹配)其它杂类Case 。When/Coalescenullifgenerate_seriesIn/not in/exists/any/all各类函数参考：存储过程Greenplum支持SQL/PYTHON/PERL/C言语构建函数，以下着重引见SQL 存储过程。一个存储过程就是一个事务，包括

45、对子过程的调用都在一个事务内存储过程构造：CREATE FUNCTION somefunc() RETURNS integer AS $DECLARE quantity integer := 30;BEGIN RETURN ;END;$ LANGUAGE plpgsql;赋值给一个变量或行/记录赋值用下面方法：identifier := expression例子：user_id := 20;执行一个没有结果的查询: PERFORM query;一个例子：PERFORM create_mv(cs_session_page_requests_mv, my_query);存储过程请参考：存储过程动态

46、SQLEXECUTE command-string INTO STRICT target;SELECT INTOExample：SELECT ID INTO VAR_ID FROM TABLEA获取结果形状GET DIAGNOSTICS variable = item , .;一个例子：GET DIAGNOSTICS integer_var = ROW_COUNT;SQL前往变量SQLERRM， SQLSTATE控制构造IF . THEN . ELSEIF . THEN . ELSELOOP, EXIT, CONTINUE, WHILE, FOR 从函数前往有两个命令可以用来从函数中前往数据：

47、RETURN 和 RETURN NEXT 。Syntax：RETURN expression;设置回调EXEC SQL WHENEVER condition action; condition 可以是以下之一： SQLERROR，SQLWARNING，NOT FOUND存储过程异常处置EXCEPTION WHEN unique_violation THEN - do nothing END;忽略错误：EXCEPTION WHEN OTHERS THEN RAISE NOTICE an EXCEPTION is about to be raised; RAISE EXCEPTION NUM:%,

48、 DETAILS:%, SQLSTATE, SQLERRM;END;错误和音讯RAISE level format , expression , .;Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example: RAISE NOTICE Calling cs_create_job(%), v_job_id;Performance Tuning Introduction to performance tuning Common performance problems Tracking down a performance problem

49、Query profiling (EXPLAIN, EXPLAIN ANALYZE) Query tuning 我的阅历：性能调优Set performance expectationsacceptable response times, queries per minute, etc.BenchmarksKnow your baseline hardware performancethroughput / capacityKnow your workloadheavy usage timesresource contentiondata contentionFocus your optimi

50、zationsApproaching a Performance Tuning Initiative Hardware Issues / Failed SegmentsResource AllocationContention Between Concurrent WorkloadsInaccurate Database StatisticsUneven Data DistributionSQL FormulationDatabase Design Common Causes of Performance IssuesDisk failuresHost failuresNetwork fail

51、uresOS not tuned for GreenplumDisk Capacity 70% maximum recommended VACUUM after updates, deletes and loadsVACUUM configuration parameters max_fsm_relations = tables + indexes + system tables max_fsm_pages = 16 * max_fsm_relations Hardware Issues Greenplum resource queueslimit active queries in the

52、systemlimit the size of a query a particular user can runPerform admin tasks at low usage timesData loading, ETLVACUUM, ANALYZEBackupsDesign applications to prevent lock conflictsConcurrent sessions not updating the same data at the same timeResource-related Configuration Parameterswork_mem = 32MBma

53、intenance_work_mem = 64MBshared_buffers = 125MBResource Allocation and ContentionDatabase statistics used by the query plannerRun ANALYZE afterData loadsRestores from backupsChanges to schema (adding indexes, etc.)Inserts, Updates, or DeletesCan configure statistics collection default_statistics_tar

54、get = 25 gp_analyze_relative_error = .25 on specific table columnsALTER TABLE name ALTER column SET STATISTICS # Database Statistics (ANALYZE)Table Distribution Key Considerations Even data distributionLocal vs. distributed operationsEven query processing Checking for data skewgpskew t schema.tableU

55、nix system utilities (gpssh)：gpssh f seg_host -sar 1 100Rebalancing a Table CREATE TABLE sales_temp (LIKE sales) DISTRIBUTED BY (date, total, customer); INSERT INTO sales_temp SELECT * FROM sales; DROP sales; ALTER TABLE sales_temp RENAME TO sales; Greenplum Data Distribution Data Type Selectionsmal

56、lest size possible to fit data INTEGER, not NUMERIC(11,2) for integer fieldssame data type across tables for join columnsconsider hash of wide join keys, using BYTEA instead of CHAR(100) varchar or text for character data Denormalization (star schema)Table Partitioning Database DesignUse sparingly i

57、n Greenplum DatabaseTry workload without indexes firstCan improve performance of OLTP type workloadsOther index considerations:Avoid on frequently updated columnsAvoid overlapping indexesUse bitmap indexes where applicable instead of B-treeDrop indexes for loadsConsider a clustered indexConfiguring

58、Index Usage:enable_indexscan = on | offDatabase Design - IndexesGeneral ConsiderationsKnow your dataMinimize returned rowsAvoid unnecessary columns/tables in result setAvoid large sorts if possibleMatch data types in joinsGreenplum-specific ConsiderationsJoin on common distribution key columns when

59、possibleConsider data distribution policy and query predicatesSQL FormulationSystem Catalog Tables and Viewspg_stat_activitypg_locks / pg_classDatabase LogsLocated in master (and segment) data directoriesUNIX system utilities (gpssh)Tracking Down a Performance ProblemExamine query plans to identify

60、tuning opportunitiesWhat to look for?Plan operations that are taking exceptionally longAre the planners estimates close to reality? (EXPLAIN ANALYZE)Is the planner applying selective predicates early?Is the planner choosing the best join order?Is the planner selectively scanning partitioned tables?I