Chinaunix首页 | 论坛 | 博客
  • 博客访问: 542076
  • 博文数量: 92
  • 博客积分: 980
  • 博客等级: 准尉
  • 技术积分: 1426
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-25 20:34
文章分类

全部博文(92)

文章存档

2015年(3)

2014年(15)

2013年(27)

2012年(38)

2011年(9)

分类: DB2/Informix

2013-06-17 23:19:38

?
数据库表空间的扩容是在数据库管理过程中常见的问题。对于DPF数据库,表空间的扩容有什么要求呢?关键性的结论是:
DPF数据库中的每个节点,都是独立的数据库(Share Nothing),他们的资源彼此独立独享,因此,每个节点的表空间,都是可以在此节点独立扩展的。对于跨多个节点的表空间,其上的表中的数据的分布依赖于设定的分区键,如果每个节点的表空间大小不一致,不会影响其数据的分布,但是,可能出现的情况是,如果一个节点的表空间满了,那么,符合此节点的分区规则的数据,将无法插入表中,而其他节点还能继续被插入。
具体测试过程如下:
一、对DPF数据库的表空间进行大小管理。
先创建一个表空间,DMS的:
create tablespace tbs3 in database partition group usergroup managed by database using (file '/home/db2inst1/tbs/tbs3.0' 1M) on dbpartitionnum (0) using (file '/home/db2inst1/tbs/tbs3.1' 1M) on dbpartitionnum (1) using (file '/home/db2inst1/tbs/tbs3.2' 1M) on dbpartitionnum (2) using (file '/home/db2inst1/tbs/tbs3.3' 1M) on dbpartitionnum (3)
DB20000I  The SQL command completed successfully.
查看现在的表空间容器的操作系统文件情况:
$ ls -lh
total 4.1M
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:23 tbs3.0
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:23 tbs3.1
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:23 tbs3.2
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:23 tbs3.3
尝试对表空间中一个节点的表空间容器进行扩容:
切换到节点3,进行表空间的扩容、缩减操作
$ export DB2NODE=3
$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
$ db2 connect to PAONLDB
 
   Database Connection Information
 
 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = PAONLDB
首先是使用all命令进行extend
$ db2 "alter tablespace tbs3 extend (all 1M)"
DB20000I  The SQL command completed successfully.
$ ls -lh
total 8.1M
-rw------- 1 db2inst1 db2iadm1 2.0M Jun 17 10:26 tbs3.0
-rw------- 1 db2inst1 db2iadm1 2.0M Jun 17 10:26 tbs3.1
-rw------- 1 db2inst1 db2iadm1 2.0M Jun 17 10:26 tbs3.2
-rw------- 1 db2inst1 db2iadm1 2.0M Jun 17 10:26 tbs3.3
然后是,使用resize命令进行表空间的缩减
$ db2 "alter tablespace tbs3 resize (all 1M)"
DB20000I  The SQL command completed successfully.
$ ls -lh
total 4.1M
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.0
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.1
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.2
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.3
可以看到,如果使用all命令,而不加具体的分区num的话,事实上,会对表空间中所有节点的容器进行增减操作;
$ db2 "alter tablespace tbs3 resize  (file '/home/db2inst1/tbs/tbs3.3' 4M) on dbpartitionnum (3)"
DB20000I  The SQL command completed successfully.
$ ls -lh                                                                               
total 7.1M
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.0
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.1
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.2
-rw------- 1 db2inst1 db2iadm1 4.0M Jun 17 10:45 tbs3.3
只增加了节点3的表空间的大小;
使用all参数:
$ db2 "alter tablespace tbs3 resize  ( all 6M) "                                       
DB20000I  The SQL command completed successfully.
$ ls -lh
total 25M
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.0
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.1
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.2
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.3
$ db2 "alter tablespace tbs3 resize  ( all 5M) on dbpartitionnum (3) "
DB20000I  The SQL command completed successfully.
$ ls -lh
total 24M
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.0
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.1
-rw------- 1 db2inst1 db2iadm1 6.0M Jun 17 10:46 tbs3.2
-rw------- 1 db2inst1 db2iadm1 5.0M Jun 17 10:47 tbs3.3
可以看到,如果不带具体的分区号,则会对所有节点的表空间容器进行调整;
单独扩展某个节点的表空间:
$ db2 "alter tablespace tbs3 extend  (file '/home/db2inst1/tbs/tbs3.3' 1M) "                                          <
DB20000I  The SQL command completed successfully.
$ ls -lh
total 5.1M
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.0
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.1
-rw------- 1 db2inst1 db2iadm1 1.0M Jun 17 10:27 tbs3.2
-rw------- 1 db2inst1 db2iadm1 2.0M Jun 17 10:43 tbs3.3
 
以上操作证明,正如开篇所说,跨多个节点(分布在分区组中)的表空间,其每个节点的表空间的大小,是彼此独立的,每个节点的同名的表空间,其大小可以不一致,对数据的分布没有影响(影响数据分布的只是分区键的选择);而且,在不同节点上,表空间的使用的大小也往往不同(因为业务数据的无规律性,即使分区键选择合理,也会导致分不同分区的数据分布不同),因此,在存储资源有限的情况下,可以根据每个节点的空间使用情况进行各自修改。
 
但这就引出了另一个问题,如果某个分区的表空间满了,又会怎么样?下面进行进一步调查
二、对DPF不同节点表空间数据分布和插入进行测试,模拟某个表空间满了的现象。
首先,调整表空间的大小,尽量小,便于问题的产生:
$ db2 "alter tablespace tbs3 resize (file '/home/db2inst1/tbs/tbs3.1' 3M) on dbpartitionnum (1)"                                 <
DB20000I  The SQL command completed successfully.
$ ls -lh
total 7.0M
-rw------- 1 db2inst1 db2iadm1 1000K Jun 17 10:57 tbs3.0
-rw------- 1 db2inst1 db2iadm1  3.0M Jun 17 11:01 tbs3.1
-rw------- 1 db2inst1 db2iadm1  2.0M Jun 17 11:00 tbs3.2
-rw------- 1 db2inst1 db2iadm1 1000K Jun 17 10:57 tbs3.3
$ home/db2inst1/tbs/tbs3.3' 501) on dbpartitionnum (3)"                                <
DB20000I  The SQL command completed successfully.
$ ls -lh
total 8.0M
-rw------- 1 db2inst1 db2iadm1 1000K Jun 17 10:57 tbs3.0
-rw------- 1 db2inst1 db2iadm1  3.0M Jun 17 11:01 tbs3.1
-rw------- 1 db2inst1 db2iadm1  2.0M Jun 17 11:00 tbs3.2
-rw------- 1 db2inst1 db2iadm1  2.0M Jun 17 11:02 tbs3.3
$ db2 "alter tablespace tbs3 resize  (file '/home/db2inst1/tbs/tbs3.3' 251) on dbpartitionnum (3)"
DB20000I  The SQL command completed successfully.
$ ls -lh
total 7.0M
-rw------- 1 db2inst1 db2iadm1 1000K Jun 17 10:57 tbs3.0
-rw------- 1 db2inst1 db2iadm1  3.0M Jun 17 11:01 tbs3.1
-rw------- 1 db2inst1 db2iadm1  2.0M Jun 17 11:00 tbs3.2
-rw------- 1 db2inst1 db2iadm1 1004K Jun 17 11:03 tbs3.3
然后,创建表,分布于所有的分区:
$ db2 "create table test (id int,name char(20)) distribute by hash(id) in tbs3"
DB20000I  The SQL command completed successfully.
然后,创建灌入数据的脚本,保证数据只是灌入分区3的表空间
$ more insert3.sh
db2 connect to paonldb
num=1
while [ $num -le 10000 ]
do
        db2 "insert into test values (3,'$num') "
        num=$(($num+1))
done
执行这个脚本,在另外的窗口中,观察表空间的变化,数据的分布情况,以及日志输出
观察表空间的变化(最后在某个时刻,某个节点的表空间分配完了):
$ db2 "select TBSP_ID,TBSP_NAME,DBPARTITIONNUM,TBSP_TOTAL_PAGES,TBSP_USABLE_PAGES,TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_PAGE_SIZE sysibmadm.TBSP_UTILIZATION where TBSP_NAME like '%TBS3%' "
 
TBSP_ID              TBSP_NAME                                                                                                                        DBPARTITIONNUM TBSP_TOTAL_PAGES     TBSP_USABLE_PAGES    TBSP_USED_PAGES      TBSP_FREE_PAGES      TBSP_PAGE_SIZE
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- -------------------- -------------------- -------------------- -------------------- --------------
                   7 TBS3                                                                                                                                          0                  250                  192                  160                   32           4096
                   7 TBS3                                                                                                                                          3                  251                  192                  192                    0           4096
                   7 TBS3                                                                                                                                          2                  512                  480                  160                  320           4096
                   7 TBS3                                                                                                                                          1                  768                  736                  160                  576           4096
 
  4 record(s) selected.
 
$
 
 
数据只是分布在节点3上
$   db2 "select distinct dbpartitionnum(id), count(*) from test group by dbpartitionnum (id)"
 
1           2         
----------- -----------
          0           1
          3        7073
 
  2 record(s) selected.
 
然后,在表空间分配完不久(这是因为,表空间的分配是按照extend blocks来进行的,所以,即使发现表空间的可用页为0了,但是在最后一个extent还没有用完时,还能继续插入),插入报错了:
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0289N  Unable to allocate new pages in table space "TBS3".  SQLSTATE=57011
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0289N  Unable to allocate new pages in table space "TBS3".  SQLSTATE=57011
这时候,发现,在节点3上已经没有空间分配给符合分区键规则的数据(需要放入节点3所在的表空间)了。
在别的节点进行数据插入(或者插入符合其他节点分区键规则的数据):
$ db2 "insert into test values (0,'test')"                                             
DB20000I  The SQL command completed successfully.
$ db2 "insert into test values (0,'test')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into test values (0,'test')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into test values (0,'test')"
DB20000I  The SQL command completed successfully.
$   db2 "select distinct dbpartitionnum(id), count(*) from test group by dbpartitionnum>
 
1           2         
----------- -----------
          0           6
          3        7073
 
  2 record(s) selected.
 
$ export DB2NODE=1
$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
 
$ db2 "insert into test values (1,'test')"                                             
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0289N  Unable to allocate new pages in table space "TBS3".  SQLSTATE=57011
$ db2 "insert into test values (2,'test')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0289N  Unable to allocate new pages in table space "TBS3".  SQLSTATE=57011
$ db2 "insert into test values (4,'test')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0289N  Unable to allocate new pages in table space "TBS3".  SQLSTATE=57011
$ db2 "insert into test values (5,'test')"
DB20000I  The SQL command completed successfully.
$   db2 "select distinct dbpartitionnum(id), count(*) from test group by dbpartitionnum>
 
1           2         
----------- -----------
          0           6
          2           1
          3        7073
 
  3 record(s) selected.
 
$
 
由此证明,dpf数据库中,数据的分布,取决于分区键的选择。同时,在某个分区的表空间满了之后,不会影响其他分区数据的继续写入,但是本分区的本表的写操作将会报错。
 
调查完毕。
阅读(5482) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~