Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3842375
  • 博文数量: 146
  • 博客积分: 3918
  • 博客等级: 少校
  • 技术积分: 8584
  • 用 户 组: 普通用户
  • 注册时间: 2010-10-17 13:52
个人简介

个人微薄: weibo.com/manuscola

文章分类

全部博文(146)

文章存档

2016年(3)

2015年(2)

2014年(5)

2013年(42)

2012年(31)

2011年(58)

2010年(5)

分类: Mysql/postgreSQL

2013-06-06 00:18:48

   最近一段时间一直在学习PostgreSQL源码中backend/storage目录下的源码,学习了smgr,学习了buffer,对PostgreSQL数据在磁盘的布局,shared buffer及其缓存替换机制有了一定的了解。本文重点讲述PostgreSQL中的数据库和数据库中的表如何在磁盘存储。当然FSM和VM文件暂不涉及。PostgreSQL大牛可以一笑而过。
   database对应的磁盘文件:
   首先是如何查看我们的PostgreSQL有几个数据库database?
   方法有
   1 psql -l
   我们看到创建任何数据库之前,PostgreSQL已经存在了3个database。
   
   2 oid2name
   oid2name也可以看到我们当前已经存在的database:
   

   那么这三个数据库在磁盘中是如何存储的呢?我们看到/usr/pgdata/base目录下存在三个目录文件,对应的恰好是三个database的OID。
   What is the FUCK OID?
   OID是一个数字,对于PostgreSQL的database和某个database内的table都会有一个唯一的OID和它对应,对于我这个解释不太满意的,可以看PostgreSQL的国内大牛德哥的博文 get PostgreSQL’s next oid。从原理到代码解释的都比较清楚。我就不唧唧歪歪了。
   新装好的的PostgreSQL会有三个默认的database,对应在pgdata目录下的base下,每个database都有自己的目录。如果我们通过createdb新创建一个database,那么,可以期待在pgdata/base目录下会新增一个以db OID为名字的目录,我们验证之:
   
   我们通过createdb manu_db创建了一个名字为manu_db的database,我们通过oid2name看到database的OID=16384,而在base目录下的确新增了一个16384的目录,目录下已经有一些文件了,哪怕目前manu_db还是个空的db。
  1.     -rw------- 1 manu manu  8192 6月 3 21:31 11906
  2.     -rw------- 1 manu manu 16384 6月 3 21:31 11907
  3.     -rw------- 1 manu manu 24576 6月 3 21:31 11907_fsm
  4.     -rw------- 1 manu manu  8192 6月 3 21:31 11907_vm
    如果你关心这些个文件都是干啥的可以用oid2name -d manu_db -f 11907来查看文件是干啥的。
   
   我们看到11907文件对应的table是pg_collation,作为初学者,我表示不懂这个表是干啥的。对于这些database默认创建的文件我们按下不表。

   table对应的磁盘文件:
   目前database对应的磁盘文件基本解决,那么如果我在manu_db中创建一个table,磁盘上会发生那些变化呢?
   
    我们通过create TABLE创建了一个名字叫friends的table,这个table中没有ID这种适合做key的字段是我的失误,我就懒得改了。
    如何查看名为friends的table对应的磁盘文件。table在PostgreSQL对应叫做relfile,是relation file的意思。我们可以查询pg_class这张表看到:
    
    我们从上图中可以看到有多种方法可以看到table和 磁盘文件的映射关系:
    1 oid2name -d manu_db 会列出manu_db中的所有table和磁盘上file的对应关系
    2 select oid,relfilenode,relname from pg_class where relname = 'friends';
    3 select pg_relation_filepath();     
  1. manu_db=# select pg_relation_filepath('friends') ;
  2.  pg_relation_filepath
  3. ----------------------
  4.  base/16384/16385
  5. (1 row)
    推荐使用第二方法或者第三种方法,目标很明确就是查找table名为friends的relfilenode。
    找到relfilenode之后,我们可以看到,在base/16384目录下新增一个文件16385,这个文件对应的就是table friend对应的文件。
    值得注意的事情有2
    1 table 的OID和table对应的磁盘文件名是相同的。一般如此,也不尽然,对表进行一个操作可以改变文件存储的名称而不改变表的OID,从而导致两者不一致。greg smith说TRUNCATE REINDEX和CLUSTER可能引起这种不一致,我还是菜鸟,不能深刻理解。
    2 随着表的不断插入新的条目,这个磁盘文件越来越大,当超过1G的时候,这个表会分文件存储,PostgreSQL叫做分Segment存储。会生成一个16385.1。
   
   这就牵扯到了buffer size 和Segment Size,relfile在PostgreSQL中存在shared buffer,shared buffer的页面大小为8192B,所以,会将buffer中的页面flush到磁盘文件中,所以是8K整数倍。我们插入一条记录看一下表friends对应的磁盘文件:
   
   果然是8K,我们可以查看这个block size和对于大的relation file多少个block开始分segment:
   
   第一个值是8K,单位是Byte,第二个值是128K,单位是个,表示128K个block组成一个segment,relation file再增大的话,就分成另一个segment,比如我们friends这个relation插入的item越来越多,文件16385大小超过128K×8KB=1G的时候,就要新增一个磁盘文件16385.1.

   那么如何查看那个relation file占据最多的磁盘空间呢。这个内容有点超前,毕竟我们才刚刚创建自己的table,但是没关系 ,这个内容很实用,我们会很好奇,自己哪个table会占用最多的磁盘空间,又占用多少磁盘空间:下面这条命令从一个老外的博客中习得(向他致谢,可惜找不到地址了,原谅我没给链接): 
  1. SELECT
  2. schemaname,
  3. tablename,
  4. pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS size_p,
  5. pg_total_relation_size(schemaname || '.' || tablename) AS siz,
  6. pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size_p,
  7. pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) AS index_size,
  8. (100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/CASE WHEN pg_total_relation_size(schemaname || '.' || tablename) = 0 THEN 1 ELSE pg_total_relation_size(schemaname || '.' || tablename) END || '%' AS index_pct
  9. FROM pg_tables
  10. ORDER BY siz DESC LIMIT 50;
       
   因为我的db没有啥数据,所以都是系统表占用的空间多,我在我公司的项目中用了这条sql,就是我们自己的relation占据排行榜的前面。

参考文献:
1 PostgreSQL 性能调校
2 一些博客,没有保存地址,十分抱歉,向前辈致敬。

阅读(16202) | 评论(5) | 转发(1) |
给主人留下些什么吧!~~

CUwangln2014-01-18 00:41:15

文章写得好,最后一个SQL语句SELECT schemaname ... 应该加上where schemaname = 'manu_db' 吧?

Bean_lee2013-09-09 13:01:15

ldming101:最近也在学习PostgreSQL存储管理这部分的内容,读了博主的文章,收获很多,感谢~

有帮助就好。谢谢鼓励

回复 | 举报

ldming1012013-09-09 11:45:45

最近也在学习PostgreSQL存储管理这部分的内容,读了博主的文章,收获很多,感谢~

Bean_lee2013-06-07 07:18:19

wjlkoorey258:来踩一下,向那些凌晨还在写博客的兄弟(不知道有没有姐妹哈)们致敬....

好久没写博客了,所以补了一篇,最近要集中学习 下PostgreSQL

回复 | 举报

wjlkoorey2582013-06-06 22:01:24

来踩一下,向那些凌晨还在写博客的兄弟(不知道有没有姐妹哈)们致敬....