Performance – The database stack

hamb-stackAs mentioned before, I frequently find myself in discussions around Oracle performance and how an Oracle database behaves on EMC storage. It turns out that often there is a lot of confusion on how the different layers interact with each other and very few people seem to understand the whole stack.

So I started a personal challenge to make a “one picture tells more than 1000 words” complete overview of the Oracle on EMC database stack.

I failed.

Turns out it’s nearly impossible to get everything in one picture without cutting corners.

So here is a simplified (and therefore incorrect) picture. It ignores certain complexities and is far from complete, and might even contain errors.

I made a few assumptions and bypasses:

  • The database is using ASM on Fibre Channel disks (picture for filesystems or NFS protocol would look very different)
  • Storage is EMC Symmetrix (EMC VNX/CLARiiON would look very similar)
  • Dual fabric SAN for high availability and load balancing
  • No Virtual provisioning, FAST-VP etc (this would make the picture much more complicated)
  • I focus on the database and redo log writer. Oracle has many more processes performing I/O. Left out for simplicity.
  • I completely ignore the network layer. Not relevant for this explanation
  • Also ignored are volume partitioning (alignment), host logical volume managers, oracle RAC etc etc etc.
  • The picture reflects RAID-5 3Data+1Parity configurations. RAID-1 or RAID-6 would look very similar.
  • Symmetrix Metavolumes are not always used. In many cases, customers assign Symm logical volumes directly to the host.
  • And much more I didn’t mention…
Database Stack

Click on the image to get a sharper view

Maybe it helps many people in starting to understand the end-to-end performance stack of an Oracle database. Feedback welcome as always!

4 Responses to Performance – The database stack

  1. ralph thissen says:

    Hi Bart,
    Heb je al eens gehoord/gekeken naar Prezi. Dit is een andere manier om presentaties te maken. In potentie zou je hier wellicht wel een compleet overzicht van de db stack in kunnen maken.
    Gr. Ralph
    P.s. altijd interessant om artikelen van je te lezen.

  2. Bart Sjerps says:

    Hallo Ralph,

    (replying in English because of my readers being mostly non-Dutch ;-)

    Thanks for the suggestion and the positive feedback!

    I made the picture in Powerpoint (not the best tool for the job, but I have to use it because it is the EMC interna de-facto standard and I use this picture in presentations as well).

    But who knows what the future will look like. Seems like even Microsoft is treating the traditional desktop as becoming irrelevant, so maybe we will all be doing our work “in the cloud” pretty soon…

    Regards,
    Bart

  3. Hi Bart
    I found your blog while searching Internet for ASMs cross platform compatibility. I must say that I have found every bit of information in this blog very interesting. I have been working with oracle databases for some years now and every now and then come across performance issues in DBs where I have found IO as the main bottleneck. I know it’s bit of an overly simplified statement… but no matter how much you tune your application or database you are bound to fetch the db block from storage sub system and that’s the slowest part in the infrastructure. With the advent of SSD and most recently PCIe based SSD it looks like storage tier has finally caught with the moore’s law where CPU and memory are sitting now. I believe next few years will be really interesting in storage space when IOPs will be measured in millions/100s of thousands rather than thousands and suddenly CPU requirements will fall dramatically as we will find out how much we over sized our systems due to slower spindle/spin based storage and perhaps build an even stronger case for virtualization. I am very excited to see EMCs Vfcache and eagerly waiting to test it out with 11gs flash cache and write through FAST cache scenarios. I am looking forward to few post on project lighting and project thunder initiatives from EMC.

    Nav
    http://practicalappsdba.wordpress.com
    (I know I have not blogged in a while but after reading your blogged and I am motivated now :- )

    • Bart Sjerps says:

      Hi Nav,

      Thanks for your feedback. You’re absolutely right that, especially with today’s fast CPU’s, the I/O to “rotating rust” disks are often the bottleneck. SSD’s help reducing response times by a factor 10 or so (huge improvement) but it can still be the bottleneck.
      Diagnosing such issues can be hard if you don’t have the right tools and/or lacking the skills to understand the metrics. So I plan to post more articles on I/O performance, including some of my field experiences with such issues, so stay tuned…

      As soon as I have some hands-on experience with VFcache myself I will post some insights there too (no hard commitments, though…)

      Cheers
      Bart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: