Performance advantage: fragment across dbspaces vs. in sin..

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 wrote:
> 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 (a...@iiug.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 wrote:
> > 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.
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l....RemoveThis@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list