Performance advantage: fragment across dbspaces vs. in single space
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@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 <red_valsen@yahoo.com> 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-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
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...@gmail.com> 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 <red_val...@yahoo.com> 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...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list
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@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 1:30 PM, red_valsen <red_valsen@yahoo.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...@gmail.com> 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 <red_val...@yahoo.com> > 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...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >