optimum extent size

[Lotusadm]

Hi,

I have a progress V6.3 database (about 5.5Gig) running on a Sun sparc 10 under Solaris 2.3. I have 58 total extents in this database they are all around 100 Meg each. The DB admin prior to me have told me that the optimum size for an extent is around 100 Meg that is why they are sized this way. Now I am upgrading to an Ultra sparc 2 with 8 18 gig Ultra wide SCSI disks. and I am wondering about doing a dump and reload to reconfigure the layout of the extents.
The disks will be using the UFS filsystem which has a logical block size of ( if memory serves me right ) 8k. Now to have the optimum usage of the disks we have discussed partioning the disks using only about 10 % of each disk for the db extents thereby reducing the distance the heads would have to travel for any data on any particular disk which would hopefully give us optimum access time. Next we would layout the extents one at a time on each disk leaving one disk for a BI and one for an AI. The next question would be what size would be best for the extents.
Figuring that you would want to keep the size of the files low enough so that they don't involve triple indirect block access and that at an 8K block size the largest a file could be to use only direct blocks would be 96K and the file size for single indirect block access would be only 16M both of which are to small that means double indirect block access would be the ticket. At an 8K block size that would mean the files sizes could be as much as 32 Gig without triple indirection. The problem is there has to be a value some where with in that parameter which gives optimum performance. Also I belive there is a limit on the number of extents in V6.3 (around 100 i Think). I heard from progress that somewhere between 300 and 500 gives optimum performance. If so then I would guess that 300 would be the best since it is on the lower end of that range.

Hopefully someone else has thought this type of situation out and can tell me if I am going in the right direction and if there is anything else I should consider in sizing this.

Thanks in Advance

Michael Fields
Systems Manager
Lotus Light Enterprises

 

[ChrisPaulson]

Why don't you convert the disks into one large stripped volume and have a few extents.

Does sun sparc support this - aix does.

 

[Lotusadm]

Hi,

We discussed that heavily here however the concensus by us (and by progress themselves) so far has been that multiple extents ditributed over multiple drives should give us as much of a performance boost as striping would. Also since raid 0 gives us no extra data integrity it just ends up being another admin layer to contend with. The other point made about raid 0 is that the drives work as a single unit rather than independanly so you are at the mercy of the raid system as to the distribution of data over the drives.
That was the thinking behind it so far.


Thanks
Michael

 

[ChrisPaulson]

But in your first post you said v6 could only cope with 100 odd extents, this means you will have to have large extents.

This means if your app spends a lot of time reading recent data (and you don't do many deletes) you will be only reading from one extent and hence hitting only one disk.

With stripping on aix you can have strip sizes as small as 8k, which is great if you are accessing a file randomly not sequentially ie a database.

How many large DBs do Progress run themselves? :awink:

 

[Lotusadm]

Good question about the progress?

As far as the rest we delete data on a regular basis. Right now we do not do dump and reloads because after deleting old data and adding new data we beleive the data has natuarlly distributed itself across all the extents over a period of time there by optimizing it. We also regularily rebuild and sort the indexes. However as you pointed out once we would do the dump and reload the new data would not be distributed as it is now. That is until after a few more deletes. May be it would be better to dump/reload the data into bigger fixed extents then leave the last extent as a variable one? We seems to add data at a fairly constat reate of between 100 to 150 meg a month. We also delete data everyother month leaving enough historical data to perform the reports necessary. Basically deleting line item detail from very old orders archiving the necessary info as needed.

The 100 extent limit hopefully would not be a problem for a while but by that time I would hope to be on a newer version of progress (best laid plans don't ya know) I beleive v8 has a 256 extent limit.
Also the 100 extents at 100 Meg would mean a ten gig DB but there is also number of open files to contend with and perhaps some streams limits that I have not even really thought about.

That is another reason to think about bigger extents so if I went to perhaps 200 or 300 meg extents the virtual db size would be doubled or tripled. But again I have been trying to figure out if the performance would go drop dramatically or not. There must be some top limit or rule of thumb. Maybe if you increase the size of the DB extents you should accordingly increase BI buffers?? or something like that.