database design for success informix 101 #include #include

Database Design for Database Design for SuccessSuccess

Informix 101Informix 101

#include <disclaimer.h>#include <disclaimer.h>

AgendaAgenda

Sizing your machineSizing your machine

Install and Configure the EngineInstall and Configure the Engine

Build a databaseBuild a database

Loading DataLoading Data

Monitoring, Tuning & Ongoing Monitoring, Tuning & Ongoing AdministrationAdministration

Odds, Ends and other Pet PeevesOdds, Ends and other Pet Peeves

Sizing Your MachineSizing Your Machine

The Data ModelThe Data Model

The holy trinityThe holy trinity

– Disk, CPU, MemoryDisk, CPU, Memory

Raid, Mirroring, StripingRaid, Mirroring, Striping

No plan of battle survives it’s No plan of battle survives it’s first contact with the enemy.first contact with the enemy.

- Clausewitz - Clausewitz

No System Design survives it’s No System Design survives it’s first contact with the data.first contact with the data.

- Parker- Parker

That’s no excuse for not making one.That’s no excuse for not making one.

The Logical Model (LDM)The Logical Model (LDM)

An informal representation of data and it’s An informal representation of data and it’s relationships.relationships.

Format of the model can be changed to fit Format of the model can be changed to fit the users understandings.the users understandings.

Used to communicate with users.Used to communicate with users.

Is NOT suited to be the foundation of a Is NOT suited to be the foundation of a database.database.

Entities, attributesEntities, attributes

The Physical Data Model (PDM)The Physical Data Model (PDM)

A formal description of a database and the A formal description of a database and the links between tables.links between tables.

Very rigid format.Very rigid format.

Points out mistakes.Points out mistakes.

Can provide a sizing estimate.Can provide a sizing estimate.

Table, Columns.Table, Columns.

A roadmap for new team membersA roadmap for new team members

A Sizing Tool

Table Rowsize NrowsEstimated # Pages Size (MB)

Number of Fragments

Pages Per Fragment

Size Per Fragment

First Extent

Next Extent

Est. Scan Time

Table 1 76 875,517,904 17,510,358 68,400 32 547,199 2,137 2188800 218,880 1160

Table 2 28 1,335,985,438 10,603,059 41,418 32 331,346 1,294 1325440 132,544 656

Table 3 47 564,747,456 7,148,702 27,925 32 223,397 873 893632 89,363 408

Table 4 8 858,007,546 3,364,735 13,143 32 105,148 411 420608 42,061 170

Total 38,626,855 150,886 4,715

Total Size 262,144 Free 111,258

Pct Free 42% Min 37.5% Free

Input: table, rowsize, nrows

Outputs: extent size, estimated scan time

Sizing is not always possibleSizing is not always possible

Have to order the machine before we know the Have to order the machine before we know the full extent of the data.full extent of the data.

Multiply the raw data size * 4 = diskMultiply the raw data size * 4 = disk

Divide by 25GB = CPUDivide by 25GB = CPU– round up to multiples of 4round up to multiples of 4

Multiply * 500MB = MemoryMultiply * 500MB = Memory

Make sure you can grow from thereMake sure you can grow from there

Make sure to indicate this is an estimate.Make sure to indicate this is an estimate.

To Raid or not to RaidTo Raid or not to Raid(a battle you will lose)(a battle you will lose)

Expensive in terms of performance. Your Expensive in terms of performance. Your machine is doing extra work – depending machine is doing extra work – depending on the Raid you choose.on the Raid you choose.

Mirroring is best, but expensive in terms of Mirroring is best, but expensive in terms of $.$.

500 disks, 50,000 MTBF = 1 failure every 500 disks, 50,000 MTBF = 1 failure every 100 hours.100 hours.

StripingStriping

Takes control out of the hands of the DBA Takes control out of the hands of the DBA and gives it to the sysadmin – who doesn’t and gives it to the sysadmin – who doesn’t have a clue.have a clue.

Hard to say no to.Hard to say no to.

Mitigate it by striping small disk sets Mitigate it by striping small disk sets (groups of 4 or 8).(groups of 4 or 8).

Worse comes to worse, benchmark.Worse comes to worse, benchmark.

You now haveYou now have

A machine.A machine.

A bunch of raw disks that have been sliced A bunch of raw disks that have been sliced up.up.

Some expectation of table and index size.Some expectation of table and index size.

A Physical Data Model expressed in terms A Physical Data Model expressed in terms of a schema.of a schema.

SetupSetup

Getting the engine onlineGetting the engine online

Layout of disksLayout of disks– rootroot– logical logslogical logs– physical logsphysical logs– datadata– temptemp

Install InformixInstall Informix

Use $INFORMIXDIR as a link, not a hard Use $INFORMIXDIR as a link, not a hard coded path.coded path.

Run as rootRun as root

Everybody knows this, right?Everybody knows this, right?

DisksDisks

mkdir /opt/server_1/dev mkdir /opt/server_1/dev (or somesuch)(or somesuch)

ln –s all disks to this directoryln –s all disks to this directory

Give them names that match underlying disks.Give them names that match underlying disks.

Be VERY careful not to overlay one link on top Be VERY careful not to overlay one link on top of another. (keep a map)of another. (keep a map)

Recovery note:Recovery note:

use a script to do the links. Save it.use a script to do the links. Save it.– $INFORMIXDIR/server_1/rebuild$INFORMIXDIR/server_1/rebuild

Raw vs Cooked DiskRaw vs Cooked Disk

Cooked disk is a fileCooked disk is a file– Uses Unix file system I/OUses Unix file system I/O– RiskRisk

Raw disk is a character special deviceRaw disk is a character special device(/dev/rdsk/c0d3s2)(/dev/rdsk/c0d3s2)– Uses Kernel I/O (KIO) or Asynchronous I/O Uses Kernel I/O (KIO) or Asynchronous I/O

(AIO)(AIO)– Engine does the workEngine does the work– 15% faster15% faster

Disk ExampleDisk Example

Raw DeviceRaw Device– /dev/rdsk/c1d0s3/dev/rdsk/c1d0s3

Link to raw deviceLink to raw device– /opt/server_1/dev/c1d0s3 -> /dev/rdsk/c1d0s3/opt/server_1/dev/c1d0s3 -> /dev/rdsk/c1d0s3

onstat –donstat –d– dbsp1.1dbsp1.1 /opt/server_1/dev/c1d0s3/opt/server_1/dev/c1d0s3– dbsp1.2dbsp1.2 /opt/server_1/dev/c2d0s3/opt/server_1/dev/c2d0s3– dbsp1.3dbsp1.3 /opt/server_1/dev/c3d0s3/opt/server_1/dev/c3d0s3

oninit -ivyoninit -ivy

Cheat and use onmonitorCheat and use onmonitor$ONCONFIG$ONCONFIG$INFORMIXDIR/etc/sqlhosts$INFORMIXDIR/etc/sqlhosts/etc/services/etc/servicesDon’t forget:Don’t forget:$INFORMIXDIR, $INFORMIXDIR, $INFORMIXSERVER, $INFORMIXSERVER, $TERM, $TERM, $ONCONFIG, $ONCONFIG, ($SQLHOSTS)($SQLHOSTS)

The engine trinityThe engine trinity

$ONCONFIG$ONCONFIG– DBSERVERNAME, ALIASDBSERVERNAME, ALIAS

SQLHOSTSSQLHOSTS– SERVERNAME, SERVICESERVERNAME, SERVICE

/etc/services/etc/services– ServiceService

$INFORMIXSERVER$INFORMIXSERVER

Move logsMove logs

Logical and Physical logs can be high Logical and Physical logs can be high activity. Good practice to move them to activity. Good practice to move them to their own disks.their own disks.Physical Physical – Change name of PHYSDBS in $ONCONFIGChange name of PHYSDBS in $ONCONFIG

Logical Logical – Add new logs to new DBSAdd new logs to new DBS– Force to next log (onmode –l)Force to next log (onmode –l)– Drop original logs (onparams –d –l Drop original logs (onparams –d –l log_idlog_id))

Temp DisksTemp Disks

More is betterMore is better

2 or 3 per cpu2 or 3 per cpu

Many shallow is better than few deep.Many shallow is better than few deep.

Used to sort, build indices, overflows. Used to sort, build indices, overflows.

Size to largest index, or largest hash join.Size to largest index, or largest hash join.

Time toProcess

TasksParallel

Subtasks

horizontalhorizontalparallelismparallelism

ScanScan

JoinJoin

SortSort

ParallelTasks

verticalverticalparallelismparallelism

What is Parallelism?What is Parallelism?

How do you turn it on?How do you turn it on?

PDQPRIORITYPDQPRIORITY

Fragment your dataFragment your data

PDQPRIORITYPDQPRIORITY

Turns on parallelism (=1)Turns on parallelism (=1)

Allocates Decision Support (DS) MemoryAllocates Decision Support (DS) Memory

$PDQPRIORITY$PDQPRIORITY

SET PDQPRIORITYSET PDQPRIORITY

Memory = DS_Memory * Memory = DS_Memory * (MAX_PDQPRIORITY/100) * (MAX_PDQPRIORITY/100) * (PDQPRIORITY/100)(PDQPRIORITY/100)

1GB * 50 * 50 /10000 = 250MB1GB * 50 * 50 /10000 = 250MB

Fragment tablesFragment tables

Four Scan Threads

One Scan Thread

FragmentationFragmentation

Create table profile (……..)Create table profile (……..)

Fragment byFragment by– round robin in dbslc1round robin in dbslc1– expression (col1 > abc in dbslc1.1,expression (col1 > abc in dbslc1.1,

col1 < ghi and col1 > def in dbslc col1 < ghi and col1 > def in dbslc 1.21.2

– hash(col1) in dbslc1hash(col1) in dbslc1– hybrid (col1) expression col2 > abc in dbslc1.1,hybrid (col1) expression col2 > abc in dbslc1.1,

col2 < ghi and col2 > def in dbslc1.2col2 < ghi and col2 > def in dbslc1.2

Fragment by hash at workFragment by hash at work

Cookie 1

Cookie 2

Cookie 3

Cookie 4

Data DisksData Disks

Wide is better than deepWide is better than deep

Break into logical groupings of dbspacesBreak into logical groupings of dbspaces– Multiples of CPU’s – to a max of 3.Multiples of CPU’s – to a max of 3.

DO NOT name according to tables.DO NOT name according to tables.– Wide.1…Wide.32Wide.1…Wide.32– Thin.1 … Thin.4Thin.1 … Thin.4– Temp.1 … Temp.16Temp.1 … Temp.16

(XPS DBslice)(XPS DBslice)

Table layoutTable layout

Size first and next extents properlySize first and next extents properly

Spread across multiple disksSpread across multiple disks

Across multiple controllersAcross multiple controllers

Balance Fragments to CPUsBalance Fragments to CPUs

BalanceBalance

HorizontallyHorizontally– Even distributions of data across disksEven distributions of data across disks– Disks matched to CPUs (data and temp)Disks matched to CPUs (data and temp)

VerticallyVertically– Spread the load of checkpoints across Page Spread the load of checkpoints across Page

cleaners and checkpoint writescleaners and checkpoint writes

Loading dataLoading data

Check out the loader.FAQ Check out the loader.FAQ www.artentech.com/downloads.htmwww.artentech.com/downloads.htm

LoadLoad

DBloadDBload

Insert CursorInsert Cursor

HPLHPL

PLoaderPLoader

Update StatisticsUpdate Statistics

What does it do?What does it do?– systables, sysindexes, systables, sysindexes, – sysdistribsysdistrib

Why do it?Why do it?How to make it fasterHow to make it faster– DBUPSPACEDBUPSPACE– Run in parallelRun in parallel

with LOCK MODE WAITwith LOCK MODE WAIT

Stored ProceduresStored ProceduresScripts at www.iiug.orgScripts at www.iiug.org

Building IndicesBuilding Indices

Why build an index?Why build an index?– Find specific rowsFind specific rows– Resolve query with index only scansResolve query with index only scans– Promote nested loop joinsPromote nested loop joins

Bitmaps and Generalized Key indicesBitmaps and Generalized Key indicesFill Factor, ClusteredFill Factor, ClusteredInvolves:Involves:– Scan, Sort, btree-appender threadsScan, Sort, btree-appender threads– Write to diskWrite to disk

Attached, Detached, FragmentedAttached, Detached, Fragmented

Detached Detached – specify storage for the indexspecify storage for the index– Otherwise uses the tablespaceOtherwise uses the tablespace

Default in 7 is attachedDefault in 7 is attached

Default in 9 is detached, 8 is detachedDefault in 9 is detached, 8 is detached

Detached means another 4 bytesDetached means another 4 bytes

Why detach?Why detach?

Monitoring, Tuning and Ongoing Monitoring, Tuning and Ongoing AdministrationAdministration

Things to tuneThings to tune

CPUCPU

MemoryMemory

DiskDisk

Network / InterconnectNetwork / Interconnect

Data ModelData Model

ProcessingProcessing

The Engine2x - 3x



The Engine2x - 3x

Disk 2x - 100x


Engine

Disk

Code10x – 1000x


Engine

Disk

Code

Data Model

10x – 10000x

TuningTuning

Big knobsBig knobs– DiskDisk– DiskDisk– DiskDisk– MemoryMemory

Little knobsLittle knobs– The rest of $ONCONFIGThe rest of $ONCONFIG

MonitorngMonitorngSo many utilities…So many utilities…

onstatonstatsysmastersysmasterOS utilities (iostat,mpstat)OS utilities (iostat,mpstat)Graphical (onperf, xtree)Graphical (onperf, xtree)Specific (ISpy)Specific (ISpy)Third Party (Patrol, Big Brother, etc)Third Party (Patrol, Big Brother, etc)Query PlanQuery PlanHome grownHome grown

… … It all boils down toIt all boils down to

What do YOU care about.What do YOU care about.

Look for problems, and monitor events Look for problems, and monitor events leading up to those.leading up to those.

Some things I watchSome things I watch

Disk fragmentation, size, upDisk fragmentation, size, up

onstat –ponstat –p

Did expected event take place?Did expected event take place?

Long checkpointsLong checkpoints

LogfilesLogfiles

errors in online.logerrors in online.log

Resource hogsResource hogs

onstat –ponstat –pdipstick & thermometer dipstick & thermometer

rolled into onerolled into one

Cache, waits, compresses, rollbacksCache, waits, compresses, rollbacks

Negative valuesNegative values

Capture them periodically and reset Capture them periodically and reset – if you care that muchif you care that much

DiskDisk

Are table fragmented/sized properlyAre table fragmented/sized properly– full scan of table – how long?full scan of table – how long?– fragment eliminationfragment elimination– < 4 extents per dbspace< 4 extents per dbspace– Has someone dropped a new table Has someone dropped a new table

somewhere?somewhere?

Good indices?Good indices?– Look at query plansLook at query plans

Different disk utilities report Different disk utilities report different thingsdifferent things

Raw Data SizeRaw Data Size– From UnixFrom Unix 32,000,000,000 32,000,000,000 charschars

Pages allocatedPages allocated– From oncheck –peFrom oncheck –pe 10,000,000 10,000,000 pagespages

Created table asCreated table as– First Size First Size 100,000 100,000 kbyteskbytes

Pages usedPages used– From npusedFrom npused 7,928,6427,928,642 pagespages

onchk.awkonchk.awk

Chunk: rootdbs Size: 19.53 Used: 13.54 Ckfree: 5.99 PctFree: Chunk: rootdbs Size: 19.53 Used: 13.54 Ckfree: 5.99 PctFree: 30.6930.69sysutils sysutils 0.62 0.62 Extents 39 *****Extents 39 *****LOGICAL LOGICAL 9.77 9.77 Extents 20 *****Extents 20 *****stores7 stores7 0.67 0.67 Extents 40 *****Extents 40 *****PHYSICAL PHYSICAL 0.98 0.98 Extents 1Extents 1sysmaster sysmaster 1.17 1.17 Extents 73 *****Extents 73 *****

Chunk: testdbs Size: 9.77 Used: 0.12 Ckfree: 9.65 PctFree: 98.78Chunk: testdbs Size: 9.77 Used: 0.12 Ckfree: 9.65 PctFree: 98.78 *** OVER 80% FREE SPACE *** *** OVER 80% FREE SPACE ***stores7 0.02 Extents 1stores7 0.02 Extents 1

Grand total disk use (MB) 27.19Grand total disk use (MB) 27.19

See HandoutSee Handout

MemoryMemory

Is there enough?Is there enough?

What does overflow look like?What does overflow look like?

Is there enough temp space to cover it?Is there enough temp space to cover it?

How are people joining tables?How are people joining tables?

The three memory modelsThe three memory models

IndicesIndices

OLTPOLTP

DSSDSS

Index BuildingIndex Building

BUFFERSBUFFERS 25% of available memory25% of available memorySHMVIRTSIZESHMVIRTSIZE 75% of available memory75% of available memoryCKPTINTVLCKPTINTVL 30003000 (50 min)(50 min)LRU_MAX_DIRTYLRU_MAX_DIRTY 8080LRU_MIN_DIRTYLRU_MIN_DIRTY 7070RA_PAGESRA_PAGES 32 (16 for 4k page)32 (16 for 4k page)RA_THRESHOLDRA_THRESHOLD 30 (15 for 4k page)30 (15 for 4k page)DBSPACETEMPDBSPACETEMP LotsLotsDS_TOTAL_MEMORYDS_TOTAL_MEMORY 90% of SHMVIRTSIZE90% of SHMVIRTSIZEDS_MAX_SCANSDS_MAX_SCANS Nbr of fragments of largest Nbr of fragments of largest tabletablePHYSFILEPHYSFILE LargeLarge

OLTPOLTP

BUFFERSBUFFERS 50-75% of available memory50-75% of available memoryLOCKSLOCKS 250 * # of users250 * # of usersPHYSBUFFPHYSBUFF Pages per I/O should be 75%Pages per I/O should be 75%LOGBUFFLOGBUFF Pages per I/O should be 75%Pages per I/O should be 75%LRUSLRUS 1 per CPUVP1 per CPUVPCLEANERSCLEANERS 1 per LRU pair1 per LRU pairSHMVIRTSIZESHMVIRTSIZE 32000 + (users * 800)32000 + (users * 800)CKPTINTVLCKPTINTVL Maximum Recovery timeMaximum Recovery timeLRU_MAX_DIRTYLRU_MAX_DIRTY 1010LRU_MIN_DIRTYLRU_MIN_DIRTY 55RA_PAGESRA_PAGES 32 (16 for 4k page)32 (16 for 4k page)RA_THRESHOLDRA_THRESHOLD 30 (15 for 4k page)30 (15 for 4k page)PHYSFILEPHYSFILE Should be 75% full at CheckpointShould be 75% full at Checkpoint

DSSDSS

BUFFERSBUFFERS Minimize, 2000Minimize, 2000

SHMVIRTSIZESHMVIRTSIZE 75% of available memory75% of available memory

SHMADDSHMADD 3200032000

SHMTOTALSHMTOTAL Available memoryAvailable memory

RA_PAGESRA_PAGES 128 (16 for 4k page)128 (16 for 4k page)

RA_THRESHOLDRA_THRESHOLD 120 (15 for 4k page)120 (15 for 4k page)

DS_TOTAL_MEMORYDS_TOTAL_MEMORY90% of SHMVIRTSIZE90% of SHMVIRTSIZE

Query ProgressQuery Progress

onstat –g mgm/rgm – resource grant manageronstat –g mgm/rgm – resource grant manager

onstat –g xqp plan_id – query plan (XPS)onstat –g xqp plan_id – query plan (XPS)

onstat –g xqs plan_id – query status (XPS)onstat –g xqs plan_id – query status (XPS)

onstat –g xmp – detailed status (XPS)onstat –g xmp – detailed status (XPS)

onstat –g ses – session statusonstat –g ses – session status

onstat –g sql – sql that is runningonstat –g sql – sql that is running

onstat –g lsc/scnonstat –g lsc/scn

xmp_mon (XPS)xmp_mon (XPS)

Query Plan (IDS)Query Plan (IDS)QUERY:QUERY:------------select a.state_cd, b.last_name, count(*)select a.state_cd, b.last_name, count(*)from tab1 a, tab2 bfrom tab1 a, tab2 bwhere a.key = b.keywhere a.key = b.keygroup by 1,2group by 1,2 Estimated Cost: 121157Estimated Cost: 121157Estimated # of Rows Returned: 1Estimated # of Rows Returned: 1 1) informix.b: SEQUENTIAL SCAN (Serial, fragments: ALL)1) informix.b: SEQUENTIAL SCAN (Serial, fragments: ALL) 2) informix.a: SEQUENTIAL SCAN (Serial, fragments: ALL)2) informix.a: SEQUENTIAL SCAN (Serial, fragments: ALL) Temporary table required for group operationTemporary table required for group operation DYNAMIC HASH JOIN (Build Outer)DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.b.hh_id = informix.a.hh_idDynamic Hash Filters: informix.b.hh_id = informix.a.hh_id

Query Plan (XPS)Query Plan (XPS)

Same as previous plus:Same as previous plus:

# of Secondary Threads = 8# of Secondary Threads = 8XMP Query PlanXMP Query Planoperoper seqidseqid bridbrid widthwidth misc infomisc info------------------------------------------------------------------------------------------------------------------scanscan 33 00 33 aascanscan 44 00 33 bbhjoinhjoin 22 00 11groupgroup 22 00 11groupgroup 11 00 11

Query StatusQuery Status

XMP Query StatisticsXMP Query Statistics

typetype segidsegid bridbrid informationinformation-------- ---------- -------- --------------------------scanscan 33 00 instinst cosvrcosvr timetime rows_prodrows_prod rows_scanrows_scan

-------- -------------- ------------ ---------------- ------------------------00 11 11 66796679 6679667911 11 00 67606760 6760676022 11 00 65596559 65596559--------------------------------------------------------------------------------------------------33 1999819998 1999819998

xmp_mon outputxmp_mon output

Current SQL statement :Current SQL statement : select table2.key_col from destination, table1 whereselect table2.key_col from destination, table1 where table2.key_col=table1.key_col into temp t1 with no logtable2.key_col=table1.key_col into temp t1 with no log

scan 4 table2scan 4 table2Operation Phase Threads RowsOperation Phase Threads Rowsscan nextscan next 3 10940593 1094059xchg openxchg open 3 10936523 1093652

scan 3 3 399918 399918 table1 scan 3 3 399918 399918 table1

hjoin 2 hjoin 2 Operation Phase Threads RowsOperation Phase Threads Rowsxchg nextxchg next 1 10936521 1093652

flxins 1 flxins 1

GrowthGrowth

chkstat.shchkstat.sh– Runs weeklyRuns weekly– Captures chunk names and sizesCaptures chunk names and sizes– Weekly report shows growthWeekly report shows growth

Rogue tablesRogue tables

check_register.shcheck_register.sh

Registry table lists every database:tableRegistry table lists every database:table

Weekly report shows tables not in registryWeekly report shows tables not in registry

Rogue Table War StoryRogue Table War Story

““Power User”Power User”Without a modelWithout a modelCreates a table to track url hits on the webCreates a table to track url hits on the web– URL (char(1000))URL (char(1000))– DateDate– CountCount

Big table full of “data”Big table full of “data”– Had to parse data on the fly to answer Had to parse data on the fly to answer

questionsquestions

Url InfoUrl Info

http://www.informix.com/answers/english/phttp://www.informix.com/answers/english/product1.htmroduct1.htm

http://www.google.com/search?hl=en&q=Ihttp://www.google.com/search?hl=en&q=Informix+Answersnformix+Answers

http://www.doubleclick.net/xyz/jdslfjlkjsdfshttp://www.doubleclick.net/xyz/jdslfjlkjsdfs

http://www.msn.com/cgihttp://www.msn.com/cgi......................

URL DataURL Data

http://wwwhttp://www

DomainDomain

//

Web pageWeb page

30% ads (doubleclick, adnet, etc)30% ads (doubleclick, adnet, etc)

10-20% dynamic content (?)10-20% dynamic content (?)

Caught in the actCaught in the act

Unregistered tableUnregistered table

Disk size spikedDisk size spiked

Warning flags raised.Warning flags raised.– ““This table is going to eat the database within This table is going to eat the database within

three months”three months”

I was wrongI was wrong

It took 4 monthsIt took 4 months

Warning ignoredWarning ignored

““Oh, it’s just temporary”Oh, it’s just temporary”

““We can delete old stuff from it”We can delete old stuff from it”

After 4 monthsAfter 4 months

Table reached 200GBTable reached 200GB

Production processes could not complete Production processes could not complete – insufficient disk space– insufficient disk space

Processes against this URL table chewed Processes against this URL table chewed up resources and could not complete – no up resources and could not complete – no disk spacedisk space

Solution was already readySolution was already ready

With that much lead time…..With that much lead time…..

Had replacement tables modeledHad replacement tables modeled

Had sizing estimates completedHad sizing estimates completed

Had written new copies of required Had written new copies of required processes to populate and queryprocesses to populate and query

Conversion scripts tested and readyConversion scripts tested and ready

Business case had been written upBusiness case had been written up

Solution implementedSolution implemented

Size of new tables was 14GB.Size of new tables was 14GB.

Solution implemented over a weekend.Solution implemented over a weekend.

New tables contained information, not raw New tables contained information, not raw data.data.

Queries cut down from 6 hours (and Queries cut down from 6 hours (and failing) to 5-300 seconds.failing) to 5-300 seconds.

MaintenanceMaintenance(*Good weekend task)(*Good weekend task)

Disk unload/reloadDisk unload/reload– When dbspace is cluttered and full of holes When dbspace is cluttered and full of holes

(oncheck –pe | onchck.awk)(oncheck –pe | onchck.awk)Barber pole effectBarber pole effect

– After many deletes (onstat –p|grep compress)After many deletes (onstat –p|grep compress)– Do dbspace by dbspaceDo dbspace by dbspace– Sizing tacticSizing tactic

First=25%, Next=75%First=25%, Next=75%

Load, alter next extentLoad, alter next extent

CheckpointsCheckpoints

What happens during a checkpoint?What happens during a checkpoint?– Write dirty pagesWrite dirty pages– Buffer managementBuffer management– Checkpoint record to Logical logCheckpoint record to Logical log– Physical log pointer shiftedPhysical log pointer shifted

How to tune a checkpointHow to tune a checkpoint

Give it less work to doGive it less work to do– Decrease PHYSFILE or CKPTINTVLDecrease PHYSFILE or CKPTINTVL

Check AIO or KIO. Add AIOVPS or Check AIO or KIO. Add AIOVPS or CPUVPSCPUVPS– (onstat –g ioq, look for queue length > 25 (onstat –g ioq, look for queue length > 25

peak, 10 avg)peak, 10 avg)

Add LRU queues – Min of 1/CPUVPAdd LRU queues – Min of 1/CPUVPLRUMAXDIRTY,LRUMINDIRTYLRUMAXDIRTY,LRUMINDIRTYFor more take IDS Masters seriesFor more take IDS Masters series

BackupsBackups

Nobody cares about the backupNobody cares about the backup

Everybody cares about the restoreEverybody cares about the restore

BackupsBackups

I don’t know a thing about them.I don’t know a thing about them.

ontape vs onbar vs unloads.ontape vs onbar vs unloads.

‘‘cold’ backups.cold’ backups.

Making them work.Making them work.

Keeping an eye on themKeeping an eye on them– bckp_status.kshbckp_status.ksh

Why do they sometimes take so much Why do they sometimes take so much longer?longer?

The Timestamp ClockThe Timestamp Clock

0

MAXINT

Light ScansLight Scans

Avoids buffersAvoids buffers

Uses it’s own buffer poolUses it’s own buffer pool

RA_PAGES & RA_THRESHHOLDRA_PAGES & RA_THRESHHOLD

onstat –g lsc, onstat –g scnonstat –g lsc, onstat –g scn

Forcing a light scanForcing a light scan

Dirty Read (or shared lock)Dirty Read (or shared lock)

Table larger than buffersTable larger than buffers

No varcharsNo varchars

export LIGHTSCANS=FORCEexport LIGHTSCANS=FORCE

Light AppendsLight Appends

Uses own buffer poolUses own buffer pool

Ignores constraints and triggersIgnores constraints and triggers

Appends new pagesAppends new pages

Avoids buffer overheadAvoids buffer overhead

JoinsJoins

HashHash– Memory = (32+rowsize+keysize)*nrowsMemory = (32+rowsize+keysize)*nrows

Sort MergeSort Merge

Nested LoopNested Loop

Push Down Semi Push Down Semi

Table

Table 2

OLTP – Index MethodOLTP – Index Method

ReturnsOne Row

Rinse and Repeat.

“We were deliriously happy if we got 1 million rows per hour…”

DSS – Hash Join MethodDSS – Hash Join Method

HashTable

Project

“…you get upset if you don’t get a million rows per minute.”

Table

Table 2

Index vs Hash JoinIndex vs Hash Join(OLTP vs DSS)(OLTP vs DSS)

LoadLoad– 1 million / Hour1 million / Hour

UpdateUpdate– 1 million / Hour1 million / Hour

DeleteDelete– 500K / Hour500K / Hour

LoadLoad– 1 million / 1 million / minuteminute

Update (Join)Update (Join)– 1 million / 1 million / minuteminute

Delete (Join)Delete (Join)– 10 million / Hour10 million / Hour

Anatomy of a tableAnatomy of a table

Tablespace entryTablespace entry– oncheck –ptoncheck –pt

Partition pagePartition pageBitmap page (2K - every 4032 pages)Bitmap page (2K - every 4032 pages)Index pagesIndex pagesData pagesData pagesFor more info take Internals classFor more info take Internals classSee HandoutSee Handout

Articles on Tuning codeArticles on Tuning code

Tuning Informix DSS Queries. Tech Notes Tuning Informix DSS Queries. Tech Notes Vol 10, Issue 1, 2000. Developer Zone Vol 10, Issue 1, 2000. Developer Zone 5/10/20025/10/2002

DSS Application processingDSS Application processing. Developer . Developer Zone 5/10/2002Zone 5/10/2002

http://www7b.boulder.ibm.com/dmdd/zones/informix/http://www7b.boulder.ibm.com/dmdd/zones/informix/

Data Model War StoryData Model War StoryProfile TableProfile Table

global cookieglobal cookie char(35),char(35), interestinterest char(12),char(12),

scorescore decimal(5,2)decimal(5,2)

Cross Reference TableCross Reference Table

global cookieglobal cookie char(35),char(35), local cookielocal cookie varchar(64),varchar(64),

domaindomain char(4)char(4)

Let’s just do a quick Let’s just do a quick model first…model first…

When we make that When we make that physical…physical…

53 GB 12 Min index = 66 GB



Million dollar mistakeMillion dollar mistake

char(35) * 1 billion = 35 GB of datachar(35) * 1 billion = 35 GB of data

… … just in the keyjust in the key

Index size is 66 GBIndex size is 66 GB

Scan time for table (1.5 GB/Disk) (12 min)Scan time for table (1.5 GB/Disk) (12 min)

Build time for indexBuild time for index

Hash join cost = 86 GB RAM, 90 minHash join cost = 86 GB RAM, 90 min

Let’s be a little saner…Let’s be a little saner…

5 GB 67 Sec index = 7 GB



Let’s go one step further…Let’s go one step further…

6 GB 81 Sec index = 2.5 GB

23 GB 5 Min index = 32 GB 36 GB 8 Min

index = 47 GB

Our new tableOur new table

Use a serial (integer) as a key instead of the Use a serial (integer) as a key instead of the cookiecookie

6 GB for this column6 GB for this column

Index size is 18 GBIndex size is 18 GB

Scan time (200 MB/disk) (81 sec)Scan time (200 MB/disk) (81 sec)

Build time for index*Build time for index*

Hash join cost = 56 GB RAM, 30 minHash join cost = 56 GB RAM, 30 min– (get rid of local = 24GB, 8 min)(get rid of local = 24GB, 8 min)

Implied cross reference table and lookupImplied cross reference table and lookup

Sites of interestSites of interest

www.iiug.orgwww.iiug.org– Community, softwareCommunity, software

comp.databases.informixcomp.databases.informix– Subscribe via email through iiugSubscribe via email through iiug

www.informix.com/answerswww.informix.com/answers– Online documentationOnline documentation

www.markscranton.comwww.markscranton.com– Trainer, lots of nuggets (onstat)Trainer, lots of nuggets (onstat)

www.artentech.comwww.artentech.com/downloads.htm/downloads.htm– [email protected]@verizon.net

database design for success informix 101 #include #include

Documents