search

timzaak / blog

8 stars 1 forks source link

PostgreSQL 学习 #25

Closed timzaak closed 2 years ago

timzaak commented 6 years ago

锁

pg-locks表 锁类型

查看当前链接服务进程pid

select pg_backend_pid();

查看当前锁

select locktype, relation::regclass as rel, virtualxid as vxid, transactionid as xid, virtualtransaction as vxid2, pid, mode, granted from pg_locks;

状态统计

统计收集器

常见问题

slowCount pg Cluster

Kafka Connector

kafka-sink-pg-json 未测试呢，先记着

timzaak commented 2 years ago

https://pgtune.leopard.in.ua/ https://github.com/jfcoz/postgresqltuner postgresql配置 优化工具。 pgbench 性能测试工具。

timzaak commented 2 years ago

https://github.com/Vonng/pigsty 集群/监控方案

timzaak commented 2 years ago

PostgreSQL 秒杀场景优化 pg_try_advisory_xact_lock 的使用 用PostgreSQL支持含有更新，删除，插入的实时流式计算 实时流式计算 stream,View

timzaak commented 10 months ago

PostgreSQL 写性能提升

按照优化顺序来搞。

  1. prepared statement 提交数据
  2. synchronous_commit = off , 取消事务提交 WAL 日志
  3. timestamp without timezone， 需要按产品需求来决定是否采用
  4. max_wal_size 增大，减少检查
  5. brin index，数字，时间
  6. 分表（PG 内置 / 手动搞）
  7. 更少的索引
  8. unlogged tables（这个就算了）
  9. batch insert（效果不好说，超脱数据库的优化范畴）

前7条实施起来，没太多难度云厂商会封一些参数，需要工单特殊处理。 unlogged tables 这个要有个前提，就是整个表都丢了也没问题，也不能基于WAL做主从备份。 batch insert 这一步都要用的话，还是最好切架构吧。这已经不是数据库的事情，需要搞多机处理、并行落库。

在腾讯云上简单测试了下，1C2G PG 15，启用1～5条， 1.5+w/s的插入速度，cpu 80%，不设置2、4条，1+w/s插入速度，cpu 100%+。 根据腾讯云公布的测试结果，简单估算一下，4C8G PG 15， 能做到10w+/s的插入速度。

create table test_insert(
    id int,
    sn text not null,
    data jsonb not null,
    time timestamp without time zone
);

create index test_insert_time on test_insert using brin(time);
create index test_insert_id on test_insert using brin(id);

-- 测试脚本
\set number random(1,1000000)
insert into test_insert(id,sn,data,time) values (:number, :sn, :json_data, current_timestamp);
# 4C8G Ubuntu 22
sudo apt-get update
sudo apt-get install postgresql-client
sudo apt-get install postgresql-contrib

pgbench -f insert_test.sql -r -c 100 -j 20 -M prepared -n -T 600 -p 5432 -h $host  -U $user -d $database --define "sn='3rdskwefoik'" --define "json_data={\"a\":1,\"b\":12390123}"

并行处理的常见架构

TimescaleDB，Kafka + ELT + Doris/TiDB/Clickhouse/HBase 至于 InfluxDB 之流的时序数据库，针对特定场景优化做到极致，反而到常规场景下就很难受，不是极端适配场景，还是不要轻易采用。

timzaak commented 6 months ago

PG 常用运维指令

-- 创建新Database + 用户授权
create database db;
create user db_user with password 'db_password';
alter database db owner to db_user;