Oracle Database Appliance Was tun mit dem Flash?

Oracle Database Appliance Was tun mit dem Flash? Sebastian Solbach BU Datenbank, ORACLE Deutschland B.V. & Co. KG April, 2016 Sebastian.solbach@oracle...
Author: Horst Linden
16 downloads 2 Views 3MB Size
Oracle Database Appliance Was tun mit dem Flash? Sebastian Solbach BU Datenbank, ORACLE Deutschland B.V. & Co. KG April, 2016 [email protected] @s2solbach

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

2

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

3

Oracle Database Appliance Complete, Simple, Reliable, Affordable, Preconfigured • Komplette Datenbank-Hochverfügbarkeitslösung in einem System • Einfache Installation und Management von Datenbanken und dazugehörigen Applikationen • Zuverlässiges System um die Performance und Verfügbarkeit Ihrer Datenbanken und Applikationen sicherzustellen

• Bezahlbar durch Capacity on Demand (CoD) Lizenzierung der Oracle Datenbank und Oracle Applikationen • Vorkonfigurierte System Lösungen (Solution-In-a-Box)

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Oracle Database Appliance X5-2 Hardware • 2x 1RU x86 Server + 1x (oder 2x) 4RU JBODs • Je Server: – 2 x 18-core 2.3 GHz Intel Xeon Processors E5-2699 v3

– 256 GB Memory, erweiterbar auf 768 GB – 600 GB gepiegelte Boot Platten – Redundantes InfiniBand Interconnect – Externes 10GBase-T Netzwerk – Optional: 10GbE SFP+

• Je JBOD (SAS - Direct-Attached) maximal 2: – 800 GB SSD für Redo Logs (4 Platten)

– 1.6 TB SSD für Datenbank Cache, Tablespaces und Temporäre Dateien (4 Platten) – 128 TB HDD für Daten, Archive Logs und Backup (16 Platten) Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

ODA X5-2 Storage Shelf

+REDO - Redo Logs SSD

• Hierarchische Storage Architektur

+FLASH - Cache SSD

• 800 GB SDD HE (High Endurance) +DATA, + RECO - Data HDD

– Redo logs, Accelerator Volume – 200 K IOPs, 25 DWPD

• 1,6 TB SSD ME (Medium Endurance) – Cache und/oder Daten – 150 K IOPs, 10 DWPD (Full Disk Writes per Day) auf 5 Jahre => 4 TB pro Tag

• 128 TB High Capacity Platten – Daten und Backup – 1,5 K IOPs Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

6

Aufteilung Plattenplatz X5-2 (8TB) Ohne Storage Expansion ASM Normal Redundancy

High Redundancy

Backup

Lokal

Extern

Lokal

Extern

Total (TB)

61,0

61,0

40,7

40,7

+DATA

24,6 (~40%)

49,2 (~80%)

16,4

30,6

+RECO

32,6 (~53%)

8,0 (~13%)

21,7

5,0

+REDO (GB)

248

248

248

248

+FLASH (GB)

744

744

744

744

ACFS

Belegt Plattenplatz in RECO

Shared Repos

Belegt Plattenplatz in RECO oder DATA Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

ODA X5-2 Storage Architecture ASM Cluster File System (ACFS) data

reco

redo

flash

repo1..N

ASM Disk Groups +DATA, +RECO, +REDO, +FLASH

HDDs

Cache SSDs

Log SSDs

Disk

Disk Group

Volumes

Used For

HDD Outer Rings

+DATA

data

Database data files

HDD Outer Rings

+DATA

Repo1.. repoN

Shared Repository for VMs, VDisk

HDD Inner Rings

+RECO

reco

Database archive logs, RMAN backups (Fast Recovery Area)

HDD Inner Rings

+RECO

Repo1.. repoN

Shared Repository for VMs, VDisk

HDD Inner Rings

+RECO

cloudfs

Clustered file system – files that need to be accessed by either server node

SSD

+REDO

redo

Database redo logs

SSD

+FLASH

flash

Frequently accessed data

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Datenbanken auf ACFS • Neue 11.2.0.4 und 12.1.0.2 nonCDB Datenbanken werden automatisch in den ACFS Filesystemen erstellt. • 12.1.0.2 CDB Datenbanken auf einem eigenen ACFS Filesystem

• Da keine Performanceunterschiede auf der ODA zwischen ACFS und ASM festgestellt werden konnten ist ACFS der Default • Snapshots der DBs basieren auf ACFS • Benefits of Oracle ACFS

• Oracle Database Appliance: Steps to Migrate Non-CDB Database to ACFS Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

9

ODA 12.1.2.X verwendet ACFS Mount Point

Diskgroup

Verwendung

/u01/app/oracle/oradata/datastore

+REDO

Redo nonCDB ACFS DBs

/u02/app/oracle/oradata/datastore

+DATA

Datenfiles nonCDBs ACFS DBs

/u01/app/oracle/fast_recovery_area /datastore

+RECO

FRA für nonCDBs ACFS DBs

/cloudfs

+RECO

Cloud Filesystem

ODA Konfigurator

/odadatafs

+REDO

Internal

Für oakcli

Shared Repositories

+DATA | +RECO

Shared VM Repositories

ODA VP

CDB

+REDO +DATA +RECO

CDBs

CDBs haben eigene ACFS Filesysteme

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Anmerkung

10

Show DBStorage # oakcli show dbstorage All the DBs with DB TYPE as non-CDB share the same volumes DB_NAMES DB_TYPE -------- ------testdb CDB

Filesystem Size ----------------/u01/app/oracle/oradata/rdotestdb 6G /u02/app/oracle/oradata/dattestdb 100G /u01/app/oracle/fast_recovery_area/rcotestdb 19G

Used ----4.15G 4.27G 0.45G

proddb

/u01/app/oracle/oradata/datastore 58G /u02/app/oracle/oradata/datastore 11946G /u02/app/oracle/oradata/flashdata 558G /u01/app/oracle/fast_recovery_area/datastore 1935G

14.28G 5.67G 121.24G 10.22G

non-CDB

Available --------1.85G 95.73G 18.55G 43.72G 11940.33G 436.76G 1924.78G

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

AutoExt ------1G 10G 1G

DiskGroup --------REDO DATA RECO

5G 1194G 55G 193G

REDO DATA FLASH RECO

11

Show Filesystem # oakcli show fs Type Total Space ext3 55851M ext3 459M ext3 93866M acfs 12232704M acfs 102400M acfs 571392M acfs 51200M acfs 5120M acfs 1981440M acfs 19456M acfs 59392M acfs 6144M

Free Space 39668M 398M 62610M 12226899M 98024M 447238M 51022M 5033M 1970979M 18993M 44771M 1895M

Total DG Space 52428736M 52428736M 1526208M 8618304M 8618304M 8618304M 8618304M 763120M 763120M

Free DG Space 27335172M 27335172M 382932M 4425040M 4425040M 4425040M 4425040M 377728M 377728M

Diskgroup Mount Point / /boot /u01 DATA /u02/app/oracle/oradata/datastore DATA /u02/app/oracle/oradata/dattestdb FLASH /u02/app/oracle/oradata/flashdata RECO /cloudfs RECO /odadatafs RECO /u01/app/oracle/fast_recovery_area/datastore RECO /u01/app/oracle/fast_recovery_area/rcotestdb REDO /u01/app/oracle/oradata/datastore REDO /u01/app/oracle/oradata/rdotestdb

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

12

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

13

Neue Datenbank Templates Appliance Manager 12.1.2.2

• Einfachere Namenskonvention – odb_|

• Unterstützung der ODA X5-2 (72 CPU Kerne) • Funktionsweise: Wie bisher auch. Auf jeder ODA • Automatische Größenanpassung des ODA Flash Caches • Unterscheidung OLTP, DSS und InMemory • Option eine komplette Datenbank ins Flash zu legen

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Datenbank Größen X5-2 (OLTP) ODB_

01s

CPU_COUNT

01 2

02

04

06

12

24

36

4

8

12

24

48

72

SGA (GB)

2

4

8

16

24

48

64

128

PGA (GB)

1

2

4

8

12

24

48

64

400

800

1200

2400

4800

7200

192

192

Processes

200

Redo Log (GB)

1 12

2

Flash Cache

6

24

IOPS (1 Shelf)

42/4K

MB/s (1 Shelf)

83

167

# DBs

36

18

48

4 72

144

250/25K

500/50K

1,5K/150K

333

500

1000

3000

9

6

3

1

83/8K 167/16K

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

ODA Fast Flash Cache – Datenbank Buffer Cache  Nur auf der ODA: „Shared Flash Cache“  Cache im Shared Storage

 Besseren I/O Durchsatz  DBWR schreibt auf Flash und Disk gleichzeitig

 Leseoperationen werden aus dem Flash Cache bedient

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Flash Cache Hintergrund Informationen • INIT.ORA Parameter: NAME VALUE ---------------------------------------------------------------------------_cluster_flash_cache_slave_file /u02/app/oracle/oradata/flashdata/.ACFS/snaps/flashcache/proddb/flash2 db_flash_cache_file /u02/app/oracle/oradata/flashdata/.ACFS/snaps/flashcache/proddb/flash1 db_flash_cache_size 12G

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

17

Flash Cache – wann Sinnvoll? • Wenn Datenbank von mehr DB Block Buffers profitiert

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

18

Agenda 1

Oracle Database Appliance

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

19

Datenbank & VM Snapshots Node-0

App VM ‘Gold’

• ROW Snapshot

Node-1

App VM Snap

• Nur Meta-Daten werden gespeichert ( CREATE PLUGGABLE DATABASE ...... SNAPSHOT COPY

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

22

ODA Fast Files:  Verbessert Snapshot Performance (Datenbanken & VM)  Beschleunigt Filesystem, indem Filesystem Metadaten auf dem Redo SSDs gespeichert werden  Copy on write Snapshot Performance entspricht nativer Filesystem- bzw. Datenbank-Performance  Vorsicht: Filesystem muss nach Upgrade angelegt werden

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

ODA Fast Files Hintergrund: Accelerator Volume # acfsutil info fs /u02/app/oracle/oradata/datastore /u02/app/oracle/oradata/datastore ACFS Version: 12.1.0.2.0 primary volume: /dev/asm/datastore-485 metadata read I/O count: 61559 metadata write I/O count: 2043 total metadata bytes read: 32963584 ( 31.43 MB ) total metadata bytes written: 3237376 ( 3.08 MB ) accelerator volume: /dev/asm/acldatstore-205 accelerator version: 2 size: 51338280960 ( 47.81 GB ) free: 26104606720 ( 24.31 GB ) metadata read I/O count: 153960 metadata write I/O count: 5462 total metadata bytes read: 75374412288 ( 70.19 GB ) total metadata bytes written: 13384192 ( 12.76 MB ) allocation success since mount: 100 % number of snapshots: 3

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

24

ACFS Accelerator Volume • Accelerator Volume seid 12.1 (COMPATIBLE.ASM/ADVM 12.1.0.2) – Version 1: Größe 32 MB pro Knoten – Version 2 (ODA): 0,4% * Volume

• Default ODA: Volume in REDO Diskgroup • Accelerator Volume wird mit mkfs angelegt: # mkfs -t acfs -h mkfs.acfs: Create mkfs.acfs: Usage: mkfs.acfs: mkfs.acfs: mkfs.acfs:

an ACFS file system. [-h] [-v] [-f] [-n name] [-a ] [size] [-n name] - Name of file system to be created [-a ] - ACFS accelerator volume pathname - ACFS primary volume pathname

http://docs.oracle.com/database/121/OSTMG/GUID-458DC5C6-2218-4C6F-866B-1B9DAB7C278D.htm Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

25

Redo Diskgroup „Größen Problem“ asmcmd> lsdg Type Total_MB NORMAL 52428736 NORMAL 1526208 NORMAL 8618304 HIGH 763120

Free_MB 27335172 382932 4425040 377728

Req_mir_free_MB 3276796 381552 538644 381560

Usable_file_MB 12029188 690 1943198 -1277

Name DATA/ FLASH/ RECO/ REDO/

• Negativer Wert entsteht durch Accelerator Volumes

• REDO ist HIGH Redundancy – verkraftet Ausfall von 2 Platten! • Usable File MB ist der Platz der benötigt wird um Redundanz wieder aufzubauen.

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

26

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

27

Komplette Datenbank im Flash • Direkte Unterstützung im oakcli • Sinnvoll wenn Datenbank < 700GB (1,4 TB mit Storage Expansion) • Viele Full Tablescans der gesammten Datenbank

• Flash für andere Datenbanken nicht verwendet wird • Tipp: Flash Cache der anderen Datenbanken ausschalten. • Tipp: Besser InMemory verwenden und Memory Expansion kaufen

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

28

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

29

I/O Berechnung beim ODA Sizing

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

30

I/O Berechnung beim ODA Sizing Total (I/O Read/s - Log File I/O Read/s) + (Total I/O Write/s - Log File I/O Write/s) * Redundanz (892,35 {TOTAL Read: Reqs per sec} 2,15 {Log File Reads: Reqs per sec}) + (811,03 {TOTAL Writes: Reqs per sec} - 485,44 {Log File Writes: Reqs per sec}) * Redundanz = (892,35 - 2,15) + (811,03 - 485,44) * Redundanz = 890,2 + 325,59 * Redundanz Normal Redundancy: 890,2 + 325,59 * 2 = 1541,38 High Redundancy: 890,2 + 325,59 * 3 = 1866,97

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

31

Temp & Undo auf Flash • Temp und UNDO kann generell sinnvoll sein • Insbesondere, wenn Temp/UNDO bei den Tablespace Statistiken auftaucht • Vorsicht: UNDO braucht häufig viel Platz, Flash bietet nur 700 GB. (UNDO_RETENTION Time beachten)

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

32

Agenda 1

Oracle Database Appliance X5-2

2

Flash Usecase 1: Flash Cache

3

Flash Usecase 2: Fast Files

4

Flash Usecase 3: Komplette Datenbank

5

Flash Usecase 4: TEMP/UNDO

6

Flash Usecase 5: Bestimmte Tabellen Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

33

Einzelne Objekte im Flash • Objekte über AWR identifizieren (ggf. zusammen mit Entwickler) • Partitioning einsetzten für große Objekte • Advanced Data Optimization (ADO, Teil der Compression Option) – Heat Map verwenden – Storage Tiering für Objekte / Partitionen

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted

34

Heat Map für Segmente D OR

S ER

• Tabellen und Partitionen und ihre Verwendung

• Aktueller Ausschnitt aus

DBA_HEAT_MAP_SEG_HISTOGRAM

OWNER ---------SH SCOTT SCOTT SCOTT SCOTT SCOTT SCOTT

OBJECT_NAME ----------------------CUSTOMERS_PK DEPT EMP EMP EMP PK_EMP PK_EMP

TRACK_TIME ---------------25.06.2013 22:48 25.06.2013 12:48 26.06.2013 12:30 25.06.2013 12:48 24.06.2013 11:47 26.06.2013 22:30 25.06.2013 22:48

WRI --NO NO YES NO NO NO NO

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

FUL --NO YES YES YES YES NO NO

LOO -YES NO NO NO NO YES YES

Storage Tiering ALTER TABLE EMPLOYEE ILM ADD POLICY TIER TO O

S ER D R

1.

Tabellen wachsen => Policy komprimiert die Daten

2.

Tablespace mit Partitionen erreicht Tablespace Tierung Grenze

3.

Partitionen werden in einen anderen Tablespace (auf HDD) verlagert

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

ODA External Documents & Links ●

ODA Sizing Tipp:

https://blogs.oracle.com/dbacommunity_deutsch/entry/sizing_oracle_database_appliance_oda











Landing Page: http://www.oracle.com/us/products/database/database-appliance/index.html

Oracle Technology Network: http://www.oracle.com/technetwork/server-storage/engineered-systems/database-appliance/index.html Documentation Library: http://docs.oracle.com/cd/E22693_01/index.htm ODA Appliance Manager http://www.oracle.com/technetwork/server-storage/engineered-systems/database-appliance/overview/oda-configurator1928685.html Oracle Database Appliance - 12.1.2 and 2.X Supported ODA Versions & Known Issues (Doc ID 888888.1) https://support.oracle.com/epmos/faces/DocumentDisplay?id=888888.1

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

38