冷菠：传统行业PG实践探索

传统行业PG实践探索冷菠2024年06月2•02PG数据库行业洞察传统行业PG实践探索目 录•01•03案例分享•04未来展望013PG数据库行业洞察41.1 “卷”无处不在1.“卷“行业：行业厂商们各显神通，争夺高地。2.“卷”市场：数据库市场一片红海。51.1“卷”无处不在3.“卷”证书：行业不景气，技多不压身，多一个证书多一份保障。“这年头，但凡少了一个证书都没法在江湖混了!!!”From DBAer卷王们。61.2 面对质疑：数据库这么卷，PG到底能不能打？开源PG如何杀出重围: 紧跟卷王脚步一起“卷”！？71.3 PG如何“卷？笔者个人认为“卷”的 姿势：能力模型用户画像稳定性可靠性弹性韧性扩展性易用性可维护性生态能力1.PG能力画像2.卷“DFX”站在巨人的肩上“卷”。028传统行业PG实践探索2.1PG探索实践：从传统走向开源标准化服务流程7*24 热线支撑一线人员+研发专家疑难故障全程定位跟踪开源生态：工具集->工具链传统底座：流程+服务+人员+资源端到端能力：自动化->自动驾驶以传统架构为底座，融合开源生态，打造端到端能力：数据迁移安装部署离线日志异构查询插件扩展透明切换SQL审核SQL发布实时监控智能巡检智能诊断智能调优全链路维护计划中已上线“裸奔”故障转移故障切换故障限流故障自愈故障发现故障识别自动化智能化自动驾驶自动审核自动发布自动巡检自动监控自动同步智能容量管理智能风险识别智能SQL建议智能流量控制智能SQL调优可靠性可观测性高性能PAF（pg auto failover）作为高可用架构，解决业务单点故障，确保业务连续性。PAF高可用利用pg_profile工具，实现类AWR可观测性。巡检&优化工具利用pg_gather工具，实现类ADDM性能诊断利器功能。性能调优工具2.2PG在传统行业实践利用开源工具探索可靠性、可观测以及最佳性能实践：112.3 可靠性实践：PAF（ PostgreSQL Automatic Failover ）PAF高可用架构保障PostgreSQL业务连续性：PAF架构：monitor节点周期性检测主从节点健康状态，主从节点自动故障切换转移，确保服务连续性。PrimaryMointorApplicationSecondaryStream replicationHealth checksHealth checksSQL(write)SQL(read)数据读写分离故障健康检测故障平滑切换故障快速转移故障快速修复122.4 可观测性&性能实践pg_profile：基于快照统计的AWR Serv统计信息 TOP SQL132.5 可观测性&性能实践pg_gather：基于实时统计的AWR+ADDM DB Summary Connecion Summary PG版ADDM0314案例分享153.1案例分享：可靠性&性能实践某医药行业客户SCM供应链库数据10TB+（总共20TB+），影响数据库性能，需进行业务分库：高可用主从数据迁移： PAF集群脑裂重组迁移方案对比痛点&优劣势数据迁移╳迁移数据量较大╳数据需要导出导入╳传统迁移工具慢╳较长的业务中断时长集群脑裂不需要进行大量数据导出导入在主备接近完全数据同步前提下，数据0丢失业务中断时间较短运维操作简化，效率提升 方案对比 方案亮点在线重组业务连续操作简化切换平滑效率提升163.2案例分享：PG可观测行实践开源工具链/工具集助力PG可观测行实践：//32C128G//优化建议值shared_buffers = 32GB -->32GBeffective_cache_size = 4GB -->64GBmaintenance_work_mem = 64M -->2GBcheckpoint_completion_target = 0.9wal_buffers = 16MBdefault_statistics_target = 100random_page_cost = 4 -->1.1effective_io_concurrency = 1 -->200work_mem = 4MB -->128Mhuge_pages = trymin_wal_size = 80 -->2GBmax_wal_size = 16GBmax_worker_processes = 8 -->32max_parallel_workers_per_gather = 2 -->4max_parallel_workers = 8 -->32max_parallel_maintenance_workers =2 -->4autovacuum_max_workers =6 -->8max_connections=5000 temp_buffers=4MB --128MB 参数调优建议 工具postgresqltuner+PGTune 某车企客户实践173.3案例分享：数据库性能优化某业务模块，补齐业务性能短板：Hint+参数调优+查询转换1.数据库性能问题2. 性能优化面临的挑战╳内存缓冲命中率╳Libcache命中率╳硬解析╳dbfile extend╳table lock wait╳trxid lock wait╳数据量大，百万/千万╳单月数据分布不均衡╳每月统计数量集中在月初/月末几天╳跨月数据分布不均衡╳某些月几百万，某些月上千万╳查询跨度月大╳跨多月甚至年╳索引selectivity低╳全表扫描 AWR分析报告 慢查询SQL1(150s) 慢查询SQL2(单表34s)SELECT A.FId AS "id" FROM t_hsas_recurbizdata a INNER JOIN t_hsas_salaryfile b ON ((B.FId = A.fsalaryfileid AND 1 = 1) AND B.forgid = 100000) WHERE (1 = 1 AND 1 = 1) ORDER BY A.FId DESC LIMIT 100000 数据库自身性能瓶颈(命中率/wait) 慢查询SQL1╳不排序需要23秒╳只查ID其他条件不变60秒╳只查ID过滤条件为索引字段，主键排序22秒╳只查ID过滤条件为索引字段，索引字段排序34秒╳只查ID过滤条件为索引字段，非索引字段排序43秒╳查询全表总数23秒 慢查询SQL2183.3案例分享：数据库性能优化优化结果：性能大幅度提升，满足测试预期：SQL1（150s->1.6s），提升近100倍SQL2（20s->0.1s）