炫”库“行动-人大金仓有奖征文-人大金仓数据库管理系统使用与参数优化

炫“库”行动，等你来战！

2021年9月16日开始，csdn社区开始了炫“库”行动，各种精品大礼包等你来拿，详情参照炫”库“行动-人大金仓有奖征文。

前言

人大金仓数据库管理系统KingbaseES（简称：金仓数据库或KingbaseES）是北京人大金仓信息技术股份有限公司自主研制开发的具有自主知识产权的通用关系型数据库管理系统。
金仓数据库主要面向事务处理类应用，兼顾各类数据分析类应用，可用做管理信息系统、业务及生产系统、决策支持系统、多维数据分析、全文检索、地理信息系统、图片搜索等的承载数据库。
那么面对如此多的数据量，如何通过SQL语句去进行高效的访问呢？这便是我们该去讨论与解决的问题。

人大金仓数据库架构

和其它数据库相比，金仓数据库有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在 存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可 以根据业务的需求和实际需要选择合适的存储引擎。

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的 通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证 安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验 证它所具有的操作权限。

那么SQL语句便是链接数据库操作的灵魂，高效的SQL必然也会是对性能有较强的加持。

SQL语句

SQL语句执行顺序

手写SQL顺序

真正执行的顺序： 随着 数据库 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动 态调整执行顺序。下面是经常出现的查询顺序：

JDBC操作

static String sql = null;
static Getcon db1 = null;
static ResultSet ret = null;

public static void main(String[] args) {
    query();
    //update();
    //add();
    //delete();
}

public static void  query(){
    sql = "select * from TB_SYS_CONFIGURE;";//要执行的SQL语句，改成自己的表什么的
    db1 = new Getcon(sql);//创建数据库对象
    try {
        ret = db1.pst.executeQuery();//执行语句，ret是结果
        while (ret.next()) {
            System.out.println(ret.getString(1)+": "+ret.getString(2) );
        }//显示数据
        ret.close();
        db1.close();//关闭连接
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void  update(){
    sql = "\n" +
            "UPDATE TB_SYS_CONFIGURE \n" +
            "SET ATTR_KEY='accessCountTest' \n" +
            "WHERE ATTR_VALUE='3456';\n";//要执行的SQL语句，改成自己的表什么的
    db1 = new Getcon(sql);//创建数据库对象

    try {
        int i = db1.pst.executeUpdate();
        if (i>0){
            System.out.println("修改成功");
        }
        else {
            System.out.println("修改失败");
        }
        ret.close();
        db1.close();//关闭连接
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void  add(){
    sql = "insert into TB_SYS_CONFIGURE (ATTR_KEY,ATTR_VALUE) values(?,?);\n";//要执行的SQL语句，改成自己的表什么的
    db1 = new Getcon(sql);//创建数据库对象
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = db1.conn.prepareStatement(sql);
    preparedStatement.setString(1,"accessADD");
    preparedStatement.setString(2,"12345");
    int i1 = preparedStatement.executeUpdate();
    if (i1>0){
        System.out.println("修改成功");
    }
    else {
        System.out.println("修改失败");
    }
    ret.close();
    db1.close();//关闭连接
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void delete(){
    sql = " delete from TB_SYS_CONFIGURE where ATTR_VALUE=7890 ";
    db1 = new Getcon(sql);//创建数据库对象
    try {
        int i1 = db1.pst.executeUpdate();
        if (i1>0){
            System.out.println(i1+"个删除成功");
        }
        else {
            System.out.println("删除失败");
        }
        ret.close();
        db1.close();//关闭连接
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

参数优化

max_connections
确定到数据库服务器的最大并发连接数。默认情况 下，KingbaseES 允许的最大连接数相对较低，默认限制为100。这个限制与共享缓冲区的大小有关，连接利用共享缓冲区中的内存。
建议设置为预期峰值负载时需要的最大连接数。请注意，每个连接都使用shared_buffer内存，以及额外的非共享内存。一般来说，如果需要超过200个连接，就应该更多地使用连接池。请谨慎设置最大并发连接数，避免开发人员不要滥用他们的系统资源。

shared_buffers
默认情况下这个值设置得比较低。因此，更新 shared_buffers 是在大多数现代操作系统上提高整体性能最有效的设置之一。

shared_buffers 没有特定的推荐值，但是为特定系统确定值的计算并不特别困难。一般来说，对于专用DB服务器， shared_buffers 的值应该大约是总系统RAM的30%。 另外，虽然较大的 shared_buffers 值可以在’读繁重’用例中提高性能，但对于’写繁重’用例，较大的 shared_buffer 值可能是有害的，因为 shared_buffers 的全部内容必须在写期间处理。

effective_cache_size
设置规划器对查询可用的磁盘缓存的有效大小的假设。这被考虑到基于代价的成本估计中。值越高，使用索引扫描的可能性越大，值越低，使用顺序扫描的可能性越大。在设置这个参数时，应该同时考虑KingbaseES 的shared_buffers 和用于数据文件的磁盘缓存部分。另外，还要考虑不同表上并发查询的预期数量，因为它们必须共享可用空间。该参数对PostgreSQL分配的共享内存大小没有影响，也不保留内核磁盘缓存，它仅用于估算目的。系统也不会假设数据在查询之间仍保留在磁盘缓存中。

建议，effective_cache_size设置系统内存 的 75%。

maintenance_work_mem
指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的 最大的内存量。其默认值是 64 兆字节（64MB）。因为在一个数据库会话中，一个时刻只有一个这样的操作可以被执行，并且一个数据库安装通常不会有太多这样的操作并发执行， 把这个数值设置得比work_mem大很多是安全的。 更大的设置可以改进清理和恢复数据库转储的性能。

注意当自动清理运行时，可能会分配最多达这个内存的 autovacuum_max_workers 倍，因此要小心不要把该默认值设置得太高，通过单独设置autovacuum_work_mem，可能会对避免这种情况出现。将其设置为中等高值会提高vacuum和其他操作的效率。执行大型ETL操作的应用程序可能需要分配多达1/4的RAM来支持大容量真空。注意，每个autovacuum 进程可能使用这么多，所以如果使用多个autovacuum进程，您可能希望降低这个值，以便他们不能要求超过1/8或1/4的可用RAM。建议，maintenance_work_mem 通常设置为系统内存的 1/16，但是数据仓库系统可以设置为系统内存的 1/8。

checkpoint_completion_target
检查点期间刷新脏缓冲区所花费的时间(占检查点间隔的百分比)。指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是0.5（50%）。通常磁盘IO的写入速度为300M/s-1000M/s，，在checkpoint_completion_target设置的越高的情况下，写入速度越低，体验越好，性能越高。反之，较低的值可能会引起I/O峰值，导致“卡死”的现象。

建议，checkpoint_completion_target可以设置为0.9（90%）。

wal_buffers
为WAL设置共享内存中的磁盘页缓冲区的数量。默认设置-1选择的大小等于shared_buffers的1/32(大约3%)，但不小于64kB也不大于一个WAL段的大小。

在每次事务提交时，WAL缓冲区的内容都被写到磁盘上，所以非常大的值不太可能有显著的好处。但是，将这个值设置为至少几个兆字节可以提高在许多客户机同时提交的繁忙服务器上的写性能。在大多数情况下，由缺省设置-1选择的自动调优应该会给出合理的结果。

在非常繁忙的高核机器上，将这个值提高到128MB是很有用的。

default_statistics_target
设置默认统计目标（直方图数量）。

为不通过ALTER table set statistics设置列特定目标的表列设置默认统计目标。较大的值会增加ANALYZE所需的时间，但可能会提高计划者评估的质量。默认值为100。

大多数应用程序可以使用默认值100。对于非常小/简单的数据库，减少到10或50。数据仓库应用程序通常需要使用500到1000。否则，在每列的基础上增加统计目标。

random_page_cost
设置规划器对非顺序获取磁盘页的成本的估计，默认为4.0。通过设置表空间参数，可以覆盖特定表空间中的表和索引的这个值。

相对于seq_page_cost降低这个值将导致系统更倾向于索引扫描；提高它将使索引扫描看起来相对更昂贵。您可以同时提高或降低这两个值，以改变磁盘I/O成本相对于CPU成本的重要性。

对机械磁盘的随机存取通常比顺序存取cost昂贵得多。但是，使用较低的默认值(4.0)，因为大多数对磁盘的随机访问，比如索引读取，都假定是在缓存中。默认值可以被认为是建模随机访问比顺序慢40倍，而预期90%的随机读取被缓存。如果您认为90%的缓存率对于您的工作负载是一个不正确的假设，那么您可以增加random_page_cost来更好地反映随机存储读取的真实成本。相应地，如果您的数据可能完全在缓存中，例如当数据库小于服务器总内存时，可以适当降低random_page_cost。相对于顺序存储，具有较低的随机读成本的存储，例如固态硬盘，也可以使用较低的random_page_cost值进行建模，例如，1.1。

尽管系统允许将random_page_cost设置为小于seq_page_cost，但这样做在物理上是不合理的。但是，如果数据库完全缓存在RAM中，那么设置它们相等是有意义的，因为在这种情况下，不按顺序访问页面不会受到负面影响。此外，在高缓存的数据库中，应该降低这两个值，因为获取RAM中已经存在的页面的成本比正常情况下要小得多。

建议，机械硬盘设置为 4, 固态硬盘设置为1.1, SAN存储设置为1.1。

effective_io_concurrency
磁盘子系统可以有效处理的并发请求的数量，在支持的系统上，默认值为1，否则为0。通过设置表空间参数，可以覆盖特定表空间中的表的这个值。仅适用于支持posix_fadvise的平台(例如Linux)。目前只影响并行位图扫描的执行，但可能会影响其他I/O操作的未来版本。

但是，如果数据库经常因为并发会话中发出的多个查询而繁忙，那么较低的值可能足以使磁盘阵列保持忙碌。高于使磁盘繁忙所需的值只会导致额外的CPU开销。ssd和其他基于内存的存储通常可以处理许多并发请求，所以最好的值可能是几百。

建议，机械硬盘设置为 2, 固态硬盘设置为 200, SAN存储设置为 300。

work_mem
work_mem 的值用于复杂的排序操作，并定义用于中间结果(如哈希表)和排序的最大内存量。当对 work_mem 的值进行适当调优时，大多数排序操作将在更快的内存中执行，而不是读写磁盘。

确保’ work_mem ‘值不要设置得太高是很重要的，因为在并行操作中，并发排序操作需要多份的’ work_mem ‘。由于这个重要的警告，理想的做法是将’ work_mem ‘的全局值设置为一个相对较低的值，然后修改任何特定查询本身，以使用更高的’ work_mem '值。

当查询调用排序、哈希或任何其他需要空间分配的结构时，都会分配work_mem，每个查询都可能发生多次。需要考虑的一件事是，不应让可用内存在边缘运行，避免OOM。总是需要留下某种类型的缓冲区，以防止内存使用高峰。所以work_mem中可用的最大内存应该是((RAM - shared_buffers) / (max_connections * 3) / max_parallel_workers_per_gather。

min_wal_size max_wal_size
只要WAL磁盘使用率保持在这个设置之下，旧的WAL文件就会被回收，以供将来在检查点使用，而不是被删除。这可以用来确保保留足够的WAL空间来处理WAL使用的峰值，例如在运行大型批处理作业时。如果这个值没有指定单位，它将被接受为兆字节。min_wal_size , 默认为80mb。

max_wal_size ，允许WAL在自动检查点期间增长的最大尺寸。除非数据库每小时写入的数据超过1GB，在这种情况下，增加日志的大小，使其至少相当于一个小时的日志

建议：

WEB ：min_wal_size = 1GB max_wal_size = 4GB
OLTP：min_wal_size = 2GB max_wal_size = 8GB
DW ：min_wal_size = 4GB max_wal_size = 16GB
MIX ：min_wal_size = 1GB max_wal_size = 4GB

max_worker_processes
设置系统可以支持的最大后台进程数。该参数只能在服务器启动时设置。默认值是8。

运行备用服务器时，必须将该参数设置为与主服务器相同或更高的值。否则，备用服务器将不允许查询。

当更改此值时，请考虑同时调整max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather。

增加到max_parallel_workers +其他workers，例如用于逻辑复制的workers和自定义后台workers。不过不会超过CPU的核数。

max_parallel_workers_per_gather
设置单个“收集”或“收集合并”节点可启动的工作人员的最大数量。并行作业从max_worker_processes建立的进程池中获取，受max_parallel_workers的限制。请注意，请求的worker数量可能在运行时实际上不可用。如果发生这种情况，该计划将使用比预期更少的worker运行，这可能是低效的。缺省值是2。将此值设置为0将禁用并行查询执行。

请注意，并行查询可能比非并行查询消耗更多的资源，因为每个工作进程是完全独立的进程，它对系统的影响与额外的用户会话大致相同。在为该设置选择值时，以及在配置其他控制资源利用率的设置(如work_mem)时，都应该考虑到这一点。资源限制(如work_mem)单独应用于每个worker，这意味着所有进程的总利用率可能比通常情况下任何单个进程的总利用率要高得多。例如，使用4个worker的并行查询使用的CPU时间、内存、I/O带宽等可能是完全不使用worker的查询的5倍。

计划使用并行查询，建议增加到4或8，这取决于核心/并发会话。

max_parallel_workers
设置系统可支持并行操作的最大worker数。缺省值为8。当增加或减少这个值时，也考虑调整max_parallel_maintenance_workers和max_parallel_workers_per_gather。另外，请注意，该值的设置高于max_worker_processes将不会产生影响，因为并行工作进程是从该设置建立的工作进程池中获取的。

如果您认为可以从并行查询，在DW系统中，设置为CPU核心数。

max_parallel_maintenance_workers
设置单一工具性命令能够启动的并行工作者的最大数目。当前，唯一一种支持使用并行工作者的工具性命令是CREATE INDEX，并且只有在构建B-树索引时才能并行。并行工作者从由[max_worker_processes] 创建的进程池中取出，数量由[max_parallel_workers] 控制。注意实际在运行时所请求数量的工作者可能不可用。如果发生这种情况，工具性操作将使用比预期数量少的工作者运行。默认值为2。将这个值设置为0可以禁用工具性命令对并行工作者的使用。

注意并行工具性命令不应该消耗比同等数量非并行操作更多的内存。这种策略与并行查询不同，并行查询的资源限制通常是应用在每个工作者进程上。并行工具性命令把资源限制maintenance_work_mem当作对整个工具性命令的限制，而不管其中用到了多少个并行工作者进程。不过，并行工具性命令实际上可能仍会消耗更多的CPU资源和I/O带宽。

计划使用并行查询，建议增加到4或8，这取决于核心/并发会话。