Re: reorgainising dbspaces

David Williams wrote: > > 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. I DID say 'even if they are table fragments. The point being made, was that very large tables will span chunks. > Also more fragments = better fragment elimination which is faster. It depends on your access strategy, and of course your fragmentation strategy. > >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! My point was aimed at people who only have one spindle available for the database server. They often assume that fragmentation is not going to help them. This is not always the case. > >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! Of course, but works better with more spindles. 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 |/ ////////| +----------------------+-----------------------------------+-----------+