德哥PostgreSQL_经验谈.pptxVIP

PostgreSQL 经验谈Digoal.zhou温馨提醒 - Happy Fathers Day2012/6/17主要内容浅析PostgreSQL数据库特点构建高效,稳定,安全,易扩展的PostgreSQL数据库系统PostgreSQL容灾PostgreSQL备份, 还原.PostgreSQL优化PostgreSQL迁移PostgreSQL压力测试PostgreSQL监控PostgreSQL CaveatPostgreSQL展望浅析 PostgreSQL 特点功能严格遵循ACID(事务操作原子性(支持savepoint),状态一致性,隔离(read committed, repeatable read, serializable),持久化(fsync xlog))在保证ACI的前提下, 降低持久化标准可以提升百倍性能. (异步提交, 异常DOWN库或DOWN机可能导致wal_buffer里面未flush到xlogfile的信息丢失(最多wal_writer_delay*3时间的wal信息),不会导致数据库不一致或不可恢复.)two phase commit(例如利用dblink做跨库事务)MVCC, 8个锁级别(DML读写不冲突)更新优化机制(HOT, 降低索引需要更新的概率, 索引字段没有被更新且被更新的记录所在的BLOCK有足够的空间存下更新后的记录时符合HOT)垃圾数据通过VACUUM机制回收(Objects VM file中的块不需要扫描, 触发full scan除外.vacuum 与DML不冲突)HOT UpdateHeap PageIndex PagePageHeadPageHeadFreeSpaceLinePoint1LinePoint2ItemPointTuple2HEAP_HOT_TUPLETuple1t_ctid, HEAP_HOT_UPDATEDHOT UpdateHeap PageIndex PagePageHeadPageHeadLP_FLAGS(2)LP_REDIRECTFreeSpaceLinePoint1LinePoint2ItemPointTuple2HEAP_HOT_TUPLETuple1Dead,vacuumedItemIdData Layout /* An item pointer (also called line pointer) on a buffer page */ /* In some cases an item pointer is in use but does not have any associated */ /* storage on the page. By convention, lp_len == 0 in every item pointer */ /* that does not have storage, independently of its lp_flags state. */typedef struct ItemIdData{ unsigned lp_off:15, /* offset to tuple (from start of page) */ lp_flags:2, /* state of item pointer, see below */ lp_len:15; /* byte length of tuple */} ItemIdData;/* lp_flags has these possible states. An UNUSED line pointer is available *//* for immediate re-use, the other states are not. */#define LP_UNUSED 0 /* unused (should always have lp_len=0) */#define LP_NORMAL 1 /* used (should always have lp_len0) */#define LP_REDIRECT 2 /* HOT redirect (should have lp_len=0) */#define LP_DEAD 3 /* dead, may or may not have storage */浅析 PostgreSQL 特点功能存储(表空间,临时表空间)字段存储选项(plain, main, external, extended)支持在线创建,删除索引(不和select,update,delete,insert冲突)索引维护(支持多个完全一致的索引,使得维护索引更加自由)索引类型(btree,hash,gin,gist,sp-gist,btr