一例日誌空間滿帶來的insert效能的優化

一例日誌空間滿帶來的insert效能的優化：
症狀：
大型資料倉儲系統，至少有20個終端連線上去做查詢等運維處理。手工調起一個指令碼，
該指令碼中使用insert...select的方式將一個較大資料量的表的資料抽取部分欄位到另一張表中,後系統反應變慢.

--應用報錯如下:
Step 2: CMD=[
insert into kf2.MM_test_cdr select 201008, 'AA', A.USR_MOB_NBR,
A.B_NBR, A.LOCAL_CD, A.CELL_CD, A.B_BRND_CD, A.CALL_DT,
A.CALL_CNT, A.CALL_DUR, A.DIR_TYP_CD from ods.to_cdr_AA201008 A with ur
]
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command. During SQL
processing it returned: SQL0964C The transaction log for the
database is full. SQLSTATE=57011

--查系統診斷日誌db2diag.log
2003-01-16-02.53.54.935308 Instance:db2inst1 Node:016
PID:144252(db2agntp (SAMPLE) 16) Appid:*.*
data_protection sqlpgrsp Probe:50 Database:SAMPLE
Log Full -- active log held by appl. handle 787273
End this application by COMMIT, ROLLBACK or FORCE APPLICATION.
:
:

--發現問題
db2 list applications;
--
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
ETL db2bp 1245 *N0.db2root.101203022137 BITEST 4
ETL db2jccDefaultQ 880 GAC8196B.GB4C.101203012535 BITEST 4
ETL db2bp 1243 *N0.db2root.101203012411 BITEST 1

--解決方法
--使用force停掉應用(不建議用db2stop force停掉資料庫,也不要用kill來直接殺程式)
db2 force application(880); --此步驟為非同步執行,可以再次用db2 list applications檢視是否停掉了應用.

--事務所需日誌空間
插入記錄 日誌記錄

--獲取日誌空間配置資訊：
--db2 get db cfg for bidw |grep log
[195/app/etl/_xx]db2 get db cfg for bitest |grep -i 'log file'
Log file size (4KB) (LOGFILSIZ) = 25000
Number of primary log files (LOGPRIMARY) = 40
Number of secondary log files (LOGSECOND) = 2
--計算日誌空間大小：
(LOGPRIMARY+LOGSECOND)*(LOGFILSIZ)*(page sizes)=(40+2)*25000*4K ~4.2G

--如果需要修改日誌空間配置引數
db2 update db cfg for using LOGPRIMARY 50
db2 update db cfg for using LOGSECOND 20
db2 update db cfg for using LOGFILSIZ 10240 --單位為頁大小(預設4K)

--對於事務日誌空間不足的情況變通的處理方法:
1.使用load cursor的方式不記錄日誌
declare my_cursor cursor for select ...;
db2 load from my_cursor of cursor insert into my_target_table nonreoverable;
2.使用匯出在倒入的方式
export to xx of del modified by coldel, select * from my_source_table;
load from xx of del modified by coldel, insert into my_target_table;
3.建表或者改表啟用not logged initially 特性
:create table my_target_table (col1 type,...) not logged initially ;
eg:create table my_target_table (id int ,col1 varchar(200)) not logged initially ;
:alter table my_target_tale activate not logged initially ;

--事務所需的事務日誌空間估算
##Insert Record|| Rollback Delete Record|| Rollback Update Record Log Record Structure
Description||Type||Offset (Bytes)
---------------------------------
Log header||DMSLogRecordHeader||0(6)
Internal||Internal||6(2)
Record Length||unsigned short||8(2)
Free space||unsigned short||10(2)
RID||char[]||12(6)
Record offset||unsigned short||18(2)
Record header and data||variable||20(variable)
Total Length: 20 bytes plus record length

Following are details about the record header and data:
Record header
? 4 bytes
? Record type (unsigned char, 1 byte).
? Reserved (char, 1 byte)
? Record length (unsigned short, 2 bytes)
Record
? Variable length
? Record type (unsigned char, 1 byte).
? Reserved (char, 1 byte)
? The rest of the record is dependent upon the record type and the table descriptor record defined for the table.
? The following fields apply to user data records with record type having the 1 bit set:
o Fixed length (unsigned short, 2 bytes). This is the length of the fixed length section of the data row.
o Formatted record (all of the fixed length columns, followed by the variable length columns).
? The following fields apply to user data records with record type having the 2 bit set:
o Number of columns (unsigned short, 2 bytes). This is the number of columns in the data portion of the data row. See Formatted user data record for table with VALUE COMPRESSION.
Note: the offset array will contain 1 + the number of columns.
o Formatted record (offset array, followed by the data columns).
A user record is specified completely by the following characteristics:
1. Outer record type is 0, or
2. Outer record type is 0x10, or
3. Outer record type has the 0x04 bit set and
1. Inner record type has the 0x01 bit set, or
2. Inner record type has the 0x02 bit set.
Note: Row compression and data capture are not compatible.


??提兩個問題
1.使用db2stop直接停資料庫,和使用kill殺程式的缺點?
2.對於insert日誌條目record部分的計算?
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23937368/viewspace-1042786/，如需轉載，請註明出處，否則將追究法律責任。