OOW: Afternoon of Day 2

Current Trends in Real World Database Performance – Andrew Holdsworth

This was a run through of issues seen by Andrew’s group in the past year (10 months to be exact)

Real World Performance Fundamentals

Things are harder with increasing dataset sizes, and transaction rates. Increasingly rigorous targets.

Ever increasing CPU power and lower memory costs. The bigger the database the more poor design will be exposed in terms of poor performance.

Two types of systems, systems that have growth below moores law that have low performance requirements. Then systems above moore’s law are the ones with the real performance systems: the majority of db’s are NOT like this.

The Performance Hacker

This is an individual that claims to be a performance expert but without any knowledge. Root cause analysis is not done. Don’t just fiddle with init.ora parameters.

The Usual performance issues

Poor execution plans

Best execution plan uses the least system resources. First challenge is creating good schema statistics that yield good cardinality estimates. Things that screw up include, data skew, bind peeking, high/low end values.

He has a graph showing how things slow down when having to access disk rather than memory.

Too many connections and these log on/off too often – has seen 15,000 connections

graph showing impact of conections on scaling increasing the number of connections lower throughput. 50 processes per core loses 1/2 the performance.

Don’t have huge number of connections!

graph on parsing performance – don’t hard parse can’t believe he has been presenting that for over 10 years, but still keeps seeing it with customers.

dealing with growth is a real challenge

capacity planning is impossible without reference data..

Today CPU is the cheapest component, now storage and associated networking are dominating the hardware budget. Software is obviously really expensive as well.

OLTP Performance

seen 15,000 SQL statements per second on some systems.

most common delay is log file sync wait. Sometimes this is caused by bugs in various versions of oracle – that’s quite refreshingly honest.

DW/BI Performance

Sees a lot of databases that have the name data warehouse but that are really OLTP systems.

Data loads often the first performance problem – some loading programs are not just loading the data but transforming and selecting data as well thus not loading as performantly as they should.

Supporting too many users on a DW.

one off reports DW require huge amounts of hardware.

Hardware Review

getting faster due to additional cores, thought not seeing them scale more than 3/4.

Storage is still the dominant costs.
Solid state becoming slightly more common.

database storage for a paradigm shift – tomorrow in Larrys presentation – this is in a hardware review rather than software?!?

infiniband beating 10GigE

Not seeing 10GigE actually being that performant

review

block size changes calculations of optimizer, also affects what data you are storing in the buffer cache and impacts contention.

I was gutted to find that I had missed lunch so I decided to skip Paul Otellini’s keynote, though I did hear where he was claiming Intel were “famous” for low power talk about LMAO.

I then had yet another nightmare taxi journey where the cabbie asks me what the cross street for my destination was. How the hell should I know what the cross street is? I come from about a gazzilion miles away from SF, your the bloody cabby!

Top 10 Things You wanted to know about ASM – Rich Long & Nitin Vengurlekar

The room is absolutely jam packed for this one and we have started 8 minutes late and people are still streaming in.

ASM Architecture

ASM instance manages  metadata

diskgroup is logical grouping of disks

This is then presented as a series of questons:

what init.ora parameters does a user need to configure for ASM instances

only 3 parameters required

ASM_DISKGSTRING
ASM_DISKGROUPS
INSTANCE_TYPE which must be ASM

how does the database interact with ASM instance

ASM is not in the I/O path so ASM does not impact performance shows diagram of database create operation – do not mention COD.

Do I need to define filesystemio_options – no it’s not necessary as the db writes to raw.

Can the ASM instance and the RDBMS instance run different versions. Yes, the ASM instance can be at lower, the same or higher version.

talking a bit about compatible.rdbms and compatible.asm

Backups

how do I backup my ASM instance – you dont! there is no database opened. everything that ASM needs to mount a diskgroup is contained upon the disk. RMAN is the recommended method for backup.

migrations

How do I migrate to a new storage array? given that the new and old storage are visible to the server just add the new disks into the diskgroup and drop the old disks

can I take my diskgroup from solaris and plug it into a linux? No as the VTOC is different, also ASM currently stored in the disk header.

How do you use ASM with multipathing software. multipath software is at a lower level so should be transparent to ASM.

is ASM constantly rebalancing to manage “hot spots” NO.

graph showing IOPS to a bunch of disks managed by ASM showing that I/O is balanced pretty much equally amongst all devices.

long Q & A session

Storing now db files coming in 11.2.

larger AU recommended at > 10TB.

question regarding ASMLIB – manageability win persistent naming, global open. really recommending ASMLIB

raw devices not supported metalink document

SAs accidentally trampling over ASM disks just need to expose more information to them though this is mitigation not cure.

external redundancy 1 big lun or multiple luns – i though they misunderstood this question.

At the end I had a brief chat with Bill Bridge and asked him if he though in the 7 long years from idea to final product whether he thought it would never see the light of day and he said several times.

I was also badgering Rich Long regarding his presentation as he definately dumbed down the RDBMS & ASM interactions. I also felt he could have been doing with a slid on Allocation Units & extent sizing as this did not really get explained until the Q & A. I fear I may have come across a bit like a fruitcake at that point, but my slides are better 😉