I have an opportunity to change the fragmentation strategy on some heavily used tables. Most records on these tables are only accessed on the day they are written. They are written, read, and updated only once or twice. Also keep in mind that we are on a SAN, so I have no control over where the data is physically stored.

So my question is... is it better to have a different partition for each day of the month ( only one partition at a time is being heavily accessed) or to use round robin to theoretically 'spread out' the storage access across more of the disk.

Thanks in advance for your opinions

Laurie

It depends.

If your application is going after singleton rows, then there is no real advantage to spreading the load. If you are doing DSS (reading large chunks of data), then you want to take as much advantage of parallelism as possible - in other words spread the load over as many "spindles" as possible. I put that in quotes, because you don't have really good control over where the data is actually landing. A rule of thumb (for DSS) is 2 dbspaces per VCPU.

j.

On Aug 20, 2012, at 5:24 PM, Laurie Gustin wrote:

If transaction rate is high, then for records with an access pattern like
this I would go for ROUND ROBIN.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Mon, Aug 20, 2012 at 5:24 PM, Laurie Gustin <lgustin (AT) utah (DOT) gov> wrote:

Laurie:


If you do consider round-robin, this would only be for the data. Placethe
index in a single dbspace, or with their own
fragmentation strategy.

Additionally, you might consider interval fragmentation. If you everdid
want to purge/archive old data this would
make it very easy and fast. The data would generally have a very high
degree of clusterness and all the
hot data would fit into the bufferpool. If you ever do report over a
period of time the data elimination is amazing,
additionally the ability for interval fragmentation to autmoatically create
fragments really helps.


John F. Miller III
STSM, Embedability Architect
miller3 (AT) us (DOT) ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)


informix-list-bounces (AT) iiug (DOT) org wrote on 08/20/2012 02:33:41 PM: