Large table redesign

[Mats Kling]

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

[Mark A]

"Mats Kling" <ma********@gmail.com> wrote in message
news:ff**************************@posting.google.c om...

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

You would have to explain a lot more about this application and whether you
are trying to optimize insert performance or query performance. A load would
be faster than an insert, if you can do that. The indexes will slow
load/inserts down considerably so make sure they are needed and that they
are configured properly for percent free (and you reorg the indexes if
possible) unless the index is always a column with an increasing value.

If you want to use SMS with large inserts, make sure you run db2empfa to
enable multipage file allocation (allocate more than one new page at a
time). See the Command Reference.

DB2 LUW partitioning is a random hash partitioning on the key you specify.
You cannot put a particular month in a particular partition. You might want
to look at creating a separate table for each month and use UNION ALL views
for retrieval. See this link:
http://www-106.ibm.com/developerwork...202zuzarte.pdf

I don't think the timestamp column is a big issue.

I don't understand you question b) about the indexes.

Are all the disks in one single array? RAID 5? Need more details about this.