Greenplum数据库基础培训课件.ppt

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 li

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

3、ster 服务l Standby 实时与 Master 节点的 Catalog 和事务日志保持同步l 每个Segment的数据冗余存放在另一个Segment上，数据实时同步l 当Primary Segment失败时，Mirror Segment将自动提供服务l Primary Segment恢复正常后，使用gprecoverseg F 同步数据。l Hash分布 CREATE TABLE DISTRIBUTED BY(column,)同样数值的内容被分配到同一个Segment上l 循环分布 CREATE TABLE DISTRIBUTED RANDOMLY 具有同样数值的行内容并不一定在同一个

4、Segment上表分布的策略表分布的策略-并行计算的基础并行计算的基础查询命令的执行查询命令的执行l 举例说明：按卡号、客户号、机构的分布方式优劣点查询命令的执行查询命令的执行SELECT customer,amount FROM sales JOIN customer USING(cust_id)WHERE date=04302008;l 将一张大表逻辑性地分成多个部分，如按照分区条件进行查询，将减少数据的扫描范围，提高系统性能。l 提高对于特定类型数据的查询速度和性能l 也可以更方便数据库的维护和更新l 两种类型:Range分区(日期范围或数字范围)/如日期、价格等List 分区,例如地区

5、、产品等l Greenplum中的表分区在使用中具有总表的继承性，并通过Check参数指定相应的子表l 分区的子表依然根据分布策略分布在各segment上l 分区是一种非常有用的优化措施，例如一年的交易按交易日期分区后，查询一天的交易性能将提高365倍！Segment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DJan 2005Feb 2005Mar 2005Apr 2005May 2005Jun 2005Jul 2

6、005Aug 2005Sep 2005Oct 2005Nov 2005Dec 2005每个分区表的数据平均分布到各个节点表分区可减少数据的搜索范围，提高查询性能Segment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3B

7、Segment 3CSegment 3DSegment 1ASegment 1BSegment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3DSELECT COUNT(*)FROM orders WHERE order_date=Oct 20 2005 AND order_date Oct 27 2005VSHash DistributionHash Distribution+Table PartitioningSegment 1ASegment 1BSeg

8、ment 1CSegment 1DSegment 2ASegment 2BSegment 2CSegment 2DSegment 3ASegment 3BSegment 3CSegment 3Dl 压缩存储 支持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支持行或列存储

9、模式 列模式目前只支持Append Only 如果常用的查询只取表中少量字段，则列模式效率更高，如查询需要取表中的大量字段，行模式效率更高 语法：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 whe

10、re crdacct_nbr=4033930000166380411;116秒测试2：只取表中少量字段，列存储更快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等命令，类似正

11、规表的操作l 外部表的优点并行方式加载ETL的灵活性格式错误行的容错处理支持多种数据源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

12、 gpfdistl gpfdist文件分发守护进程启动:gpfdist-d/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_expen

13、ses_loaderrors SEGMENT REJECT 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 UNIQU

14、E constraintslOptional 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)

15、FORMAT 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 reltuples=400000,relpag

16、es=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 EXAMPLECOPY mytable FROM/data/myfile.c

17、sv 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;l Drop indexes and recreate after load Increase maintenance_work_mem parameter to speed up CREAT

18、E INDEX operationsl Run ANALYZE after loadl Run VACUUM after load errors、delete、upate。l Do not use ODBC INSERT to load large volumes of datal 限制查询队列的激活数l 防止系统过载(CPU,disk I/O,memory)l Resource Queue Limits ACTIVE THRESHOLDEXAMPLE:CREATE RESOURCE QUEUE adhoc ACTIVE THRESHOLD 10 IGNORE THRESHOLD 1000.0

19、;COST THRESHOLDEXAMPLES:CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1000000.0 NOOVERCOMMIT;CREATE RESOURCE QUEUE batch1 COST THRESHOLD 1e+6;l Highly interactive web-based performance monitoringl Real-time and historic views of:Resource utilizationQueries and query internalsDashboardSystem MetricsQue

20、ry MonitorBackups and Restoresl Parallel backups(gp_dump)l Parallel restores(gp_restore)l Automating dump/restores(gpcrondump,gpdbrestore)l Non-parallel backups and restores(pg_dump/pg_restore/psql)l 用于在同构环境间迁移数据结构、数据、function备份恢复l 用于在异构环境间迁移数据结构、数据、functionl Each active segment is dumped in paralle

21、ll Dump files created in 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

22、_dump-gp-d=/home/gpadmin/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 gpdblOn the master host gp_catalog_1_ gp_cdatabase_1_ gp_dump_1_ gp_dump_status_1_ lOn the segment hos

23、ts gp_dump_0_gp_dump_status_0_l 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 restoredl Examples Restore an Greenplum database using b

24、ackup 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=i5l gpcrondumpCalls to gp_dumpCan be called directly or can schedule using CRONSend email notificationsFlex

25、ible 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 to pre-place dump files on segmentsl Green plum also supports pg_dump and pg_restore l Useful

26、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 COPY commandsgp-syntax option includes DISTRIBUTED BY statements in DDLl Dump a database called mydb into a SQL-script file

27、: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 tar 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

28、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 named 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 fold

29、ed 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.sqll pgAdmin3 图形化管理和SQL执行/分析/监控工具 psql 行命令操作和管理工具l pgAdmin3 is the leading graphical Open Source ma

30、nagement,development and administration tool for PostgreSQL Greenplum has contributed 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 inf

31、ormationdatabase name(-d|PGDATABASE)master host name(-h|PGHOST)master port(-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

32、“SELECT*FROM foo;”l Non-interactive mode(multiple commands)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

33、db_name(connect to this database)l q(quit 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上的valuel Located in master or s

34、egment 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

35、 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 Greenplum s

36、uperuserl 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 EXAMPLEEXAMPLE#TYPE DATABASE USER CIDR-ADDRESS METHOD localallall trust hostall all127.0.0.1/32 trust hostall al

37、l: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=b

38、.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 schemalStanda

39、rd 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 参考 l 日期函数Extrac

40、t（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_dateCurrent_timeCurrent_timestampNow()Timeofday()在

41、 事务中发生变化，以上函数在事务中不变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支持SQL/PYTHON/PERL/C语言构建函数，以下着重介绍SQL 存储过程。一个存储过程就是一个事务，包括对

42、子过程的调用都在一个事务内 存储过程结构：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);存储过程请参考：存储过程请参考：l动态SQLEXE

43、CUTE 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.THEN.ELSELOOP,EXIT,CONTINUE,WHILE,FOR l从函数返回有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT

44、。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 RAISE NOTICE an EXCEPTION is about to be raised;RAISE EXCEPTION NUM:%,DETAILS:%,SQLSTATE,SQLERRM;E

45、ND;l错误和消息RAISE level format,expression,.;Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example:RAISE NOTICE Calling cs_create_job(%),v_job_id;Performance Tuningl Introduction to performance tuningl Common performance problemsl Tracking down a performance probleml Query profiling(EXPLAIN,EXPLAIN

46、ANALYZE)l Query tuningl 我的经验：lSet performance expectationsacceptable response times,queries per minute,etc.BenchmarksKnow your baseline hardware performancethroughput/capacityKnow your workloadheavy usage timesresource contentiondata contentionlFocus your optimizationslHardware Issues/Failed Segment

47、slResource AllocationlContention Between Concurrent WorkloadslInaccurate Database StatisticslUneven Data DistributionlSQL FormulationlDatabase Design lDisk failureslHost failureslNetwork failureslOS not tuned for GreenplumlDisk Capacity 70%maximum recommended VACUUM after updates,deletes and loadslV

48、ACUUM configuration parameters max_fsm_relations=tables+indexes+system tables max_fsm_pages=16*max_fsm_relations l Greenplum resource queueslimit active queries in the systemlimit the size of a query a particular user can runlPerform admin tasks at low usage timesData loading,ETLVACUUM,ANALYZEBackup

49、slDesign applications to prevent lock conflictsConcurrent sessions not updating the same data at the same timelResource-related Configuration Parameterswork_mem=32MBmaintenance_work_mem=64MBshared_buffers=125MBlDatabase statistics used by the query plannerlRun ANALYZE afterData loadsRestores from ba

50、ckupsChanges to schema(adding indexes,etc.)Inserts,Updates,or DeletesCan configure statistics collection default_statistics_target=25 gp_analyze_relative_error=.25 on specific table columnsALTER TABLE name ALTER column SET STATISTICS#lTable Distribution Key Considerations Even data distributionLocal