Re: Segregation of indexes in separate dbspaces still valid?
Fair but hard question :) In fact it depends on the database size and the kind of data and usage... But I assume you want numbers, so I'll risk to say that for some tens to a few hundreds of GB instances I don't discuss until 8GB per chunk. If anybody wants to use more I usually ask why. I recall an instance that is over 2TB where data is kept for 7-8 months.... and data is permanently added to the "current" month and then it stays there for another 7 months... Queries are more or less equally spread across all months... I mention this as an example where I can be easily convinced beyond that limit... But honestly there is no real "scientific" base for this. Whenever possibly I like to split data logically.... Regards. On Thu, Feb 2, 2012 at 2:04 PM, Link, David A <DALink@west.com> wrote: > Out of curiosity, Fernando, what would you consider a very big chunk?**** > > ** ** > > *From:* informix-list-bounces@iiug.org [mailto: > informix-list-bounces@iiug.org] *On Behalf Of *Fernando Nunes > *Sent:* Wednesday, February 01, 2012 5:08 PM > *To:* IIUG Informix List > *Subject:* Re: Segregation of indexes in separate dbspaces still valid?*** > * > > ** ** > > I think so, for a variety of reasons: > > 1- It's a good idea to split the "data" (data and indexes) in several > dbspaces/chunks (for reasons explained ahead). Sometimes splitting data is > difficult so, that can be a pretext to split accesses. > 2- Although generally you don't control what happens in the SAN, informix > itself has "conscience" of the dbspaces/chunks. For example during > checkpoints the pages are ordered by chunk in order to optimize writes. > Putting everything together is not the best choice (that's why I also > dislike very big chunks in general). Technically you could put most of the > existing instances in one chunk > 3- You can have different page sizes/ buffer pools > 4- It may be good for fault tolerance. If by any chance you loose an index > dbspace you may still access your data... > 5- Fragmentation (don't like the name, really prefer "partitioning") > allows you to take advantage of fragment elimination and PDQ (if the > version/edition you're using supports it). In fact in today we should be > able to use PDQ scans even with just one fragment (precisely because of > SANs) > > Regards.**** > > On Wed, Feb 1, 2012 at 6:42 PM, red_valsen <red_valsen@yahoo.com> wrote:** > ** > > As a budding Informix DBA in the last century, I was told that placing > indexes in a dbspace dedicated only to indexes and apart from the > table's data enhanced performance. Is this still true? Even with the > advent of SAN technology? and using Informix fragmentation (data > partitioning)? regardless of the size of the fragments? or size of the > index/table? or size of the dbspace and its chunks? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list**** > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...**** > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...