Database Extents

jmac13

jmac13

Member
Hi All,

(using open edge 10.2b)

We have a few databases where the extents get bigger than 2gig then fall over (See example st file below). I know from reading this thread

http://www.progresstalk.com/showthr...ea-Grown-Too-Large&highlight=database+extents

that with a Enterprise Licence you can enable Large files which would allow the extent to go over the 2gig limit. my question is there Disadvantage to this? Or is it all just about disk space? If you’ve got plenty of room can I just switch that on so that it doesn’t fall over?

Thanks

Code: 
#
b G:\CustomerDatabases\DSSLaunceston\sop.b1
#
d "Schema Area":6,64;1 G:\CustomerDatabases\DSSLaunceston\sop.d1
#
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d1 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d2 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d3 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d4 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d5 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d6 f 2000000
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d7 f 2097152
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d8 f 728704
d "sop":7,64;1 G:\CustomerDatabases\DSSLaunceston\sop_7.d9
#
d "reports":8,64;1 G:\CustomerDatabases\DSSLaunceston\sop_8.d1 f 1508736
d "reports":8,64;1 G:\CustomerDatabases\DSSLaunceston\sop_8.d2
#
d "comms":9,64;1 G:\CustomerDatabases\DSSLaunceston\sop_9.d1
#
d "ledger":10,64;1 G:\CustomerDatabases\DSSLaunceston\sop_10.d1
#
d "WhatIf":11,64;1 G:\CustomerDatabases\DSSLaunceston\sop_11.d1
#
d "Deckling":12,64;1 G:\CustomerDatabases\DSSLaunceston\sop_12.d1
 
I've no idea about what effect large files has, but it looks to me that you need to keep a closer watch on when your variable extents are getting filled and add new fixed extents to keep things tidy.
 
yeah defo Cringer.. I'm not currently in charge of the DB admin stuff yet but I maybe so I'm trying to find out what the best way is..I shall have a look how to add extents in advance.. I know how to add them online but not in advance
 
We usually try and keep the variable extents empty. If the variable starts to get data in it then it's a warning that it's time to do something and add fixed extents. That way you've got around 2Gb worth of data warning before everything goes bang.
 
As always - it depends ...
  • on the file system: What limit does the file system have on file sizes and up to which size is it able to handle them efficiently.
  • on your database: How big is your database - in general I tend to have the database consist of as few files as possible. I would go for few large extents in favor of many small extents that clutter your file system - from an administrative view, not performance.
Having a database consisting of many small won't cause a noticeable performance penalty - unless for the start of processes that need to open all files (database broker or a single user client, for example) or you hit the maximum number of file handles. Our production database is 200 GB large, runs on Sun Solaris Sparc 64Bit and the default extent size is 8GB. Heavy Regards, RealHeavyDude.
 
well RHD i think All are system are on NTFS so shouldnt be an issue with file size unlike FAT32, The Area of "SOP" which contains a lot of main tables is about 13gig and has 8 extents.. I'd rather just have one massive extent to be onest. Because at the moment we are added and extent every time it gets close to the 2gig limit which is a pain in the back side when we've got lots of sites
 
You should enable large file extents, just make sure your backup software can handle the large extent sizes.
 
There is no performance downside to large files.

If your system supports them (and all current operating systems and file systems do) then there are no downsides and it should be the default.
 
cheers everyone I shall inform my company that they need to use large files thanks for the reassurance
 
As Tom said, there's no downside. So I enable it on every DB I encounter where it's off. It also eliminates the "ticking time bomb" where an area grows into the variable extent and you have to catch it before it hits 2 GB.

I don't know why it isn't the default in 11.0 for new DBs; what harm would that do?
 
I think the reason why it is not enabled by default is the limitation on the work group database license. AFAIK, from the contents of the database itself it does not know whether it is running a work group or enterprise license. The main differences between enterprise and work group, apart from the large files, are tuning options on the broker (spin lock IIRC) and the background writers.

Heavy Regards, RealHeavyDude.
 
You must log in or register to reply here.
Back
Top