ReaderMX.
PostgreSQL码农集散地

AI永远不可能取代人类DBA! 因为

Pdigoal约 9 分钟

拔电源

Image
本周六直播预约中:

回顾：大新闻: PG 64-bit XID 设计和patch出炉, 它真的来了?

嘉宾: 黄长城教授、邱神医、陈卫星、德哥

直播议题:

  • 各位介绍一下自己, 以及与数据库相关的经历

  • PG32位事务号的背景

  • 为了复用事务号, 数据库使用了什么手段

  • 复用事务号的技术, 会给数据库带来哪些风险?  

  • 说说大家踩到过的坑? 特别是什么类型的数据库负载最容易踩坑

  • 总结一下避坑经验. 

  • 64位事务号经过了哪些设计和讨论? 

  • 目前最新的64位事务号设计解读 

  • 目前的64位设计版本靠谱吗? 为什么? 

  • PG 18 真的会升级xid到64位吗? 

  • 真的需要64位xid吗? 

文中参考文档点击阅读原文打开, 同时推荐2个学习环境: 

1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》

2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、最佳实践等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

关注公众号, 持续发布PostgreSQL、PolarDB、DuckDB等相关文章. 

资深DBA头痛的TPCH性能优化, AI 10秒就搞定了Image

tpch是测试数据库olap场景性能的一种评测方法, 包括了数据库建模、数据生成、SQL查询等几个方面. 一般在数据库选型阶段, 对比各个产品性能时会比较关注.

如果你是数据库厂商的, 要和别人去PK性能, 给你一个环节, 你怎么优化? 22条sql的tpch都让老DBA捏把汗, 何况是99条sql的tpcds?

但是, 当我把问题稍微细化一下, chatgpt信手拈来, 这才是老DBA的痛, 因为老DBA更相信自己, 而不相信AI, 而新DBA则上来就很熟AI 的使用, 学习新生产力工具的速度更快. 怪不得各个大厂的DBA TL都不要35岁以上的一线技术人员:

请看chatgpt的表演

1 给定一台ubuntu服务器, 基于postgresql数据库测试tpch, 以优化性能为目的, 请给出操作系统的配置, 数据库的配置postgresql.conf, tpch数据生成的方法和测试方法

以下是针对您的要求的一个概括的操作指南。为了达到最优化，将需要对硬件资源、操作系统参数、数据库配置以及查询执行进行调优。这涉及很多因素，这里将给出一些基本的设置和步骤。

操作系统配置

  • 调整内核参数：编辑 /etc/sysctl.conf 文件以提升系统性能，例如：

    # 增大可用的端口范围  
    net.ipv4.ip_local_port_range = 1024 65535  
    # 减少swap使用  
    vm.swappiness = 10  
    # 提高文件系统的性能  
    vm.dirty_ratio = 20  
    vm.dirty_background_ratio = 10

  • 调整文件限制：编辑 /etc/security/limits.conf 文件以避免文件打开数等限制：

    * soft nofile 65536  
    * hard nofile 65536

  • 调整缓存和交换分区：如果服务器内存足够大，尽量减少swap的使用来增加性能。

PostgreSQL 配置 (postgresql.conf)

  • 内存配置：

    shared_buffers = '25% of RAM'           # e.g., 4GB  
    effective_cache_size = '50% of RAM'     # e.g., 8GB  
    work_mem = '{RAM/number of connections}'# e.g., 50MB  
    maintenance_work_mem = '512MB'

  • 查询优化：

    random_page_cost = 1.1               # 物理磁盘I/O性能较高时可以调低  
    effective_io_concurrency = 200       # 根据你的磁盘IOPS进行调整  
    max_parallel_workers_per_gather = 4  # 基于CPU核心数调节

  • 写入性能：

    wal_level = minimal                     # 减少写前日志的数据量  
    synchronous_commit = off                # 减少等待wal记录同步到磁盘的时间  
    checkpoint_completion_target = 0.9      # 检查点完成目标，避免IO高峰

  • 连接与复制设置：

    max_connections = 200                    # 合理配置最大连接数

TPC-H 数据生成

使用 TPC 提供的 dbgen 工具来生成 TPC-H 数据集。首先需要下载 TPC-H 工具包

  1. 解压并编译 dbgen：

    tar -xzf tpch_x.x.x.zip  
    cd tpch_x.x.x/dbgen  
    make

  2. 使用 dbgen 生成数据，其中 "-s" 参数指定数据规模（例如，1代表1GB的数据）：

    ./dbgen -s 1

TPC-H 测试执行

  1. 将 dbgen 生成的.tbl文件导入 PostgreSQL 数据库：

    psql -U <username> -d <databasename> -c "COPY <tablename> FROM '/path/to/file.tbl' WITH (FORMAT csv, DELIMITER '|', HEADER false);"

  2. 运行 TPC 提供的查询示例，例如：

    psql -U <username> -d <databasename> -f queries/01.sql

  3. 使用 EXPLAIN ANALYZE 对每个查询进行运行时分析，根据输出对查询或数据库进行进一步优化。

额外建议

  • 监控服务器负载，分析内存、CPU、I/O 瓶颈。

  • 考虑使用 PostgreSQL 的并行查询功能来加速复杂查询。

  • 检查查询计划，确认是否需要添加索引以加速查询执行。

  • 实践针对具体硬件、工作负载和查询特性的调优。

  • 配置好备份和监控，以便能够对系统的性能变化做出及时反应。

需要强调的是，上述配置只是出发点，具体的调优参数会依赖于硬件、工作负载以及 PostgreSQL 的版本。应该对每一个改变进行基准测试，并监测其对性能的具体影响。

2 为了提升tpch查询性能, 应该创建哪些索引

优化 TPC-H 查询性能时，应考虑在经常用作连接、筛选和排序的列上创建索引。一些经典的列对于TPC-H中的查询通常包括以下情况：

  1. 主键约束自动创建的索引：TPC-H 模式设计中的每个表都有一个主键，PostgreSQL 会自动为主键创建一个唯一的B树索引。

  2. lineitem 表：

  • 连接 orders 表使用的 orderkey

  • 执行日期范围搜索的 shipdate

  • 聚合或排序时使用的 suppkey

  • 多列索引 l_orderkey, l_suppkey 可以提升 Join 操作的性能。

  • orders 表：

    • 连接 customer 表使用的 custkey

    • 执行日期范围搜索的 orderdate

    • 多列索引 o_custkey, o_orderdate 可以加速连接和筛选查询。

  • partsupp 表：

    • partkey 和 suppkey，它们经常用在连接操作中。

  • customer 表：

    • 连接其他表时使用的 custkey

    • 经常作为筛选条件的 mktsegment

  • supplier 表：

    • 连接其他表时使用的 suppkey

  • part 表：

    • 连接其他表时使用的 partkey

    • 经常作为筛选条件的 type

    创建索引的通用 SQL 格式如下：

    CREATE INDEX index_name ON table_name (column_name);

    创建复合索引（多列索引）：

    CREATE INDEX index_name ON table_name (column1, column2, ...);

    举个例子，针对 lineitem 表中的 shipdate 创建索引的代码会是：

    CREATE INDEX lineitem_shipdate_idx ON lineitem (shipdate);

    创建索引时需注意以下几个点：

    • 索引会加速查询的同时也会稍微拖慢写操作（INSERT, UPDATE, DELETE），因为数据库需要同步更新索引。因此应根据实际使用频率和需求权衡是否创建索引。

    • 对于 TPC-H 数据仓库类型的系统，由于读操作远多于写操作，通常建立更多索引有利于查询性能。

    • 创建索引前应分析具体查询，并在热点列上建立索引。

    • 应考虑对查询中的 JOIN、WHERE、ORDER BY 和 GROUP BY 子句中的列创建索引。

    • 索引最好在批量导入数据后创建，以避免导入时的性能损失。

    • 索引不是越多越好，不必要的索引会增加存储空间的使用，还会降低数据库的维护操作效率（如插入、更新和删除）。所以在创建索引之前，请仔细考虑是否真的需要。

    请记住，创建索引只是优化查询性能的众多手段之一。务必要根据具体查询的特性，以及数据库的使用模式进行综合性的考量和调整。

    3 请问用什么工具评测tpch压测过程中, 资源是否被充分利用?

    评测 TPC-H 压测过程中资源利用情况，我们通常关注 CPU 占用率、内存使用情况、磁盘 I/O 性能和网络性能等指标。对于这些指标的监控，有多种工具可以使用：

    1. 操作系统内置工具：

    • top 或 htop：实时监控系统资源。

    • vmstat：提供关于系统内存、交换、CPU 等信息。

    • iostat：监视系统输入输出设备和CPU的使用情况。

    • sar：收集、报告和存储系统活动信息。

    • perf：Linux性能分析工具，功能强大。

  • 数据库性能监视工具：

    • pg_stat_statements：PostgreSQL 的扩展，提供SQL执行统计信息。

    • pg_top：实时监视PostgreSQL数据库服务器的运行状态。

  • 图形化监控工具：

    • Grafana + Prometheus：强大的组合，可以监视几乎所有的系统和应用性能指标。

    • Nagios：企业级的监测解决方案，支持对服务器、网络及其服务的监控。

    • Zabbix：企业级开源监控解决方案，功能全面。

  • 其他监测工具：

    • pgBadger：PostgreSQL日志分析器，提供详细的报告。

    • Monit：轻量级工具，用于管理和监控Unix系统。

  • 云提供商的监控服务（如果在云上运行）：

    • Amazon CloudWatch：针对AWS服务的监控工具。

    • Google Stackdriver：Google Cloud平台的监控、日志记录和诊断服务。

    • Azure Monitor：用于Azure资源的综合工具。

    这些工具可以帮助你监控服务器资源使用情况，包括 CPU 使用率、内存使用量、磁盘读写速率和网络带宽利用率等。通过这些资源的监控数据，你可以评估当前的硬件资源是否已经被完全利用，哪些资源是瓶颈，以及哪些资源还有优化的空间。

    值得注意的是，在TPC-H压测中，除了系统级别的监控之外，对数据库查询的执行计划的分析也非常重要，它可以帮助我们理解每条查询语句的资源利用情况，并进行相应的优化。

    4 如果可以修改postgresql内核. 请问如何修改能提升tpch的数据导入和查询性能

    直接修改 PostgreSQL 的内核是一个高度专业和风险较高的任务，通常需要深厚的数据库内部结构和C语言编程知识。这样的改动在生产环境中不太常见，因为它可能引入新的问题或兼容性问题。但是，假设你有足够的知识和资源，以及在一个可以控制的环境中，以下是一些可能的修改方式来提升 TPC-H 的数据导入和查询性能：

    数据导入性能

    1. 并行化数据加载: 改动 PostgreSQL 的数据加载工具 (如 COPY)，使其能够并行工作。这可能涉及到修改内存分配器、IO 子系统等，以支持并发处理。

    2. Bulk Insert: 修改对写入操作的处理，使得 PostgreSQL 在导入大量数据时，能够批量写入，减少磁盘 I/O 操作。这可以通过更智能的预写日志（WAL）管理来实现。

    3. 更改 Checkpoint 策略: 修改内核中的 Checkpoint 行为，延迟一些磁盘同步操作，以减少在大规模数据导入期间的性能开销。

    查询性能

    1. 优化查询执行器: 改进查询执行器的算法，例如使用更高效的连接算法，或优化聚合操作的内存使用。

    2. 增强索引系统: 扩展现有的索引系统，加入更多的索引类型，例如 Bit-map 索引或空间索引，提供更多的查询优化选择。

    3. 改进内存管理: 修改查询执行过程中的内存管理策略，比如引入更智能的内存分配和回收机制。

    4. 增强并行查询: 进一步开发并行查询能力，允许更广泛的查询操作（如某些类型的聚合）在多核心上并行执行。

    5. WAL 日志优化: 修改 WAL 日志系统，允许更精细的控制来减少对只读查询的影响。

    通用性能改进

    1. 就地更新: 修改表的更新机制，使得 PostgreSQL 可以根据情况，就地更新行，而非默认的全部进行 MVCC (多版本并发控制) 机制下的写入。

    2. 改进存储引擎: 扩展或改进 PostgreSQL 的存储引擎，使其支持更多的数据和索引结构，例如 columnar store 对于 OLAP 类型的查询会有显著的性能提升。

    3. I/O 调度: 改善 PostgreSQL 对异步 I/O 的支持，例如引入 I/O 调度机制，以最大化利用磁盘的性能。

    4. 自动优化: 引入更多的自动调优机制，比如自动收集统计信息和自动创建索引。

    在尝试任何这些改动之前，强烈建议对 PostgreSQL 的代码结构和执行过程有很深的理解。在现实中，大部分性能改进往往都是通过配置优化、适当的硬件选择和良好的架构设计来实现，而不是直接修改数据库内核。如果实在需要更深层次的定制化改动，可能需要与更多的 Postgres 开发者和社区合作来确保这些改进可以安全和高效地集成进 PostgreSQL 主线。

    本期彩蛋-招商中,有需要的小伙伴可联系嵌入...

    文章中的参考文档请点击阅读原文获得. 

    欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
    近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
    Image

    KEEP THE THREAD GOING

    更多来源文章