Hi all,
We are logging approx. 3 million records every day into a history
table.
Last week we ran into the 64 GB limit in UDB 8 so we recreated the
table with 8 k pagesize to get some breathingroom before we hit the
128 GB limit.
We are considering partitioning and I just wanted to check with you
that our proposal is the best one:
Table structure is:
Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- -----
CREATED SYSIBM TIMESTAMP 10
0 No
OWNER_ID SYSIBM INTEGER 4
0 No
TRANS_ID SYSIBM BIGINT 8
0 No
EVENT_ID SYSIBM SMALLINT 2
0 No
OBJECT_ID SYSIBM INTEGER 4
0 No
CLASS_ID SYSIBM SMALLINT 2
0 No
PARAM_INDEX SYSIBM SMALLINT 2
0 No
VALUE_CHANGE SYSIBM BIGINT 8
0 No
VALUE_ACC SYSIBM BIGINT 8
0 No
Indexes are:
CREATED
OBJECT_ID,CREATED
OWNER_ID,CREATED
TRANS_ID
Our assumption is:
The rowsize is 48 bytes which means we will not benefit from a
pagesize larger than 8kB
We create with option PCTFREE 0 and APPEND ON in a dedicated SMS
tablespace
CREATED and TRANS_ID are increasing with every insert, but since many
hosts are writing into this table, they will not arrive perfectly
ordered which leads to the assumption that we will not benefit from
clustering.
We are thinking that we want to partition on "CREATED" and have one
month of data in each partition. This is currently about 100 million
rows / partition and the speed of entry increases with about 4% every
month (so next month will be 104 new million rows) - that will
currently be about 4,8 GB data per current partition.
For business reasons we want to keep about two years online, and after
what we will purge monthly.
Inserts and searches are performed 24/7, where searches are mostly
performed on recent data but sometimes older data is searched also.
So here are my questions:
a) Do you have better suggestions for creating this table?
b) Should we alter the XXX,CREATED indexes to CREATED,XXX ?
c) Is there a siginificant performance gain in altering TIMESTAMP to
something with less precision?
and finally, I would really appreciate recommendations on how to
configure the new diskarray that soon arrive to get best performance
with the table mentioned above.
(HP MSA30 , dualchannel U320 with 14 drives)
Kindly regards,
/Mats