"Mats Kling" <mats.kling@gmail.com> wrote in message
news:ffaa2ab7.0501180210.4b855f8e@posting.google.c om...[color=blue]
> 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[/color]
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.
