Is there any advantage to fragmenting across multiple dbspaces instead
of simply fragmenting in the same space? Aside from performance, are
there any other reasons to fragment across dbspaces?

Using IDS 11.50.FC4 on HPUX 11.23.

Yes, at least for any insert/update/delete heavy tables. Dirty pages are
flushed at checkpoint time by dbspace with one CLEANER thread per dbspace
flushing in parallel. So, the more dbspaces involved the more cleaners and
so the faster dirty pages get out to disk. At LRU flush time, cleaners are
assigned one per LRU queue so it is less of an issue, however, in
configuring 11.50+, with non-blocking checkpoints, we have gone back to
preferring the checkpoint time CHUNK writes over idle time LRU writes, so
checkpoint performance, while it will not "normally" block transactions, the
faster we can get the dirty pages flushed the better and the less chance
that a transaction will have to wait for all or part of the flush process
and the less time it will be blocked if it does have to wait.

On top of that, if the dbspaces have been configured on different disk
structures (can you guess that I don't like SANs?) you can get more total
peak throughput out of the table's fragments than if they are all in the
same dbspace on a single structure. This will affect reads and writes,
especially if Parallel Data Query (PDQ) is enabled for some or all queries.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)
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 Tue, Jan 11, 2011 at 11:50 AM, red_valsen <red_valsen (AT) yahoo (DOT) com> wrote:

Art,

The application is not OLTP-oriented, but uses a data warehouse
structured database. Aside from periodic ETL operations, the database
supports read-only operations. The underlying bulk storage is a SAN,
too. Further, the project has segregated their data functionally by
dbspace -- or rather, dbspaces, so whereas a single dbspace of, say,
12-GB would have satisfied a space requirement, three 12-GB dbspaces
were allocated to support by-dbspace fragmentation, yielding vast
tracts of empty space. By consolidating fragments, diskspace can be
used more efficiently, and the performance gains of fragmentation
would be retained.
Unfortunately, because of the poorly allocated disk, there may not be
enough space to test this! As an aside, we've seen order-of-magnitude
improvements with other data warehouse-based projects that went from
no fragmentation to fragmentation just in the same dbspace.

On Jan 11, 12:16*pm, Art Kagel <art.ka... (AT) gmail (DOT) com> wrote:

No question. For a DW database the only issue is will you get any
performance gain from using multiple dbspaces due to getting more spindles,
channels, and cache involved. In this case, if the dbspaces are all on the
same SAN, then there's little reason not to consolidate the fragments into
fewer dbspaces. Your ETL load times may suffer a bit, but, again, given the
SAN setup, not so much that it will hurt.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)
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 Tue, Jan 11, 2011 at 1:30 PM, red_valsen <red_valsen (AT) yahoo (DOT) com> wrote: