Aspekte der DB2 Administration

(1)

Aspekte der DB2 Administration

Manfred Päßler, IBM SW-Group [email protected]

(2)

Agenda

Probleme eines Admins

Möglichkeiten mit DB2 Boardmitteln (V8)

Speicherautomation in DB2 Viper (self tuning memory

management)

(3)

Die Sorgen eines DB Admins...

Laufen alle Anwendungen, die sich auf meine Datenbanken

stützen, ok?

Kann ich den Status dokumentieren?

Wie löse ich das Problem????? Oder kann die Datenbank selbst

etwas lösen???

Habe ich für Datencrashs genügend vorgesorgt (->Backups)

Kann ich schnell genug einen möglichst zeitnahen Zustand wieder

herstellen (->Recovery)?

Schaffe ich „Aufräumarbeiten“ (runststats, reorgs) in dem schmalen

Wartungsfenster?

Was für Auswirkungen haben zusätzliche Anwendungen X, Y auf

meine Datenbanken (Plattenplatz, Performance, Hauptspeicher)?

Werde ich immer rechtzeitig informiert (möglichst schon so früh,

dass ich gegensteuern kann)?

(4)

Welche Tools kommen mit DB2?

Minimierung von Ausfallzeiten

Benutzungsergonomie

Monitoring

(5)

Minimierung von geplanten Ausfallzeiten

DB/System Changes

-

Dynamic Space Management

-

Software upgrades

-

Schema changes ( enhanced “alter

table” in Viper)

Data Changes

-

Guaranteed Clustering (MDC)

-

Self-Throttling Utilities

-

Self Managing and Resource Tuning

(SMART 1))

-

Self Tuning Memory Management

(SMART 2 in Viper)

System Maintenance

-

Online & Incremental Backup

-

Online Index create and

reorg

-

Online Table reorg

-

Flash Copy Support

(split/mirror backup)

-

Partition- & Tablespace-level

backup

(6)

Minimierung von ungeplanten Ausfallzeiten

System Failure

-

Cluster Failover Support

-

Stand-by Database

-

Log mirroring

-

Fast Recovery (e.g. parallel

restore)

-

Built-in redundancy / can't be

turned off

–

Consistency bits

–

Automatic mirroring

of critical data files

Human/Application Errors

–

PIT – Point in Time

Recovery

–

Dropped table recovery

–

Unlimited active log space

Disaster Recovery

–

Replication

–

Log shipping

(7)

Beispiel: HA on Linux with DB V8.1

2 x 2.4GHz Intel XEON 2GB RAM

IBM Total Storage FAStT700 456GB total storage QLogic Fibre Channel Host Adapter QLogic Fibre Channel Host Adapter

xSeries 335

DB2 V8.1 SUSE 8.1 Professional Heartbeat software from the High Availability Linux Project

Active

Idle

(8)

0 500 1000 1500 2000 2500 3000 3500 4000 0 200 400 600 800₁₀00 ₁₂00 ₁₄00 ₁₆0 0 18 00 2000 2200 24002600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 84008600 8800 9000 9200 9400 9600

High Availability Results – 1M Subscribers:

Time, secs

Th

ro

ug

hp

ut

-tx

n/

se

c

9s failover

80% read

20% write

(9)

HADR für 24x7h Betrieb

Mit HADR kann ein Rechner sehr schnell bei einem Ausfall von einem anderen Rechner übernommen werden, mit NULL Daten und Transaktionsverlusten! Geht der

primary Rechner wieder online, erfolgt automatisch eine Re-Syncronisierung (im Unterschied z.B. von Oracle‘s RAC!!!). Client Re-Routing findet den Secondary Server automatisch.

Übernahmezeit (Entdecken des

Ausfalls des Primary, Umschalten auf Secondary, bereinigen der Logs,

betriebsbereit) <15 sek.

SW-Wartung (Aufspielen neuer

Patches OS+DB) im laufenden Betrieb möglich!

HTTP & App.

(10)

Benutzungsergonomie

Information Center

Automatic Computing

Design Advisor

Renaming tables and tablespaces

Restoring to different code page

PIT to local time

(11)

Index Advisor

MQT Advisor

MDC Advisor

Partitioning Advisor

Combined Advisor

_DB2 Optimizer SQL Workload Database Structure Indexes/MQTs/MDCs Designed by DB2 for YOUR

Environment

(12)

Design Advisor Preliminary Results

Performance improvement 0% 20% 40% 60% 80% 100% 120%

Baseline New design

R el at iv e W or kl oa d ex ec ut io n tim e 84.54% time reduction 6.46x faster DB2 Design Advisor Recommendation summary: 20 new indexes 6 new MDC dimensions 4 new partitioning keys 2 new MQTs

(13)

Monitoring

snapshot and event monitor table functions (#32)

select *

from table(snapshot_database('db2ii', -1)) as t

update monitor switches using statement on;

select

total_usr_cpu_time as totuser, total_sys_cpu_time as totsys, total_exec_time as totexec,

substr(stmt_text,1,100)as stmt_txt

from table (snapshot_dyn_sql('db2ii',-1)) as stmts order by totexec desc;

Health Monitor Activity Monitor

(14)

Self Tuning Memory Management in Viper

Don’t care about memory

tuning…

(15)

DB2’s database memory model – shared memory

All memory heaps are contained within the database shared memory set

On non-Windows (except 64-bit AIX) platforms the set memory is all

allocated at database startup and can not grow beyond its allocated size

On Windows and 64bit AIX the memory is allocated at startup but can grow or shrink as needed

On 32 bit platforms set size is limited On 64 bit platforms set size virtually unlimited

(16)

In version 8 the best memory tuning technique is to use the

configuration advisor

autoconfigure CLP command

Sets all important memory parameters Tunes most workloads very well

With OLTP workloads it can tune as well as performance experts

Considerations with the advisor results

Configuration is static

If workload changes over time configuration may become out of date

Configuration is dependant on DBA description of workload

If workload isn’t as described, configuration may be less than optimal

(17)

Memory Tuning in Viper - STMM

Viper will introduce a revolutionary memory tuning system called the “Self Tuning Memory Manager” (STMM)

Works on main database memory parameters

Sort, locklist, package cache, buffer pools, and total database memory Hands-off online memory tuning

Requires no DBA intervention

Senses the underlying workload and tunes the memory based on need Can adapt quickly to workload shifts that require memory redistribution Adapts tuning frequency based on workload

Is able to tune multiple databases and instances on the same box at the same time

Works in non-partitioned and in partitioned (DPF) environments (but all partitions have to be configured similar)

(18)

STMM and the buffer pools

Trades memory between buffer pools based on relative need

New metrics determine where memory is most needed such that

total system time is reduced

Zero, one or more buffer pools can be set to AUTOMATIC

In newly created Viper databases, all buffer pools default to

Decreasing the buffer pools can take a lot of time

Must write out all dirty pages in memory being freed If pages are in use the resize may wait on locks

A large percentage of tuning time could be spent on alter buffer pools

Not necessarily a concern, just something to keep in mind

STMM can be set off individually for each bufferpool

(19)

STMM and DATABASE_MEMORY

STMM tunes DATABASE_MEMORY if it is set to AUTOMATIC or a

numeric value

If set to AUTOMATIC (use it in AIX and Windows only), memory is taken from, and returned to, the OS if required by the database

DBA need not know how much memory to allocate to DB2 This is the default for newly created Viper databases

If set to a numeric value, memory is given to AUTOMATIC heaps up to the numeric value

Allows DBA to set total memory consumption for the database DB2 will then distribute the memory to optimize performance

If set to COMPUTED, no DATABASE_MEMORY tuning will occur

When database starts, memory requirements are computed based on the

heap configuration

Once the database starts, the database shared memory set is allocated based on the computation

(20)

Scenarios where STMM shines

Bufferpool Tuning

Difficult to tune memory when there are multiple buffer pools

Works so well that STMM is being used to tune benchmark systems in house

Varying workloads

Periods with high transaction throughput

Periods with long running transactions or online utilities

Unknown memory requirements

Tunes quickly enough to bring production systems from out of the box configuration to optimal in an hour or less

Performs several weeks of manual (trial and error) tuning every hour Will stop tuning automatically when it reaches optimal configuration

(21)

STMM in action – Against default and benchmark

configurations

63796 16713 63302 10000 20000 30000 40000 50000 60000 70000 Tr an sa ct io ns P er M in ut e

(22)

STMM in action – Dropping an important index

10 agents executing queries with “order by” clause At first, queries use indexes to avoid sorting

After several iterations some of the indexes are dropped

Simulates DBA error in dropping vital indexes

Lack of indexes forces sorts to be performed

Dramatically increases the demand on the sort memory

With only manual tuning, workload will likely get much slower STMM should be able to alleviate some of the burden…

(23)

STMM in action – Dropping an important index

TPCH Query 21 - After drop index - Average times for the 10 streams

0 1000 2000 3000 4000 5000 6000 7000 Ti m e in s ec on ds

(24)

Ergänzen der mitgelieferten DB2 Funktionalitäten mit

Tools

Kontinuierliches Überwachen mit Historisierung

Erkennen von Trends

Dokumentieren des Systemzustandes

Mehr Hilfen, was zu tun ist

Rücksetzen von „kleinen“ Datenveränderungen

Schnelles Kopieren von Daten auch aus Offline-Medien

....

(25)

DB2 Tools - Overview

Generate Test Data On Demand

DB2 Test Database Generator

Quering and delivering DB2 (MP and z/OS) data from internet browser. Database Workbench for DB2 on the net

DB2 Web Query Tool

Rapid programming of form based DB2 applications (running on Windows or browser)

Easy editing of DB2 Data

DB2 Table Editor

FAST unload of Database/TS/Table data into various formats.

Unloads from backups also

DB2 High Performance Unload

FAST and intelligent Recovery of Databases/Tablespaces/Tables

Recovery of Database/Tablespace/Table into other DB2 database

DB2 Recovery Expert

Monitoring and Tuning of multible DB2 Systems from one common GUI

(26)

Learn more

DB2 Viper

http://ibm.com/db2/viper

DB2 UDB Homesite

http://www-306.ibm.com/software/data/db2/udb/

A look at the new functions in DB2 Universal Database V8.2

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0404zikopoulos/index.html

DB2 UDB Version 8.2 and autonomic computing

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0406zikopoulos/index.html

An Overview of High Availability and Disaster Recovery for DB2 UDB

http://www-128.ibm.com/developerworks/db2/library/techarticle/0304wright/0304wright.html

Automating DB2 HADR Failover on Linux using Tivoli System Automation

ftp://ftp.software.ibm.com/software/data/db2/linux/tsa_hadr.pdf

Delivering Ten Second Failover for High Volume Transactional Telco Applications with IBM DB2 Universal Delivering Ten Second Failover for High Volume

Transactional Telco

(27)

DM Tools Homepage

http://www.ibm.com/software/data/tools

DB2 Performance Expert for z/OS V2

http://www.redbooks.ibm.com/abstracts/sg246867.html?Open

DB2 Performance Expert for MP

Simplify Performance Management and tuning with DB2 Performance Expert, Part I+II

http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0409schuetz/

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0409schuetz2/index.html

Web Query Tool V1.2, SG24-6832-00

DB2 Table Editor Tool Version 4.2, SG24-6833-00

DB2 Recovery Expert for Multi Platform, SG24-6421-00

DB2 Test Database Generator

http://www-306.ibm.com/software/data/db2imstools/db2tools/db2tdbg/

Aspekte der DB2 Administration