AI, BI, DB, SAN and Heresy - Forum - OpenEdge RDBMS - Progress Community

AI, BI, DB, SAN and Heresy

 Forum

AI, BI, DB, SAN and Heresy

This question is not answered

Greetings

Given the march of time, the advance of technology and Moore's Law, I see more and more people, many of whom I have the utmost respect for, advocating that given a powerful enough machine and SAN, the need to

  • Keep different file extents physicallys separate
  • Use only fixed length extents

has become somewhat dated. A good rule of thumb perhaps, especially for under-powered systems but no more than that. The arguments seem to be along the lines of :

  • When you think you are putting extents on a particulr spindle, you are really just putting the whole lot in the SAN (somewhere) where it will be dealt (physically) with by the SAN and its cache.

and

  • The impact of growing a variable extent is now negligble

I can see this being true of db and bi extents butthe idea of having your ai extents on the same "anything" as everything else still scares the proverbial out of me (okay, I'm old school).

Comments please?

Nigel.

All Replies
  • I don't especially care for having AI on the same devices as the db either.

    It isn't so much "given a powerful enough..." but rather the reality that SAN storage is very often forced down the DBA's throat and even when the storage guys tell you that it is on "separate LUNs" they are either flat out lying or just do not really understand how their SAN actually works.

    So your best defense is to get the ai logs shipped somewhere outside the scope of that storage device as soon as possible.  Better yet, implement OE Replication and make sure that the replication target is in a different data center and continue to manage your ai extents as if you do not have replication running (belt and braces...)

    --
    Tom Bascom
    tom@wss.com

  • SANs are not DB friendly devices.  They exist to make life easy for storage administrators and to provide false economies for bean counters while quietly sucking budgets dry to fund sale weasel yachts.

    --
    Tom Bascom
    tom@wss.com

  • My advice is:

    put everything except for after-image extents onto one big filesystem that is striped and mirrored

    Feel free to use variable-size extents.

    Always use only type ii data areas.

    For after-image extents, best practice is to put them on a separate filesystem from the data and before-image extents. About 8 variable size extents is a good practice rule of thumb.

    Never use less than 4 ai extents.

    Always use the archiver daemon to copy ai extents off the system.

    You can estimate the space required for after-imaging by looking at the number of checkpoints over some period of time (say a week) and multiplying by the before-image cluster size. In the same time period you will generate that much after-image data. Note that certain operation like tablemoves, month-end, quarter-end or year-end processing can generate much more after-image data that average and you should take that into account.

    YMMV, transportation, meals, and accomodations not included.

  • Forgot to add:

    use RAID 10 or mirrored SSD. no RAID 5, RAID 6, RAID DP, etcetera. Use of any of these will cost you more money because

    - your nightly batch jobs may not finish in time

    - all write-intensive maintenance tasks that involve high volumes of output will take much longer. this includes backups, restore from backup, index rebuilds, data dumps, table and index moves,

    - your disaster recovery time will be at least doubled and likely more.

  • Regarding the distinct filesystem for after-imaging...  I agree with Gus.  Even though a SAN is almost certainly going to use the same physical disks you still get some benefits by having a discrete filesystem.  It makes it harder for someone to kill everything with a "rm *" and the ilk.  And, in my experience, human error is at least as big a risk as hardware error.

    --
    Tom Bascom
    tom@wss.com

  • Ahh...a repeat of DBA Day in Ontario...I respectfully disagree, except for the part where you have to get those archived AI files out to a different data centre in a different city ASAP.

    If everything is on the SAN, I'm not sure if there is still a performance gain to having the AI files on a separate FS if the underlying LUNs are all writing to the same SAN and SAN cache.  Maybe there is, but probably only at a very high write rate. There USED to be issues on AIX with large LUNs and benefits to having separate FS, but those were bugs that have since been fixed.  Maybe other O.S.'s still suffer from these issues.  I will concede that if your bean counters insist on RAID 5 for the DB (don't worry, the vendor said it's GRRRREAAT!!"), you can try to negotiate RAID 10 LUNs for the AI/BI.

    If it's a small site with local disks in the physical server, my argument is that I need to maximize the use of ALL the spindles.  A physical box with eight 2.5" bays is not going to perform optimally if I reserve two in mirror for the operating system, two in mirror for the AI files and four in RAID 10 for the data files.  Note that there's no room for a hot spare disk here either.  I am going to put all 8 bloody drives in one big RAID 10 and maximize my throughput.

    At least SSDs are fast becoming affordable.

    Paul

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • What are you respectfully disagreeing about?  ;)

    As I read the original post the concern isn't so much about performance as it is that having the ai logs mixed with the db is a recoverability risk.  The old-school approach of keeping them on different disks was primarily for recoverability -- if you lose the db disk you still have the ai logs.

    Of course even with that setup there was still the risk that an event that takes out the whole disk subsystem (bad controller, SAN falling through the floor, fire, flood, theft of equipment, storage admin forgetting that this is PROD not DEV...) would still result in the both the db and the ai logs being destroyed together.  So while you get /some/ benefit from distinct disks it doesn't solve every media loss problem.

    --
    Tom Bascom
    tom@wss.com

  • Since I am argumentative and ornery by nature, I will point out that the original question was "Comments please?"  I have commented and I continue to comment.  Nigel would expect no less from me and would be disappointed otherwise.

    I am respectfully disagreeing that there is any significant benefit to putting AI files in a separate FS for 99% of the Progress installations out there.  Certainly there must be a 1% that would benefit for various reasons.

    Paul <tongue-firmly-in-cheek> Koufalis

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • The AI files IMO should be on a different vdisk/LUN, which would make it easier to possibly replicate the content off SAN in case it goes kablam. As Tom said, not primarily for performance reasons, but for being able to recover if the whole SAN ends up in flames. (metaphorically speaking). Unlikely yes, but better safe than sorry. If one values the data that is.

  • I'm not at all argumentative :)

    IMHO the distinct filesystem is worth it for the reason that I mentioned.  It makes it harder to accidentally delete the ai files with the same command that deletes the db.  Not impossible -- but slightly harder.  In my book that is a worthy benefit.

    Libor makes a good point but I wouldn't hold my breath believing that just because the storage people tell you it is a separate LUN that your disks are not actually all the same.  But it is worth trying.  Sometimes it is even actually true.

    --
    Tom Bascom
    tom@wss.com

  • If you do not separate your after-image extents from the data extents then there is little point in having them. so you might as well rfutil foo -C aimage end.

  • Why is that?  I would argue that:

    a) If it's on a SAN and the SAN is down, you're hosed anyways

    b) if it's on a SAN and you're "fortunate" enough to have only hosed the DB LUNs, then you better have a written procedure on how to mount the LUNs/file systems holding the AI files on another server (i.e. exportvg/importvg on AIX)

    c) if it's a physical machine with local drives, you better have easy and immediate access to another server that can take those hot swap drives and mount them to recuperate the live AI files.  

    IMHO, at most sites they would not be able to recover the last, live AI file regardless of it's physical placement OR the time to recuperate it would exceed the maximum down time allowed.  They would choose to forego the last -aiarcinterval seconds worth of transactions so that they could concentrate on restoring and rolling forward what they have.  IF there's a sysadmin with nothing to do, maybe he can fiddle around trying to recover that last AI file.

    Finally, I would argue that if losing the last 5 minutes of transactions is the end of the world for your business, then you should be running OE Replication.

    Ouhhh...now I'm really getting ornery.  Must be my Medula Oblongata acting up again.

    Paul

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • "Ouhhh...now I'm really getting ornery.  Must be my Medula Oblongata acting up again"

    PK: Have you noticed that your medula oblongata acting up always seems to affect everyone elses gluteus maximus?

    N/

  • You've been talking to my wife haven't you?

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • > a) If it's on a SAN and the SAN is down, you're hosed anyways

    True, but if you have backed up your DB and have kept your AI off the SAN at the first place, then you have a way of putting your database back together. I think that's the main point of keeping AI (also) separated, outside the SAN.

    And can someone change the topic from ... Heresy to Hennessy ? :-D