Gaussdb中100万条数据（50列）大概能占用多大空间？

HDC.2024大会可以了解各行各业的新技术和未来发展趋势，倾听业界各行各业大咖的精彩演讲，大家分享观会体会和心得？欢迎表达出来

1. 存在锁争抢在cn上执行select * from pg_stat_activity where query like '%vacuum%';找到vacuum full的pid查看该线程的等待状态，如果等待状态是acquire lock，说明存在锁等待select * from pg_thread_wait_status where tid = 139878309295872;在pg_locks中查询vacuum full在等哪个锁select * from pg_locks where pid = 139878309295872 and granted = 'f';查看持有该锁的线程select * from pg_locks where relation = 544793 and granted = 't';查看该线程对应的语句select query from pg_stat_activity where pid = 139877539612416;根据语句判断是否可以杀掉该语句继续做vacuum full，或者另外找时间窗做vacuum full。2. 存在IO/网络问题导致事务无法提交执行一个简单的create table语句，如果create table语句执行也很慢，说明存在IO/网络问题，进一步排查IO和网络。3. 系统表过大导致vacuum full慢vacuum full任意一张表时，都会扫描pg_class、pg_partition、pg_proc三张系统表，当这三个系统表过大时，也会导致vacuum full较慢。可以在排除IO/网络问题（即create table语句不慢）后，对空表做vacuum full，观察执行速度，如果空表做vacuum full也比较慢，则说明就是这三张系统表较大导致vacuum full任意表都慢。4. 排除以上场景之后，可以查看表定义中是否使用了PCK当存在PCK时，表做vacuum full时会进行全排序，此时如果表较大或psort_work_mem设置较小，就会导致PCK排序时产生下盘，进行外排，效率急剧下降。可以通过调大psort_work_mem进行规避。

1、vacuum只是将删除状态的空间释放掉，转换到能够重新使用的状态，但是对于系统来说该数据块的空闲空间并没有反应到系统的元数据中，并不进行空间合并。而vacuum full实质上是重建了整个表，以达到空间合并的效果。2、vacuum执行过程中对表加4级锁，不会影响表的增删改查，而vacuum full对表加8级锁，执行过程中表无法访问。3、vacuum对列存表无效。建临时表：数据库会新建一个临时表，临时表继承老表所有属性。这个阶段会对pg_class申请“RowExclusiveLock”锁，因为需要插入记录。拷贝数据：将原来的数据copy到temp表中。对临时表，老表以及索引都以“AccessExclusiveLock”模式打开。另外对于toast，只是lock，不打开。在这个过程中完成Dead Tuple的清理。重建索引：是在交换之后完成的，重建索引时，会更新一些统计信息。对表申请“ShareLock”锁。删除临时表：索引重建完成后，将带有老物理文件的新临时表进行删除。

回收空间数据库总是不断地在执行删除，更新等操作。良好的空间管理非常重要，能够对性能带来大幅提高。执行delete操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的update或insert操作中该部分的空间是不能够被重用的。在数据库中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。经过vacuum清理后，空间才能得到释放。VACUUM回收已删除元组占据的存储空间。在一般的数据库操作里，那些已经DELETE的元组或者被UPDATE过后过时的元组是没有从它们所属的表中物理删除的；在完成VACUUM之前它们仍然存在。因此我们有必须周期地运行VACUUM，特别是在常更新的表上。冻结tuple的xid在每条记录（tuple）的header中，存放xmin,xmax信息(增删改事务ID)。transactionID的最大值为2的32次，即无符整形来表示。当transactionID超过此最大值后，会循环使用。这会带来一个问题：就是最新事务的transactionID会小于老事务的transactionID。如果这种情况发生后，就没有办法按transactionID来区分事务的先后，也没有办法实现MVCC了。因此用vacuum后台进程，按一定的周期和算法触发vacuum动作，将过老的tuple的header中的事务ID进行冻结。冻结事务ID，即将事务ID设置为“2”（“0”表示无效事务ID；“1”表示bootstrap，即初始化；“3”表示最小的事务ID）。被冻结的事务ID比任何事务都要老。这样就不会出现上面的这种情况了。更新visibility map在数据库中，有一个visibility map用来标记那些page中是没有dead tuple的。这有两个好处，一是当vacuum进行scan时，直接可以跳过这些page。二是进行index-only scan时，可以先检查下visibility map。这样减少fetch tuple时的可见性判断，从而减少IO操作，提高性能。另外visibility map相对整个relation，还是小很多，可以cache到内存中。

1、enable_show_any_tuples参数可以显示已经被delete但是还未vacuum回收的脏数据，此参数只在只读事务中生效，是session级的参数；在这里我们需要将脏数据按照xmax排序，取最大的xmax，这是为了保证我们只恢复此次delete删除的数据，不多恢复之前已经delete的数据；在这里不能使用建新表insert into的方式恢复，因为enable_show_any_tuples参数只在只读事务中生效，一旦在dn开启read write事务，此参数将失效，也就无法再看到delete的数据。2、通过gsql的方式，将查询结果导出到文本，如果数据量较大，可以分批处理gsql -d postgres -p 25330 -c "set enable_show_any_tuples = on; select * from test where a < 50 and xmax = '413471'" > result；3、对文本进行处理，通过copy的方式重新导回到数据库内；4、对每个dn都执行以上操作，即可恢复误删除的数据。

unique sql视图提供了丰富的信息，用户可以根据需要选取对自己有帮助的信息使用。本节针对客户在生产环境中遇到的实际情况，举例说明几种该视图的使用方法，可供性能优化参考。查询异常的行活动导致的磁盘争用     异常的行活动可能引起磁盘争用，导致业务运行缓慢。通过查看扫描的行数、返回的函数、更改的行数等指标的波动情况，可以发现异常的行活动，帮助定位原因。postgres=# select sum(n_returned_rows) n_returned_rows, sum(n_tuples_fetched) n_tuples_fetched,    sum(n_tuples_returned) n_tuples_returned, sum(n_tuples_inserted) n_tuples_inserted,    sum(n_tuples_updated) n_tuples_updated, sum(n_tuples_deleted) n_tuples_deleted from pgxc_instr_unique_sql; n_returned_rows | n_tuples_fetched | n_tuples_returned | n_tuples_inserted | n_tuples_updated | n_tuples_deleted    查询Top SQL对资源的占用情况     可以基于执行时间、CPU时间、扫描行数、物理读/逻辑读等指标，对unique SQL视图中的SQL语句进行排序，找出占用资源最多的那些SQL语句，有针对性地其分析对性能的影响和原因，帮助查找和定位问题。例如,按SQL执行时间顺序或倒序排序：SELECT user_name, unique_sql_id, query, total_elapse_time FROM pgxc_instr_unique_sql ORDER BY total_elapse_time ASC 或 DESC;按SQL执行占用CPU时间进行顺序或倒序排序：SELECT user_name, unique_sql_id, query, cpu_time FROM pgxc_instr_unique_sql ORDER BY cpu_time ASC 或 DESC;按SQL顺序扫描行数顺序或倒序排序：SELECT user_name, unique_sql_id, query, n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_returned ASC 或 DESC;按SQL总扫描行进行顺序或倒序排序：SELECT user_name, unique_sql_id, query, n_tuples_fetched + n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_fetched + n_tuples_returned ASC 或 DESC;按SQL执行执行器时间进行顺序或倒序排序：SELECT user_name, unique_sql_id, query, execution_time FROM pgxc_instr_unique_sql ORDER BY execution_time ASC 或 DESC;按SQL执行物理读次数进行顺序或倒序排序：SELECT user_name, unique_sql_id, query, n_blocks_fetched FROM pgxc_instr_unique_sql ORDER BY n_blocks_fetched ASC 或 DESC;按SQL执行逻辑读次数进行顺序或倒序排序：SELECT user_name, unique_sql_id, query, n_blocks_hit FROM pgxc_instr_unique_sql ORDER BY n_blocks_hit ASC 或 DESC;查询逻辑读/物理读数量     逻辑读/物理读过多可能导致SQL语句占用较多的CPU时间。通过查询unique SQL视图可以得到sql语句逻辑/物理读数据块的数量，辅助判断响应过慢的原因：查询物理读块数量：SELECT n_blocks_fetched FROM pgxc_instr_unique_sql;查询逻辑读块数量：SELECT n_blocks_hit FROM pgxc_instr_unique_sql;诊断内存配额不足导致性能低下 如果数据库缓冲区设置得太小，会导致每个SQL语句执行的结果不能被缓存，当前SQL执行完毕如果有其他SQL执行就会把内存中上一个或上几个SQL缓存的执行结果挤出去，下一轮如果当前这个SQL再次执行时候又需要从磁盘进行物理IO读取数据，而不能直接从缓存中获取数据，进而导致SQL执行性能较差。     缓冲区配额是否足够大，可以通过命中率来判断。缓冲区命中率=n_blocks_hit/n_blocks_fetched，可以通过查询unique SQL来诊断是否存在内存配额不足的问题：SELECT (n_blocks_hit/ n_blocks_fetched) AS hit_ratio from pgxc_instr_unique_sql;

用户执行SQL语句时，每一个SQL语句文本都会进入解析器（Parser），生成“解析树”（parse tree）。遍历解析树中各个结点，忽略其中的常数值，以一定的算法结合树中的各结点，计算出来一个整数值，用来唯一标识这一类SQL，这个整数值被称为Unique SQL ID，Unique SQL ID相同的SQL语句属于同一个“Unique SQL”。例如，用户先后输入如下两条SQL语句：select * from t1 where id = 1;select * from t1 where id = 2;      这两条SQL语句除了过滤条件的常数值不同，其他地方都相同，由此生成的解析树的拓扑结构完全相同，故Unique SQL ID也相同。因此两条语句属于如下同一个Unique SQL：select * from t1 where id = ?;      GaussDB内核会对所有上面形式的SQL语句汇总统计信息，通过视图呈现给用户。通过这种方式，可以排除一些无关的常量值的干扰，获得某一类SQL语句的统计数据，为性能分析和问题定位提供数值依据。注意：对于Unique SQL ID的计算，只会排除常数值，而不会排除其他的差异。例如，SQL语句“select * from t2 where id = 1;” 与上面的SQL不属于同一个Unique SQL，不同用户，从不同的CN节点执行的相同的SQL语句也不属于同一个Unique SQL。Unique SQL如何统计收到SQL请求后，GaussDB内核首先算出其Unique SQL ID。如果该Unique SQL ID已存在，则直接更新相关的统计信息。如果不存在，首先创建一个Unique SQL，然后再更新统计信息。

CM 组件提供了四种服务 CM Agent, CM Server, OM Monitor, cm_ctl，与各类实例服务组件（CN, DN, GTM 等）一起构成了整个数据库集群系统。cm_ctlCM提供的外部接口工具，通过命令行执行集群的启动、停止、状态查询、主备倒换、备机重建等功能除启动和停止外，主要通过与 CM Server 的消息传递执行命令可在任意节点执行并获取到相同的结果对应 cm_ctl 二进制文件，非常驻服务OM Monitor由系统定时任务拉起负责 CM Agent 的运行状态监控对应 om_monitor 二进制文件，所有节点常驻服务CM Agent由 OM Monitor 拉起负责拉起和停止所在节点的 CN, DN, GTM, CM Server（如果存在）；监控实例状态并上报至 CM Server；执行 CM Server 下发的命令等对应 cm_agent 二进制文件，所有节点常驻服务CM Server由 CM Agent 拉起，是整个集群管理组件的大脑负责接收 cm_ctl 发送的命令并下发至 CM Agent；接收并处理 CM Agent 上报的实例状态，下发仲裁指令保证各类故障和异常场景下集群的可用性对应 cm_server 二进制文件，常驻服务CM与各类组件的主备数据同步、倒换、重建等机制高度融合，提供告警、重启、倒换、隔离等手段，赋予数据库实例故障恢复及自愈的高可用（HA）能力，保证数据的可靠性和完整性，最终实现集群对外的业务连续性。该部署形态的特点是：多个 CN 对等在任意 CN 上执行 SQL 语句均可得到相同的结果GTM 主备架构主 GTM 故障后，备 GTM 升主提供服务DN 主备从架构数据通过 shard 的方式存储在多个主 DN 上，并且有两个副本，因此任意单点故障不会导致数据丢失可交叉部署成为安全环CM Server 主备架构主 CM Server 故障后，备 CM Server 升主提供服务。

利用GaussDB(DWS)的迁移工具，用户能够非常容易的将数据从线下的Teradata、Oracle等传统数仓快速搬迁上云。迁移主要分为应用迁移和数据迁移两部分。应用迁移是指由于线下传统数据仓库的语法及功能不同，导致业务脚本、存储过程等需要改造适配，为此，GaussDB(DWS)把深耕市场多年、成功迁移数十套Teradata和Oracle数仓的成功经验，开发为一套完整的语法迁移工具，能够支持对数据类型、SQL语法、DSQL脚本、存储过程等语法的自动化转换，对Teradata的常用语法自动化转换率超过90%，对Oracle超过60%。对于动辄几十TB、数百TB的海量数据而言，数据迁移速度极大程度影响业务停机的时间，这对网络、入库能力和迁移工具的效率都提出了很高的要求，以我们去年的某次数据搬迁为例，1PB的数据仅用11小时即完成传输，加上准备工作和数据校验的时间，端到端也仅用时17小时，搬迁速率91TB/小时，并且做到数据0丢失。 

产品所有内部组件CN、DN、GTM、CM等采用多活或主备设计，通过集群管理进行故障检测和切换。其次，在硬件层面，除了最基本的宕机、断网的直接故障外，GaussDB(DWS)还针对夯死、慢节点、亚健康等僵而不死的复杂场景，做了大量的建模和针对性优化，能够实现故障的准确探测和自愈。在数据可靠性方面，对于数仓而言，数据存一份有单点故障问题，存三份又太浪费资源，一般来讲数据一主一备是个相对合理的选择，但在故障造成网络分区的场景下，很容易出现双主“脑裂”问题，造成数据不一致。GaussDB(DWS)独创的“主-备-从”技术，引入“主”、“备”、“从”三种角色。集群正常时数据仅在主备间进行同步，发生单点故障时数据向“从”同步，从而保证任何状况下都有两副本的数据冗余。在网络分区等异常场景下，一旦主备产生数据分叉，从备又可以承担仲裁者的角色，通过日志比对找到持有正确数据的节点继续提供服务。从而既完美解决了一主一备的脑裂问题，又能够仅用两副本空间代价实现接近三副本的可靠性。对于可靠性要求更高的客户，我们还提供了双集群容灾能力，通过跨AZ、跨Region的物理复制，实现异构集群容灾。时间有限，我们本次只粗略介绍了GaussDB(DWS)高可用技术的一小部分，通过多年的技术积累，我们基本做到了“数据无忧、永远在线”的目标。

实时数仓的快主要体现在两个方面。首先是入库速度快，与传统数仓不同，数据的加载不再是T+1的大批量加载模式，而是更加实时的高并发小批量模式。DWS实时数仓时序数据单机入库性能达10w/s，流数据达60w/s，并能够线性扩展。其次是计算分析快，支持基于流式数据的持续计算查询，预置了丰富的时序和流处理函数，通过SQL即可完成复杂流式计算，可实现亿级数据，秒级聚合。正所谓一切皆SQL，经历了几十年的发展，SQL依然是最简洁高效的数据开发语言，能极大的简化应用开发。以Druid监控的一个场景为例，原先1900行的脚本，在GaussDB(DWS)实时数仓中采用SQL语句，仅用150行代码就能实现同样的功能，开发效率提升10+倍。从技术上看，大集群需要攻克通信风暴、故障容错和数据备份恢复一致性三大难题。我们通过独创的Multi-Streams多流通信技术，支持集群内百亿级的通信连接，突破了大规模通信的技术瓶颈。在高可用方面，大规模集群下硬件故障成为常态，我们积累了多年，做了大量硬件故障感知及容错处理的工作，来保证大规模集群下的集群自愈和业务可用。在备份恢复方面，我们不仅通过多层级并行实现了线性扩展，还做到了完全在线的全局强一致物理备份，甚至支持表级别的细粒度恢复，竞争力达到了业界领先。

融合分析能力是云原生数据仓库GaussDB(DWS)核心亮点之一。GaussDB(DWS)采用一套SQL引擎，支持Oracle、Mysql、HDFS等多源数据融合分析，并通过算子下推、加速集群等技术对分析性能进行了大幅优化，在数据免搬迁的前提下，实现了跨源数据免搬迁、高效分析。GaussDB(DWS)云原生数据仓库支持冷热数据多温存储，热数据存储于数仓内部，以获得良好的查询分析性能，冷数据可分级存储到更低成本的OBS中，不仅降低存储成本，并且在OBS内，通过合法鉴权，数据能够共享开放，供其他引擎处理分析，GaussDB(DWS)当前已经支持表内不同分区间的冷热数据存储，未来还将支持更细粒度、更加智能的冷热数据管理。并行的第一个层级，是集群内物理节点间的并行，CN将计划动态分布到多个服务器，通过分布式执行框架，将查询计划在集群内多台物理节点并行执行；第二个层级，是算子级并行，在每个服务器内，查询算子能够利用一个节点内多个CPU核心进行并行计算；第三个层级，是在一个CPU核心的指令序列中支持SIMD指令，结合我们的向量化引擎，实现一个指令同时操作多条数据。同时，我们还集成了现代编译器技术，利用LLVM框架，运行时动态生成执行代码，减少无关指令生成；数据量越大，可获得的性能提升效果越好。正是因为有这样一个全并行计算引擎，我们可以将系统资源最大化利用，提供极致的分析性能。随着金融风控，以及IoT场景对数据实时处理分析的诉求，我们正式发布了GaussDB(DWS)实时数仓版本，快上加快，将快发挥到极致。

数据库的使用者在书写SQL语句时，会根据自己已知的情况尽力写出性能很高的SQL语句。但是当需要写大量SQL语句，且有些SQL语句的逻辑极为复杂时，数据库使用者就很难写出性能较高的SQL语句。而每个数据库都有一个类似人的大脑的查询优化器模块，它接收来自语法分析模块传递过来的查询树，在这个查询树的基础上进行逻辑上的等价变换、物理执行路径的筛选，并且把选择出的最优的执行路径传递给数据库的执行器模块。查询优化器是提升查询效率非常重要的一个手段。由于优化器基于统计信息和估算模型生成计划，当估算出现偏差时，计划可能出现问题，性能较差，使语句的执行变得奇慢无比。通常，查询优化器的优化过程对数据库使用者是透明的。在上一篇博文《GaussDB(DWS)性能调优系列实战篇五：十八般武艺之路径干预》中，Gauss DB(DWS)提供了可通过配置GUC参数的方式，全局的干预查询计划的路径生成。本次，将介绍另一种可以人工干预计划生成的功能--plan hint。Hint是一种通过SQL语句中的注释传递给优化器的指令，优化器使用hint为语句选择执行计划。在测试或开发环境中，hint对于测试特定访问路径的性能非常有用。例如，您可能知道某些表优先进行连接，可以有效减少中间结果集大小，在这种情况下，可以使用提示来指示优化器使用更好的执行计划。Plan hint功能属于语句级的调控，仅对当前语句的当前层次生效，可以帮助我们在调优的过程中，针对特定的语句，通过plan hint进行人工干预，选择更高效的执行计划。GaussDB(DWS)的Plan hint有以下种类:Join顺序的hint：调整join顺序Scan/Join方法的hint：指定或避免scan/join的方法Stream方法的hint：指定或避免redistribute/broadcast行数hint：对于给定结果集，指定行数，或对原有估算值进行计算调整倾斜值hint：在倾斜优化时，指定需要倾斜处理的特殊值

 索引是对数据库表中一列或多列的值进行排序的一种结构；使用索引可快速访问数据库表中的特定信息；分类：行存表索引/列存表索引- 行存表索引 - B-Tree索引：适合数据重复度低的数据字段， 例如 身份证号码 等字段；*B-Tree索引 - 优点：有B-tree索引，就像翻书目录一样，可以通过索引直接定位到要查询的数据（减少了I/O操作）；另外查询性能与表中数据量无关；*注意：不适合键值重复率较高的字段上创建B-Tree索引；- 列存表索引 - PCK索引（Partial Cluster Key 局部聚集）：一种针对列存的约束条件；一般在建表时创建，在数据导入时，根据约束，在列存存储单元（CU）内对数据做聚集；*Psort索引：一种列存局部索引，对列存存储单元（CU）内的数据，创建局部索引(MIN/MAX index稀疏索引)，提高查询效率；*PCK索引 - 注意：先创建后使用，之前入库的数据不会自动根据索引聚集；行存选择索引注意事项：    - 查询条件列上创建B-tree index, 也可以创建组合索引, distinct值比较少的列不适合建立index；    - 行存不适合建立太多B-tree index, 然后做数据导入，这样的导入性能非常差；一般这种情况需要禁用该表的索引，待数据导入后重建index；列存选择索引注意事项     - 查询条件出现最多的列，例如filter条件或者join列上建立partial cluster key（约束）；    - 条件列上可以建立psort index, 也可以创建组合索引；