Re: reorgainising dbspaces

I like Greg Dewinter's comments best and would basically go with his recommendatations. I would add some note, however (when have I been able to keep silent?): Bridget Reitsma wrote: > > Hi > > This weekend I intend to reorg some of my dbspaces, at the moment I have 3 > databases all in the same dbspace with the indexes in a separate dbspace. So I > have decided to get smart and move them around. We are using 730UC5 with Raid > 1+0. > > Here's the plan > level 0 archive > export all the databases Determine #extents and number of pages for all tables and adjust the EXTENT SIZE and NEXT SIZE clauses (or add them) in the schema file that dbexport creates (or replace it completely with one created by myschema -s -a which calculates extents for you [note that myschema ALWAYS outputs IN dbspace clauses so you will have to edit the file anyway]). Of course drop the databases first, then: > drop dbspaces > level 0 archive You can lose this archive, ignore the dire warnings onspaces outputs. The worst case is that the machine crashes before the next archive and you restore the first one above and have to drop the databases and dbspaces again, a few minutes work. > create dbspaces > import databases > update stat's Get that archive in now, test later. > test > level 0 archive > > I have just a few quick questions: > First when reorging the dbspace is it best to have one dbspace per 2 gig chunk > or one dbspace per database or option c something else? I would put each database into it's own dbspace, even if that uses only a small part of a partition, just allocate other chunks from the same partition for other dbspaces. Keep active databases on independent disks especially databases that are active at the same parts of the day. If a database has several very active tables you may want to isolate them on different disks in which case the database will be living in two dbspaces one for most tables and another for a few outliers. > What is the dbimport command to import tables into separate dbspaces (I > couldn't find it in the books but I assuming it something like dbimport > 'database name' -d 'dbspace name' -t 'table name' - can someone point me in > the direction of which book it is in (I have all the informix manuals and it > has managed to elude me). Yes, you use the -d <dbspace> option to dbimport, the only way to redirect a single table to a dbspace other than where the system tables will be created, by the -d option, is to edit the schema file and add or modify the "IN <dbspace>" clause. > Also how does the plan look - have I missed anything? > > Thanks for all you help - I just need to check it with the gurus before I do > it to our production databases - didn't get a chance to do it to our test > databases. Have fun. Art S. Kagel

In article <380F4CBF.26597D46@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >I like Greg Dewinter's comments best and would basically go with his >recommendatations. I would add some note, however (when have I been able >to keep silent?): > >Bridget Reitsma wrote: >> >> Hi >> >> This weekend I intend to reorg some of my dbspaces, at the moment I have 3 >> databases all in the same dbspace with the indexes in a separate dbspace. So I >> have decided to get smart and move them around. We are using 730UC5 with Raid >> 1+0. >> >> Here's the plan >> level 0 archive >> export all the databases > >Determine #extents and number of pages for all tables and adjust the >EXTENT SIZE and NEXT SIZE clauses (or add them) in the schema file that >dbexport creates (or replace it completely with one created by myschema -s -a >which calculates extents for you [note that myschema ALWAYS outputs >IN dbspace clauses so you will have to edit the file anyway]). > >Of course drop the databases first, then: > >> drop dbspaces >> level 0 archive > >You can lose this archive, ignore the dire warnings onspaces outputs. The >worst case is that the machine crashes before the next archive and you >restore the first one above and have to drop the databases and dbspaces >again, a few minutes work. > >> create dbspaces level 0 archive to filesystem :- Touch an empty file chmod 666 the file Set TAPEDEV to the file Do a level 0 archive. Since everthing is dbexport'ed if things fail after this restore the level 0 archive with ontape -r to quickly recreate an empty online instance will all your chunks. Saves working out what the onspaces command are to recreate the chunks (saves having to remember devices/offsets/sizes). This should be small since the dbspaces are empty. (At most 53 pages per dbspace i.e. 53*2 = 106K per dbspace, a few Mb). >> import databases >> update stat's > >Get that archive in now, test later. Yep, testers may delete/bulk update things by mistake! > >> test >> level 0 archive >> >> I have just a few quick questions: >> First when reorging the dbspace is it best to have one dbspace per 2 gig chunk >> or one dbspace per database or option c something else? > >I would put each database into it's own dbspace, even if that uses only a Agreed, I go with :- 1 dbspace = 1 chunk = 1 disk i.e. chunks and dbspaces are the same thing. So you can control which disk tables/indexes are on by moving them into different dbspaces. Also keep 1 temp dbspace per disk which spreads temp tables across the disks in round robin fashion. Remember temp tables are heavily read/write intensive and it's better to balance the load across the disks. >small part of a partition, just allocate other chunks from the same >partition for other dbspaces. Keep active databases on independent disks >especially databases that are active at the same parts of the day. If a >database has several very active tables you may want to isolate them on >different disks in which case the database will be living in two dbspaces >one for most tables and another for a few outliers. > >> What is the dbimport command to import tables into separate dbspaces (I >> couldn't find it in the books but I assuming it something like dbimport >> 'database name' -d 'dbspace name' -t 'table name' - can someone point me in >> the direction of which book it is in (I have all the informix manuals and it >> has managed to elude me). > >Yes, you use the -d <dbspace> option to dbimport, the only way to redirect >a single table to a dbspace other than where the system tables will be >created, by the -d option, is to edit the schema file and add or modify >the "IN <dbspace>" clause. > >> Also how does the plan look - have I missed anything? >> >> Thanks for all you help - I just need to check it with the gurus before I do >> it to our production databases - didn't get a chance to do it to our test >> databases. > >Have fun. > >Art S. Kagel -- David Williams

David Williams wrote: > > In article <380F4CBF.26597D46@bloomberg.net>, Art S. Kagel > <kagel@bloomberg.net> writes [SNIP] > >I would put each database into it's own dbspace, even if that uses only a > Agreed, I go with :- > > 1 dbspace = 1 chunk = 1 disk i.e. chunks and dbspaces are the same > thing. So you can control which disk tables/indexes are on by > moving them into different dbspaces. This formulation only works if your tables and databases are small. I've got 20GB tables! Even with 5 fragments each dbspace has to be 2 - 2GB chunks and I don't always want to fragment like that! So a small disclaimer, like: "If your database/table can fit in 2GB or less then 1 dbspace = 1 chunk = ..." would be instructive. > Also keep 1 temp dbspace per disk which spreads temp tables across > the disks in round robin fashion. Remember temp tables are heavily > read/write intensive and it's better to balance the load across > the disks. [MORE SNIP] Art S. Kagel

In article <38106A77.490B918@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >David Williams wrote: >> >> In article <380F4CBF.26597D46@bloomberg.net>, Art S. Kagel >> <kagel@bloomberg.net> writes >[SNIP] >> >I would put each database into it's own dbspace, even if that uses only a >> Agreed, I go with :- >> >> 1 dbspace = 1 chunk = 1 disk i.e. chunks and dbspaces are the same >> thing. So you can control which disk tables/indexes are on by >> moving them into different dbspaces. > >This formulation only works if your tables and databases are small. I've >got 20GB tables! Even with 5 fragments each dbspace has to be 2 - 2GB >chunks and I don't always want to fragment like that! So a small >disclaimer, like: > "If your database/table can fit in 2GB or less then 1 dbspace = 1 > chunk = ..." Surely fragment across 2Gb dbspaces. More dbspaces = mre scan threads. Also scanning two fragment in parallel on the same disk will result in a lot of time spending seeking back and forth between 2Gb chunks. Average seek = middle of chunk 1 to middle of chunk 2 on the disk = seeking across 1Gb of data!! So quite large seeks then, even with SCSI I/O queuing and elevator seeking on the drive?!! > >would be instructive. > >> Also keep 1 temp dbspace per disk which spreads temp tables across >> the disks in round robin fashion. Remember temp tables are heavily >> read/write intensive and it's better to balance the load across >> the disks. >[MORE SNIP] > >Art S. Kagel -- David Williams