Segregation of indexes in separate dbspaces still valid?

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?

First let me start out by saying that along with BIG disks, SAN technology was the WORST thing that ever happened to databases - ALL databases - in terms of performance. I can build a JBOD based database system that will outperform a SAN based one every time! Now, isolating indexes from data is a good thing, yes. Always has been because the disk access patterns of indexes and data are so different. By isolation I mean separate spindles or physical array structures. Just placing the indexes on a different LUN on a SAN that is physically built from the same set of spindles as you data dbspaces will not do it for you. Informix versions since 10.00 have also had the ability to create dbspaces with different page sizes. Indexes, it happens, appear to shine on wide pages. Page sizes of 8K, 16K, or even 32K will improve the performance of many indexes on significantly sized tables and especially for longer index keys. This also gets you an additional bonus. The index pages will be in a separate cache from the data so that index pages and data pages are no longer fighting for cache space. 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 Wed, Feb 1, 2012 at 1: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 >

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...