Fixed Extent Size and performance

[nineinch]

Hi,

Is there any perfomance difference when using one large fixed extent or several fixed extents of 2 GB instead.
Assuming that for both situations the fixed extents are not full.

Regards, Rene

Red Hat Enterprise Linux 5 64 bit
Open Edge 10.2

 

[RealHeavyDude]

Nope.

But there are other things you might take into consideration:

• With many small files you might exceed the limit for file handles on the system.
• Depending on the OS handling very large files > 10 - 15 GB might be cumbersome.

I prefer to have my extents to be around 10 GB large.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

There may have been a difference in an earlier age of OSes and file systems that couldn't handle large files well, or at all. I have seen DBs where all extents are carefully kept to sizes of 2 GB or less, where no one involved can articulate why or provide evidence that it improves any performance metric. I just hear that "we've always done it that way".

I think there is more performance bang for the buck in exploring optimization of data storage areas based on data characteristics, as well as utilizing the alternate buffer pool (in 10.2B+). When you go down that road, you may often end up with more storage areas. So combining that with the approach of having many small files per area gives you a lot of files (and thus more handle usage, as RHD said) and a lot of high water marks to track and manage.

I prefer a fully variable structure; it's just a lot less headache. I'd rather a DBA spend his time on analysis and proactive maintenance than on extent management tasks.

 

[goz]

My experience (10.2A + Windows box) is that a variable structure costs in performance when you create a lot of data, I guess it's because of having to allocate space.

 

[tamhas]

Bottom line, one *may* be able to create circumstances in which there are measurable differences, but given a modern operating system, modern hardware, and a modern version of Progress, those circumstances are going to be very "special", i.e., unlikely to be something that one can detect in any production system. Possible, yes, but highly unusual. Substitute 10-20 year old operating system, 10-20 year old hardware, and 10-20 year old version of Progress and one is likely to come to different conclusion ... big surprise!