Today was a very full day…
Real-World Database Performance Techniques and Methods
Real-World Database Performance Roundtable
I went to the primer session on Tuesday and both sessions today. I don’t think there was anything particularly new to me here, but listening to the experiences of a team like that helps you crystallize things in your mind. The three sessions were well presented and the last session, which was a Q&A session, through up a few interesting things.
Take-home messages in no particular order:
- It all boils down to the SQL and the cardinality of the steps in the execution plan. If the optimizer gets the correct cardinality for each step it should make the correct decision. If it doesn’t it will make a bad decision. When you spot a problem SQL you need to find out why it make that bad decision.
- Debug methodically. If only a fraction of your SQL performs badly, making global changes to the database is a very bad idea. Fix the problems, don’t tamper with the whole system.
- You have to decide if your aim is to have predictable execution plans or to let execution plans evolve over time. Either choice comes with issues. To make plans predictable, use SQL Profiles, hints, check high/low values, avoid histograms and don’t be too quick to gather new stats. If you want plans to evolve, gather stats regularly and accept that plans will change over time. Identify poorly performing SQL when it arises and figure out why it is going wrong.
- Tune 3rd party apps, and Oracle Apps the way the vendor recommends, even if it doesn’t make sense to you. Some applications are quite fussy and doing what you think is correct may be the wrong move.
- Your aim should be to spread I/O evenly across all disks. ASM will do this for you.
- In Oracle 11g, you should gather statistics with the auto sample size, unless you know of a specific reason not to. Don’t make random decisions on sample size.
- The team always use 8K block sizes because that’s what the majority of the database testing is done against and what the majority of people use.
- Partitioning: Global stats tend to give predictable execution plans, but that’s not always a good thing. Partition stats give a more accurate picture, but won’t always result in predictable execution plans. You have to decide which you want.
- High/low values are important stats. Don’t default to extreme values that make high/low values unrepresentative as this affects the optimizer.
There’s a lot of information in the slides, so check them out when they come online. The main message is you should be methodical and consistent. Nothing new in that. 🙂
Oracle’s New Database Accelerator: Query Processing Revolutionized
Oracle’s New Database Accelerator: A Technical Overview
These two sessions were renamed to include the Exadata name. I didn’t record the new names, but I guess that will be obvious when you see the list of presentations.
Take-home messages:
- The exadata storage server is also known as a storage cell.
- You can buy the storage cells on their own and use them to build your own database system, or buy the preconfigured database machine.
- The system is only supported on Linux at the moment, but other platforms will follow. I assume this means databases on other platforms plugging into the same Linux storage appliances.
- The storage cells are single purpose. They are for holding database files only. If you want a shared filesystem for any other file types you need to use something else. So you guys who want to use it to store MP3s and pr0n better store it in BLOBs in the database. 🙂
- The iDB protocol (like iSCSI) presents storage to ASM. No OS config (LUNs mount points) is necessary. At its simplest, you can consider each storage cell as a single disk when viewed from ASM.
- Storage cells don’t talk to each other, only to the database. The cells are aware of the database state and the database knows about the cells because of ASM.
- Multiple databases can use the same storage cell.
- The storage is presented to the database as shared-everything, but it has elements of shared-nothing architecture. Because the presentation to the database is unchanged, it is suitable for use with any existing application without change.
- There is an EM plugin and several tools, but for the most part it needs no management. It is essentially a storage appliance (their words), but it can do some clever stuff.
- Because of the presentation of storage cells to ASM, adding new storage cells is like adding disks. It triggers an ASM rebalance.
- Normal ASM mirroring (2 or 3-way) is present, but it makes sure it mirrors to different cells, not just different disks in a cell, so it can tolerate a disk or a cell failure.
- The storage cells should not need a software upgrade to work with new versions of Oracle, but if new offload features require changes, you may be restricting the offload functionality by not upgrading the cell server software. This was likended to the ASM compatibility modes in 11g.
- Resource management information is passed from the database to the storage cells so I/O is prioritized to match the database resource management.
- When you offload processing to the storage cell, only the relevant column information is returned. As a result, this won’t be stored in the buffer cache, but it is possible to cache it in the SQL result cache.
- Up to 6 of the prebuilt database machines can fit together by just cabling them. You only need to worry about extra infiniband switches if you want more than 6. I can see this being a big problem for me… Not! 🙂
- It is designed for intensive query work, like warehouses. The performance of insert, update and delete operations is just like any other block storage device. That’s what the guy said, but I guess it was a glib statement. I would assume it is still faster than most storage devices due to the wicked network bandwidth.
This list represents many of the points made in the two talks, but I’ve started to read some of the stuff on Kevin Closson‘s blog, and the related links and it’s making me a bit nervous about my random ramblings. I’m going to leave this list here to remind me of what I heard, but you really need to check out the white papers.
There were some neat slides explaining the cell server software and the way the project evolved.
The biggest surprise to me was how few people were actually at these sessions. I blogged previously to say I was underwhelmed by the announcement, but I was still curious. It seems not many other people were. 🙁
After hearing a bit more about the kit I can see why some people are really excited by it. It is very clever, but I still think it is pretty irrelevant to grunt DBA/Developers like me. Of course, I reserve the right to get excited by it at a later date and back track on all my cynical rantings. When they are in the bargain section at PC World I will race to the shop to buy one, but until then I guess I’ll be stuck with crappy old disks. 🙂
Oracle ACE Director Birds-of-a-Feather Tips and Techniques Panel
This was my only real job at OpenWorld and I was pretty nervous going in to it. When you present a formal session you are talking about a specific area and you have an idea of what questions might come up. Sitting on a panel without access to Google and no time to compose your thoughts is a whole different ball game. Luckily the verbal diahorea gene that runs in my family was expressed in full effect and I managed to do more than my fair share of talking. Thanks to Lewis for setting this up. Thanks to the other guys on the panel for answering the questions that would have stumped this chump. Most of all thanks to the attendees for turning up and asking questions. I hope you got something out of it. 🙂
I think I’ll let the events of the last few days sink in, then write a summing up post tomorrow.
Cheers
Tim…