A Poor Mans Load Simulation

1
A Poor Mans Load Simulation

• Tom Bascom
• Greenfield Technologies
• PUG Challenge 2003
• tom_at_greenfieldtech.com

2
Overview
3
The Challenge of Complexity

• A Volatile Business Environment
• System Load expands to consume all resources
• Something is Always changing
• But it was supposed to be transparent!

4
Why Simulate?

• Finding the Limits
• Capacity Planning
• Root Cause Analysis
• Minimizing Cost
• Optimizing Resources
• QoS Assurances

5
Considerations of Fidelity

• Assumptions
• Characterizing the Load
• Where are the bottlenecks?
• How do we (realistically) drive them?
• Users are fickle

6
Assumptions

• Were interested in the database
• READ activity is, by far, the largest consumer of
  database resources.
• WRITE activity is insignificant.
• A 4gl session is a 4gl session (in terms of
  resource use)

7
Characterizing the Load

• Historical data collection is a must
• Baseline db Performance
• Logical IO ops/sec
• Latch timeouts
• IO by User
• Tablestats

8
Where are the bottlenecks?

• Database
• Latch contention
• Transaction throughput
• Operating System
• Disk
• CPU
• Memory
• Network
• Application

9
How do we drive them?

• With some 4gl code that reads records as the
  application does.
• Distributed among many sessions.
• Profiled according to tablestats.

10
Users are fickle

• They work on different things
• at different rates
• pretty much randomly.
• IO by User (promon) is our friend.

11
IO by User
12
Scenarios

• Goals of the Test
• Testing Methodology
• Historical Data

13
IO Distribution
14
A Simulation Controller

• You already have everything that you need
• A generic Load driver
• Tuning the driver to your Load
• Controlling test runs

15
z.i

• do while k
• for each 1 no-lock
• k k 1.
• if k j then leave.
• end.
• end.

16
x.i

• if x
• else if x
• else if x
• else if x
• else if x
• else if x
• else if x
• else if x
• else if x

17
Pace.p

• do while true
• j random( r / 20 , r 20 ).
• i i j.
• k 0.
• x random( 1, 1448 ).
• x.i
• t time - s.
• do while (( i / t ) r )
• pause 1 no-message.
• t time - s.
• end.
• end.

18
Shell Script

• cat ioload while read RATE
• do
• sleep 1
• echo RATE
• mbpro DBNAME PF -p pace.p \
• -rand 2 -param RATE LOG
• done

19
Reading the Tea Leaves

• Live Demo???
• Summary Screen
• IO by User
• Operating System Metrics
• Actual cases

20
Top

• 351pm up 153 days, 428, 1 user, load
  average 0.50, 2.04, 2.67
• 117 processes 116 sleeping, 1 running, 0 zombie,
  0 stopped
• CPU0 states 4.1 user, 1.1 system, 0.0
  nice, 94.3 idle
• CPU1 states 0.2 user, 0.0 system, 0.0
  nice, 99.3 idle
• CPU2 states 2.3 user, 0.0 system, 0.0
  nice, 97.1 idle
• CPU3 states 0.3 user, 0.0 system, 0.0
  nice, 99.2 idle
• Mem 512944K av, 423456K used, 89488K free,
  0K shrd, 82392K buff
• Swap 1044184K av, 17288K used, 1026896K free
  243172K cached
• PID USER PRI NI SIZE RSS SHARE STAT CPU
  MEM TIME COMMAND
• 18766 tom 15 0 2932 2932 2468 S 2.5
  0.5 002 _progres
• 18802 tom 15 0 2928 2928 2460 S 1.1
  0.5 001 _progres
• 18775 tom 15 0 2920 2920 2456 S 0.7
  0.5 001 _progres
• 18820 tom 15 0 2916 2916 2452 S 0.7
  0.5 000 _progres
• 18883 tom 15 0 2924 2924 2460 S 0.3
  0.5 000 _progres
• 18901 tom 15 0 2932 2932 2468 S 0.3
  0.5 000 _progres
• 18865 tom 15 0 2920 2920 2452 S 0.1
  0.5 000 _progres
• 18892 tom 15 0 2944 2944 2480 S 0.1
  0.5 000 _progres
• 18946 tom 15 0 2932 2932 2468 S 0.1
  0.5 000 _progres

21
SAR -u

• Linux 2.4.18-14smp (localhost.localdomain)
  08/28/2003
• 035212 PM CPU user nice
  system idle
• 035217 PM all 1.30 0.00
  0.30 98.40
• 035222 PM all 1.00 0.00
  0.00 99.00
• 035227 PM all 2.25 0.00
  0.25 97.50
• 035232 PM all 1.05 0.00
  0.30 98.65
• 035237 PM all 1.80 0.00
  0.30 97.90
• Average all 1.48 0.00
  0.23 98.29

22
PROMON

• 08/28/03 Activity Summary
• 155338 08/28/03 1547 to 08/28/03 1553
  (6 min 33 sec)
• Event Total Per Sec Event
  Total Per Sec
• Commits 0 0.0 DB Reads
  248 0.6
• Undos 0 0.0 DB Writes
  2 0.0
• Record Reads 977145 2486.3 BI Reads
  15 0.0
• Record Updates 0 0.0 BI Writes
  1 0.0
• Record Creates 0 0.0 AI Writes
  0 0.0
• Record Deletes 0 0.0
  Checkpoints 0 0.0
• Record Locks 0 0.0 Flushed at
  chkpt 0 0.0
• Record Waits 0 0.0
• Rec Lock Waits 0 BI Buf Waits 0
  AI Buf Waits 0
• Writes by APW 0 Writes by BIW 0
  Writes by AIW 0
• DB Size 797 K BI Size 2168 K
  AI Size 0 K
• Empty blocks 44 Free blocks 0
  RM chain 4
• Buffer Hits 99 Active trans 0

23
PROMON

• 08/28/03 Activity Summary
• 155413 08/28/03 1554 to 08/28/03 1554
  (10 sec)
• Event Total Per Sec Event
  Total Per Sec
• Commits 0 0.0 DB Reads
  0 0.0
• Undos 0 0.0 DB Writes
  0 0.0
• Record Reads 24812 2481.2 BI Reads
  0 0.0
• Record Updates 0 0.0 BI Writes
  0 0.0
• Record Creates 0 0.0 AI Writes
  0 0.0
• Record Deletes 0 0.0
  Checkpoints 0 0.0
• Record Locks 0 0.0 Flushed at
  chkpt 0 0.0
• Record Waits 0 0.0
• Rec Lock Waits 0 BI Buf Waits 0
  AI Buf Waits 0
• Writes by APW 0 Writes by BIW 0
  Writes by AIW 0
• DB Size 797 K BI Size 2168 K
  AI Size 0 K
• Empty blocks 44 Free blocks 0
  RM chain 4
• Buffer Hits 100 Active trans 0

24
PROMON

• 08/28/03 Activity Summary
• 155533 08/28/03 1555 to 08/28/03 1555
  (2 sec)
• Event Total Per Sec Event
  Total Per Sec
• Commits 0 0.0 DB Reads
  0 0.0
• Undos 0 0.0 DB Writes
  0 0.0
• Record Reads 4605 2302.5 BI Reads
  0 0.0
• Record Updates 0 0.0 BI Writes
  0 0.0
• Record Creates 0 0.0 AI Writes
  0 0.0
• Record Deletes 0 0.0
  Checkpoints 0 0.0
• Record Locks 0 0.0 Flushed at
  chkpt 0 0.0
• Record Waits 0 0.0
• Rec Lock Waits 0 BI Buf Waits 0
  AI Buf Waits 0
• Writes by APW 0 Writes by BIW 0
  Writes by AIW 0
• DB Size 797 K BI Size 2168 K
  AI Size 0 K
• Empty blocks 44 Free blocks 0
  RM chain 4
• Buffer Hits 100 Active trans 0

25
PROMON

• 08/28/03 Activity Summary
• 155552 08/28/03 1555 to 08/28/03 1555
  (2 sec)
• Event Total Per Sec Event
  Total Per Sec
• Commits 0 0.0 DB Reads
  0 0.0
• Undos 0 0.0 DB Writes
  0 0.0
• Record Reads 5159 2579.5 BI Reads
  0 0.0
• Record Updates 0 0.0 BI Writes
  0 0.0
• Record Creates 0 0.0 AI Writes
  0 0.0
• Record Deletes 0 0.0
  Checkpoints 0 0.0
• Record Locks 0 0.0 Flushed at
  chkpt 0 0.0
• Record Waits 0 0.0
• Rec Lock Waits 0 BI Buf Waits 0
  AI Buf Waits 0
• Writes by APW 0 Writes by BIW 0
  Writes by AIW 0
• DB Size 797 K BI Size 2168 K
  AI Size 0 K
• Empty blocks 44 Free blocks 0
  RM chain 4
• Buffer Hits 100 Active trans 0

26
IO by User

• -------- Database ----- ---- BI -----
  ---- AI -----
• Usr Name Access Read Write Read
  Write Read Write
• 0 tom 69 18 2 15
  1 0 0
• 5 tom 346693 144 0 0
  0 0 0
• 6 tom 308355 52 0 0
  0 0 0
• 7 tom 298295 0 0 0
  0 0 0
• 8 tom 169383 0 0 0
  0 0 0
• 9 tom 141844 0 0 0
  0 0 0
• 10 tom 109180 7 0 0
  0 0 0
• 11 tom 107386 0 0 0
  0 0 0
• 12 tom 92483 0 0 0
  0 0 0
• 13 tom 89759 14 0 0
  0 0 0
• 14 tom 82814 4 0 0
  0 0 0
• 15 tom 69935 0 0 0
  0 0 0
• 16 tom 70222 0 0 0
  0 0 0
• 17 tom 54890 0 0 0
  0 0 0
• 18 tom 53697 0 0 0
  0 0 0
• 19 tom 50947 4 0 0
  0 0 0

27
IO by User

• ...
• 51 tom 10213 0 0 0
  0 0 0
• 52 tom 9060 0 0 0
  0 0 0
• 53 tom 9100 0 0 0
  0 0 0
• 54 tom 8999 0 0 0
  0 0 0
• 55 tom 8050 0 0 0
  0 0 0
• 56 tom 8148 0 0 0
  0 0 0
• 57 tom 8020 0 0 0
  0 0 0
• 58 tom 7935 0 0 0
  0 0 0
• 59 tom 7907 0 0 0
  0 0 0
• 60 tom 6894 0 0 0
  0 0 0
• 61 tom 6982 0 0 0
  0 0 0
• 62 tom 6985 0 0 0
  0 0 0
• 63 tom 6898 0 0 0
  0 0 0
• 64 tom 5783 0 0 0
  0 0 0
• 65 tom 5831 0 0 0
  0 0 0
• 66 tom 5798 0 0 0
  0 0 0
• 67 tom 5792 0 0 0
  0 0 0

28
Table Monitor

• 161433 Table Read
  Monitor 08/29/03
• Overall Cumulative Rate 2,386/sec
  Overall Interval Rate 3,322/sec
• Total
  Rate Percentage
• Table Accum Inter
  Accum Inter Accum Inter
• -------------------- --------- ---------
  --------- --------- ------- -------
• Order-Line 1,279,816 16,378
  1,524 1,638 2.00 58.00
• Customer 96,617 3,839
  115 384 0.00 14.00
• Invoice 253,424 3,273
  302 327 0.00 12.00
• Order 293,117 2,509
  349 251 1.00 9.00
• Item 69,552 900
  83 90 0.00 3.00
• State 83,617 777
  100 78 0.00 3.00
• Ref-Call 31,047 314
  37 31 0.00 1.00
• Local-Default 21,452 24
  26 2 0.00 0.00
• Salesrep 21,822 0
  26 0 0.00 0.00

29
Questions
?

• http//www.greenfieldtech.com/downloads.shtml