database design for success n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design for Success PowerPoint Presentation
Download Presentation
Database Design for Success

Loading in 2 Seconds...

play fullscreen
1 / 93

Database Design for Success - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

Database Design for Success. Informix 101. #include <disclaimer.h>. Agenda. Sizing your machine Install and Configure the Engine Build a database Loading Data Monitoring, Tuning & Ongoing Administration Odds, Ends and other Pet Peeves. Sizing Your Machine. The Data Model

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Design for Success' - mackenzie


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda
Agenda
  • Sizing your machine
  • Install and Configure the Engine
  • Build a database
  • Loading Data
  • Monitoring, Tuning & Ongoing Administration
  • Odds, Ends and other Pet Peeves
sizing your machine
Sizing Your Machine
  • The Data Model
  • The holy trinity
    • Disk, CPU, Memory
  • Raid, Mirroring, Striping
slide6
No System Design survives it’s first contact with the data.

- Parker

That’s no excuse for not making one.

the logical model ldm
The Logical Model (LDM)
  • An informal representation of data and it’s relationships.
  • Format of the model can be changed to fit the users understandings.
  • Used to communicate with users.
  • Is NOT suited to be the foundation of a database.
  • Entities, attributes
the physical data model pdm
The Physical Data Model (PDM)
  • A formal description of a database and the links between tables.
  • Very rigid format.
  • Points out mistakes.
  • Can provide a sizing estimate.
  • Table, Columns.
  • A roadmap for new team members
a sizing tool

Input: table, rowsize, nrows

A Sizing Tool

Outputs: extent size, estimated scan time

sizing is not always possible
Sizing is not always possible
  • Have to order the machine before we know the full extent of the data.
  • Multiply the raw data size * 4 = disk
  • Divide by 25GB = CPU
    • round up to multiples of 4
  • Multiply * 500MB = Memory
  • Make sure you can grow from there
  • Make sure to indicate this is an estimate.
to raid or not to raid a battle you will lose
To Raid or not to Raid(a battle you will lose)
  • Expensive in terms of performance. Your machine is doing extra work – depending on the Raid you choose.
  • Mirroring is best, but expensive in terms of $.
  • 500 disks, 50,000 MTBF = 1 failure every 100 hours.
striping
Striping
  • Takes control out of the hands of the DBA and gives it to the sysadmin – who doesn’t have a clue.
  • Hard to say no to.
  • Mitigate it by striping small disk sets (groups of 4 or 8).
  • Worse comes to worse, benchmark.
you now have
You now have
  • A machine.
  • A bunch of raw disks that have been sliced up.
  • Some expectation of table and index size.
  • A Physical Data Model expressed in terms of a schema.
setup
Setup
  • Getting the engine online
  • Layout of disks
    • root
    • logical logs
    • physical logs
    • data
    • temp
install informix
Install Informix
  • Use $INFORMIXDIR as a link, not a hard coded path.
  • Run as root
  • Everybody knows this, right?
disks
Disks
  • mkdir /opt/server_1/dev (or somesuch)
  • ln –s all disks to this directory
  • Give them names that match underlying disks.
  • Be VERY careful not to overlay one link on top of another. (keep a map)

Recovery note:

  • use a script to do the links. Save it.
    • $INFORMIXDIR/server_1/rebuild
raw vs cooked disk
Raw vs Cooked Disk
  • Cooked disk is a file
    • Uses Unix file system I/O
    • Risk
  • Raw disk is a character special device

(/dev/rdsk/c0d3s2)

    • Uses Kernel I/O (KIO) or Asynchronous I/O (AIO)
    • Engine does the work
    • 15% faster
disk example
Disk Example
  • Raw Device
    • /dev/rdsk/c1d0s3
  • Link to raw device
    • /opt/server_1/dev/c1d0s3 -> /dev/rdsk/c1d0s3
  • onstat –d
    • dbsp1.1 /opt/server_1/dev/c1d0s3
    • dbsp1.2 /opt/server_1/dev/c2d0s3
    • dbsp1.3 /opt/server_1/dev/c3d0s3
oninit ivy
oninit -ivy
  • Cheat and use onmonitor
  • $ONCONFIG
  • $INFORMIXDIR/etc/sqlhosts
  • /etc/services
  • Don’t forget:

$INFORMIXDIR,

$INFORMIXSERVER,

$TERM,

$ONCONFIG,

($SQLHOSTS)

the engine trinity
The engine trinity
  • $ONCONFIG
    • DBSERVERNAME, ALIAS
  • SQLHOSTS
    • SERVERNAME, SERVICE
  • /etc/services
    • Service
  • $INFORMIXSERVER
move logs
Move logs
  • Logical and Physical logs can be high activity. Good practice to move them to their own disks.
  • Physical
    • Change name of PHYSDBS in $ONCONFIG
  • Logical
    • Add new logs to new DBS
    • Force to next log (onmode –l)
    • Drop original logs (onparams –d –l log_id)
temp disks
Temp Disks
  • More is better
  • 2 or 3 per cpu
  • Many shallow is better than few deep.
  • Used to sort, build indices, overflows.
  • Size to largest index, or largest hash join.
slide23

What is Parallelism?

Sort

vertical

parallelism

horizontal

parallelism

Join

Time to

Process

Scan

ParallelTasks

Parallel

Subtasks

Tasks

how do you turn it on
How do you turn it on?
  • PDQPRIORITY
  • Fragment your data
pdqpriority
PDQPRIORITY
  • Turns on parallelism (=1)
  • Allocates Decision Support (DS) Memory
  • $PDQPRIORITY
  • SET PDQPRIORITY
  • Memory = DS_Memory * (MAX_PDQPRIORITY/100) * (PDQPRIORITY/100)
  • 1GB * 50 * 50 /10000 = 250MB
fragmentation
Fragmentation
  • Create table profile (……..)
  • Fragment by
    • round robin in dbslc1
    • expression (col1 > abc in dbslc1.1,

col1 < ghi and col1 > def in dbslc 1.2

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

col2 < ghi and col2 > def in dbslc1.2

data disks
Data Disks
  • Wide is better than deep
  • Break into logical groupings of dbspaces
    • Multiples of CPU’s – to a max of 3.
  • DO NOT name according to tables.
    • Wide.1…Wide.32
    • Thin.1 … Thin.4
    • Temp.1 … Temp.16
  • (XPS DBslice)
table layout
Table layout
  • Size first and next extents properly
  • Spread across multiple disks
  • Across multiple controllers
  • Balance Fragments to CPUs
balance
Balance
  • Horizontally
    • Even distributions of data across disks
    • Disks matched to CPUs (data and temp)
  • Vertically
    • Spread the load of checkpoints across Page cleaners and checkpoint writes
loading data
Loading data
  • Check out the loader.FAQ www.artentech.com/downloads.htm
  • Load
  • DBload
  • Insert Cursor
  • HPL
  • PLoader
update statistics
Update Statistics
  • What does it do?
    • systables, sysindexes,
    • sysdistrib
  • Why do it?
  • How to make it faster
    • DBUPSPACE
    • Run in parallel
      • with LOCK MODE WAIT
  • Stored Procedures
  • Scripts at www.iiug.org
building indices
Building Indices
  • Why build an index?
    • Find specific rows
    • Resolve query with index only scans
    • Promote nested loop joins
  • Bitmaps and Generalized Key indices
  • Fill Factor, Clustered
  • Involves:
    • Scan, Sort, btree-appender threads
    • Write to disk
attached detached fragmented
Attached, Detached, Fragmented
  • Detached
    • specify storage for the index
    • Otherwise uses the tablespace
  • Default in 7 is attached
  • Default in 9 is detached, 8 is detached
  • Detached means another 4 bytes
  • Why detach?
things to tune
Things to tune
  • CPU
  • Memory
  • Disk
  • Network / Interconnect
  • Data Model
  • Processing
things to tune1
Things to tune

The Engine

2x - 3x

things to tune2
Things to tune

Disk 2x - 100x

The Engine

2x - 3x

things to tune3
Things to tune

Code

10x – 1000x

Disk

Engine

things to tune4
Things to tune

Data Model

Code

Disk

Engine

10x – 10000x

tuning
Tuning
  • Big knobs
    • Disk
    • Disk
    • Disk
    • Memory
  • Little knobs
    • The rest of $ONCONFIG
monitorng so many utilities
MonitorngSo many utilities…
  • onstat
  • sysmaster
  • OS utilities (iostat,mpstat)
  • Graphical (onperf, xtree)
  • Specific (ISpy)
  • Third Party (Patrol, Big Brother, etc)
  • Query Plan
  • Home grown
it all boils down to
… It all boils down to
  • What do YOU care about.
  • Look for problems, and monitor events leading up to those.
some things i watch
Some things I watch
  • Disk fragmentation, size, up
  • onstat –p
  • Did expected event take place?
  • Long checkpoints
  • Logfiles
  • errors in online.log
  • Resource hogs
onstat p dipstick thermometer rolled into one
onstat –pdipstick & thermometer rolled into one
  • Cache, waits, compresses, rollbacks
  • Negative values
  • Capture them periodically and reset
    • if you care that much
slide47
Disk
  • Are table fragmented/sized properly
    • full scan of table – how long?
    • fragment elimination
    • < 4 extents per dbspace
    • Has someone dropped a new table somewhere?
  • Good indices?
    • Look at query plans
different disk utilities report different things
Different disk utilities report different things
  • Raw Data Size
    • From Unix 32,000,000,000 chars
  • Pages allocated
    • From oncheck –pe 10,000,000 pages
  • Created table as
    • First Size 100,000 kbytes
  • Pages used
    • From npused 7,928,642 pages
onchk awk
onchk.awk

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

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

Grand total disk use (MB)     27.19

See Handout

memory
Memory
  • Is there enough?
  • What does overflow look like?
  • Is there enough temp space to cover it?
  • How are people joining tables?
index building
Index Building
  • BUFFERS 25% of available memory
  • SHMVIRTSIZE 75% of available memory
  • CKPTINTVL 3000 (50 min)
  • LRU_MAX_DIRTY 80
  • LRU_MIN_DIRTY 70
  • RA_PAGES 32 (16 for 4k page)
  • RA_THRESHOLD 30 (15 for 4k page)
  • DBSPACETEMP Lots
  • DS_TOTAL_MEMORY 90% of SHMVIRTSIZE
  • DS_MAX_SCANS Nbr of fragments of largest table
  • PHYSFILE Large
slide53
OLTP
  • BUFFERS 50-75% of available memory
  • LOCKS 250 * # of users
  • PHYSBUFF Pages per I/O should be 75%
  • LOGBUFF Pages per I/O should be 75%
  • LRUS 1 per CPUVP
  • CLEANERS 1 per LRU pair
  • SHMVIRTSIZE 32000 + (users * 800)
  • CKPTINTVL Maximum Recovery time
  • LRU_MAX_DIRTY 10
  • LRU_MIN_DIRTY 5
  • RA_PAGES 32 (16 for 4k page)
  • RA_THRESHOLD 30 (15 for 4k page)
  • PHYSFILE Should be 75% full at Checkpoint
slide54
DSS
  • BUFFERS Minimize, 2000
  • SHMVIRTSIZE 75% of available memory
  • SHMADD 32000
  • SHMTOTAL Available memory
  • RA_PAGES 128 (16 for 4k page)
  • RA_THRESHOLD 120 (15 for 4k page)
  • DS_TOTAL_MEMORY 90% of SHMVIRTSIZE
query progress
Query Progress
  • onstat –g mgm/rgm – resource grant manager
  • onstat –g xqp plan_id – query plan (XPS)
  • onstat –g xqs plan_id – query status (XPS)
  • onstat –g xmp – detailed status (XPS)
  • onstat –g ses – session status
  • onstat –g sql – sql that is running
  • onstat –g lsc/scn
  • xmp_mon (XPS)
query plan ids
Query Plan (IDS)

QUERY:

------

select a.state_cd, b.last_name, count(*)

from tab1 a, tab2 b

where a.key = b.key

group by 1,2

Estimated Cost: 121157

Estimated # of Rows Returned: 1

1) informix.b: SEQUENTIAL SCAN (Serial, fragments: ALL)

2) informix.a: SEQUENTIAL SCAN (Serial, fragments: ALL)

Temporary table required for group operation

DYNAMIC HASH JOIN (Build Outer)

Dynamic Hash Filters: informix.b.hh_id = informix.a.hh_id

query plan xps
Query Plan (XPS)
  • Same as previous plus:

# of Secondary Threads = 8

XMP Query Plan

oper seqid brid width misc info

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

scan 3 0 3 a

scan 4 0 3 b

hjoin 2 0 1

group 2 0 1

group 1 0 1

query status
Query Status

XMP Query Statistics

type segid brid information

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

scan 3 0 inst cosvr time rows_prod rows_scan

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

0 1 1 6679 6679

1 1 0 6760 6760

2 1 0 6559 6559

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

3 19998 19998

xmp mon output
xmp_mon output

Current SQL statement :

select table2.key_col from destination, table1 where

table2.key_col=table1.key_col into temp t1 with no log

scan 4 table2

Operation Phase Threads Rows

scan next 3 1094059

xchg open 3 1093652

scan 3 3 399918 399918 table1

hjoin 2

Operation Phase Threads Rows

xchg next 1 1093652

flxins 1

growth
Growth
  • chkstat.sh
    • Runs weekly
    • Captures chunk names and sizes
    • Weekly report shows growth
rogue tables
Rogue tables
  • check_register.sh
  • Registry table lists every database:table
  • Weekly report shows tables not in registry
rogue table war story
Rogue Table War Story
  • “Power User”
  • Without a model
  • Creates a table to track url hits on the web
    • URL (char(1000))
    • Date
    • Count
  • Big table full of “data”
    • Had to parse data on the fly to answer questions
url info
Url Info
  • http://www.informix.com/answers/english/product1.htm
  • http://www.google.com/search?hl=en&q=Informix+Answers
  • http://www.doubleclick.net/xyz/jdslfjlkjsdfs
  • http://www.msn.com/cgi...........
url data
URL Data
  • http://www
  • Domain
  • /
  • Web page
  • 30% ads (doubleclick, adnet, etc)
  • 10-20% dynamic content (?)
caught in the act
Caught in the act
  • Unregistered table
  • Disk size spiked
  • Warning flags raised.
    • “This table is going to eat the database within three months”
i was wrong
I was wrong
  • It took 4 months
warning ignored
Warning ignored
  • “Oh, it’s just temporary”
  • “We can delete old stuff from it”
after 4 months
After 4 months
  • Table reached 200GB
  • Production processes could not complete – insufficient disk space
  • Processes against this URL table chewed up resources and could not complete – no disk space
solution was already ready
Solution was already ready
  • With that much lead time…..
  • Had replacement tables modeled
  • Had sizing estimates completed
  • Had written new copies of required processes to populate and query
  • Conversion scripts tested and ready
  • Business case had been written up
solution implemented
Solution implemented
  • Size of new tables was 14GB.
  • Solution implemented over a weekend.
  • New tables contained information, not raw data.
  • Queries cut down from 6 hours (and failing) to 5-300 seconds.
maintenance good weekend task
Maintenance(*Good weekend task)
  • Disk unload/reload
    • When dbspace is cluttered and full of holes (oncheck –pe | onchck.awk)
      • Barber pole effect
    • After many deletes (onstat –p|grep compress)
    • Do dbspace by dbspace
    • Sizing tactic
      • First=25%, Next=75%
      • Load, alter next extent
checkpoints
Checkpoints
  • What happens during a checkpoint?
    • Write dirty pages
    • Buffer management
    • Checkpoint record to Logical log
    • Physical log pointer shifted
how to tune a checkpoint
How to tune a checkpoint
  • Give it less work to do
    • Decrease PHYSFILE or CKPTINTVL
  • Check AIO or KIO. Add AIOVPS or CPUVPS
    • (onstat –g ioq, look for queue length > 25 peak, 10 avg)
  • Add LRU queues – Min of 1/CPUVP
  • LRUMAXDIRTY,LRUMINDIRTY
  • For more take IDS Masters series
backups
Backups

Nobody cares about the backup

Everybody cares about the restore

backups1
Backups
  • I don’t know a thing about them.
  • ontape vs onbar vs unloads.
  • ‘cold’ backups.
  • Making them work.
  • Keeping an eye on them
    • bckp_status.ksh
  • Why do they sometimes take so much longer?
light scans
Light Scans
  • Avoids buffers
  • Uses it’s own buffer pool
  • RA_PAGES & RA_THRESHHOLD
  • onstat –g lsc, onstat –g scn
forcing a light scan
Forcing a light scan
  • Dirty Read (or shared lock)
  • Table larger than buffers
  • No varchars
  • export LIGHTSCANS=FORCE
light appends
Light Appends
  • Uses own buffer pool
  • Ignores constraints and triggers
  • Appends new pages
  • Avoids buffer overhead
joins
Joins
  • Hash
    • Memory = (32+rowsize+keysize)*nrows
  • Sort Merge
  • Nested Loop
  • Push Down Semi
oltp index method
OLTP – Index Method

Rinse and Repeat.

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

dss hash join method
DSS – Hash Join Method

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

index vs hash join oltp vs dss
Load

1 million / Hour

Update

1 million / Hour

Delete

500K / Hour

Load

1 million / minute

Update (Join)

1 million / minute

Delete (Join)

10 million / Hour

Index vs Hash Join(OLTP vs DSS)
anatomy of a table
Anatomy of a table
  • Tablespace entry
    • oncheck –pt
  • Partition page
  • Bitmap page (2K - every 4032 pages)
  • Index pages
  • Data pages
  • For more info take Internals class
  • See Handout
articles on tuning code
Articles on Tuning code
  • Tuning Informix DSS Queries. Tech Notes Vol 10, Issue 1, 2000. Developer Zone 5/10/2002
  • DSS Application processing. Developer Zone 5/10/2002

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

data model war story
Data Model War Story

Profile Table

global cookie char(35),

interest char(12),

score decimal(5,2)

Cross Reference Table

global cookie char(35),

local cookie varchar(64),

domain char(4)

when we make that physical
When we make that physical…

610 GB 138 Min index = 726 GB

53 GB 12 Min index = 66 GB

53 GB 12 Min index = 66 GB

million dollar mistake
Million dollar mistake
  • char(35) * 1 billion = 35 GB of data
  • … just in the key
  • Index size is 66 GB
  • Scan time for table (1.5 GB/Disk) (12 min)
  • Build time for index
  • Hash 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

53 GB 12 Min index = 66 GB

53 GB 12 Min index = 66 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 table
Our new table
  • Use a serial (integer) as a key instead of the cookie
  • 6 GB for this column
  • Index size is 18 GB
  • Scan time (200 MB/disk) (81 sec)
  • Build time for index*
  • Hash join cost = 56 GB RAM, 30 min
    • (get rid of local = 24GB, 8 min)
  • Implied cross reference table and lookup
sites of interest
Sites of interest
  • www.iiug.org
    • Community, software
  • comp.databases.informix
    • Subscribe via email through iiug
  • www.informix.com/answers
    • Online documentation
  • www.markscranton.com
    • Trainer, lots of nuggets (onstat)
  • www.artentech.com/downloads.htm
    • Jack.parker4@verizon.net