Segregation of indexes in separate dbspaces still valid?

[red_valsen]

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?

[Art Kagel]

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Fernando Nunes]

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.

_______________________________________________
Informix-list mailing list
Inform...@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...

[Link, David A]

Out of curiosity, Fernando, what would you consider a very big chunk?

 

From: informix-l...@iiug.org [mailto:informix-l...@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_v...@yahoo.com> wrote:

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Fernando Nunes]

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 <DAL...@west.com> wrote:

Out of curiosity, Fernando, what would you consider a very big chunk?

 

From: informix-l...@iiug.org [mailto:informix-l...@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_v...@yahoo.com> wrote:

_______________________________________________
Informix-list mailing list
Inform...@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...