DB2 SMS tablespace 容器大小不等测试
程序员文章站
2022-07-09 17:13:10
...
看到官方文档有这句:
When all space in a single container in an SMS table space is allocated to tables, the table space is considered full, even if space remains in other containers。
The SMS table space is full as soon as any one of its containers is full. Thus, it is important to have the same amount of space available to each container.
就是说,无论SMS有多少个容器,只有其中一个满了,就这个表空间都满了
测试:
引用
When all space in a single container in an SMS table space is allocated to tables, the table space is considered full, even if space remains in other containers。
The SMS table space is full as soon as any one of its containers is full. Thus, it is important to have the same amount of space available to each container.
就是说,无论SMS有多少个容器,只有其中一个满了,就这个表空间都满了
测试:
--建立两个大小不一的文件系统 [root@localhost mnt]# df -h Filesystem Size Used Avail Use% Mounted on /tmp/test1_s.img 4.9M 22K 4.6M 1% /mnt/test1_s /tmp/test1_big.img 9.7M 1.1M 8.1M 12% /mnt/test1_big --建立SMS表空间 db2 => create tablespace SMSFULLTEST managed by system USING ('/mnt/test1_s/','/mnt/test1_big/') DB20000I The SQL command completed successfully. --表空间信息 [db2inst2@localhost db2dump]$ db2pd -alldbs -tablespace 4 Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:46:48 -- Date 04/28/2011 04:20:45 Tablespace 4 Configuration: Address Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name 0x9D140B50 SMS Regular 4096 32 Yes 64 1 1 Off 2 0 31 SMSFULLTEST Tablespace 4 Statistics: Address TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped 0x9D140B50 2308 2308 2308 0 0 0 0 0x00000000 1303934864 0 No Tablespace 4 Autoresize Statistics: Address AS AR InitSize IncSize IIP MaxSize LastResize LRF 0x9D140B50 No No 0 0 No 0 None No Containers: Address ContainNum Type TotalPgs UseablePgs PathID StripeSet Container 0x9D0EE960 0 Path 1155 1155 - 0 /mnt/test1_s 0x9D0EEB60 1 Path 1153 1153 - 0 /mnt/test1_big --向里面塞入数据 db2 => create table SMSFULL1 as (select * from syscat.tables) definition only in SMSFULLTEST DB20000I The SQL command completed successfully. db2 => insert into SMSFULL1 select * from syscat.tables DB20000I The SQL command completed successfully. db2 => insert into SMSFULL1 select * from SMSFULL1 DB20000I The SQL command completed successfully. db2 => insert into SMSFULL1 select * from SMSFULL1 DB20000I The SQL command completed successfully. --省略 db2 => insert into SMSFULL1 select * from SMSFULL1 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0968C The file system is full. SQLSTATE=57011 --表空间很快满了 --文件系统情况,看到其中一个100% [root@localhost test1_big]# df -h Filesystem Size Used Avail Use% Mounted on /tmp/test1_s.img 4.9M 4.7M 0 100% /mnt/test1_s /tmp/test1_big.img 9.7M 5.7M 3.6M 62% /mnt/test1_big --再看看两个目录下的文件情况 [root@localhost test1_big]# du -sh * 4.6M SQL00002.DAT 1.0K SQLTAG.NAM [root@localhost test1_big]# cd - /mnt/test1_s [root@localhost test1_s]# du -sh * 4.6M SQL00002.DAT 8.0K SQL00002.LB 8.0K SQL00002.LBA 1.0K SQLTAG.NAM --可以看到SMS是平均的吧数据分配到每个容器当中 --附上diag log信息 2011-04-28-04.10.14.243363+480 E1389098G1271 LEVEL: Error (OS) PID : 7398 TID : 1983900560 PROC : db2sysc 0 INSTANCE: db2inst2 NODE : 000 DB : SAMPLE APPHDL : 0-7620 APPID: *LOCAL.db2inst2.110427193357 AUTHID : DB2INST2 EDUID : 1453 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, oper system services, sqloseekwrite64, probe:40 MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full." DIA8312C Disk was full. CALLED : OS, -, pwrite OSERR: ENOSPC (28) DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes File Handle = 26 File System Block Size = 1024 bytes File System Type = ext3 File Handle Flags : Require Sector Align = No DIO/CIO Mode = Yes Raw Block Device = No Reserved Handle = No Flush On Close = No Thread-Level Lock = No Write-through Mode = Yes File Not Tracked = Yes DATA #2 : unsigned integer, 4 bytes 131072 DATA #3 : signed integer, 8 bytes 4718592 DATA #4 : signed integer, 4 bytes -1 DATA #5 : String, 105 bytes Search for ossError*Analysis probe point after this log entry for further self-diagnosis of this problem. 2011-04-28-04.10.14.371039+480 I1390370G2589 LEVEL: Error (OS) PID : 7398 TID : 1983900560 PROC : db2sysc 0 INSTANCE: db2inst2 NODE : 000 DB : SAMPLE APPHDL : 0-7620 APPID: *LOCAL.db2inst2.110427193357 AUTHID : DB2INST2 EDUID : 1453 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100 CALLED : OS, -, pwrite OSERR: ENOSPC (28) DATA #1 : String, 146 bytes A total of 5 analysis will be performed : - User info - ulimit info - Target file info - I/O attempt - File system Target file handle = 26 DATA #2 : String, 184 bytes Real user ID of current process = 503 Effective user ID of current process = 503 Real group ID of current process = 500 Effective group ID of current process = 500 DATA #3 : String, 370 bytes Current process limits (unit in bytes except for nofiles) : mem (S/H) = unlimited / unlimited core (S/H) = unlimited / unlimited cpu (S/H) = unlimited / unlimited data (S/H) = unlimited / unlimited fsize (S/H) = unlimited / unlimited nofiles (S/H) = 65534 / 65534 stack (S/H) = 10485760 / unlimited rss (S/H) = unlimited / unlimited DATA #4 : String, 260 bytes Target File Information : Size = 4718592 Link = No Reference path = N/A Type = 0x8000 Permissions = rw------- UID = 503 GID = 500 Last modified time = 1303935014 DATA #5 : String, 33 bytes I/O attempt not implemented yet. DATA #6 : String, 372 bytes File System Information of the target file : Block size = 1024 bytes Total size = 5073920 bytes Free size = 0 bytes Total # of inodes = 1280 FS name = /dev/loop0 Mount point = /mnt/test1_s FSID = 1792 FS type name = ext2 DIO/CIO mount opt = None Device type = N/A FS type = 0xe CALLSTCK: [0] 0xB322C481 /home/db2inst2/sqllib/lib32/libdb2osse.so.1 + 0xB8481 [1] 0xB322C3B5 ossLogSysRC + 0x97 [2] 0xB321E6D9 /home/db2inst2/sqllib/lib32/libdb2osse.so.1 + 0xAA6D9 [3] 0xB321D1D8 ossErrorAnalysis + 0x28 [4] 0xB5D48928 sqloSystemErrorHandler + 0x61C [5] 0xB4AB5E29 sqloseekwrite64 + 0x46B [6] 0xB4AB578C sqloWriteBlocks + 0xBC [7] 0xB466AB72 _Z15sqlbWriteBlocksP16SqlbOpenFileInfoPvxjjPjP12SQLB_GLOBALS + 0x40 [8] 0xB51149C0 _Z18sqlbSMSDirectWriteP20SQLB_DIRECT_WRITE_CB + 0x284 [9] 0xB515DB15 _Z15sqlbDirectWriteP20SQLB_DIRECT_WRITE_CB + 0x109 2011-04-28-04.10.14.376525+480 E1392960G804 LEVEL: Error PID : 7398 TID : 1983900560 PROC : db2sysc 0 INSTANCE: db2inst2 NODE : 000 DB : SAMPLE APPHDL : 0-7620 APPID: *LOCAL.db2inst2.110427193357 AUTHID : DB2INST2 EDUID : 1453 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbSMSDirectWrite, probe:99 MESSAGE : ADM6017E The table space "SMSFULLTEST" (ID "4") is full. Detected on container "/mnt/test1_s" (ID "0"). The underlying file system is full or the maximum allowed space usage for the file system has been reached. It is also possible that there are user limits in place with respect to maximum file size and these limits have been reached. 2011-04-28-04.10.14.377416+480 I1393765G505 LEVEL: Error PID : 7398 TID : 1983900560 PROC : db2sysc 0 INSTANCE: db2inst2 NODE : 000 DB : SAMPLE APPHDL : 0-7620 APPID: *LOCAL.db2inst2.110427193357 AUTHID : DB2INST2 EDUID : 1453 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbSMSDirectWrite, probe:825 MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full." DIA8312C Disk was full.
上一篇: ifconfig
下一篇: activiti5.22.0资料整理