Greenplum数据库基础培训new_2(共100张).pptx

《Greenplum数据库基础培训new_2(共100张).pptx》由会员分享，可在线阅读，更多相关《Greenplum数据库基础培训new_2(共100张).pptx（100页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Wei.LiSenior Data ArchitectureAlpine Solution2010/05l 海量并行处理 (Massively Parallel Processing) DBMSl 基于 PostgreSQL 8.2 相同的客户端功能 增加支持并行处理的技术 增加支持数据仓库和BI的特性 外部表(external tables)/并行加载(parallel loading) 资源管理 查询优化器增强(query optimizer enhancements)l psqll pgAdmin IIIl ODBC/Datadirectl JDBCl Perl DBIl Pythonl

2、 libpqlOLE DBl 访问系统的入口l 数据库侦听进程 (postgres)l 处理所有用户连接l 建立查询计划l 协调工作处理过程l 管理工具l 系统目录表和元数据（数据字典）l 不存放任何用户数据l 每段（Segment）存放一部分用户数据l 一个系统可以有多段l 用户不能直接存取访问l 所有对段的访问都经过Masterl 数据库监听进程(postgres)监听来自Master的连接Segmentl Greenplum数据库之间的连接层l 进程间协调和管理l 基于千兆以太网架构l 属于系统内部私网配置l 支持两种协议：TCP or UDPInterconnectl Standby

3、节点用于当 Master 节点损坏时提供 Master 服务l Standby 实时与 Master 节点的 Catalog 和事务日志保持同步Master/Standby 镜像保护镜像保护l 每个Segment的数据冗余存放在另一个Segment上，数据实时同步l 当Primary Segment失败时，Mirror Segment将自动提供服务l Primary Segment恢复正常后，使用gprecoverseg F 同步数据。数据冗余数据冗余-Segment 镜像保护镜像保护l Hash分布 CREATE TABLE DISTRIBUTED BY (column ,) 同样数值的内容

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

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

6、BSegment 1CSegment 1DSegment 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 1CSegm

7、ent 1DSegment 2ASegment 2BSegment 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 3ASegm

8、ent 3BSegment 3CSegment 3DSELECT 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 Sca

9、n VS. Partition Pruning表分区示意图表分区示意图l 压缩存储 支持ZLIB和QUICKLZ方式的压缩,压缩比可到10:1 压缩表只能是Append Only方式 压缩数据不一定会带来性能的下降，压缩表将消耗CPU资源，而减少I/O资源占用l 语法CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); l Greenplum支持行或列存储模式 列模式目前只支持Append Only 如果常用的查询只取表中少量字段，则列模式效率更高，如查询需要取表

10、中的大量字段，行模式效率更高 语法： CREATE TABLE sales2 (LIKE sales) WITH (appendonly=true, orientation=column);l 效率比较测试：测试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：只

11、取表中少量字段，列存储更快select crdacct_status from 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秒l 外部表的特征Read-only数据存放在数据库外可执行SELECT, JOIN, SORT等命令，类似正规表的操作l 外部表的优点并行方式加载ETL的灵活性格式错误行的容错处理支持多种

12、数据源l 两种方式 External Tables: 基于文件 Web Tables: 基于URL或指令l 利用并行数据流引擎，Greenplum可以直接用SQL操作外部表l 数据加载完全并行Master主机Segment主机内部互联网千兆以太网交换机gpfdistgpfdistSegment主机Segment主机Segment主机外部表文件外部表文件ETL服务器内部网络l 并行数据加载提供最好的性能l 能够处理远程存储的文件l 采用HTTP协议l 200 MB/s data distribution rate per gpfdistl gpfdist文件分发守护进程启动:gpfdist -d

13、 /var/load_files/expenses -p 8080 -l /home/gpadmin/log &l 外部表定义: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 R

14、EJECT LIMIT 10000 ROWS ;lLoad good rows and catch poorly formatted rows, such as: rows with missing or extra attributes rows with attributes of the wrong data type rows with invalid client encoding sequenceslDoes not apply to constraint errors: PRIMARY KEY, NOT NULL, CHECK or UNIQUE constraintslOpti

15、onal error handling clause for external tables:LOG ERRORS INTO error_table SEGMENT REJECT LIMIT count ROWS | PERCENT( PERCENT based on gp_reject_percent_threshold parameter )lExampleCREATE EXTERNAL TABLE ext_customer (id int, name text, sponsor text) LOCATION ( gpfdist:/filehost:8081/*.txt ) FORMAT

16、TEXT ( DELIMITER | NULL ) LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5 ROWS; 外部表加载异常处理外部表加载异常处理l Data resides outside the databasel No database statistics for external table datal Not meant for frequent or ad-hoc accessl Can manually set rough statistics in pg_class: UPDATE pg_class SET reltu

17、ples=400000, relpages=400 WHERE relname=myexttable;外部表静态统计优化外部表静态统计优化l 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 tablesl

18、EXAMPLECOPY mytable FROM /data/myfile.csv WITH CSV HEADER; （文件生成在Master）COPY 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 命令命令l Drop indexes and recreate after load

19、Increase maintenance_work_mem parameter to speed up CREATE INDEX operationsl Run ANALYZE after loadl Run VACUUM after load errors、delete、upate。l Do not use ODBC INSERT to load large volumes of data数据加载性能优化提示数据加载性能优化提示l 限制查询队列的激活数l 防止系统过载(CPU, disk I/O, memory)资源负载管理资源负载管理(Workload Management)l Resou

20、rce Queue Limits ACTIVE THRESHOLDEXAMPLE: CREATE RESOURCE QUEUE adhoc ACTIVE THRESHOLD 10 IGNORE THRESHOLD 1000.0; COST THRESHOLDEXAMPLES: CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1000000.0 NOOVERCOMMIT;CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1e+6;Greenplum 性能监控器性能监控器l Highly interactive web-

21、based performance monitoringl Real-time and historic views of:Resource utilizationQueries and query internalsDashboardSystem MetricsGreenplum Performance MonitorQuery MonitorGreenplum Performance MonitorBackups and Restoresl Parallel backups (gp_dump)l Parallel restores (gp_restore)l Automating dump

22、/restores (gpcrondump, gpdbrestore)l Non-parallel backups and restores(pg_dump/pg_restore/psql)备份与恢复备份与恢复l 用于在同构环境间迁移数据结构、数据、function备份恢复并行备份和恢复并行备份和恢复（gp_dump/gp_restore）l 用于在异构环境间迁移数据结构、数据、function串行备份和恢复串行备份和恢复（pg_dump/pg_restore）l Each active segment is dumped in parallell Dump files created in

23、segment data directory by defaultl Supports compression (gzip)l Ensure sufficient disk space for dump filesl A backup set is identified by a timestamp keylExample: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/gpa

24、dmin/backups gpdbBack up a particular schema only: gp_dump -n myschema mydatabaseBack up a single segment instance only (by noting the dbid of the segment instance): gp_dump -gp-s=i5 gpdbRunning a Parallel Backup (gp_dump)lOn the master host gp_catalog_1_ gp_cdatabase_1_ gp_dump_1_ gp_dump_status_1_

25、 lOn the segment hosts gp_dump_0_gp_dump_status_0_Dump Files Created by gp_dumpl Use gp_restore commandl Need timestamp key from gp_dumpl Make sure dump files are placed on correct segment hostsl Make sure database exists before restoringl Database-level server configuration settings are not restore

26、dl Examples Restore an Greenplum database using backup files created by gp_dump:gp_restore -gp-k=2005103112453 -d gpdb Restore 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)l gpcrondum

27、pCalls to gp_dumpCan be called directly or can schedule using CRONSend email notificationsFlexible dump optionsCan copy configuration filesCan dump system catalogsCan dump global objectsCan include a post-dump scriptl gpdbrestoreRestores from gpcrondump filesCan restore from an archive host no need

28、to pre-place dump files on segmentsScheduling Routine Backupsl Green plum also supports pg_dump and pg_restore l Useful for migrating data to/from other DBMSl pg_dump creates a single dump fileCan be slow on very large databasesRun at low-usage timesSupports compressionCan dump data as INSERT or COP

29、Y commandsgp-syntax option includes DISTRIBUTED BY statements in DDLNon-Parallel Backups and Restoresl Dump a database called mydb into a SQL-script file:pg_dump mydb db.sqll To reload such a script into a (freshly created) database named newdb:psql -d newdb -f db.sqll Dump a Greenplum database in t

30、ar file format and include distribution policy information:pg_dump -Ft -gp-syntax mydb db.tarl To dump a database into a custom-format archive file:pg_dump -Fc mydb db.dumpl To reload an archive file into a (freshly created) database named newdb:pg_restore -d newdb db.dumpl To dump a single table na

31、med mytab:pg_dump -t mytab mydb db.sqll To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote 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 mix

32、ed-case name, you need something like:pg_dump -t MixedCaseName mydb mytab.sqlNon-Parallel Backups and Restores Examplel pgAdmin3 图形化管理和SQL执行/分析/监控工具 psql 行命令操作和管理工具l pgAdmin3 is the leading graphical Open Source management, development and administration tool for PostgreSQL Greenplum has contributed

33、 extensive GPDB-specific enhancements With GPDB 3.3, Greenplum ships and supports this tooll 监控活动session ,同SQL：select * from pg_stat_activity;l 监控锁，从pg_lock中获取信息l 可以停止正在运行的SQLl Connect through the masterl Connection informationdatabase name (-d | PGDATABASE)master host name (-h | PGHOST)master port

34、(-p | PGPORT)user name (-U | PGUSER)l First time connections template1 database default superuser account (gpadmin)l Interactive modepsql mydatabasemydatabase=# SELECT * FROM foo; l Non-interactive mode (single command) psql mydatabase ac “SELECT * FROM foo;”l Non-interactive mode (multiple commands

35、) psql mydatabase af /home/lab1/sql/createdb.sqll (Use semi-colon (;) to denote end of a statement)l ? (help on psql meta-commands) l h (help on SQL command syntax)l dt (show tables) l dtS (show system tables) l dg or du (show roles) l l (show databases)l c db_name (connect to this database) l q (qu

36、it psql) l ! (Enter into shell mode)l df (show function)l dn(show schema)l Set search_path=l timingpostgresql.confLocalLocalLocalLocall 参数参考Adminguidel 重要参数:max_connection,share_buff,work_meml Local 变量的修改，如max_stack_depth需要修改所有segment上的valueLocal, Global, and Master-Onlyl Located in master or segmen

37、t instances data directoryl Used to set configuration parameters on the system levell Parameters that are using the default setting are commented out (#)l Requires a restart (or reload using gpstop -u) for changes to take effectl View a specific parameter setting Example: SHOW search_path;l View all

38、 parameter settings Example: SHOW ALL;l Set parameter Example: set search_path=public set client_encoding=gb18030 l 客户端授权是否允许从某个客户端的连接用户是否可以连接到所请求的数据库l pg_hba.conf file基于host address, database, and/or DB user account控制权限位于master和segment实例的数据目录中系统初始化时进行default配置l Local connections allowed for Greenpl

39、um superuserl Remote connections not allowedl EXAMPLE# TYPE DATABASE USER CIDR-ADDRESS METHOD local all gpadmin ident sameuser local all all ident sameuser host all gpadmin 127.0.0.0/0 trust l EXAMPLE# TYPE DATABASE USER CIDR-ADDRESS METHOD localallall trust hostall all127.0.0.1/32 trust hostall all

40、:1/128 trust hostall all10.0.0.206/32 trust hostcarddwetl21.104.138.12/32md5 hostgpadminall21.104.138.0/24md5l gpstop -u 可与在不重启数据库方式下，让设置生效l 具体参考Gp sql language：l 注意事项： DELETE ,UPDATE在两表关联时，两个表的distribution必须一致。 如：delete from tablea using tableb where tablea.id=tableb.idUPDATE table a as a SET desc=

41、 b.desc FROM tableb as b WHERE a.id=b.id 以上操作 table a,table b 必须使用相同的分布，必要时可以使用 alter table set distribution 进行分布修改。l 常用数据类型 CHAR,VARCHAR,TEXT Smallint ,integer,bigint Numeric, real,double precision Timestamp,date,time Boolean Array 类型。如 integerl 其它数据类型请参考lAll system catalogs in pg_catalog schemalSt

42、andard PostgreSQL system catalogs (pg_*)lGreenplum-specific catalogs: gp_configuration gp_distribution_policy gp_id gp_version_at_initdb pg_resqueue pg_exttable pg_tables pg_class pg_stat_activitylTo list all system catalog tables in psql: dtSlTo list all system views in psql: dvSl其它 catalog 参考 Syst

43、em Catalog Tables and Viewsl 日期函数Extract（day|month|year。 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_

44、dateCurrent_timeCurrent_timestampNow()Timeofday() 在 事务中发生变化，以上函数在事务中不变l 字符串处理函数Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char, | (字符串连接)substring(string from pattern/ * ，like，simillar to (模式匹配)l 其它杂类Case 。When/Coalescenullifgenerate_seriesIn/not in/exists/any/all各类函数参考：l Greenplum支持

45、SQL/PYTHON/PERL/C语言构建函数，以下着重介绍SQL 存储过程。 一个存储过程就是一个事务，包括对子过程的调用都在一个事务内 存储过程结构：CREATE FUNCTION somefunc() RETURNS integer AS $DECLARE quantity integer := 30;BEGIN RETURN .;END;$ LANGUAGE plpgsql; 赋值给一个变量或行/记录赋值用下面方法：identifier := expression例子：user_id := 20; 执行一个没有结果的查询: PERFORM query;一个例子：PERFORM crea

46、te_mv(cs_session_page_requests_mv, my_query);存储过程请参考：存储过程请参考：l动态SQLEXECUTE command-string INTO STRICT target;lSELECT INTOExample：SELECT ID INTO VAR_ID FROM TABLEAl获取结果状态GET DIAGNOSTICS variable = item , .;一个例子：GET DIAGNOSTICS integer_var = ROW_COUNT;lSQL返回变量SQLERRM， SQLSTATEl控制结构IF . THEN . ELSEIF .

47、 THEN . ELSELOOP, EXIT, CONTINUE, WHILE, FOR l从函数返回有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT 。Syntax：RETURN expression;l设置回调EXEC SQL WHENEVER condition action; condition 可以是下列之一： SQLERROR，SQLWARNING，NOT FOUNDl异常处理EXCEPTION WHEN unique_violation THEN - do nothing END;忽略错误：EXCEPTION WHEN OTHERS THEN RAIS

48、E NOTICE an EXCEPTION is about to be raised; RAISE EXCEPTION NUM:%, DETAILS:%, SQLSTATE, SQLERRM;END;l错误和消息RAISE level format , expression , .;Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example: RAISE NOTICE Calling cs_create_job(%), v_job_id;Performance Tuningl Introduction to performance tu

49、ningl Common performance problemsl Tracking down a performance probleml Query profiling (EXPLAIN, EXPLAIN ANALYZE)l Query tuningl 我的经验：性能调优性能调优lSet performance expectationsacceptable response times, queries per minute, etc.BenchmarksKnow your baseline hardware performancethroughput / capacityKnow yo

50、ur workloadheavy usage timesresource contentiondata contentionlFocus your optimizationsApproaching a Performance Tuning Initiative lHardware Issues / Failed SegmentslResource AllocationlContention Between Concurrent WorkloadslInaccurate Database StatisticslUneven Data DistributionlSQL FormulationlDa