Transcription
Exadata:from Beginner to Advancedin 3 HoursArup NandaLongtime Oracle DBA(and now DMA)
Why this Session? If you are– an Oracle DBA Familiar with RAC, 11gR2 and ASM– about to be a Database Machine Administrator (DMA) How much do you have to learn? How much of you own prior knowledge I can apply?What’s different in Exadata?What makes it special, fast, efficient?Do you have to go through a lot of training?Exadata: from Beginner to Advanced in 3Hours2
What is Exadata It is like an appliance containing– Storage, Flash Disks, Database Servers, InfinibandSwitches, Ethernet Switches, KVM (some models) But it is not an appliance. Why?– additional software to make it a better databasemachine– Components can be managed independently That’s why Oracle calls it a Database Machine(DBM) And DMA – Database Machine AdministratorExadata: from Beginner to Advanced in 3Hours3
Anatomy of an Oracle DatabaseInstanceCombination of Memory Areas Background ProcessesSELECT NAMEFROM CUSTOMERSWHERE STATUS 'ANGRY'UPDATECUSTOMERSSET BONUS 1MWHERE STATUS 'ANGRY'datafile1datafile2StorageExadata: from Beginner to Advanced in 3Hours4
RAC Databasedatafile1datafile2StorageExadata: from Beginner to Advanced in 3Hours5
Query ProcessingJILLSELECT NAMEFROM CUSTOMERSWHERE STATUS 'ANGRY'datafile1datafile2Database BlockStorageExadata: from Beginner to Advanced in 3Hours6
Components for PerformanceCPUMemoryNetworkLess I/O betterperformanceI/O ControllerDiskExadata: from Beginner to Advanced in 3Hours7
What about SAN Caches? Success of SAN caches is built upon predictiveanalytics They work well, if a small percentage of disk isaccessed most often– The emphasis is on disk; not data Most database systems– are way bigger than caches– need to get the data to the memory to process-- I/O at the disk level is still high Caches are excellent for filesystems. or very small databasesExadata: from Beginner to Advanced in 3Hours8
What about In-Memory DBs Memory is still more expensive How much memory is enough? You have a 100 MB database and 100 MB buffercache The whole database will fit in the memory, right? NO! Oracle database fills up to 7x DB size buffer mb-database-completely-in.htmlExadata: from Beginner to Advanced in 3Hours9
The Solution A typical query may:– Select 10% of the entire storage– Use only 1% of the data it gets To gain performance, the DB needs to shed weight It has to get less from the storage. Filtering at the storage level. The storage must be cognizant of the dataCPUMemorySELECT NAMEFROM CUSTOMERSWHERE STATUS 'ANGRY'Filteringshould beApplied HereNetworkI/O ControllerDiskExadata: from Beginner to Advanced in 3Hours10
The Magic #1CPUiDBMemoryNetworkI/O ControllerDiskExadata: from Beginner to Advanced in 3HoursThecommunicationbetween CPUand Disk carriesthe informationon the query –columns andpredicates. Thisoccurs as a resultof a specialprotocol callediDB.11
Magic #2 Storage Cell Server iDB Disk1Disk2Cells are Sun BladesRun Oracle EnterpriseLinuxSoftware called ExadataStorage Server (ESS)which understands iDBDisk3Exadata: from Beginner to Advanced in 3Hours12
Magic #3 Storage IndexesStorage Indexes store in memoryof the Cell Server the areas onthe disk and the MIN/MAX valueof the column and whether NULLexists. They eliminate disk I/O.SELECT FROM TABLEWHERE COL1 1Storage IndexDisk1Disk2Disk3MIN 3MIN 4MIN 1MIN 3MAX 5MAX 5MAX 2MAX 5Disk4Exadata: from Beginner to Advanced in 3Hours13
Checking Storage Index Useselect name, value/1024/1024 as stat valuefrom v mystat s, v statname nwhere s.statistic# n.statistic#and n.name in ('cell physical IO bytes saved by storage index','cell physical IO interconnect bytes returned by smartscan’)OutputSTAT NAME STAT VALUE---------- ---------SI Savings5120.45Smart Scan1034.00Exadata: from Beginner to Advanced in 3Hours14
Checking Offloading of an SQLselectsql id,child number child#,plan hash value plan hash,executions execs,(elapsed ons)/decode(px servers executions,0,1,px servers )) avg elapsed time in secs,px servers ) avg par deg,decode(io cell offload eligible bytes,0,'No','Yes') Offloaded,decode(io cell offload eligible bytes,0,0,100*(io cell offload eligible bytesio interconnect bytes)/decode(io cell offload eligible bytes,0,1,io cell offload eligible bytes)) "%age IOSaved",buffer gets/decode(nvl(executions,0),0,1,executions) avg liofrom v sqlwhere sql text like SQL Statement Comes Here %'Exadata: from Beginner to Advanced in 3Hours15
Why Not? Pre-requisite for Smart ScanDirect PathFull Table or Full Index Scan 0 PredicatesDisabling Smart Scanscell offload processing Simple ComparisonOperatorsfalse;kcfis storageidx disabled Other Reasons– Cell is not offload capable true;–––– The diskgroup attributecell.smart scan capable set toFALSE;– Not on clustered tables, IOTs,etc.Exadata: from Beginner to Advanced in 3Hours16
Magic #4 Flash CacheThese are flash cardspresented as disks; notmemory to the Storage Cells.They are similar to SAN cache;but Oracle controls what goeson there and how long it stays.datafile1datafile2StorageExadata: from Beginner to Advanced in 3Hours17
Magic #5 Process Offloading Bloom Filters Functions Offloading– Get the functions that can be offloaded V SQLFN METADATA Decompression– (Compression handled by Compute Nodes) Virtual ColumnsExadata: from Beginner to Advanced in 3Hours18
ComponentsCPUDatabase Node(Sun Blade. OEL)MemoryOracle 11gR2 RACNetworkInfiniBand SwitchI/O ControllerStorage CellDiskExadata Storage ServerDisks, FlashExadata: from Beginner to Advanced in 3Hours19
Put Together: One Full RackRAC ode 1Node 1InfiniBandSwitchCell 1Cell 1Cell 1Cell 1DatabaseNode 8Network SwitchClientsconnect to thedatabasenodes.Cell 14Exadata: from Beginner to Advanced in 3Hours20
How it LooksX2-2 QtrX2-2 HalfX2-2 FullX2-8 FullNumber ofCompute NodesTotal ComputeNode ProcessorCoresTotal ComputeNode MemoryNumber ofStorage ServersNumber of SASDisks in Storage2482244896160196 GB384 GB768 GB4 TB3714143684168168StorageCapacity - HP21.6 TB50.4 TB100.8 TB100.8 TBStorageCapacity - HC108 TB252 TB504 TB504 TBNumber ofInfiniBandSwitches2333Source: upcoming book Exadata Recipes by Clarkefrom ApressExadata: from Beginner to Advanced in 3Hours21
Disk Layout ComputeNodes Storage Cell Disks (hard and flash) areconnected to the cells.The disks are partitioned at thecellSome partitions are presentedas filesystemsThe rest are used for ASMdiskgroupsAll these disks/partitions arepresented to the compute nodesExadata: from Beginner to Advanced in 3Hours22
NodeDisk PresentationfilesystemCellfilesystemExadata: from Beginner to Advanced in 3Hours23
Command ComponentsComputeNodesStorage CellLinux Commands – vmstat, mpstat, fdisk, etc.ASM Commands – SQL*Plus, ASMCMD, ASMCADatabase Commands – startup, alter database, etc.Clusterware Commands – CRSCTL, SRVCTL, etc.Linux Commands – vmstat, mpstat, fdisk, etc.CellCLI – command line tool to manage the Cell5-part Linux Commands article serieshttp://bit.ly/k4mKQS4-part Exadata Command Reference article serieshttp://bit.ly/lljFl0Exadata: from Beginner to Advanced in 3Hours24
Administration SkillsSkillSystem AdministratorStorage AdministratorNetwork AdministratorDatabase AdministratorCell AdministrationNeeded15%0%5%60%20%DBASys AdminNetwork AdminExadata: from Beginner to Advanced in 3HoursCell Admin25
One Cluster?One 1Int1Dev2Exadata: from Beginner to Advanced in 3Hours26
Many Clusters?QA ClusterQA1QA2QA ClusterProd ClusterQA3Prod1Prod2Prod ClusterExadata: from Beginner to Advanced in 3HoursProd3DevIntDev1Int1DevInt27
Disk FailuresDatafileblock1Cell 1Cell 2block1block1Exadata: from Beginner to Advanced in 3Hours28
Disk FailuresDatafileblock1Cell 1block1Cell 2Cell 3block1block1High RedundancyExadata: from Beginner to Advanced in 3Hours29
Playing Nice Database Resource Manager I/O Resource Manager Cell FencingQAProdComputeNodesStorage CellsExadata: from Beginner to Advanced in 3Hours30
Divide and ConquerDatabaseDBAMachineSystem AdminNetworkNetwork AdminStorage?DBASys AdminNetwork AdminCell AdminExadata: from Beginner to Advanced in 3Hours31
Combined hineNew RoleNetworkStorageDBASys AdminNetwork AdminCell AdminExadata: from Beginner to Advanced in 3Hours32
Other QuestionsQ: Do clients have to connect using Infiniband?A: No; Ethernet is also availableQ: How do you back it up?A: Normal RMAN Backup, just like an Oracle DatabaseQ: How do you create DR?A: Data Guard is the only solutionQ: Can I install any other software?A: Nothing on Cells. On nodes – yesQ: How do I monitor it?A: Enterprise Manager, CellCLI, SQL CommandsExadata: from Beginner to Advanced in 3Hours33
Backup and DR No SAN connectivity Only NASExadata– Infiniband– Tape , Disk Pool DR–––––InfinibandNo Storage Level ReplicationBackupOnly Data GuardSupplemental LoggingDeviceForce features/availability/maa-wp-dr-dbm-130065.pdf Golden GateExadata: from Beginner to Advanced in 3Hours34
ETL and aMicroStrategyExalyticsExadata: from Beginner to Advanced in 3Hours35
Overall Activities Physical Aspects– Delivery, power, network components, etc. Layout PlanningInstallation and ConfigurationData MigrationAdministration– Who manages it– Backup and Disaster Recovery Application DevelopmentExadata: from Beginner to Advanced in 3Hours36
Power Delivery Units Over or Under the unit Power Requirement– Single-Phase Low Voltage Americas / Japan / Taiwan)– Single-Phase High Voltage (EMEA & APAC (excluding Japan /Taiwan)– Three-Phase Low Voltage (Americas / Japan / Taiwan)– Three-Phase High Voltage (EMEA & APAC (excluding Japan /Taiwan)Exadata: from Beginner to Advanced in 3Hours37
Network Ports NET0– Admin Interface NET1, NET2– Network Access to Nodes NET3– Backup Network IB– Infiniband Network– IP Addr: Qtr Rack: 5; Half Rack: 11; Full rack: 22Exadata: from Beginner to Advanced in 3Hours38
Configuration WorksheetPre-delivery SurveyGenerate config filesRun checkip.shPower on and validate componentsConfigure KVMConfigure IBConfigure Cisco SwitchExadata: from Beginner to Advanced in 3HoursOracle HW1.2.3.4.5.6.7.8.ACSInstallation Activities39
Exadata: from Beginner to Advanced in 3HoursACS9. Configure IP to PDUs10. Validate Storage Cells11. Validate Compute Nodes12. Config files from USB13. Firstboot and applyconfig.sh14. Stage Oracle Software on Node 115. Run OneCommandOracle HWInstallation, contd.40
Summary Exadata is an Oracle Database running 11.2 The storage cells have added intelligence about dataplacement The compute nodes run Oracle DB and Grid Infra Nodes communicate with Cells using iDB which cansend more information on the query Smart Scan, when possible, reduces I/O at cells evenfor full table scans Cell is controlled by CellCLI commands DMA skills 60% RAC DBA 15% Linux 20%CellCLI 5% miscellaneousExadata: from Beginner to Advanced in 3Hours41
Resources My Articles– 5-part Linux Commands article series http://bit.ly/k4mKQS– 4-part Exadata Reference article series http://bit.ly/lljFl0 OTN Page on Exadata– /index.html Tutorials– html OTN Exadata Forum– D 829 Exadata SIG– http://www.linkedin.com/groups?home &gid 918317Exadata: from Beginner to Advanced in 3Hours42
Thank You!My Blog: arup.blogspot.comMy Tweeter: arupnandaExadata: from Beginner to Advanced in 3Hours43
ASM Commands - SQL*Plus, ASMCMD, ASMCA Clusterware Commands - CRSCTL, SRVCTL, etc. Database Commands - startup, alter database, etc. . bility/maa-wp-dr-dbm-130065.pdf Golden Gate 34 Exadata Backup Device Infiniband Exadata: from Beginner to Advanced in 3 Hours. ETL and Reporting 35 Exadata Informatica MicroStrategy Infiniband Exalytics
