PowerPoint 演示文稿

Size: px

Start display at page:

Download "PowerPoint 演示文稿"

饶穷溥
6 years ago
Views:

1 PostgreSQL Enteprise Appliance Digoal.Zhou 7/6/2011

2 Database LifeCycle 运维阶段试运阶段 DBA 测试阶段开发阶段设计阶段需求阶段

3 用户就是上帝

4 需求阶段回避非刚性干掉不合理挖掘潜在需求合理头脑风暴需求评审刚性

5 设计阶段业务类型例举浅析 APPStore 型 ( 静态 ) 资源基础信息资源排行榜资源打分, 评论等包月类信息活跃数据 < 内存大小 SNS 型 ( 动态 ) 个人属性社会关系 ( 好友, 群组 ) 分享 ( 微博, 即时消息, 图片, 视频, 音乐, 博文 ) 磁性应用 ( 社区网游 ) 活跃数据 > 内存大小网游型 ( 动态 ) 个人属性 ( 经验值, 等级, 装备, 金钱 ) 关系 ( 好友, 群组 )

6 设计阶段数据库挑战浅析 APPStore 型 ( 静态 ) 读请求 (70%) 白名单, 黑名单, 资源信息, 包月信息等写请求 (30%) 资源信息增删改, 新增日志请求数到达数据库极限应用层缓存刷新或失效硬件故障 IDC 故障用户体验

7 设计阶段系统设计浅析 APPStore 型 ( 静态 ) DDNS Clients HTTP/WAP HTTP/WAP Cache Cache RW RO RW RO RW RSP RSS LOG RSS LOG Hot-Standby IDC-1 Hot-Standby IDC-2

8 设计阶段系统设计浅析异步流复制介绍 Standby 2 archlog xlog 1 WalRcvData->receivedUpto Primary startup process postmaster wal receiver send PM_SHUTDOWN_2 PMSignal recover 3 WalRcvData->conninfo WalRcvData->receiveStart fork wal sender read datafiles postmaster XLOG fork WAL buffer read write APP Other backend processes write wal writer

9 设计阶段 Parameter Tuning : Primary max_wal_senders wal_sender_delay ( The sleep is interrupted by transaction commit ) wal_keep_segments vacuum_defer_cleanup_age ( the number of transactions by which VACUUM and HOT updates will defer cleanup of dead row versions. ) Standby hot_standby # wal apply & SQL on standby conflict reference parameter max_standby_archive_delay ( the maximum total time allowed to apply any one WAL segment's data. ) max_standby_streaming_delay ( the maximum total time allowed to apply WAL data once it has been received from the primary server ) wal_receiver_status_interval ( minimum frequency, The standby will report the last transaction log position it has written, the last position it has flushed to disk, and the last position it has applied.) hot_standby_feedback (send feedback to the primary about queries currently executing on the standby. )

Only one sync standby at the same time 3.

10 设计阶段系统设计浅析同步流复制介绍 Standby(s) startup process recover datafiles 1 3 archlog 2 WalRcvData->receivedUpto postmaster WalRcvData->conninfo WalRcvData->receiveStart xlog wal receiver postmaster 1. Many standbys 2. Only one sync standby at the same time 3. Sync wide: Cluster Database User Session Transaction fork wal sender read XLOG Primary PM_SHUTDOWN_2 PMSignal APP fork Other backend processes waiting/releasing WAL buffer write read write wal writer

11 设计阶段 Parameter Tuning : Primary max_wal_senders wal_sender_delay wal_keep_segments vacuum_defer_cleanup_age synchronous_replication synchronous_standby_names ( primary_conninfo in standby s primary_conninfo ) Standby hot_standby max_standby_archive_delay max_standby_streaming_delay wal_receiver_status_interval hot_standby_feedback

12 设计阶段系统设计浅析 APPStore 型 ( 静态 ) 日志分区表 (1) tbl_parten APP-UNKnown Triggers CHECK crttime<= CHECK crttime<= yyyy-mm-dd CHECK crttime<= yyyy-mm-dd tbl_child tbl_child tbl_child

2011-01-01 CHECK crttime<= yyyy-mm-dd CHECK

13 设计阶段系统设计浅析 APPStore 型 ( 静态 ) 日志分区表 (2) 父表可有可无, 有的话管理方便 APP-Known tbl_parten CHECK crttime<= CHECK crttime<= yyyy-mm-dd CHECK crttime<= yyyy-mm-dd tbl_child_yy yymm tbl_child_yy yymm tbl_child_yy yymm

14 设计阶段数据库挑战浅析 SNS 型 ( 动态 ) 写请求 (50%) 读请求 (50%) 个人属性数据 > 内存大小社会关系数据 > 内存大小分享数据 > 内存大小磁性应用数据 > 内存大小活跃数据 > 内存大小

15 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库功能性拆分关系库磁性应用库个人属性库分享库

16 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库功能性拆分微博库其他博文库即时消息库分享库视频库图片库音乐库

17 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库功能性拆分一般游戏库还可分区域应用 1 库 (s) 应用 n 库 (s) 磁性应用库应用 2 库 (s)

18 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库水平拆分库 1 个人属性库库 n 库 2

19 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库水平拆分库 1 社会关系库库 n 库 2

20 设计阶段系统设计浅析 SNS 型 ( 动态 ) 数据库水平拆分 PostgreSQL LogData Application & DataRouting Application & DataRouting PostgreSQL PostgreSQL PostgreSQL PostgreSQL mod(userid,4)=0 mod(userid,4)=1 mod(userid,4)=2 mod(userid,4)=3 UserDataSet Combined to One of Integrity Data

21 设计阶段系统设计浅析 SNS 型 ( 动态 ) IDC 内部高可用设计 HA VIP Standby S LOG P

22 设计阶段系统设计浅析 SNS 型 ( 动态 ) 异地容灾设计 pg_xlog Storage Cloud Storage Cloud Standby_A WAN Compr ess Transm it pg_xlog pg_xlog Standby_B Standby_C

23 设计阶段系统设计浅析 SNS 型 ( 动态 ) 混合场景设计 Tuning settable run-time parameter using ALTER DATABASE or ALTER ROLE. Connect to Same Database_A postgresql.conf : synchronous_commit = on User_A APP1 APP2 User_B User_C APP3 User_D APP4 User_A : synchronous_commit = off User_B : Inherit postgresql.conf value. Database_A Database_B Database_C PostgreSQL Database Cluster Database_B : synchronous_commit = off Database_C: Inherit postgresql.conf value.

Eliminate complex operation SQL 优化 Use prepared statement 批量提交 Eliminate big resule set return at once

24 开发阶段 SCHEMA 设计字段类型, 长度, 存储格式 fillfactor, autovacuum_enabled, threshold, STATISTICS 相关参数索引, 约束,PK,UK,FK,CHECK 函数开发 Estimate data exists use perform replace select count(*) Eliminate long transaction Eliminate complex operation SQL 优化 Use prepared statement 批量提交 Eliminate big resule set return at once 结果集指针 Eliminate use database operation resource when application can do it Eliminate full table scan, use index when need it

25 测试阶段 Loadrunner Pgbench 平均, 最高, 最低 TPS,QPS,SQL 响应时间.

26 试运阶段调整系统参数 /etc/sysctl.conf kernel.shmmni kernel.shmall kernel.sem fs.file-max fs.aio-max-nr net.ipv4.ip_local_port_range net.ipv4.tcp_tw_recycle net.ipv4.tcp_max_syn_backlog net.ipv4.ip_conntrack_max net.ipv4.tcp_timestamps net.core.rmem_default net.core.rmem_max net.core.wmem_default net.core.wmem_max vm.overcommit_memory vm.overcommit_ratio vm.lowmem_reserve_ratio /etc/security/limits.conf nofile nproc core memlock

27 试运阶段调整数据库参数 # Connection listen_addresses max_connections superuser_reserved_connections unix_socket_directory unix_socket_permissions tcp_keepalives_idle tcp_keepalives_interval tcp_keepalives_count # Memory shared_buffers maintenance_work_mem max_stack_depth ( ulimit -s ) # Kernel Resource Usage max_files_per_process # Cost-based Vacuum Delay vacuum_cost_delay vacuum_cost_limit # Background Writer bgwriter_delay bgwriter_lru_maxpages bgwriter_lru_multiplier # Asynchronous Behavior effective_io_concurrency (asynchronous I/O requests. Currently, this setting only affects bitmap heap scans)

28 试运阶段 # WAL Settings wal_level synchronous_commit wal_sync_method (open_datasync, fdatasync, fsync, fsync_writethrough, open_sync) wal_buffers wal_writer_delay commit_delay commit_siblings # WAL Checkpoints checkpoint_segments checkpoint_timeout checkpoint_completion_target checkpoint_warning # WAL Archiving archive_mode archive_command archive_timeout # Streaming Replication # Synchronous Replication # Standby Servers # Planner Method # Planner Cost Constants random_page_cost effective_cache_size # Genetic Query Optimizer # Other Planner Options default_statistics_target constraint_exclusion

29 试运阶段 # Log log_destination logging_collector log_directory log_truncate_on_rotation log_rotation_age log_rotation_size log_min_duration_statement log_checkpoints log_lock_waits log_statement # RUNTIME STATISTICS # AUTOVACUUM PARAMETERS autovacuum log_autovacuum_min_duration autovacuum_vacuum_threshold autovacuum_analyze_threshold autovacuum_vacuum_scale_factor autovacuum_analyze_scale_factor autovacuum_freeze_max_age autovacuum_vacuum_cost_delay autovacuum_vacuum_cost_limit # CLIENT CONNECTION DEFAULTS deadlock_timeout statement_timeout

30 运维阶段监控权限管理审计备份, 恢复测试性能报告优化, 与开发人员一起提高系统性能扩容资源调配 ( 如业务量下滑, 对应的数据库不再需要高端的硬件设备, 涉及数据迁移, 数据库合并等 ) 日志分析空间回收 ( 如删除归档, 备份并删除不再需要的数据 )

31 运维阶段监控监控要素系统 :CPU, 内存, 交换分区, 网络,IOPS, 磁盘, Kernel 报错, 硬件故障等数据库 : 慢查询,DEAD TUPLE 比例, 锁等待事件, buffer 使用情况, 严重异常日志等常用软件实时告警趋势图 sendmail

32 权限管理运维阶段最小权限连接 :pg_hba.conf 最小权限对象 :grant / revoke 审计登录审计 DDL 操作审计按用户审计 ( 目前 PostgreSQL 还不支持 )

33 运维阶段备份指纹信息指纹信息指纹信息指纹信息指纹信息指纹信息 DB1 DB2 DB3 DB4 DB5 生产系统 SAN DBx DBy 指纹信息 2 压缩传输 1 3 Cloud Storage(s) 调度数据库生产系统 SAN DB1 归档 DB2 归档 DB1 归档 DB2 归档 DBx 归档 Dby 归档 DBx 归档 Dby 归档

34 运维阶段扩容 ( 接近平滑 )

35 运维阶段

36 运维阶段

37 运维阶段

38 Thanks Thanks all people contribute to PostgreSQL. Digoal.Zhou Blog

ORACLE Enterprise Linux 6.3下ORACLE11g的安装

ORACLE Enterprise Linux 6.3下ORACLE11g的安装 ORACLE Enterprise Linux 6.3 环境下 ORACLE11g 的安装文档 1 安装前的参数配置 Auther:chenzhuzuo@163.com 以下操作需要一 root 用户的身份进行操作 1.1 在文件 /etc/sysctl.conf 中添加如下内容 fs.le-max = 6815744 fs.aio-max-nr