Re: reorgainising dbspaces
David Williams wrote: > > 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?!! I agree with most of your comments. However, I do not agree with 1 dbspace per disk, or indeed 1 dbspace per chunk. As Art said, size dictates how may chunks a dbspace requires. If you need to store two 3 Gb tables in a dbspace, even if they are table fragments, then you are compelled to add more than one chunk. Just make sure that those chunks all reside on the same disk if possible, which I think is what David was alluding to. Also, don't underestimate the throughput of a disk. A modern disk will handle multiple dbspaces and indeed give greater throughput that with only one dbspace. I have seen query times drop dramatically (with PDQ) when a table was fragmented across dbspaces on the same disk! The bottom line with any database is, IO is the biggest bottleneck. The best way to alleviate that is lots of spindles, as many as you can afford. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+
In article <7vajn9$kh0$1@news.xmission.com>, Mark D. Stock <mdstock@myda s.freeserve.co.uk> writes >> 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?!! > >I agree with most of your comments. However, I do not agree with 1 >dbspace per disk, or indeed 1 dbspace per chunk. As Art said, size >dictates how may chunks a dbspace requires. > >If you need to store two 3 Gb tables in a dbspace, even if they are >table fragments, then you are compelled to add more than one chunk. Just >make sure that those chunks all reside on the same disk if possible, >which I think is what David was alluding to. > ?? Why store two 3Gb tables in a dbspace? Surely fragment them across several 2Gb dbspaces, 1 dbspace per disk. You want to have them across mutliple spindles so that the parallel scan threads do not hit the same disk in parallel. More then one scan thread accessing a disk means it will seek back and forth between the two areas of the disk being scanning. More seeks = much slower. Also more fragments = better fragment elimination which is faster. >Also, don't underestimate the throughput of a disk. A modern disk will >handle multiple dbspaces and indeed give greater throughput that with >only one dbspace. I have seen query times drop dramatically (with PDQ) >when a table was fragmented across dbspaces on the same disk! Try it with the same table fragmented across dbspaces on different disks. That should be even faster! > >The bottom line with any database is, IO is the biggest bottleneck. The >best way to alleviate that is lots of spindles, as many as you can >afford. > True. More fragments also helps! >Cheers, -- David Williams