Verwaltung sehr großer Datenmengen mit Sybase 15dbst/material/20060628_118_bittner.pdf · •Performance & Effektivität •Skalierfähigkeit(Daten & Anwender) OLTP=einfache Query-verarbeitet

Verwaltung sehr großer Datenmengenmit Sybase 15

Jürgen BittnerSQL GmbH

Deckblatt

DB-Stammtisch 06/2006, 2

Wie groß sind VLDB ? - 2005


•Performance & Effektivität•Skalierfähigkeit(Daten & Anwender)

OLTP=einfache Query-verarbeitet 10s von Rows-1000s von Rows pro Sekunde-einfache (½-Seite) SQL Statements

DSS=komplexe Query-verarbeitet M(B)ILLIONS von Rows-Millionen von Rows pro Sekunde-komplexe (10-Seiten) SQL Statements

spaltenbasiertes IQ Multiplex(entworfen vor wenigen Jahren für DSS)

zeilenbasiertes DBMS(entworfen vor +20 Jahren für OLTP) DB2, Oracle, MS SQL, ASE, NCR etc.

2x-1000x

2x-1000x

DSS versus OLTP Workload


Verwaltung großer Datenmengen mit

• Adaptive Server IQ

– Neues zu IQ 12.6

• Adaptive Server Enterprise 15 – Konzepte und Funktionalitäten für große Datenmengen

– Partitionen– Berechnete Spalten und Funktions-basierter Index– Verbesserungen der Anfrage-Verarbeitung

• Arbeitstabellen• Hash-Join

– Optimierer-Steuerung


Der traditionelle RDBMS-Ansatz

Mon Einr Typ Land Prod Abs

0105 32 G SA Werne 12

0105 36 G MV Becks 9

0105 38 G SA Radeb 28

0105 41 K NS Jever 11

0105 43 G SA Radeb 9

0105 46 G BY Paula 3

0105 47 M NW Dortm 70

0105 49 K SA Lands 12

Berechne den durchschnittlichen Absatz von „Radeberger“in Gastronomie-Einrichtungen in Sachsen je Monat

• Benutze einen Index wenn verfügbar- benötigt normalerweise Table Scan

• Gehe zu den ausgewählten Datenseiten und addiere die Zahlen- Zufällige Verteilung der Daten führt dazu, daß fast alle Seiten gelesen werden müssen.

- Auf jeder Seite müssen alle - auch die irrelevanten - Daten gelesen werden.

Traditioneller Ansatz:

SELECT AVG (Abs), SUM(Abs)/AnzGSA/36

FROM Absatz,

(SELECT COUNT(DISTINCT Einr) AS AnzGSA

FROM Absatz

WHERE Land = ‘SA‘ AND

Typ = ‘G‘)

WHERE Land = ‘SA‘ AND

Typ = ‘G‘ AND

Prod = ‘Radeb‘


Sybase IQ: Es werden nur die relevanten Spalten gelesen

Vorteile:Ohne weitere Techniken kann IQ den Disk-I/O sehr stark reduzieren

Vertikale Partitionierung

Mon Einr Typ Land Prod Abs

9805 32 G SA Werne 12

9805 36 G MV Becks 9

9805 38 G SA Radeb 28

9805 41 K NS Jever 11

9805 43 G SA Radeb 9

9805 46 G BY Paula 3

9805 47 M NW Dortm 70

9805 49 K SA Lands 12

Berechne den durchschnittlichen Absatz von Radebergerin Gastronomie-Einrichtungen in Sachsen


Sybase IQ Advanced Bit-Mapped Indizes

25 Jahre bewährte Technologie (z.B. Model 204)Vor Sybase IQ nur für Daten mit geringer Kardinalität(< 100 Werte) verwendbar Sybase IQ erweitert diesen Wert auf > 1.000 Werte durch die Kombination von Bitmaps und Komprimierung.Ohne weitere intelligente Indizes allerdings von begrenztem Nutzen, da nur wenige Queries nur Bitmaps brauchen

Bitmap Index für Land

row-id BY MV NS NW SA1 0 0 0 0 12 0 1 0 0 03 0 0 0 0 14 0 0 1 0 05 0 0 0 0 16 1 0 0 0 07 0 0 0 1 08 0 0 0 0 1

LandSAMVSANSSABYNWSA


“Wieviele Männer sind in Kalifornien nicht versichert?“

GeschlechtMMWMMW

800 Bytes/Satz

20MSätze

StaatCACANYCAMACT

RDBMSRDBMSVersichert

JNJNJN

800 Bytes x 20M 16K Seite = 1.000,000 I/Os

Verarbeitet grosse Mengen nicht benötigter DatenErfordert oft “Full Table Scan”

M CA JM CA NW NY JM CA N

12

43

Geschlecht Staat Versichert

= 2+ +1101

0101

1101

20MBits

20M Bits x 3 Spalten / 816K Seite

= 470 I/Os

Dramatische I/O-Reduzierung


OLTP DBMS vs. IQ-Multiplex engine

1 2 3 4 ….. 100 Db page64KB-512KB 1 2 3 4 …. 100

IQ MultiplexSQL: Create table ABCyellow, blue, red..magenta

SQL:Select sum (red) from ABC

OLTP-centricDBMS

Db page2-32KB

Small size, random I/O gets less than 1MB/s-3MB/s from single disk

IQM large, semi-sequential I/O gets 10MB/s-30MB/s from single disk

1: Standard SQL language: (no patents, trivial migration and retraining)2: Column structure ( patented, invisible to users )

-Designed for DW: Ad-Hoc/complex queries (no OLTP tradeoffs, never FTS)- 90%-99% reduction in I/O compared with traditional_DBMS-based DW-45,000 columns/table => enables complexity AND flexibility, low RAM use

3: Indexing: Bit-wise, Word, Bitmap etc. (patented, invisible to users )-Speed: designed for DSS=>2x-1,000x faster than OLTP-centric DBMS-Data-driven (vs “one-size-fits-all”, I.e. B-tree)-low-cost(=no index “explosion”): index every column (some multiple times)

4: Data Compression ( patented, invisible to users )-Additional reduction of DB size AND I/O by 30-80% (typ); improves security-IQM DB size(fully indexed) : 75% of raw data (typ); IQM as an ARCHIVE?-OLTP-centric DB size is 3x-10x of raw data size =>4x-15x more disk than IQM-small IQM size & low I/O=>fast/low_cost Backup/R, HA and Disaster Site site

5: IQ Multiplex-Shared-disk (non-partitioned data) with 97% multi-node scalability-ZERO inter-node interference => Architecture with Built-in High Availability-Scalability and Availability beyond MPP and pure-SMP+HA solutions


Administration der Indexstrukturen

Indexerstellung zur Designzeit

• Keine Datenspeicherung• Kardinalität• Datentyp

Keine Pflege der Indexstatistiken

Indextypen Sybase IQ


Index Advisor

• Vorschläge von Indextypen für

– Single Column Predicate (e.g. HG/LF, Date)– Join Column– Subquery Predicate– Group By Column– Two Columns compared in Same Table (CMP index)

• Erkennt ungeeignete Index-Definitionen


IQ Multiplex 12.5 (und davor)

IQ Main Store

Developer Tables

Base Tables

1 Sybase IQ Write Server N Sybase IQ Query Server

DBAs

Analysts

Developers

ETL Processes

WEB Access

Query Only UsersAnalyst Tables


IQ Multiplex 12.6 w/ Local Store

N Sybase IQQuery Only Server

Base Tables

1 Sybase IQ Write Server

DBAs

ETL Processes

WEB Access

Query Only Users

Analysts

Developers

IQ Main Store DeveloperTables

M Query Serverwith a

Local Store

Analysis Area

Local Store


IQ Multiplex – Local Store für Query Server

• Query Server ermöglichen einen 'Local IQ Store' , der es den Benutzern erlaubt, permanente Objekte in der Datenbank anzulegen

– Unterstützung für Benutzer wie Analysten und Entwickler, die ihreeigenen Tabellen oder Prozeduren kreieren wollen

• Objekte, die im Local Store angelegt wurden, werden persistent nacheinem Recycle des Query Servers

• Diese Objekte sind nur für die Benutzer sichtbar, die mit dem Query Server verbunden sind, zu dem der Local IQ Store gehört

– Objekt-Zugriff gemäß Standard Rechten

• Ein Backup des IQ Main Store realisiert NICHT ein Backup im Local IQ Stores

– Local Store backup muß separat ausgeführt werden– Gleiches gilt für die Konsistenz-Prüfung der Daten im Local Store


SUN-Sybase Referenz DWH - Weltgrößtes DWH in Menlo Park Business Case Telco

•2002

•179 Milliarden Zeilen

•48 TB Inputdaten

•Kompression der Inputdaten v. 48 TB auf 22 TB

•Ladezeit: 160 Millionen CDRs in < 1 h

•2004

•1 Billion Zeilen

•155 TB Inputdaten

•Kompression der Inputdaten v. 155 TB auf 55 TB

•Ladezeit: bis zu 1,8 Mrd. Datensätze pro Stunde


2005: World’s TopTenCompany DB size Input data(GB) DBMS Syst Arch. DBMS vendor server storage

AT&T 26,269 94,305 Daytona SMP AT&T Sun SunAmazon.com 13,001 34,219 France Telecom 29,232 29,735 Health Insurance Review Agency 11,942 29,299 IQ MPP/Cluster Sybase HP EMCBarclays Bank 6,408 24,756 Teradata MPP/Cluster Teradata NCR LSIFedEx Services 9,981 14,745 Teradata MPP/Cluster Teradata NCR EMCSamsung Card. 7,684 14,567 IQ MPP/Cluster Sybase HP HPKmart 12,592 13,874 Teradata MPP/Cluster Teradata NCR LSICho-Hung Bank 3,361 12,350 IQ MPP/Cluster Sybase Sun HitachiLG Card 6,336 12,313 IQ MPP/Cluster Sybase Sun EMC

Company Input data(GB) DB size DBMS Syst Arch. DBMS vendor server storageFrance Telecom 29,735 29,232 SMP AT&T 94,305 26,269 Daytona SMP AT&T Sun SunSBC NULL 24,805 Teradata MPP/Cluster Teradata NCR LSIAnonymous 8,591 16,191 DB2 MPP/Cluster IBM IBM IBMAmazon.com 34,219 13,001 Oracle SMP Oracle HP HPKmart 13,874 12,592 Teradata MPP/Cluster Teradata NCR LSIClaria Corporation 4,361 12,100 Oracle SMP Oracle Sun HitachiHealth Insurance Review Agency 29,299 11,942 IQ MPP/Cluster Sybase HP HitachiFedEx Services 14,745 9,981 Teradata MPP/Cluster Teradata NCR EMCVodafone D2 GmbH NULL 9,108 Teradata MPP/Cluster Teradata NCR LSI

Company rows (M) DBMS Syst Arch. DBMS vendor server storageAT&T 496,041 Daytona SMP AT&T Sun Sun

Nielsen Media Research 380,000 IQ MPP/Cluster Sybase Sun SunFrance Telecom 156,788 Oracle SMP Oracle HP HPSBC 144,417 Teradata MPP/Cluster Teradata NCR LSIKmart 133,079 Teradata MPP/Cluster Teradata NCR LSIFedEx Services 89,343 Teradata MPP/Cluster Teradata NCR EMCKroger 77,837 DB2 MPP/Cluster IBM IBM IBMLG Card 74,703 IQ SMP Sybase Sun EMCHealth Insurance Review Agency 72,093 IQ MPP/Cluster Sybase HP HitachiAT&T Wireless Services 64,534 Teradata MPP/Cluster Teradata NCR NCR

Company input size GB rows (M) DBMS Syst Arch. DBMS vendor server storageComScore Networks Inc. 14,464 61,361 IQ MPP/Cluster Sybase HP EMCComScore Networks Inc. 19,604 44,195 IQ MPP/Cluster Sybase Dell EMCComScore Networks Inc. 14,636 40,523 IQ MPP/Cluster Sybase Dell EMCOrdina 6,402SQL Server SMP Microsoft IBM IBMArclight Systems LLC 6,246SQL Server SMP Microsoft IBM EMCEdgars Consolidated Stores 5,543SQL Server SMP Microsoft IBM IBMHewlett Packard Company 3,704 Oracle SMP Oracle HP HPGomez 3,418SQL Server SMP Microsoft HP otherDataQuick 3,273SQL Server SMP Microsoft Dell EMCNAREX, Inc 3,228SQL Server SMP Microsoft Dell Dell

Company input size GB DB size DBMS Syst Arch. DBMS vendor server storageComScore Networks Inc. 19,604 8,852 IQ MPP/Cluster Sybase HP EMCComScore Networks Inc. 14,464 7,458 IQ MPP/Cluster Sybase Dell EMCComScore Networks Inc. 14,636 7,336 IQ MPP/Cluster Sybase Dell EMCHewlett Packard Company 2,165 Oracle SMP Oracle HP HPHewlett Packard Company 1,755 Oracle SMP Oracle HP HPArclight Systems LLC 1,640SQL Server SMP Microsoft IBM IBM

(exploded) DB SIZE Windows

( Exploded ) DB Size, All Environments & UNIX

Input DATA Size, All Environments & UNIX

DB ROWS All Environments & UNIX

DB ROWS Windows

www.wintercorp.com

29 TB

15 TB

12 TB

380 B

74,7 B

72 B

China Telecom :134 B

IRS :15 B Citadel :83 B

61 B

SKCC :170 B


Neue Limits

ASE 15.0 very large storage system Vor ASE 15

Maximale device Anzahl = 231 (2 Billion) 256

Maximale device Größe = 4TB 32 GB

Datenbanken/Server = 32 767 (noch)

Das maximale Speichervolumen:

Datenbank-Größe:

231 pages * 16KB pg = 32 TB 256 * 32 GB = 8 TB

Theoretisches Speichervolumen eines Servers:

32,767 DB’s * 32 TB = 1 EB (exabyte) = 1,048,544 TB


Überblick zum Partitionieren

• Große Tabellen benötigen entsprechend großeRessourcen:

– Table Scans benötigen viele E/A– Verwaltungsaufgaben benötigen viel Zeit – und viele Sperren– OLTP und DSS behindern sich gegenseitig

• Durch die Partitionen werden die Tabellen in Teilmengenzerlegt:

– Die Daten jeder Partition können unabhängig verarbeitet werden– Die Bearbeitung kann parallel erfolgen– Administrative Aufgaben können gezielt auf speziellen Partitionen

erfolgen


Vor ASE 15

• Partitionieren durch Segmente

• Nur die Tabellen (nicht die Indexe) sind partitioniert

• Primäre Ziele

• Reduzieren der last page Konflikte• Parallelisierte query-Verarbeitung• Parallelisiertes dbcc checkstorage• Parallelisiertes create index


ASE 15 Semantische Partitioinierung

• Partitionierte Tabellen und Indexe

– Partition types in ASE 15.x:• Range • List • Hash• Round-robin (ersetzt Segment)

– Partition maintenance• Add oder Alter eine oder mehrere partitions

• Globale und lokale Indexe auf partitions

– Clustered/non-clustered indexes

• Verbesserung der query-Verarbeitung

– Optimizer und execution support (Parallelisieren & Eliminieren)

• Partition-basierte Wartung

– Update statistics auf einer oder allen partitions– Truncate, reorg, dbcc, bcp (out) partition


Range Partition

• Es werden Grenzwerte für jede Partition festgelegt:

– Die Werte in den Datensätzen sind kleiner oder gleich diesemGrenzwert

• Günstige Anwendung bei:

– Großen Tabellen in Anwendungen mit hohen Anforderungen an die Performance.

• “ Gleitende Sichten” auf die Daten sind möglich.

– Datenmengen können ohne Auswirkungen auf andere Partitionenhinzugefügt oder gelöscht werden.

• Die Balance der Partition hängt von den definierten Grenzwerten ab.


Range Partition – Syntax und Beispiel

create table tablename(column definitions)

partition by range (columnlist)(partitionlabel values


Range Partition

seg1

1/3/051/3/051/4/051/5/051/5/051/5/05

q1

seg2

4/1/054/2/054/3/054/2/05

q2

seg3

7/5/057/5/057/6/057/7/057/8/057/8/05

q3

seg4

10/1/0510/2/0510/3/0510/4/0511/2/0512/2/05

q4sp_helpartition:

name type partition_type partitions partition_keys----- ---------- -------------- ----------- --------------sales base table range 4 salesdatepartition_name partition_id pages segment-------------- ------------ ----------- -------q1 1024003648 452 seg1q2 1040003705 331 seg2q3 1056003762 422 seg3q4 1072003819 628 seg4


List Partition

• Der Nutzer definiert Listen mit Werten für jede Partition:

– Mindestens ein Wert muss jeder Partition zugewiesen werden– Jeder Wert darf nur einer Partition zugewiesen werden– Die Werte sind in der Partition nicht sortiert

• Günstige Anwendung bei wenigen unterschiedlichenSchlüsselwerten mit häufiger Wiederholung.

• Die Balance der Partition hängt von den zugeordnetenWerten ab.


List Partition – Syntax und Beispiel

create table tablename(column definitions)

partition by list (column name)([partition label] values (constant[,...])[on segmentname ]... )

create table customers

(cust_id char(10) not null,cust_name varchar(30) not null,state char(2) not null,

phone char(10) not null)partition by list (state)(west values ('CA', 'OR', 'WA') on seg1,east values ('NY', 'NJ') on seg2


List Partition

sp_helpartition:

name type partition_type partitions partition_keys

---------- ---------- -------------- ---------- --------------

cust_table base table list 2 statepartition_name partition_id pages segment

-------------- ------------ ----------- -------west 864003078 62 seg1east 880003135 41 seg2Partition_Conditions-------------------------VALUES ('CA', 'OR', 'WA')VALUES ('NY', 'NJ')

customers

NYNYNJNY

seg2seg1

CACAORWACAOR

west east


Hash Partition

• Adaptive Server verwendet eine Hash Funktion um eineentsprechende Partition anzusteuern

– Basierend auf bis zu 31 Schlüsselspalten

• Günstige Anwendung bei:

– DSS Operationen– Große Tabellen mit vielen Partitionen– Tabelle mit unsortierten Daten–

• Die Balance der Partitionen ist von der Anzahl derdoppelten Schlüsselwerte abhängig.


Hash Partition – Syntax und Beispiel

create table lineitem

( l_orderkey int not null,l_partkey char(10) not null,l_suppkey char(4) not null,

l_linenumber int not null,l_quantity int not null)

partition by hash (l_orderkey, l_linenumber)(litem_hash1 on seg1,litem_hash2 on seg2,litem_hash3 on seg3)

create table tablename(column definitions)partition by hash (column list){(partitionlabel [on segmentname] [,...])| number_of_partitions [on (segmentname [,...])]}


Round Robin Partition

• Es sind keine Angaben zur Aufteilung notwendig

– Die Sätze werden den Partitions “rotierend” zugewiesen– Die Sätze sind in den Partitionen unsortiert– Ermöglicht eine hohe Parallelität bei mehreren Einfügepunkten

• Diese Methode ist annähernd vergleichbar mit derArbeitsweise von 1 Partition/ Segment der älteren ASE Versionen

• Sichert die Kompatibilität mit älteren Versionen

• Die Partitionen sind automatisch in guter Balance (Löschen ?!)


Risiko Partition Key Update

create table t (status int, other_cols ... )partition by list (status) (ptn1 values (1), ptn2 values (2), ptn3 values (3))

ptn1

ptn2

ptn3 3

1

2

update t set a = 1 where a = 3

select * from tErgebnis:- Nur Zeilen mit 2!- Zeilen mit 3 und 1 fehlen!


Partitionierung des Index

• Verbesserte Parallelität durch mehrere Eintrittspunkte• Reduzierte root page contention

• Index size ergibt sich aus der Zeilenanzahl in jeder Partition• Für kleinere Partitionen werden weniger index pages durchsucht

bzw. traversiert

Vor ASE 15 nicht partitionierter Index Lokaler Index auf partitionierter Tabelle


Globale Indizes

• Dieser Indextyp enthält die Daten aller Partitionen derTabelle.

• Bei “range”, “hash” oder “list” Partitionierung muß derIndex “nonclustered” sein.

• Bei “round robin” Partitionierung sind Clustered oderNonclustered Indizes möglich.


Non-clustered Global Index

Create unique index ci_nkey_ckey on customer(c_nationkey,c_custkey)on segment4


Lokale Indizes

• Jede Partition hat ihre eigene Indexstruktur

• Kann clustered oder nonclustered sein

• Bei einem lokalen “clustered index” sind die Daten innerhalb der Partition sortiert.

• Wenn ein Index lokal, unique und clustered ist:

– müssen die Index- alle Partitionspalten enthalten– muß die Reihenfolge der Index- der der Partitionspalten entsprechen

• Lokale Indizes mit den Eigenschaften “Unique clustered” sind nichtmöglich bei Tabellen die “round robin” partitioniert sind (mit mehr als einerPartition).


Non-clustered Local Index

Create unique index ci_nkey_ckey on customer(c_nationkey,c_custkey)on segment4 local index


Clustered (Local) Index

• Customer-Tabelle ist range-partitioniert auf der Spalte c_custkey

Create unique clustered index ci_nkey_ckey on customer(c_custkey, c_nationkey)


Zugriffsplan - Beispiel


Geringerer Einfluß von Administrationsarbeiten

• Folgende Funktionen können auf einzelnen Partitionenausgeführt werden:

– reorg rebuild– update statistics– truncate table– dbcc– bcp in / out

• Mit der richtigen Plazierung der Partitionen auf verschiedene physische Device sind reduzierteWartungszeiten möglich.


Computed Column Index und Function Based Index

• Berechnete Spalten

– Tabellenspalte wird als Ausdruck definiert– Ausdrücke können Spalten der gleichen Zeile referenzieren– Ausdrücke können Funktionen, arithmetische Operatoren, case Ausdrücke,

Globale Variable, Java Objekte, Pfadnamen oder andere Ausdrücke enthalten– Materialisiert oder nicht materialisiert (virtuelle Spalte)– Determiniert oder nicht determiniert

create table [database.[owner].] table_name(column name

{datatype | {compute | as} expression[materialized | not materialized]} ...

• Index auf berechneten Spalten

– Eine oder mehrere Spalten des Schlüssels sind berechnete materialisierte Spalten

• Funktionsbasierter Index

– Ein Index mit einem oder mehreren determinierten Ausdrücken anstelle von Spalten

create table rental_not_materialized(cust_id int, start_date as getdate(), last_change_dt datetime )

Insert into rental_not_materialized (cust_id, last_change_dt)Values (1,getdate())

Select * from rental_not_materialized

cust_id start_date last_change_dt-------- ------------------- --------------

1 Mar 30 2005 4:00PM Mar 16 2005 3:14PM

create table rental_materialized(cust_id int, start_date as getdate()materialized, last_change_dt datetime)

Insert into rental_materialized (cust_id, last_change_dt)Values (1,getdate())

Select * from rental_materialized

cust_id start_date last_change_dt-------- ------------------- --------------

1 Mar 16 2005 3:14PM Mar 16 2005 3:14PM

create [unique] [nonclustered] index index_nameon [owner.]table_name(expression [asc | desc][,expression [asc | desc ]...


Function Based Index - Beispiel

– 100,000-Zeilen-Tabelle; Index auf (a*10)

select * from t3 where a*10 = 76543210

– Vor ASE 15: 2-3 Sekunden; 16,000 I/Os

– In ASE 15.0: < 50 Millisekunden; 3 I/Os


Computed Column Index und Function Based Index

• Index auf berechneten Spalten

• Funktionsbasierter Index

alter table ordersadd up11 as unit*price*1.1 materializedgocreate index up11_ix on orders (up11)go

create index ix1 on orders (unit*price*1.1)

select unit*price*1.1 from orders where up11 > 200

benutzt Index auf up11, nicht auf (unit*price*1.1)

select unit*price*1.1 from orders where unit*price*1.1 > 200

benutzt Index auf (unit*price*1.1), nicht auf up11


Weniger E/A für Arbeitstabellen

• Beispiel:

select count(*), type from titles group by typeorder by type

• Vor Adaptive Server 15.0:

– Die Arbeitstabelle wird physisch angelegt– 624 I/O auf der Tabelle und 29 I/O auf der Arbeitstabelle

• Adaptive Server 15.0

– Die Arbeitstabelle wird logisch angelegt– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle


Weniger E/A bei Arbeitstabellen

• Beispiel:

select sum(total_sales), max(price), type, pub_idfrom titlesgroup by type, pub_id


– Eine Arbeitstabelle für group by und die Aggregate – 624 I/O auf der Tabelle titles, 8056 I/O auf der Arbeitstabelle

• Adaptive Server 15.0 :

– Verwendung der “hash based” Gruppierung im Speicher– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle


Weniger E/A bei Arbeitstabellen

•Vor ASE 15.0:

STEP 1 The type of query is SELECT (into Worktable1). GROUP BY FROM TABLE titles ...Table Scan ...TO TABLE Worktable1.

STEP 2 The type of query is SELECT.

FROM TABLE Worktable1 ...

Table: titles scan count 1, logical reads: (total=624) ...Table: Worktable1 scan count 1, logical reads: (total=8056) ...

• ASE 15.0:

The type of query is SELECTROOT:EMIT Operator ...| Using Worktable1 for internal storage. | |SCAN Operator | | FROM TABLE | | titles | | Table Scan.

Table: titles scan count 1, logical reads: (total=624) physical reads: (total=0)...


Verbesserungen bei der “distinct” - Klausel

• Beispiel:

– select distinct type from titles


– Eine Arbeitstabelle für distinct– 624 I/O auf der Tabelle titles, 5125 I/O auf der Arbeitstabelle

• Adaptive Server 15.0:

– Verwendung der “hash based” distinct Methode im Speicher– 624 I/O auf der Tabelle und 0 I/O auf der Arbeitstabelle


Verbesserungen bei der OR Klausel

• Beispiel:

select * from titles_idprwhere title_id between "BU" and "BW"OR price between 2.99 and 4.99

• -- Indizes auf title_id und price


– Der Optimierer verwendet die ODER Strategie– Es werden 255 logische I/O benötigt


– Verwendung der speicherbasierten “hash based union”Methode

– Es werden 123 logical I/O benötigt


Verbesserungen bei der Union - Klausel

• Beispiel:

select title_id, title,type from titleswhere title_id < "T7"

union select title_id, title, price from titles

where price > $45.00-- Keine Indizes auf title_id oder price


– Zwei Tabellen - Scans und eine Arbeitstabelle für distinct– Es werden 6770 I/O benötigt


– Verwendung der “hash based” union distinct Methode– Es werden 1248 I/O benötigt


Verbesserungen bei der Union - Klausel

Vor Adaptive Server 15.0:

STEP 1

... Table Scan ...

TO TABLE Worktable1.

STEP 1

The type of query is INSERT.

The update mode is direct.

FROM TABLE ...

STEP 1

The type of query is SELECT.

This step involves sorting.

FROM TABLE Worktable1.

Using GETSORTED ...

Adaptive Server 15.0:

...

|HASH UNION Operator has 2 children.

| |SCAN Operator

| | FROM TABLE

| | titles

| | Table Scan.

| | Forward Scan ...

| |SCAN Operator

| | FROM TABLE

| | titles

| | Table Scan. ...


Nicht übereinstimmende Datentypen

• Beispiel:

– select * from A, B where A.a=B.b

– A.a - float– B.b - int , indiziert– B ist eine große Tabelle


– Indexauswahl ist problematisch – der Optimierer kann den Startpunkt im Index B nicht ermitteln, da A.a einen anderenDatentyp hat


– Indexauswahl konvertiert die Werte automatisch– Index B wird für entsprechende Werte in A.a verwendet


Nicht übereinstimmende Datentypen

1. set showplan on

2. Index auf die total_sales Spalte der Tabelle titles anlegen

create index idx_ts on titles(total_sales)

3. Anfrage ausführen:

declare @x floatselect @x = 3000.00select title_id, total_sales

from titles where total_sales = @x

The type of query is SELECT...

SCAN Operator | FROM TABLE | titles | Index : idx_ts ...


Datenverteilung

• Beispiel:

select * from authors, publishers where authors.state = publishers.stateand authors.state = "CO“


– Einzelne, häufig auftretende Werte führen zu einem Ungleichgewicht– Dieses Ungleichgewicht führt dazu, daß der Optimierer die Join –

Reihenfolge falsch berechnet– Geschätzte Kosten – 5400 I/O


– Joinhistogramme werden dynamisch angelegt und dadurch die Zugriffskosten korrekter geschätzt

– Geschätzte Kosten – 360 I/O


Hash Join

select t1.c1, t2.c2, t2.c1, t2.c3 from t1, t2where t1.c2 = t2.c3

t1c1 c2

2432114765

ABCXYZDEFGHIBCA

ZZYYXXWWVVTT

ABCBCADEFXYZABCDEF

887766554433

123456

c4c3c2c1

t2


Hash Join

Hash Tabellet2

c1 c2 c3 c4

1234567

887766554433…

ABCBCADEFXYZABCDEF

ZZYYXXWWVVTT

C6 1, ABC 2, BCA

CF 3, DEF

10B 4, XYZ

5, ABC

6, DEFABCXYZDEFGHIBCA

2432114765

c2c1

t1

24 ABC 1 ABC24 ABC 5 ABC…

t1.c1 t1.c2 t2.c1 t2.c3


Sehr große Datenmengen

• Beispiel:

– select count(*), count(T1.price) – from titles T1, titles T2– where isnull(T1.price, 0) = isnull(T2.price, 0) – and rtrim(T1.title_id) rtrim(T2.title_id)

• Kein Index, Join mit Ausdrücken und Ergebnis ist sehr groß


– Der Optimierer wählt den nested loop– Benötigte Zeit 41,106 ms, ungefähr 3.1 Millionen I/O


– Der Optimierer wählt den Hash bzw. Merge Join– Beim Hash Join benötigt die Anfrage 320 ms mit 1248 I/O


Einstellen der Optimierungsziele und Gültigkeit

allrows_oltp Zur schnellen Optimierung für OLTP wird nur einebegrenzte Anzahl von Verfahren benutzt

e.g. nested loop join, keine Parallelisierung

allrows_mix (default) generiert Pläne für gemischte Lastprofilehauptsächlich allrows_oltp + merge joins, Parallelisierung

allrows_dss generiert optimale Pläne für hoch-komplexe DSShauptsächlich allrows_mix + hash joins

Server: sp_configure "optimization goal", 0, "allrows_oltp“

Session: set plan optgoal allrows_dss

Query: select * from A order by A.aplan "(use optgoal allrows_oltp)"


Optimierungsaufwand bei großer Join-Anzahl

select * from T1, T2, ... T50where T1.a = T2.a AND T2.a = T3.a ...AND T49.a = T50.a

• Vor Adaptive Server 15.0:die Optimierung der Join-Reihenfolge kann einen wesentlichen Zeitanteil benötigen(~ TabAnz!, 12! = 479M)

set table count n

• Adaptive Server 15.0 ermöglicht die Kontrolle über den Anteil der Optimierungszeit:

Server: sp_configure "optimization timeout limit", 10

Session: set plan opttimeoutlimit 10

Query: select * from A order by A.aplan "(use opttimeoutlimit 10)“

Prozentanteil der Gesamtbearbeitungszeit, die der Optimierer zurErstellung des Planes verwenden darf (Standard: 10%)


Optimierer Kostenmodell ab ASE 15

• Kostenschätzung im Optimierer:

– Kostenschätzung vor ASE 15:• Logischer Zugriff: 2• Physischer Zugriff: 18

– ASE 15: bessere Annäherung an die Realität:• Logischer Zugriff : 2• Physischer Zugriff : 25• CPU - je Zeile, die gesucht oder verglichen wird: 0.1


Optimierungsaufwand - Beispiel

• Zusätzlicher Optimierungsaufwand führt zu besserenAusführungsplänen.

• Die Einsparungen bei der Ausführung sollten natürlich mit demzusätzlichen Optimierungsaufwand abgewogen werden.

Optimization Timeout (%)

Parse/Compile CPU Time Execution Time

10 1600 ms 10 ms

50 22,800 ms 3 ms

select *from authors, titleauthor, titles_idpr, titles_pridtitl, publishers, roysched, titles, salesdetail, sales, stores, discounts, blurbswhere titles.title_id = roysched.title_idAND titles.title_id = titles_idpr.title_idAND titles.title_id = titles_pridtitl.title_idAND titles.pub_id = publishers.pub_idAND blurbs.au_id = authors.au_idAND titleauthor.au_id = authors.au_idAND titleauthor.title_id = titles.title_idAND titles.title_id = salesdetail.title_idAND salesdetail.ord_num = sales.ord_numAND salesdetail.stor_id = sales.stor_idAND sales.stor_id = stores.stor_idAND stores.stor_id = discounts.stor_id

Set option show brief


Automatisierung für Update Statistics


Archive Database Access (ADA)

– DBCC checks directly on database dumps (archives)• Ability to run DBCC directly on database dumps without having

the database fully loaded.

– Object Level Recovery • Customers have long requested the ability to retrieve data from

a dump that may have been accidentally deleted or corrupted.

• Current method used by customers: reload of the entire database which is time consuming


Archive Database Access (ADA)

ASE

Archive Database

Real Database

Real Database

Real Database

Database Dump File(acts as a DB device)

Database Devices


ADA - Object Level Recovery

– Archive databases can be accessed with regular SQL, as any other database

• SELECT * INTO FROM ..

• Cross-server: use CIS (proxy tables) and SELECT INTO


Erreichte Effekte

LaufzeitLaufzeit

Zugriffsanzahl


Query Processing – Die Änderungen

Transact-SQL via TDS(sprocs stored in text

in syscomments table)ParsingParse Step

Normalization Step

Parse Tree

Query Plan Generation Compile

Query Tree(“p-code”-like tree storedin sysprocedures table)

Data and other Internal Resources

ExecuteQuery Plan

( only resides in memory)

AccessMethods

Managers(buffer, lock, etc.)

TransactionManagementUtilities

Execution Engine New in 15.0!

New in 15.0!

New in 15.0!

New in 15.0!

Resolve NamesNormalize

Resolve Views, Aggregates & Subqueries

Protection Map

New in 15.0!

DeckblattWie groß sind VLDB ? - 2005DSS versus OLTP WorkloadVerwaltung großer Datenmengen mit Der traditionelle RDBMS-AnsatzVertikale PartitionierungSybase IQ Advanced Bit-Mapped IndizesDramatische I/O-ReduzierungOLTP DBMS vs. IQ-Multiplex engineAdministration der IndexstrukturenIndex AdvisorIQ Multiplex 12.5 (und davor)IQ Multiplex 12.6 w/ Local StoreIQ Multiplex – Local Store für Query ServerSUN-Sybase Referenz DWH - Weltgrößtes DWH in Menlo Park Business Case Telco�2005: World’s TopTenVerwaltung großer Datenmengen mit Neue LimitsVerwaltung großer Datenmengen mit Überblick zum PartitionierenVor ASE 15ASE 15 Semantische PartitioinierungRange PartitionRange Partition – Syntax und BeispielRange PartitionList PartitionList Partition – Syntax und BeispielList PartitionHash PartitionHash Partition – Syntax und BeispielRound Robin PartitionRisiko Partition Key UpdatePartitionierung des IndexGlobale IndizesNon-clustered Global IndexLokale Indizes Non-clustered Local Index�Clustered (Local) Index�Zugriffsplan - BeispielGeringerer Einfluß von AdministrationsarbeitenVerwaltung großer Datenmengen mit Computed Column Index und Function Based Index Function Based Index - BeispielComputed Column Index und Function Based IndexVerwaltung großer Datenmengen mit Weniger E/A für ArbeitstabellenWeniger E/A bei ArbeitstabellenWeniger E/A bei ArbeitstabellenVerbesserungen bei der “distinct” - KlauselVerbesserungen bei der OR KlauselVerbesserungen bei der Union - KlauselVerbesserungen bei der Union - KlauselNicht übereinstimmende DatentypenNicht übereinstimmende DatentypenDatenverteilungVerwaltung großer Datenmengen mit Hash JoinHash JoinSehr große DatenmengenVerwaltung großer Datenmengen mit Einstellen der Optimierungsziele und GültigkeitOptimierungsaufwand bei großer Join-Anzahl Optimierer Kostenmodell ab ASE 15Optimierungsaufwand - BeispielAutomatisierung für Update StatisticsArchive Database Access (ADA)Archive Database Access (ADA)ADA - Object Level RecoveryErreichte EffekteQuery Processing – Die Änderungen

Documents

Verwaltung sehr großer Datenmengen mit Sybase 15dbst/material/20060628_118_bittner.pdf · •Performance & Effektivität •Skalierfähigkeit(Daten & Anwender) OLTP=einfache Query-verarbeitet