步骤 =>（1）进入维护模式：$ gpstart -m；（2）连接到master，做cata_log维护，例如：$ PGOPTIONS='-c gp_session_role=utility' psql template1；（3）完成管理任务后，在进入生产模式production mode前，必须停止工具模式utility mode
9.更新数据 GP中UPDATE的限制条件: 1.distribution key 不能被更新 2.mirror启动时，在update语句中不能使用STABLE 或 VOLATILE 3.不支持RETURNING UPDATE SQL示例: UPDATE products SET price = 10 WHERE price = 5; --更新单行或多行数据 10.删除数据 GP中使用DETELE的限制条件: 1.mirror启动时，在delete语句中不能使用STABLE 或 VOLATILE 2.不支持RETURNING 3.Truncate是不扫描table。 DETELE SQL示例: DELETE FROM products WHERE price = 10; --根据where条件删除 DELETE FROM products; --删除表中所有数据 TRUNCATE mytable; --清空表 11.【事务】 BEGIN 或START TRANSACTION --开启事务 END 或 COMMIT --结束事务 ROLLBACK --回滚 SAVEPOINT -- 分段提交或回滚事务 12.【索引】 在OLTP环境中，为了追求最快的响应时间而大量的使用索引。一般都是单条命中或少量数据集。 但是GP一般都是用来做OLAP，基本都是全表扫描，故应该尽量少的使用索引。 GP推荐在没有加任何索引的情况下，查看下你的查询的消耗情况。(注：有主键的表，系统会自动创建主键索引。) 12.1建索引需考虑： （1）查询负载：BI一般都访问大数据集，因此不能利用上索引。针对OLAP型数据库，序列读取大批量数据比用索引随机扫描的性能要好。 （2）已压缩的表：索引能提高压缩append-only表的性能 （3）不要频繁更新的字段上建索引 （4）选择性的创建B-tree索引：索引选择度 = 同列的distinct个数 / 全部行数，例如：一个表有1000行，某列有800个唯一值，索引的选择度是0.8。唯一索引的选择度是1.0，这是最好情况 （5）在低选择性的列使用Bitmap索引：GP新增Bitmap索引，在postgresql是没有的 （6）索引列通常用来做join：常常被用来join的字段，建索引可以提高join的性能。(如: 外键) （7）索引列经常被频繁使用在where语句中 （8）避免重复列创建索引 （9）批量导入数据需删除索引：导入大量数据时,先删除索引,等数据导完后再重建。这样速度会更快。 （10）考虑一个cluster索引：clustering an index意味着物理上数据在硬盘上是被排序过的。由于数据之间物理上更近了，读取的时候更有序。 12.2索引类型： （1）B-tree；（2）GiST --用于GIS；（3）Bitmap (psotgres中无此索引类型) 注：gp中hash和GIN索引都被停用了。 12.3创建索引 CREATE INDEX title_idx ON films (title); --默认创建B-tree索引 CREATE INDEX gender_bmp_idx ON employee USING bitmap (gender);--创建bitmap索引 12.4检查索引使用 尽管在gp中索引不用维护和优化，但是检查索引使用时的负载情况还是很重要的。 查看EXPLAIN中出现的提示： （1）Index Scan （2）Bitmap Heap Scan （3）Bitmap Index Scan （4）BitmapAnd or BitmapOr 12.5管理索引 在某些情况下，一个低性能的索引可能需要进行REBUILD。update和delete是不更新bitmap 索引的，因此如果你要update或delete一个有bitmap索引的表的话，将需要rebuild。 REINDEX my_table; --重建my_table上的所有index REINDEX my_index; --重建my_index索引 DROP INDEX title_idx; --删除索引title_idx 13.GP支持分区表，主要用来存放大表，如fact table 目的：（1）把大数据切片,便于查询；（2）便于数据库维护；（3）分区创建时，每个分区会自带一个Check约束，来限定数据的范围。Check约束也用于执行查询时定位分区。 支持分区类型：（1）范围分区 range partition；（2）列表分区 list partition；（3）组合分区 （1）partition 和 distribution的区别 distribution -- 物理上拆分表数据、能并行执行查询 partition -- 逻辑上拆分大表数据提高查询性能、利于数据仓库维护工作 （2）查看表分区 pg_partitions --查看创建分区信息 pg_partition_templates --查看是用subpartition模板创建的子分区 pg_partition_column --查看分区字段 14.选择列的数据类型 （1）字符类型，gp中CHAR、VARCHAR、TEXT在性能上没有什么差异。但其它数据库系统中char有性能优势。在大多数情况下，可以用CHAR来替代TEXT 或 VARCHAR。 （2）数字类型，最好使用最小的数据类型。 用INT or SMALLINT 代替 BIGINT。 （3）当需要跨表做join的时候，需要保证数据类型是一致。 不然的话，gp将做数据类型转换，造成性能消耗。 （4）GP中还包含一些集合数据类型。 15.约束：兼容postgresql，包含：check、not null、unique、primary key，暂不支持Foreign Key 16.选择分布策略：DISTRIBUTED BY (哈希分布)和DISTRIBUTED RANDOMLY(随机分布 Round-Robin) 考虑条件(重要性依次排序) （1）Even Data Distribution：为了得到最好的性能，所有segment的数据量应该是相等的。如果出现不平衡的话，在查询的时候，数据量多的segment的负载就会很大。 （2）Local and Distributed Operations：要做join、sort或aggregation的操作的话，segment-level(segment内部)比system-level(segment之间)要快。 （3）Even Query Processing：每个Segment都获得相等的查询请求负载。 17.表存储方式 （1）Heap 或 Append-Only存储：GP默认使用堆表。堆表最好用在小表，如：维表(初始化后经常更新)。Append-Only表不能update和delete。一般用来做批量数据导入。 不建议单行插入。 CREATE TABLE bar (a int, b text) WITH (appendonly=true) DISTRIBUTED BY (a); （2）Row 或 Column-Oriented 存储：行存储、列存储、混合存储 a.数据需要更新 行存储 => 表数据被导入后，如果需要更新的话 列存储 => 只适合append-only表。 b.经常插入数据 行存储 => 如果频繁插入数据 列存储 => 对于写操作没有做优化 (同一行的列值必须写到磁盘的不同位置) c.多列查询请求 行存储 => 在select或where子句中，查询所有列或大部分列 列存储 => 在where或having子句中，查询单列的值汇总或单行过滤 SELECT AVG(salary)... WHERE salary > 10000 SELECT salary, dept ... WHERE state='CA' d.表中许多列 行存储 => 同一次请求很多列 或 行数据大小相对较小 列存储 => 使用宽表，查询时候仅仅查少数列 e.压缩 行存储 => 不可用 列存储 => 可用 如：(注：使用列存储必须是append-only表) CREATE TABLE bar (a int, b text) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a); （3）使用压缩 (Append-Only表才适用)：可以数据库内置的压缩(zlib 或 QuickLZ)。如果使用了已压缩的文件系统，建append-only表将不能使用压缩功能。 在选择append-only表的压缩类型和级别的时候需考虑一下因素：a.CPU的使用率；b.压缩率/磁盘大小；c.压缩速率；d.解压速率/扫描速率。 尽管我们为了减少数据容量大小而使用压缩功能，但是我们必须考虑到数据在压缩与解压的过程中的时间和cpu的消耗。 压缩的性能取决于硬件、查询调优设置、其它因素。 QuickLZ - 低压缩率、低cpu消耗、压缩数据块 zlib - 高压缩率、低速 CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); (注: QuickLZ的压缩级别只有level1，zlib能够设置从1-9) 18.Schema是Database中逻辑组织object和data。 在同一Database中，不同schema的对象可以使用相同的名称。例如：A schema 中表叫tab1，B schema中表也可以叫tab1。但是在同一个schema中就会报错。 SELECT * FROM myschema.mytable; 注意：如果sql中指定了schema名字的话，就查询指定schema，否则查询search path中配置参数。 （1）用户级schema public => GP默认安装，default schema （2）系统级schema pg_catalog => 包含系统数据字典表，内建的数据类型、函数和操作符。 information_schema => 包含标准视图的集合，视图都是从系统数据字典表中提出的信息。 pg_toast => 存放大对象 (GP内部使用) pg_bitmapindex => 存放bitmap索引对象 (GP内部使用) pg_aoseg => 存放append-only表 (GP内部使用) pg_toolkit => 管理schema,查询系统日志文件和其它系统度量单位。包含一些外部表、视图和函数。 19.Database GP能够创建1个或多个数据库，但是client程序每次只能访问1个数据库(不能跨库查询)。建库的时候不指定模板的话，默认使用template1(template1是在GP初始化就已经生成的)。 除这个模板以外，还有2个其它模板： template0 和 Postgres (供系统内部使用，不应该被删除和修改)。template0能创建一个完整干净的数据库，里面只有GP预定义的一些标准对象。 （1）3种建库方式 => CREATE DATABASE new_dbname; (客户端连接,必须是superuser或有建库权限) $ createdb -h masterhost -p 5432 mydatabase (命令行建库) => CREATE DATABASE new_dbname TEMPLATE old_dbname; (克隆数据库) （2）2种删库方式 (注:删库是不能回滚,慎用！) => \c template1; => DROP DATABASE mydatabase; (客户端连接,先连接template1,然后再drop) $ dropdb -h masterhost -p 5432 mydatabase(命令行) （3）管理 SELECT datname FROM pg_datbase;(列出数据库清单) => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog; (修改数据库参数) 20.Tablespace & Filespace Tablespaces 允许每台机器使用多种文件系统，并决定如何才能使用最佳的物理存储方式。 表空间被使用的多种原因：a.针对数据使用频率来选择不同的存储类型；b.控制某些数据库对象的 I/O Performance。 filespace指的是所有组件的文件系统位置的集合。1个filespace可以被1个或多个tablespace使用。以下2个表空间使用的filespace是pg_system(GP初始化的时候指定的)： pg_global --(存放系统元数据)gp_default --(template0和template1库的缺省表空间)
21.【限制并发】postgresql.conf文件参数 （1）max_connection --最大连接数 要变更的话，master和segment都要修改。 segment的值必须是master的5-10倍。 （2）和max_connection相关参数，max_prepared_transactions --最大预备事务数 master的max_prepared_transactions必须设置为大于或等于max_connection, segment的max_prepared_transactions应该设置和master一样。 例如: 在$MASTER_DATA_DIRECTORY/postgresql.conf(含standby master)中: max_connection = 100 max_prepared_transactions = 100 在$SEGMENT_DATA_DIRECTORY/postgresql.conf中: max_connection = 500 max_prepared_transactions = 100 修改步骤如下： a.停库 $ gpstop b.修改master参数 c.修改每个segment参数 d.重启 $ gpstart 22.【加密的C/S连接】GP支持SSL连接。 条件如下： 1.client和master server需要安装openSSL 2.设置master中postgresql.conf参数：ssl=on。当ssl模式开启时候，将会搜索master的数据目录包含的2个文件: server.key(server private key)和server.crt (server certificate) 23.GP是通过roles来管理数据的访问控制。Roles包含2个概念：Users和Groups。 一个role可以是一个数据库的user或group，也可以是两者兼备。Role能拥有数据库的对象（例如：tables），并且能够把访问数据库对象权限开放给其它的role。一个Role也可是另一个角色的成员，子role可以继承父role的权限。 （1）每个GP数据库系统可以说是数据库roles(users和groups)的集合。一般登入时默认使用操作系统当前用户登入。Roles被定义成系统级别，意味着在系统中他们能访问所有的数据库。 （2）【gpadmin用户】：在初始化数据库的时候，一般都使用gpadmin (既是操作系统用户，又是数据库的超级用户),它拥有数据库的最高权限。gpadmin的权限太大了，一定要控制好权限，只用来做一个数据库维护工作(如：数据库升级或扩展)。 （3）Role和User的区别：ROLE + LOGIN权限 = USER （4）【创建Groups】 Role也是一个Group，使用GRANT和REVOKE来增加和删除role。 例如： =# CREATE ROLE admin CREATEROLE CREATEDB; =# GRANT admin TO john, sally; =# REVOKE admin FROM bob; 可以针对独立对象权限进行授权： =# GRANT ALL ON TABLE mytable TO admin; =# GRANT ALL ON SCHEMA myschema TO admin; =# GRANT ALL ON DATABASE mydb TO admin; 24.【查询计划】：查看Query Plan的时是从底至上的。 Motion --主要是涉及到多个节点之间完成任务的话，节点之间会发生数据移动。 Slice --GP在执行查询时为了获得最优性能，把任务进行了切片。一个查询计划是根据motion来进行切分的，每个slice以motion作为边界。 Redistribute Motion --Segment之间移动数据。这个是非常之消耗性能的。在前期表设计的时候就要避免这样的操作。 Gather Motion --Segment将数据传给Master。不是所有的查询计划都调用gather motion。例如：CREATE TABLE AS SELECT ...这个就没有gather motion,结果被存储到新表里面是不需要经过master的。 25.【并行查询】 Query Dispather(QD) --Master节点查询任务处理，负责创建和分配查询计划、收集传送最终结果 Query Executor(QE) --Segment节点查询处理，负责完成数据的计算、多个QE之间的中间结果通讯。为查询计划的每个slice会被分配到至少一个worker process Gangs --工作于相同slice的查询计划的相关process。当一个slice任务完成后,这个gang会把数据向上级gang传递。内部通信是通过内部进程来控制的。 26.GP测试加载效率达到 每小时2TB。 27.【收费方式】 1. 按容量收费，1T起步，买的越多的话价格越便宜 2. 买一体机Data Computing Appliance (DCA)，EMC的硬件和GP绑在一起卖的 28.关于interconnect冗余 为了保证网络高可用，需要部署2个千兆交换机。如果是线下测试环境的话，只要部署1个千兆交换机就行了。 Interconnect网络连接层作用：负责各segment节点进程通信，使用标准的千兆交换机。 数据传输缺省使用UDP协议。使用UPD时，GP会做额外数据包校验和对未执行的也会做检查。故在可靠性上，基本和TCP上是等价的，在性能和扩展性上，却优于TCP。 使用TCP的话，GP有1000个segment的限制，UDP则没有。 29.Greenplum数据库是在postgreSQL开发出来的，基于MPP(massively parallel processing)和shared-Nothing架构（Oracle RAC是shared everything架构）。 主要用在数据仓库中，做大规模数据和复杂的查询功能所涉及。 与现有的数据仓库解决方案(Oracle、IBM、Microsoft、Sybase和Teradata)相比有他自己的优势：1.速度更快 2.支持数据量更大，扩展性较好 3.价格更低 缺点：1.对局域网带宽要求很高，一般都是千兆交换机。2、不支持在线扩容，扩容的话至少要增加2台以上的机器。后若不是成2倍扩展，需要重新平均分布所有数据。