沉浸式学习PostgreSQL|PolarDB 20: 学习成为数据库大师级别的优化技能

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 在上一个实验《沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo》 中, 学习了如何部署odoo和polardb|pg.由于ODOO是非常复杂的ERP软件, 对于关系数据库的挑战也非常大, 所以通过odoo业务可以更快速提升同学的数据库优化能力, 发现业务对数据库的使用问题(如索引、事务对锁的运用逻辑问题), 数据库的代码缺陷, 参数或环境配置问题, 系统瓶颈等.

作者

digoal

日期

2023-10-12

标签

PostgreSQL , PolarDB , 数据库 , 教学


背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

  • 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

ARM机器使用以下docker image:

业务场景1 介绍: 学习成为数据库大师级别的优化技能

在上一个实验《沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo》 中, 学习了如何部署odoo和polardb|pg.

由于ODOO是非常复杂的ERP软件, 对于关系数据库的挑战也非常大, 所以通过odoo业务可以更快速提升同学的数据库优化能力, 发现业务对数据库的使用问题(如索引、事务对锁的运用逻辑问题), 数据库的代码缺陷, 参数或环境配置问题, 系统瓶颈等.

优化实验

odoo的压力测试脚本在如下目录中: PolarDB-PG-Benchmark/tests

1 优化TOP SQL性能

这个优化是比较通用的, 古话说得好, 擒贼先擒王, 打蛇打七寸. 找出TOP SQL, 对资源消耗的头部SQL进行优化, 效果立竿见影.

准备: 插件pg_stat_statements

实验步骤:

1、清理pg_stat_statements插件计数

2、使用固定的数据量、压测时长、并发、压测请求数 开启压测, 关闭压测

3、利用pg_stat_statements, 记录总资源消耗、被优化SQL的单次请求平均消耗. 用于评判优化效果.

4、利用pg_stat_statements找top sql并优化, 方法可参考:

索引优化参考:

5、执行完优化手段后, 再次重复1-3的操作.

对比优化效果:

  • 总资源消耗
  • 被优化SQL的单次请求平均消耗
  • odoo压测结果变化

2 发现业务逻辑问题 - 锁冲突为例

这个实验主要用于发现业务层的数据库使用问题, 其中最突出的是锁等待的问题, 例如持锁时间过长, 持锁级别过高, 死锁等. 这些问题都会导致并发能力下降甚至雪崩.

准备: 开启log_lock_waits, 配置deadlock_timeout.

1、配置等待事件采集. pgpro-pwr, performance insight, pg_stat_monitor, pg_wait_sampling 都可以, 参考:

2、使用固定的数据量、压测时长、并发、压测请求数 开启压测, 关闭压测

3、观测数据库日志 $PGDATA/pg_log$PGDATA/log 目录.

通过锁超时日志, 找到对应会话和SQL, 分析业务问题.

4、通过前面配置的 perf insight, pg_stat_monitor, pg_wait_sampling 找到TOP锁等待事件和对应的SQL.

5、优化手段举例:

秒杀场景可以用advisory lock来优化.

业务并行请求都锁冲突问题, 可以调整并行逻辑, 在业务层面进行线程-数据映射逻辑优化, 对锁进行隔离进行优化.

业务使用大锁, 且长时间持有锁带来的问题优化:

在事故现场如何发现锁冲突是什么业务导致的?

雪崩的优化方法:

6、通过规范可以避免业务使用数据库不当导致的大部分问题

3 环境和参数性能优化

1、使用固定的数据量、压测时长、并发、压测请求数 开启压测

在压测过程收集数据, 观测问题

2、OS层观测工具参考

  • iostat
  • pmstat
  • mpstat
  • vmstat
  • tcpdump
  • top
  • ps
  • gdb
  • pstack

指标参考:

3、发现问题

  • 内存不足?
  • 存储请求延迟、IOPS、带宽瓶颈?
  • 文件系统锁等待瓶颈?
  • 网络包转发、带宽?
  • numa问题?
  • 中断问题, cpu 绑核?

4、优化手段参考

4 整体变慢的性能优化通用方法

5 找出代码缺陷

最后这个实验, 是要找出数据库|OS内核的性能瓶颈.

1、使用固定的数据量、压测时长、并发、压测请求数 开启压测

在压测过程收集数据, 观测问题

2、观测工具参考

  • perf
  • dtrace
  • systemtap

工具使用方法参考:

附赠一些常用的学习资料

知识点

要求的知识点比较综合, 需掌握os,存储,网络,数据库等原理和常用的工具, 方法论等.

思考

1 除了以上优化, 通常还需要结合数据库的原理进行优化, 例如

2 数据库中有大量的优化器因子配置项, 参数优化和硬件配置有什么关系?

3 数据库的cbo, geqo优化器优化规则说的是什么? 和统计信息如何配合? 如何选择JOIN方法JOIN顺序?

参考

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2天前
|
关系型数据库 数据库 数据安全/隐私保护
使用PostgreSQL进行高级数据库管理
【5月更文挑战第17天】本文介绍了使用PostgreSQL进行高级数据库管理,涵盖性能调优、安全性加强和备份恢复。性能调优包括索引优化、查询优化、分区和硬件配置调整;安全性涉及权限管理、加密及审计监控;备份恢复则讨论了物理备份、逻辑备份和持续归档。通过这些实践,可提升PostgreSQL的性能和安全性,确保数据资源的有效管理。
|
2天前
|
缓存 关系型数据库 MySQL
如何优化MySQL数据库查询性能
MySQL是一款常用的关系型数据库,但在实际使用过程中,由于数据量增加和查询操作复杂度增加,会导致查询性能下降。本文将介绍一些优化MySQL数据库查询性能的方法。
|
4天前
|
数据库
sqlserver数据库学习感悟(1)----关于group by
sqlserver数据库学习感悟(1)----关于group by
8 0
|
4天前
|
Cloud Native 关系型数据库 分布式数据库
PolarDB是阿里云自主研发的关系型云原生数据库
【5月更文挑战第14天】PolarDB是阿里云自主研发的关系型云原生数据库
40 3
|
4天前
|
SQL 缓存 大数据
优化数据库性能的五大策略
传统的数据库性能优化常常集中在SQL查询优化和索引设计上,然而,在当今大数据时代,优化数据库性能需要综合考虑更多因素。本文将介绍五大策略,从硬件资源利用、数据模型设计、查询优化、缓存策略到数据库配置调整,为您提供全面的数据库性能优化方案。
|
4天前
|
存储 关系型数据库 MySQL
Percona XtraBackup是否支持PostgreSQL数据库备份?
【5月更文挑战第13天】Percona XtraBackup是否支持PostgreSQL数据库备份?
50 1
|
4天前
|
数据库
编程日记02:个人站优化数据库和日志
编程日记02:个人站优化数据库和日志
12 0
|
4天前
|
关系型数据库 Serverless 分布式数据库
PolarDB PostgreSQL版Serverless技术原理解读
数据库是现代企业IT系统中非常重要的一部分。在创建数据库时,客户往往需要比较保守地去配置数据库集群的资源,包括CPU、内存、存储以及连接数等多种参数配置,以确保业务能够在波峰和波谷都能平稳运行。在这种情况下,客户购买的集群资源在业务波谷时期会被闲置,导致整体成本偏高;而在业务压力增长阶段,集群资源又应对不足。Serverless数据库可以很好地解决这个问题。它能够让数据库集群资源随客户业务负载动态弹性扩缩,将客户从复杂的业务资源评估和运维工作中解放出来。 本文描述PolarDB PostgreSQL版Serverless的构建中, 如何实现弹得快、弹得准、弹得稳、弹得广的几个关键技术点。
75781 5
PolarDB PostgreSQL版Serverless技术原理解读
|
4天前
|
缓存 关系型数据库 数据库
【Docker 专栏】Docker 与容器化数据库的集成与优化
【5月更文挑战第9天】本文探讨了Docker与容器化数据库集成的优势,如快速部署、环境一致性、资源隔离和可扩展性,并列举了常见容器化数据库(如MySQL、PostgreSQL和MongoDB)。讨论了集成方法、注意事项、优化策略,包括资源调整、缓存优化和监控告警。此外,强调了数据备份、恢复测试及性能评估的重要性。未来,随着技术发展,二者的集成将更紧密,为数据管理带来更多可能性。掌握此技术将应对数字化时代的机遇与挑战。
【Docker 专栏】Docker 与容器化数据库的集成与优化
|
4天前
|
存储 关系型数据库 分布式数据库
数据库索引回表困难?揭秘PolarDB存储引擎优化技术
PolarDB分布式版存储引擎采用CSM方案均衡资源开销与可用性。
数据库索引回表困难?揭秘PolarDB存储引擎优化技术

相关产品

  • 云原生数据库 PolarDB
  • http://www.vxiaotou.com