?
数据库表空间的扩容是在数据库管理过程中常见的问题。对于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数据库中,数据的分布,取决于分区键的选择。同时,在某个分区的表空间满了之后,不会影响其他分区数据的继续写入,但是本分区的本表的写操作将会报错。
调查完毕。