Any reason for fixes size extents (ai + bi)

Posted by Jens Dahlin on 25-Oct-2017 12:26

Planning a move to 11.7 and new servers (why not change everything at once so everybody can blame each other when something fails). I am also planning to make some structural changes to our databases. 

Are there any reasons at all to have fixed size bi + ai areas for smaller (<10 GB) databases? We've stopped having fixed size data areas a long time ago and I'm thing why not just get rid of everything fixed size. 

Related question: should blocksize match Linux (4096) or just be as big as possible? Or is it set, test and monitor that will answer that?

All Replies

Posted by Rob Fitzpatrick on 25-Oct-2017 12:39

There could be advantages to using fixed-size AI extents in a database with very high transaction volume, owing to avoiding extend operations.  With sizes less than 10 GB, I wouldn't worry about that at all.  

With BI I tend to create two extents: one fixed extent that is 4x the largest cluster size I intend to use, and one variable.

With DBs this size, keeping things variable will minimize administration work from managing extents.  Also, use the AIMD to manage AI archiving.  I wouldn't expect users to see any noticeable performance impacts.  I assume, with new servers, that you will have decent hardware.

I always use 8 KB DB block size, in all OSes.  Touch wood, I've never had a problem with doing that.

Posted by Dmitri Levin on 25-Oct-2017 13:03

For smaller (<10 GB) databases everything goes. :) Make it all variable.

For more serious consideration let's consider AI and BI separate.

The fixed size BI guaranties that you have the space already allocated. There is nothing wrong with having variable BI as long as you are 200% sure you will not run out of space on the file system. And the best bet for that is 1) have BI on a file system separate of everything else 2) to have much more space then you need. Like if your average BI is size X. Have 10 * X available. 3) Monitor BI area size and growth with alerts if it is unusually big.

I would have AI always variable. Same space requirements here. You should have space for disaster, meaning probably enough for one week of AI growth. I assume some kind of database replication in place.

Posted by Jens Dahlin on 25-Oct-2017 13:24

Thanks! Yes: hardware seem more than sufficient. Everything 40-50% faster than the old servers without us really adapting the system in any way. Very early load testing indicate that the first bottleneck will be bandwith rather than cpu or io (there's only so much data that can be transfered on a 1 gbit connection). Possibly we could have looked at lower specifications but this was within budget...

Yes. After imaging and OE Replication is running.

This thread is closed