点. 缺点, 文件较大. 优点, 还原时间和XLOG数量有关, 一般建议勤备份基 础数据文件. 缩短恢复耗时. 《PostgreSQL Selectivity Tablespace PITR》 42 PostgreSQL - 典型的物理备份 digoal=# select * from pg_start_backup(now()::text); rsync -acvz --exclude=pg_xlog $PGDATA/*
backup_host::backup_pgdata/$DATE digoal=# select * from pg_stop_backup(); 或者使用pg_basebackup代替以上步骤 pg_basebackup -D $backup_dir/$DATE -f p -x stream-h $PRIMARY -p $PGPORT -U $replica XLOG归档文件的备份可以在archive_command里面就做成同时写原程和 本地. 因此计划好基础数据文件备份就好了. 43 PostgreSQL - wal sender process pg_receivexlog pg_receivexlog, 利用流复制协议进行实时归档. 增强数据保护级别. PREPARE 1.主节点数据库上创建一个superuser或replication角色用于流复制 2.配置主节点(或级联standby节点)的pg_hba.conf允许客户端和主节点建立流复制的连接 host replication postgres ip地址 trust 3.配置主节点(或级联standby节点)wal_level(archive/hot_standby), max_wal_senders(>0) 4.确保远程归档节点和主节点(或级联standby节点)网络(数据库监听端口)可达 wal writer XLOG 写入 读取 网络传输 流复制协议 XLOGs postgres
main process 请求连接,认证 fork 目录 写入 fork 44 PostgreSQL - 平常在重大操作前勤创建还原点(用于物理还原) pg_create_restore_point 逻辑还原 custom格式的备份文件可以使用pg_restore -j 并行还原. 如果备份文件比较大, 需要做全还原的话, 想调整还原的表的顺序怎么办? PostgreSQL Logical Backup's TOC File 物理还原 PITR还原目标 recovery_target_name recovery_target_time recovery_target_xid PostgreSQL datafile backup and recovery case 45 PostgreSQL - OLTP优化 OLTP系统特点 高并发 小事务, 查询(多), 新增(多), 更新(多), 删除(较少) 备份 Speedup 查询 新增 更新, 删除 备份 46 speedup select Table Index TOAST Block 1 2 3 4 5 6 7 8 2^n Block 1 2 3 4 5 6 7 8 2^n Block 1 2 3 4 5 6 7 8 2^n OS Cache(文件系统 block) shared_buffer LRU DataFiles postgresql blocksize 可能存在重复cache 内存 47 TOAST TOAST和HEAP TABLE存储不太一样，TOAST表一般包含如下字段 : tableoid --TOAST表的OID cmax xmax cmin xmin ctid chunk_id -- HEAP表通过TOAST pointer把这行中一个被TOAST的列关联到这 里 chunk_seq -- 同一个chunk_id如果大于TOAST_MAX_CHUNK_SIZE，将被切 片存储。这里存储切片后的序号。 chunk_data -- 真实的数据
chunk_id + chunk_seq = primary key 48 speedup select SQL层面的优化 索引 选择合适的索引访问方法 (btree, gist, gin, btree-gist, sp-gist, hash), 选择合适的列, (驱动列), 选择合适的条件(partial index), 选择合适的数据类型 range类型的例子. PostgreSQL range gist index 20x+ speedup than Mysql index combine query IP地址匹配的应用案例, 原来使用的是两个int字段进行范围匹配. btree索引. 3600 tps. 修改成int8range类型, gist索引, 8Wtps. 合适的JOIN算法 nestloop, hashjoin, mergejoin 合适的成本 random_page_cost 49 speedup select IOPS优化 OS Cache 数据库关闭后还可能存在 posix_fadvise shared_buffer 数据库关闭后清空, LRU算法 利用pgfincore speedup select 持久化 POSIX_FADV_WILLNEED 抛弃
POSIX_FADV_DONTNEED 镜像, 预读 至内存( -- 保存表,索引,TOAST对应的OS PAGE CACHE状态表 的snapshot, -- 通过状态表预加载OS PAGE CACHE ) 50 extend select 利用流复制或其他复制技术扩展查询 hot standby 平台必须一致, 操作系统种类一致, 数据库版本要求一致 londiste3 跨平台, 跨数据库版本, 跨操作系统的复制 基于触发器, 延时相比hot standby高, 性能影响相比hot standby高 其他复制软件 slony-I
pgpool-II ... 应用层缓存 memcache redis 51 speedup complex select 将运算迁往APP层, 简化数据库SQL 子查询过多或JOIN表数量过多的情况下可以考虑让优化器按照SQL书写顺序进 行关联, 杜绝优化器选择不优执行计划的情况.(from_collapse_limit=1 |
join_collapse_limit=1) 数据冗余, 减少JOIN 适当的数据冗余减少跨表查询 宽表 把窄表返回大量条记录改为宽表返回少量记录可以降低查询的IO请求, 对 内存小且存储IO能力不强的场景比较有效. 但是注意, 宽表可能带来一个问 题(热点更新) 事务级work_mem调整, 优化GROUP BY, 排序. 如 set local work_mem to '512MB'; 应用层缓存, 将实时查询转非实时性查询 分区表查询优化, 1. constraint_exclusion, 2. 封装SQL时直接使用子表, 3. 分区字 段上使用约束和索引, 4. SQL在分区字段带过滤条件时, 使用常数. 52 speedup insert batch insert insert into table (columns) values (),(),...,(); (20W+ tuples inserted per second) copy (40W+ tuples inserted per second) begin; sqls; end; (10W+ tuples inserted per second) 分组提交, 减少IO请求次数. commit_delay commit_siblings 异步提交 wal_writer_delay synchronous_commit 选择合适的wal_sync_method (pg_test_fsync) 选择合适的
相关推荐：
loading