阿里云 PostgreSQL_开发实践_4 | 学习笔记

本文涉及的产品
对象存储 OSS,20GB 3个月
对象存储 OSS,内容安全 1000次 1年
对象存储 OSS,恶意文件检测 1000次 1年
简介: 简介:快速学习阿里云 PostgreSQL_开发实践_4

开发者学堂课程【PostgreSQL 云上开发实践:阿里云 PostgreSQL_开发实践_4】学习笔记,与课程紧密联系,让用户快速学习知识。

课程地址:https://developer.aliyun.com/learning/course/528/detail/7107


阿里云 PostgreSQL_开发实践_4


目录:

一、数据老化实践

二、分级存储

三、复杂业务逻辑延迟问题优化

四、权限体系

五、行级权限控制

六、为什么会膨胀

七、freeze 风暴、追溯

八、预测、预防 FREEZE 风暴

九、可定义 SLA 的备份恢复架构设计

十、分区建议

十一、快速构造海量测试数据


一.数据老化实践

https://github.com/digoal/blog/blob/master/201712/20171208_01.md

我们可以去安排一些这样的调度任务,把我们的数据根据业务上的一些访问的规则,相对较老的数据任务调度的形式,比如说每天或者每周又或者每月月初,我们可以把一些相对来说时间比较久远的数据从分区表里面把它给剥离出来,把它给放到 OSS 里面存储,那么做法的话,那就需要依赖 PG 里面的特性,叫做继承,

利用 PG 的分区表的特性,以及继承的特性,可以使我们的存到不同的存储空间的数据,把它给联合起来。

image.png


二.分级存储

l https://help.aliyun.com/knowledge_detail/43352.html

l 热数据

-实例本地存储

l 访问频次较低数据

- oss 外部表存储

- 压缩格式选择 

l 继承与分区约束

- 每个 OSS 外部表负责一部分数据

- 使用约束

- 建立 OSS 外部表继承关系


三. 复杂业务逻辑延迟问题优化

比方说咱们有一些非常复杂的业务逻辑 那么事物的话涉及到很多次的数据库交互,那么你会发现说,如果每一次交互假设要耗掉在网络上消耗的时间,

比如说是毫秒,那我们要交互100次的话,实际上在网络上消耗的时间100个毫秒,那么100次100次的交互,如果在数据库里面,可以很快的完成。

image.png

一个事物当中假设有20条C口,包括基于组键的增删改查, 一个操作就设计20条C口,那么使用交互式的方法,我们发现说我们的 TPS 只能达到18000,如果我们把这样一些业务逻辑都放到数据库里面,就跟数据库只交互一次,那么我的 TPS 可以达到5万多。

 image.png


四. 权限体系

实例权限:

修改 pg_hba.conf

数据库权限:

grant 赋予是否允许连接或创建 schema 的权限

revoke 回收

schema权限:

grant 赋予允许查询schema中的对象,或在 schema 中创建对象

revoke 回收

object 权限:

grant 赋予

revoke 回收

表空间:

grant 赋予允许在对应表空间创建表,物化视图,索引,临时表

revoke 回收

 

五. 行级权限控制

l RLS

-https://www.postgresql.org/docs/10/static/sql-createpolicy.html

-https://github.com/digoal/blog/blob/master/201602/20160203_03.md

-https://github.com/digoal/blog/blob/master/201504/20150409 _01.md

-CREATE POLICY name ON table_name [ AS { PERMISSIVE |

RESTRICTIVE } ] [ FOR {ALL | SELECT [ INSERT | UPDATE | DELETE} ][ To { role_name | PUBLIC | CURRENT_USER |SESSION_USER } [,.…]][USING ( using_expression ) ] [ WITH CHECK ( check_expression ) ]

 

六. 为什么会膨胀

l 表、索引、物化视图为什么会膨胀

l 如何检查膨胀

-https://github.com/digoal/blog/blob/master/201306/20130628_01.md

https://raw.githubusercontent.com/digoal/pgsql_admin_script/master/generate _report.sh

l 膨胀如何处理

https://github.com/digoal/blog/blob/master/201610/20161030_O2.m

l 如何预防膨胀

- https://github.com/digoal/blog/blob/master/201511/20151109_01.md

- https://github.com/digoal/blog/blob/master/201504/20150429_02.md

 

七. freeze 风暴、追溯

l 业务空闲、But lO\CPU 偏高。

- select age(a.relfrozenxid),last_autovacuum,last_vacuum,schemaname,a.relname,pg_size_pretty(pg_total_relation_size(relid))from pg_class a, pg_stat_all_tables b where

aoid=b.relid and a.relkind in ('r', 'm') order bylast_autovacuum nulls last;


八. 预测、预防 FREEZE 风暴

预防

表级 autovacuum freeze 参数

AUTOVACUUM_ANALYZE_SCALE_FACTOR AUTOVACUUM_MULTIXACT_FREEZE_MAX_AGE

AUTOVACUUM_VACUUM_THRESHOLD TOAST.AUTOVACUUM_FREEZE_MIN_AGE

TOAST.AUTOVACUUM_VACUUM_COST_LIMIT

AUTOVACUUM_ANALYZE__THRESHOLD AUTOVACUUM_MULTIXACT_FREEZE_MIN_AGE FILLFACTOR

TOAST.AUTOVACuUM_FREE2E_TABLE_AGE TOAST.AUTOVACUUM_VACUUM_SCALE_FACTOR

-AUTOVACUUM_ENABLED 


九. 可定义SLA的备份恢复架构设计

https://github.com/digoal/blog/blob/master/201711/20171129_02.md

数据库监控、健康指标

l https://github.com/digoal/pgsql_admin_script/blob/maste

l 膨胀对象

l 操作系统信息(资源限制、防火墙、硬件错误、磁盘寿。

l 未引用大对象

l 剩余年龄

l 数据库

l 长事务、2PC

l 错误日志

l 归档状态

l SQL

l 备库延迟

l 空间利用率

l HA 状态

l 空间变化趋势

l 订阅延迟

l TOP 对象

l 简单密码

l 数据库连接(总、己使用、剩余、认证中、库级、用户级)

l TOP SQL (Io\cPU\CALLS\timing)

l 未使用索引

l 索引数超过N的对象

l 回滚比例

l QPS\TPS

l 垃圾比例

l 序列剩余

l 安全风险(触发器、事件触发器、规则、SQL 注入、密码错误尝试、对象名)

多实例管理、监控

https://github.com/dalibo/pgbadger

https://www.pgadmin.org/

http://pgstatsinfo.sourceforge.net/


十. 分区建议

l 单表多大建议使用分区表

- 非常频繁更新的表(考虑到 autovacuum 的速度)

·2亿

·指表中频繁被更新的记录数在2亿以内,表可以大到20亿不分区。

- 更新、删除不频繁或毫无的表(考虑到设计 rewrite 的 DDL ,建索引,逻辑备份等的速度)

·20亿


十一. 快速构造海量测试数据

有趣的功能:

1、SRF

返回多条记录的函数。

返回一批数值、时间戳、或者数组的下标。

例子,生成一批顺序值。

postgres=# select id from generate_series(1,10) t(id);  

id  

2、随机数

random()  

例子,生成一批随机整型

postgres=# select (random()*100)::int from generate_series(1,10);  

int4  

3、随机字符串

md5(random()::text)  

例子,生成一批随机字符串

postgres=# select md5(random()::text) from generate_series(1,10);  

md5                  

4、重复字符串

repeat('abc', 10)  

例子,生成重复2次的随机字符串

postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);  

5、随机中文

$$ language plpgsql strict;  

postgres=# select gen_hanzi(10) from generate_series(1,10);  

gen_hanzi        

----------------------  

騾歵癮崪圚祯骤氾準赔  

縬寱癱办戾薶窍爉充環  

鷊赶輪肸蒹焷尮禀漽湯  

6、随机数组

create or replace function gen_rand_arr(int,int) returns int[] as $$    

---------------------------------  

{69,11,12,70,7,41,81,95,83,17}  

{26,79,20,21,64,64,51,90,38,38}  

7、连接符

postgres=# select concat('a', ' ', 'b');  

concat  

--------  

a b  

(1 row)  

8、自定义函数

通过自定义函数,可以生成很多有趣的数据。

随机身份证号

l https://github.com/digoal/blog/blob/master/201711/20171121_01.md

l pgbench

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
133 6
|
2月前
|
SQL 关系型数据库 MySQL
Mysql学习笔记(三):fetchone(), fetchmany(), fetchall()详细总结
MySQL中用于数据检索的`fetchone()`, `fetchmany()`, `fetchall()`函数的功能、SQL语句示例和应用场景。
64 3
Mysql学习笔记(三):fetchone(), fetchmany(), fetchall()详细总结
|
2月前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
74 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
|
2月前
|
关系型数据库 MySQL 数据库
Mysql学习笔记(四):Python与Mysql交互--实现增删改查
如何使用Python与MySQL数据库进行交互,实现增删改查等基本操作的教程。
68 1
|
2月前
|
人工智能 自然语言处理 关系型数据库
阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成
近日,阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成。
|
6月前
|
SQL Oracle 关系型数据库
MySQL学习笔记
MySQL学习笔记
41 0
|
4月前
|
SQL druid Java
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)(下)
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)
60 3
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)(下)
|
4月前
|
SQL Java 关系型数据库
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)(上)
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)
157 3
Java数据库部分(MySQL+JDBC)(二、JDBC超详细学习笔记)(上)
|
4月前
|
SQL 关系型数据库 MySQL
Java数据库部分(MySQL+JDBC)(一、MySQL超详细学习笔记)(下)
Java数据库部分(MySQL+JDBC)(一、MySQL超详细学习笔记)
40 6
|
4月前
|
SQL 关系型数据库 MySQL
MySQL学习笔记
这篇文章是一份关于MySQL数据库操作的学习笔记,涵盖了数据库的终端操作、数据类型、建表约束、事务处理以及SQL的连接查询等基础知识点。