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

传统行业PG实践探索冷菠2024年06月202PG数据库行业洞察传统行业PG实践探索目 录0103案例分享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 extendtable lock waittrxid 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），提升近200倍SP_SET_PARA_VALUE(1,BUFFER,16000);SP_SET_PARA_VALUE(1,BUFFER,19);SP_SET_PARA_VALUE(1,SESS_POOL_SIZE,128);SP_SET_PARA_VALUE(1,VM_POOL_SIZE,128);SP_SET_PARA_VALUE(1,SORT_BUF_SIZE,2048);SP_SET_PARA_VALUE(1,DICT_BUF_SIZE,1024);SP_SET_PARA_VALUE(1,RECYCLE,2048);SP_SET_PARA_VALUE(1,PARALLEL_POLICY,2);SP_SET_PARA_VALUE(1,MAX_PARALLEL_DEGREE,64);SP_SET_PARA_VALUE(1,PARALLEL_THRD_NUM,16);性能优化结果：SQL执行时间降低到1s左右。参数优化 慢SQL1优化：top分页+并行hint（1.5s）慢SQL2优化：等价改写(0.1s)SELECT A.FId AS id FROM biz_dm_swc.t_hsas_recurbizdata a where exists(select 1 from biz_dm_swc.t_hsas_salaryfile bwhere a.fsalaryfileid=b.fid and b.forgid=100000)ORDER BY A.FId DESC LIMIT 100000 050100150SQL1SQL2慢SQL优化对比优化前优化后附录文件：0419未来展望20开源PG未来展望对标业界成熟产品，特性补齐新功能特性合作共赢，共建繁荣社区社区生态待探索领域与PGer们共勉：心之所向，行之所往，未来可期。未来技术风口AI技术融合