解决方案:openGauss自动驾驶平台DBMind索引推荐功能在民生银行的生产实践

　　解决方案:openGauss自动驾驶平台DBMind索引推荐功能在民生银行的生产实践

　　一、指数推荐背景

　　1.1 指数推荐意义

　　索引推荐作为关系数据库系统中的一个重要问题，越来越受到人们的关注。索引的目的是为了提高查询效率，就好比字典的检索页。试想一下，如果没有检索页的位置，对于数据库中乱序插入的字典，每次都需要检查所有的行，才能找到需要的数据。，对于一张几万条记录、几百万条记录的表来说，成本是难以接受的。不同场景对索引配置的要求不同。比如数据库长时间不做update操作，追求极致的查询性能，可以添加尽可能多的索引。相反，如果数据库经常更新，

　　DBMind是openGauss自动驾驶平台，提供智能指标推荐服务。根据检测到的数据库负载，它可以识别性能不佳和可以改进的 SQL。基于全工作量的成本增加，综合考虑推荐索引的“性价比”，给出了索引配置。结果。另外，对于系统中存在冗余索引，会进行提示，运维人员可以进行相应的索引清理。

　　1.2 民生银行的业务特点

　　民生银行拥有非常庞大的用户群。openGauss在民生银行承载了多种类型的生产业务，其中大部分以复杂查询为主。在一些典型场景下，复杂业务的SQL语句甚至超过40kb。对于这种形式的SQL语句，如果靠人工经验进行索引调优，浏览SQL语句是一件非常痛苦的事情。索引调优显然更加困难。

　　同时，在民生银行的生产场景中，还具有以下业务特点：

　　基本上，Java 连接器用于连接数据库以执行 SQL 语句。执行的SQL语句是PBE（Parse Bind Execute）语句，以prepare-execute的形式执行，看不到SQL语句参数的具体值 ；

　　由于业务层使用了ORM（Object-Relational Mapping）框架（如MyBatis），大部分业务SQL都是自动生成的，手动理解难度较大；

　　同时，民生银行拥有上百个数据库节点。随着业务的发展，如果每个实例都需要手动配置，其工作量可想而知。

　　由于民生银行的数据库使用场景较多，对索引推荐的要求也各不相同。具体有以下几种业务场景：

　　当前正在运行的业务很慢，您想为当前正在运行的业务做指标推荐。这时候需要对pg_stat_activity系统表中显示的SQL语句做索引推荐；

　　如果需要分析过去某段时间的SQL语句流向，可以使用ASP函数从pg_asp系统表中获取SQL语句的id，然后与记录在dbe_perf.statement表获取这段时间采样的SQL语句，然后对其进行分析；

　　需要对业务SQL语句进行全量分析，但是没有部署SQL流量监控平台，所以需要从pg_log数据库日志中获取SQL执行日志流量。

　　通过民生银行一段时间的生产实践，利用各种复杂的边界场景进行测试，进一步加强了DBMind的指标推荐功能，在民生银行的生产实践中取得了满意的效果。性能提升从50%到数倍不等。

　　接下来，对于整个索引推荐流程，我们以下面的SQL流采集

方式为例，详细说明如何采集

SQL流，然后对这段时间的工作负载进行索引配置。

　　2.SQL管道集合

　　索引推荐是根据用户给定的加载文件进行推荐分析，其格式为一批以分号分隔的SQL语句，例如：

　　SELECT c1, c2 FROM t1;SELECT count(1) from t2;SELECT c1, c2 FROM t1, t2 WHERE t1.id = t2.id;SELECT count(1) from t2;SELECT c1, c2 FROM t1, t2 WHERE t1.id = t2.id;…

　　openGauss支持多种SQL流水线采集，在用户无法提供SQL流水线时帮助用户采集SQL流水线。

　　2.1 从日志中采集

SQL管道

　　a) 分别查询当前数据库的GUC参数：

　　此时可以记录log_statement和log_min_duration_statement这两个参数，用于后续的参数恢复。

　　tpcc=# show log_statement; log_statement --------------- none(1 row)<br />tpcc=# show log_min_duration_statement; log_min_duration_statement ---------------------------- 1min(1 row)<br />tpcc=# show log_line_prefix;  log_line_prefix   -------------------- %m %u %d %h %p %S (1 row)

　　b) 通过gs_guc函数设置GUC参数，在数据库节点开启完整的SQL管道采集

：

　　gs_guc reload -D $DATADIR  -c "log_min_duration_statement = 0" -c "log_statement= 'all'"

　　参数说明

　　其中，设置log_min_duration_statement为0表示采集

所有的SQL语句；设置log_statement为all表示在pg_log错误日志中记录SQL语句信息。这里的gs_guc命令可以通过修改postgresql.conf配置文件来修改数据库参数。这里的参数-D用于指定postgresql.conf配置文件所在的目录。gs_guc的其他配置参数可以使用--help命令查看，也可以参考命令的文档。

　　注意：相关参数对性能有一定影响，请谨慎使用。

　　c) 日志文件分析，采集

指定时间段的SQL流

　　gs_dbmind component extract_log $GAUSSLOG workload.sql '%m %c %d %p %a %x %n %e' -d postgres -U omm --start_time '2021-07-06 00:00:00'

　　参数说明

　　gs_dbmind是openGauss的DBMind函数的调用命令；$GAUSSLOG用于指定pg_log日志的存放目录，其中收录

多个不同时间段的日志文件：

　　dbmind_user@linux173 ~/test/data/pg_log                                                                                                                           > $ ls                                                                                                                                                                                    postgresql-2022-06-06_115802.log  postgresql-2022-06-22_000000.log postgresql-2022-07-25_000000.log  postgresql-2022-09-04_000000.log postgresql-2022-09-28_000000.log

　　日志内容如下，收录

时间、数据库、SQL等信息：

　　

" />

　　输出的workload.sql如下：

　　SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = '4' AND s_quantity < '15' AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = '4' AND d_id = '6'         )     ) AS L;SELECT c_first, c_middle, c_last, c_balance     FROM bmsql_customer     WHERE c_w_id = '4' AND c_d_id = '10' AND c_id = '1021';SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = '4' AND o_d_id = '10' AND o_c_id = '1021'       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = '4' AND o_d_id = '10' AND o_c_id = '1021'          );

　　d) 数据库节点恢复相关的GUC参数

　　gs_guc reload -D $DATADIR  -c "log_min_duration_statement = 1min" -c "log_statement= none"

　　使用pg_log记录SQL语句的好处是获取的SQL语句全面，不易遗漏；缺点是采集数据量大，需要注意磁盘空间占用。

　　注意：需要恢复 GUC 参数以避免日志文件膨胀。

　　2.2 基于ASP系统表的SQL采集

　　如果用户比较关注一段时间内的ASP采样SQL，需要保证数据库开启ASP相关参数，通过系统表gs_asp获取指定时间段内的SQL。由于ASP表中并没有记录具体的SQL语句内容，所以我们需要和dbe_perf进行通信。语句视图（必须有sysadmin或monitor admin权限才能查询视图）获取SQL语句的内容。由于dbe_perf.statement表只能在postgres数据库下查询，所以我们需要在postgres数据库下执行如下查询语句：

　　SELECT regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\\n\\r]+', ' ', 'g') as q FROM dbe_perf.statement S INNER JOIN gs_asp G ON G.unique_query_id = S.unique_sql_id INNER JOIN pg_database D ON G.databaseid = D.oid WHERE D.datname ='{database}' AND G.sample_time &gt; '{start_time}' and G.sample_time &lt; '{时间结束}';

　　用户可以将上述查询语句中的{database}、{start_time}和{end_time}的内容替换为自己想要查询的值。

　　使用这种方式采集

SQL语句的好处是占用存储空间小，但是依赖的dbe_perf.statement系统表中的SQL语句数据已经被匿名化，存在一定程度的失真；同时，ASP机制是抽样采集，可能无法完全覆盖。

　　2.3 基于语句系统表的SQL集合

　　如果数据库中没有启用ASP（即enable_asp参数的值为off），我们在一段时间内是无法显式知道SQL管道的。此时可以通过视图dbe_perf.statement获取指定数据库的所有SQL信息。同样的，你必须有sysadmin权限或者monitor admin权限才能查询视图，你可以替换下面语句中的{database}和{schema}字段：

　　选择 regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\\n\\r]+', ' ', 'g') as q from dbe_perf.statement其中 db_name='{database}' 和 schema_name='{schema}'；

　　该方法是在没有启用ASP时的一种备份方法，无法显式获取一段时间内的SQL语句信息，获取的信息是全局视角的统计信息。

　　2.4 基于pg_stat_activity系统表的SQL采集

　　当用户需要优化当前执行的SQL语句时，通过pg_stat_activity获取当前执行的语句，将{database}字段替换为需要查询的数据库名。这种方式的优点是采集

成本小，处理时间短，缺点是只能看到当前正在执行的语句。

　　SELECT regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\\n\\r]+', ' ', 'g') as q FROM pg_stat_activity WHERE state != 'idle' and datname='{database}';

　　2.5 获取SQL管道的一些问题

　　从dbe_perf.statement和dbe_perf.statement_history视图获取的SQL语句默认不收录

具体值，具体值在openGauss中会被替换为问号（？）字符，以达到数据保密的效果。DBMind的索引推荐可以以“模板”的形式对SQL语句进行分析，但是分析的粒度也是以“模板”的形式为基础的。如果用户希望关闭参数匿名化过程，可以将GUC参数track_stmt_parameter的值设置为on；

　　对于PBE形式的SQL管道，DBMind也推荐这种形式的PBE。PBE流程是SQL语句的形式，带有参数占位符“$”，它们在openGauss数据库内部的执行逻辑不同于带有特定值的SQL语句的执行流程。它也不同于参数匿名化的过程（值用？代替）。因此，如果只是想为PBE形式的SQL语句推荐索引，不需要将参数track_stmt_parameter的值设置为on；

　　如果SQL语句的长度很长，可能会超过openGauss数据库中为字符串分配的长度，所以数据库内部的系统表或视图中记录的值可能会被截断。也就是说，从数据库的系统表或视图中获取的SQL语句是不完整的。这时，如果想尽可能完整地记录SQL语句的全貌，就需要将截断阈值设置一个较大的值。该参数由 GUC 参数 track_activity_query_size 决定。默认值为 4096 个字符。在实际生产场景中，可以设置较大的值。有点，比如40960个字符，但是需要考虑内存的实际情况。如果内存不够，则需要进行权衡。

　　三、指数推荐的使用

　　3.1 指标推荐算法简介

　　图中各部分含义如下：

　　Indexable Columns：候选索引列，是候选联合索引中列的来源

　　多列索引生成：候选联合索引

　　原子配置：原子索引配置

　　Configuration Enumeration：通过贪心算法枚举索引配置

　　上图所示的方法是工作负载级索引推荐的典型工程实现框架。在DBMind的具体实现过程中，有很多优化细节和改进的实现方式。工程实现的内容不是本文的重点，不再详述。在这里，大致介绍一下各个部分的流程：

　　根据索引生成算法为单条SQL语句推荐索引

　　基于openGauss优化器筛选和验证推荐结果，生成候选索引

　　通过贪心策略在当前工作负载上生成最优索引配置

　　整个索引推荐过程使用虚拟索引，避免了索引创建过程带来的不可避免的时间和空间开销

　　虚拟索引的逻辑和真实优化器规划的一样，不用担心评估结果不正确导致的问题。

　　

" />

　　3.2 OLTP场景

　　On-line Transaction Processing，在线交易处理强调对大量在线日常交易数据的处理。该场景操作的数据量较小，事务往往比较快，涉及增删改查。在配置索引时，只保留提升较大的索引，并整合相关索引，避免不必要的写入开销。

　　这是因为索引维护成本在OLTP场景下会更加明显。如果为表中的字段创建了很多索引，在查询时不受影响，但在update、delete、insert操作时会受影响。索引维护有写放大作用。我们选择的TPC-C benchmark有很多数据修改操作，所以这是一个很好的演示例子。这里演示如何使用索引推荐的功能。这里选择10个仓库的TPC-C，TPC-C的benchmark使用benchmark-sql5.0：

　　回显密码 | gs_dbmind 组件 index_advisor $port $database workload.sql

　　--max-n-distinct 1 --min-reltuples 10 --use-all-columns --multi-iter-mode --min-improved_rate 0.5 --max-index-columns 3 --show-benefits

　　参数说明

　　其中--max-n-distinct指定distinct数的倒数最大值为1，即distinct的最小数为1，--min-reltuples指定最小记录数为10红色字体为关键参数，multi-iter-mode指定贪心算法，min-improve_rated指定最小改进比例为50%，max-index-columns 3指定最大联合索引列数为3 .echo password 就是把密码通过管道输入stdin，后面我们就不需要交互输入密码了。$port $database 代表用户的数据库端口和数据库名称。

　　经过一段时间后，我们可以得到如下推荐结果：

　　与原指数相比，推荐指数提升了约25%（tpm 32479.92提升至41600.54）

　　这里可以看到返回了两个报表段，一个是“generate candidate indexes”，表示根据工作负载文件选择的候选索引，“determine optimal indexes”表示识别出的最佳索引，并给出索引创建方法DDL语句。

　　3.3 OLAP 场景

　　On-line Analytical Processing，在线分析处理是在数据仓库多维模型的基础上实现的各种面向分析的操作的集合。该场景的特点是操作数据量大，以查询为主，很少涉及数据变更。在索引配置的时候，可以考虑保留更多的索引。为了演示方便，我们以通用基准测试TPC-H为例，演示索引推荐过程。使用如下SQL语句推荐索引：

　　回显密码 | gs_dbmind 组件 index_advisor $port $database workload.sql --schema public --min-reltuples 10 --max-n-distinct 1 --use-all-columns --multi-iter-mode --min-improved -rate 0 --max-index-columns 5 --show-benefits

　　参数说明

　　其中min-improved_rate设置为0%，即保留所有可以提升的索引，最大联合索引的列数放宽为5。echo password、$port、$的含义数据库同上。

　　执行一段时间后，可以得到推荐的结果：

　　这里我们比较一下指数推荐前后的区别。我们的测试结果表明：与原来的索引相比，22条tpch语句的总耗时从10194ms缩短到8524ms。当然，我们这里使用的数据量并不大。当数据量更大时，效果可能更明显。

　　四、指数推荐结果解读

　　上面我们已经介绍了“生成候选指标”和“确定最优指标”的含义，这是最基本的两个输出部分。通过民生银行的生产实践，我们进一步优化了展示报表的结果，增加了细粒度的指标推荐效果评估报表。指标推荐报告从上到下分为：候选指标、最终指标、指标收益、已有指标、当前负载无用指标、冗余指标、历史有效指标。

　　在索引收益方面，优先考虑提高负载较多的索引，并按照成本增加比例的降序展示对应的SQL，突出显示关键索引和SQL，结果如下图所示：

　　细粒度SQL语句索引性能提升效果如下图所示。通过这个图，用户可以进一步决定应该创建哪个索引，哪个索引对于当前业务来说是比较必要的。

　　下图演示了可以分析当前系统中已有的索引效果，为用户进行索引维护提供依据，即哪些索引目前仍然有效，哪些可以删除。根据民生银行的生产实践，删除指标其实是有风险的，需要和业务方进行评估。毕竟采集到的SQL流只是一种抽样形式。如果采样没有抓到低频业务，贸然删除索引可能会对部分任务造成较大影响。

　　索引推荐过程消耗的时间与业务SQL语句的复杂度、表的复杂度、SQL管道的规模呈正相关。因此，指标推荐过程的具体耗时是不确定的。为此，我们还通过民生银行复杂的生产场景对指标推荐功能进行了优化，采用并行计算、非阻塞IO、缓存、优化算法等方式，大大降低了指标推荐过程的开销。将民生银行一项复杂业务的推荐时间从40分钟优化为4分钟，进一步提升了该功能的易用性。另外，通过这个过程，我们还实现了推荐进度条功能，方便用户直观的看到当前的推荐进度，增加易用性。效果如下：

　　5. 指数推荐的其他常见问题

　　由于系统字符数限制，部分SQL会被截断。索引推荐会自动识别无效的SQL语句，并跳过这部分SQL。如果要防止系统截断SQL语句，可以按照上面的方法增加GUC。参数track_activity_query_size的值；但是这个过程会消耗额外的内存空间，所以也可以在使用索引推荐功能后回调；

　　部分系统表（或系统视图）提取的SQL语句缺少频率信息，会导致索引推荐在工作负载层面的成本效益评估不准确；可以，但是增删改查的比例不一定准确。DBMind的索引推荐功能会考虑不同业务的增删查改比例，进而确定推荐索引的数量；

　　由于用户提供的SQL管道难以覆盖全量的SQL，所以当前负载相关的无效索引的展示只能作为参考。谨慎删除相关指标需要与业务方进行全面的沟通和评估。

　　附件：DBMind下载方法：

　　目前DBMind已经脱离openGauss社区数据库内核代码仓库。仓库路径为： 或阅读原文获取最新的DBMind版本，按照readme中的说明进行安装部署。目前openGauss发布后自带的gs_dbmind命令是一个稳定版，但是没有上述指标推荐的一些新功能。

　　-结尾-

　　seo外链群发工具 解决方法:网站如何操作才能避免被惩罚?

　　网站如何运营才能避免被处罚？

　　网站的日常优化需要站长对网站进行维护和更新。但是很多时候，网站会被降级或者被处罚，很多站长对此并不了解。其实很多时候，站长并不打算进行一些违规操作。因此，学会处理网站的基本违规行为就显得尤为重要。如何运营网站才能避免被处罚？文章会总结一些经验技巧。

　　一、网站内容

　　网站每天都会添加基础内容，需要站长检查文章内容。网站不能被大量采集

或转载，即使网站权重很高，时间一长，网站的权重也会大大流失。搜索引擎喜欢新鲜事物，所以网站需要进行一些原创或伪原创的内容。只有这样，网站才会越来越被搜索引擎喜欢，自然会增加网站的收录率。

　　

" />

　　2. 标题和描述

　　网站标题和描述信息是蜘蛛爬取了解和了解网站推广的前两个信息点。站点 关键词 在标题和描述中设置。搜索引擎也根据这两部分来判断网站优化关键词的基本情况。但是标题和描述不能 关键词 堆得太高才能脱颖而出 关键词。这样只会引起搜索引擎的反感，甚至会受到搜索引擎的惩罚。

　　3.网站链接

　　1、在外链方面，避免使用群发工具或软件

　　

　　如果新站上线，网站的外链对网站的权重和流量有很大的帮助。但是如果每天都添加外链，对于站长来说工作量会很大。因此，很多站长使用一些群发工具或软件来操作。因为这些软件群发外链都是一些黑链接，给网站优化带来很多弊端，甚至可能被搜索引擎惩罚，所以建议站长们在发布外链时慎用群发软件。

　　2、站内链接，避免死链接过多。

　　另外，很多人喜欢在权重高的内页上有大量的网站推广关键词。这个原则是可以遵循的。另外，还有一个友情链接，为什么要放在网站上呢。站长在做友情链接的时候，会很重视网站权重、快照和收录。符合条件者互换链接。但是，交换的友情链接需要定期检查。如果发现对方链接被处罚，需要立即清除对方链接，以免自己站点受到牵连。

　　【如何运营网站才能避免被处罚？】相关文章：