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.
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_val...@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-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
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_val...@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-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Out of curiosity, Fernando, what would you consider a very big chunk?
From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@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_val...@yahoo.com<mailto:red_val...@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-l...@iiug.org<mailto:Informix-l...@iiug.org>
http://www.iiug.org/mailman/listinfo/informix-list
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-list-boun...@iiug.org [mailto:
> informix-list-boun...@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_val...@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-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list****
