PostgreSQL+经验谈PostgreSQL.docx

PostgreSQL+经验谈PostgreSQL.docx

《PostgreSQL+经验谈PostgreSQL.docx》由会员分享，可在线阅读，更多相关《PostgreSQL+经验谈PostgreSQL.docx（14页珍藏版）》请在冰豆网上搜索。

PostgreSQL+经验谈PostgreSQL

PostgreSQLDigoal.zhou6/16/20121 - HappyFather'sDay22012/6/17浅析PostgreSQL数据库特点构建高效,稳定,安全,易扩展的PostgreSQL数据库系统PostgreSQL容灾PostgreSQL备份,还原.PostgreSQL优化PostgreSQL迁移PostgreSQL压力测试PostgreSQL监控PostgreSQLCaveatPostgreSQL展望3PostgreSQL功能严格遵循ACID（事务操作原子性（支持savepoint）,状态一致性,隔离（readcommitted,repeatableread,serializable）,持久化（fsyncxlog））在保证ACI的前提下,降低持久化标准可以提升百倍性能.（异步提交,异常DOWN库或DOWN机可能导致wal_buffer里面未flush到xlogfile的信息丢失（最多wal_writer_delay*3时间的wal信息）,不会导致数据库不一致或不可恢复.）twophasecommit（例如利用dblink做跨库事务）MVCC,8个锁级别（DML读写不冲突）更新优化机制（HOT,降低索引需要更新的概率,索引字段没有被更新且被更新的记录所在的BLOCK有足够的空间存下更新后的记录时符合HOT）垃圾数据通过VACUUM机制回收（Object'sVMfile中的块不需要扫描,触发fullscan除外.vacuum与DML不冲突）4HOTUpdateHeapPageIndexPageLinePoint1LinePoint2Tuple1Tuple2PageHeadPageHeadItemPointt_ctid,HEAP_HOT_UPDATEDHEAP_HOT_TUPLEFreeSpace5HOTUpdateHeapPageIndexPageLinePoint1LinePoint2Tuple1Tuple2PageHeadPageHeadItemPointDead,vacuumedHEAP_HOT_TUPLEFreeSpaceLP_REDIRECT6PostgreSQL功能存储（表空间,临时表空间）字段存储选项（plain,main,external,extended）支持在线创建,删除索引（不和select,update,delete,insert冲突）索引维护（支持多个完全一致的索引,使得维护索引更加自由）索引类型（btree,hash,gin,gist,sp-gist,btree-gist,btree-gin）函数语言（C,SQL,plpgsql,plperl,pltcl,plpython,...）支持plpgsql函数debugger游标（支持[no]scroll,with[out]hold,支持游标删除,更新（wherecurrentof））触发器（[perrow|perstatement]INSERT,DELETE,UPDATE[ofcolumn,],TRUNCATE）规则（perstatement,doalso|instead|nothing）支持分区表（支持基于触发器,规则的分区表,多层分区支持,支持按照分区约束优化查询扫描）7PostgreSQL功能继承（一个表可以继承多个表,也可以被多个表继承,支持多级继承,不允许环路）约束（notnull,check,unique,primarykey,exclude约束）数据类型（数字,货币,字符,比特流,字节流,时间,布尔,枚举,几何,网络,全文检索（中文分词nlpbamboo）,UUID,XML,JSON,ARRAY,自定义复合,range,oid）内建函数（字符,时间,数字,聚集,数学,比特,字节流,规则表达式,枚举,几何,网络,全文检索,序列,JSON,XML,数组,范围,窗口,集合,触发器,系统管理等类型的函数）外部数据源表（PostgreSQL,文件,MySQL,Oracle,Sybase,ODBC,redis,couchDB,...）,支持外部表的JOIN,支持外部表的统计信息收集,目前仅支持外部表只读.（外部表在异构平台数据迁移中非常便利）支持的几种join方法（hashjoin,nestloop,merge）8PostgreSQL举例exclusion约束CREATETABLEtest（idint,geocircle,EXCLUDEUSINGGIST（geoWITHpg_catalog.&&））;INSERTINTOtestvalues（1,'<（0,0）,2>':

:

circle）;INSERTINTOtestvalues（1,'<（4.1,0）,2>':

:

circle）;INSERTINTOtestvalues（1,'<（-1.9,0）,2>':

:

circle）;ERROR:

conflictingkeyvalueviolatesexclusionconstraint"test_geo_excl"DETAIL:

Key（geo）=（<（-1.9,0）,2>）conflictswithexistingkey（geo）=（<（0,0）,2>）.9c1c2c3ForeignDataForeignTable（s）NOTNEEDServer（s）FDWFileForeignTable（s）UserMapping（s）Server（s）FDWOracleForeignTable（s）UserMapping（s）Server（s）FDWMySQLForeignTable（s）UserMapping（s）Server（s）FDWPostgreSQLForeignTable（s）UserMapping（s）Server（s）FDWRedisForeignTable（s）UserMapping（s）Server（s）FDW（s）OthersExternalDataSourceAPIConnINFOAUTHINFOTABLEDEFINE10PostgreSQL功能权限管理（表空间,数据库,schema,表,视图,序列,fdw,域,函数,大对象,复合类型,其中表支持列级权限）权限分配（系统（全局）配置,数据库级配置,用户级配置,会话级,事务级配置）在线备份,还原（基于时间点/事务号/自定义恢复点的恢复）standby（hot_standby（支持standby在恢复的同时提供查询）,warm_standby（恢复时不提供查询）,同步/异步standby（支持事务粒度的）,支持级联的standby,支持暂停和继续恢复,）数据块复制（流复制,logshipping）SQL复制（触发器（londiste3,slony-I）,SQL分发（pgpool-II））基于SQL分发的复制（架设于客户端和数据库之间）需要注意,必须所有节点都执行成功事务才能算成功.否则数据就不一致了.基于触发器的复制（架设于主库和slave库之间）则不需要考虑跨库事务的问题.11PostgreSQL功能CommonTableExpression（WITH语法,可以构造逻辑复杂的查询,如递归,树形查询,支持WITH中使用DELETE,update,select等.）SQL支持insert,update,deletereturning语法,适用后续需要对先前记录进行更改的操作,如二次确认.计划器（explainanalyzeverbosecostsbufferstimingformat[textxmljsonyaml]）12WITH（CommonTableExpressions）UNIONALL去重复（去重复时NULL视为等同）图中所有输出都涉及UNION[ALL]的操作,包含以往返回的记录和当前返回的记录非递归子句递归子句OUTPUTTEMPWorkingTABLE2读取1输出TWT有无数据4有,递归4无,结束递归递归子句5读取TEMPIntermediateTABLE（替换掉TWT的内容后清空自己）6同时输出3输出7TWT清空并被替换6输出循环开始13PostgreSQL性能支持自动平滑调整的checkpoint全文检索类型以及索引（例如中文分词nlpbamboo）gin,gist索引（有效提高范围查询,几何类型,多值类型如array等的查询性能,支持exclude约束,临近值查询加速,排序等.）INDEXONLYSCAN（需要用到visibilitymap,并不是所有tuple都只需要扫描Index）支持表空间级COST值（支持不同的表空间配置不同的IO成本）支持多个临时表空间部分索引（createindexont（column）where?

）表JOIN的顺序和算法优化（可强制按SQL书写的顺序进行JOIN（join_collapse_limit=1）,也可以让数据库穷举选出最优JOIN顺序）SQL子查询的算法优化（可强制按照子查询结构计算执行计划（from_collapse_limit=1）,或让数据库把子查询中的表提升到上一级的表中进行关联,从而选择最优的关联顺序.）14PostgreSQL性能unloggedtable支持并行还原（pg_restore -j）（Cache预加载）,通过pgfincore加载数据至OSCache,同时支持查看数据表,索引,toast表等当前的哪些数据块在OSCache里面,可以把这些数据记录到数据库中,重启后按照这个记录加载这些数据块到OSCache里面.动态性能视图（pg_stat_statements,pg_stats,pg_statio_xxx_tables|sequences|indexes,pg_stat_activity,pg_stat_xxx_tables|indexes|bgwriter|database|database_conflicts|replication|functions）hint（enable_bitmapscan,enable_hashagg,enable_hashjoin,enable_indexscan,enable_indexonlyscan,enable_material,enable_mergejoin,enable_nestloop,enable_seqscan,enable_sort,enable_tidscan）优化器可用的成本维度（seq_page_cost,random_page_cost,cpu_tuple_cost,cpu_index_tuple_cost,cpu_operator_cost,effective_cache_size,表空间级cost）15PostgreSQL其他国际化（列级别的collation,库级collation,多国语言,多字节编码）平台支持（Windows,unix,linux,bsd,solaris,HPUX,AIX,...）函数（支持100个参数）模块（btree-gin,btree-gist,citext,dblink,file_fdw,hstore,isn,ltree,pageinspect,passwordcheck,pgbench,pg_buffercache,pg_freespacemap,pg_standby,pg_stat_statements,pgstattuple,pg_trgm,pg_upgrade,sepgsql,tablefunc,uuid-osp,...）more（[github,pgfoundry,pgxn],other[postgis]...）审计（[断开]连接,按用户或数据库维度的审计,客户端IP,端口,用户,数据库,SQL,执行时间...）连接鉴权（client_ip,user,dbname,认证方法（reject,md5,trust,ldap,...））16PostgreSQL限制LimitValueMaximumDatabaseSizeUnlimitedMaximumTableSize32TBMaximumRowSize1.6TBMaximumFieldSize1GBMaximumRowsperTableUnlimitedMaximumColumnsperTable250 - 1600dependingoncolumntypesMaximumIndexesperTableUnlimited17PostgreSQL许可开源,BSD可定制化例如,自建复合类型,枚举类型自定义accessmethod表名最大长度限制的修改,src/include/pg_config_manual.h#defineNAMEDATALEN64reinitdbboolean值默认输出格式的修改,src/backend/utils/adt/bool.cchange:

result[0]=（b）?

't':

'f';remakesoftware18PostgreSQLPostgreSQL性能参考数据HPDL360G5（2*E5440,14GMEM,6*146GSAS10K转）CentOS5.xx64,PostgreSQL9.2batchinsert（20columns/tuple,200bytes/tuple,64tuples/batch,RESULT:

23Wtuples/s,3616tps,1.9ms/avgrequest）insert（20columns/tuple,200bytes/tuple,RESULT:

78579tps,0.2ms/avgrequest）select（5000Wtuples/table,10Gbytes/table,20columns/tuple,200bytes/tuple,按pk查询,RESULT:

72113tps,0.17ms/avgrequest）update（1000Wtuples/table,2Gbytes/table,20columns/tuple,200bytes/tuple,按pk更新,RESULT:

28474tps,0.48ms/avgrequest）delete（1000Wtuples/table,2Gbytes/table,20columns/tuple,200bytes/tuple,按pk删除,RESULT:

41372tps,0.28ms/avgrequest）19,,,PostgreSQL经验谈性能稳定安全可靠高可用扩展20PostgreSQL -OSPostgreSQLbackendProcessespostgresmainprocesspostgressystemprocessesarch,wal_writer,bgwritercheckpointer,statscollectorautovacuumlauncher|workerstartup,sysloggerOScachesharedbuffer,wal_bufferStorageRAIDCardcacheharddiskbufferharddiskmediumforkClientAccessSpeedDownCPUcache21PostgreSQL - CPU的考量要点每个postgresql进程在工作时最多只分配一个CPU核使用.高并发的场景同系列的多核CPU比较有优势低并发复杂运的算场景同系列的频率高的CPU比较有优势内存的考量要点并发连接数,活跃数据,shared_buffer的配置大小,常用SQL中groupby,distinct的比例和work_mem的配置大小,autovacuumwork进程的配置数量以及maintenance_work_mem的配置大小.OS配置推荐linuxx64位内核参数（kernel.shmmax,kernel.shmall,kernel.shmmni,kernel.sem,net.core.rmem_default,net.core.rmem_max,net.core.wmem_default,net.core.wmem_max,vm.overcommit_memory,fs.aio-max-nr）limit（nofile,nproc,core,memlock）blockdev --setra扇区（512字节）22PostgreSQL - 存储配置cache（打开带断电保护的读写cache）raid（建议RAID10,更新频繁的数据库不建议使用RAID5）medium（ssdor机械盘）（更新频繁的数据库建议使用满足IOPS需求的存储或SSD,或将频繁更新的数据表空间放到SSD上）网络的考量要素几个需要着重考虑网络的场景（bigresult[set],backup,容灾）连接池PostgreSQL和客户端交互是一对一的进程模式,因此不适合直接与高并发的短连接应用直接连接.加连接池是比较好的解决办法.（如pgbouncer）数据库编译时需要考量的要点with-wal-blocksize（根据pg_test_fsync结果选择最优）,with-blocksize（OLAP系统或经常需要读取大量数据进行分析的系统可以选择较大的blocksize）23PostgreSQL - 文件系统考量ext4,xfs,zfs数据库集群初始化时需要考量的要点 --encoding（满足需求的情况下选择单字符占有字节数少的,方便未来和其他业务线或国际化的话建议选择UTF8） --locale（C） --xlogdir（选择独立的存储） --pgdata（选择独立的存储）影响性能的postgresql.conf主要参数shared_bufferswork_memmaintenance_work_memvacuum_cost_delay,vacuum_cost_limitsynchronous_commit,wal_writer_delay,wal_buffers24PostgreSQL - 影响性能的主要参数:

postgresql.confwal_sync_methodcommit_delay,commit_siblingscheckpoint_segments,checkpoint_completion_targetrandom_page_cost,effective_cache_sizevacuum_cost_delay,vacuum_cost_limitautovacuum,autovacuum_vacuum_cost_delay,autovacuum_vacuum_cost_limitlog_lock_waits,deadlock_timeouttemp_tablespaces25PostgreSQL - SQL平均响应时间抖动每秒处理SQL请求数抖动抖动和什么有关?

checkpoint突发性资源争抢锁等待...稳定性目标不影响业务如数据库异步操作性能在业务接受范围内26PostgreSQL - 权限最小化pg_hba.confiptables角色权限防止SQL注入应用层过滤器,禁用简单查询协议（SimpleQueryprotocol（PQexec））密码复杂度策略长度,组成（大小写,数字,特殊字符）审计登陆,登出,DDL,密码加密传输,存储（encrypted）,数据加密传输,存储防火墙外网访问限制不建议数据库直接暴露在外网上27PostgreSQL - 单节点的PostgreSQL如何确保数据可靠性?

（XLOGfsync）确保成功提交的数据在发生异常后可恢复,未提交的事务在发生异常后回退.注意这里指的异常不包括存储故障fsync=onfull_page_writes=onsynchronous_commit=on关闭没有断电保护的持久化存储的cache（一般指硬盘）,（存储,RAID卡一般都会带断掉保护电池）一个降低可靠性,不破坏数据一致性,但是可以大大提高性能的参数（DinACID）synchronous_commit=off关闭synchronous_commit后,正常关闭数据库不会导致数据丢失.异常DOWN机或DOWN库则最多可能丢失wal_writer_delay*3的最近时间的xlog信息.（也就是未从wal_buffersflush到xlogfile的信息）28PostgreSQL- 数据库高可用的显著特点要求数据可靠性为什么要求数据可靠性?

A刷卡消费了1000元购买商品,在数据库中完成了这笔事务.数据库DOWN机Failover,数据库从主库切换到备份库A消费1000元购买商品的这个事务不能丢失.久经考验的高可用方案RHCS,共享存储,VIP共享存储,节点切换前要fence主库,确保主库不再对数据发生操作.同一时刻只有一个节点在对数据文件进行读写.缺陷不能低于存储故障的风险.如果配置的多播心跳,网络抖动会造成节点切换.网络情况不好可选仲裁磁盘.29PostgreSQL - 久经考验的高可用方案同步流复制,不共享存储,VIP优点是不需要采购昂贵的存储,可以抵御存储单点故障.切换前fence主库缺点是同步流复制对性能有影响（下降5%左右）.203458765/异步流复制不适合引入高可用方案,原因是会导致切换后丢失事务信息.如A消费的事务可能在切换后丢失.（当然如果业务系统容忍这种丢失,也可以考虑的.）30PostgreSQL - 扩展应该和应用紧密结合和应用不相干的扩展举例中间件（pgpool-II）,读写分离,分布式.和应用结合数据库扩展读写分离（流复制）,分表分库跨库事务,全局一致性备份和还原（目前只有Postgr