Transcript Slide 1
Informix Chat with the Lab
August 16, 2006
Stuart Litel, President, International Informix Users Group
Jerry Keesee, Director of the Informix Lab
John F. Miller III, Senior Technical Staff Member
Keynote Presentation – “Information as a Service... A New Era in Databases”
Bob Picciano, VP of IBM Database Services, Information Management Division
Educational Seminars
Hands on with Advanced features
Where do our speakers come
from?
of IDS 10
IBM US
Using Perl, DBI and CGI with
Databases
IBM UK
IBM DE
IBM CZ
Informix users
Technical Presentations
Database Performance
Disaster Recovery and Data
Availability
Application Development
Security
Examples and Solutions
IDS Internals
Informix support
Products & Services Exhibitions
For conference information and
registration, go to www.iiug.org/conf
* Note: All registration is
handled by IDUG – The
International DB2 User
Group (IDUG).
Announcing IIUG / IDUG North America 2007
May 6 – 10, San Jose California
•
•
•
•
•
•
•
•
Call for presentations is now under way! This is YOUR opportunity to share
your Informix experience and expertise with an audience of your peers.
FREE conference registration if your presentation is selected.
Single session (60 minutes) or Double (120 minutes)
Abstract deadline is September 1st, 2006 – so start working on it now!
All that is needed for abstract submission is Title, Brief description of the
session and an outline.
Entire presentation is not due for a few months after being selected. Over
50 Informix sessions to be presented by fellow Informix users like yourself.
Go to the IIUG Website for more information and a link to the Call for
Presentations –
http://www.iiug.org/conf/
See you in San Jose…. The Heart of Silicon Valley.
Overview
• Recent Changes to onstat
• View I/O through onstat
– Disk IO
– Network IO
• Memory Usage
• Cache Sizes
• View Action by
– User
– Table
Recent onstat changes
• onstat –m
– Displays the message file when the server is
offline
• onstat –c
– Displays the configuration file when the server is
offline
New onstat Options
• onstat –g env
environment
• onstat –g stm
usage
• onstat –g rwm
• onstat –C
• onstat –g cac
caches
User/System
Statement memory
Read/Write Mutex
Btree Scanner
Display various
Looking at Environment
onstat –g env [sid]
Server Environment
cmd> onstat -g env
Server start-up environment:
Variable
Value [values-list]
DBDELIMITER
|
DBPATH
.
DBTEMP
/tmp
Session Environment
cmd> onstat -g env 147
Environment for session 147:
Variable
Value [values-list]
CLIENT_LOCALE en_US.8859-1
DBDELIMITER
|
DBPATH
//jmiller_94
DBTEMP
/usr/tmp
Memory by SQL Statement
onstat –g stm
• Shows all currently active SQL statements
• Shows the amount of memory each SQL
statement currently using in bytes
command>
session
sdblock
bca2018
session
sdblock
bc4d018
bc4d140
bc4d268
onstat -g stm
16 -------------------------------------heapsz statement ('*' = Open cursor)
11936 *select * from customer, orders
14 --------------------------------------heapsz statement ('*' = Open cursor)
2048 Database 'insdel'
8496 select count(*) from informix.systables;
6264 *select * from t2
Watching The Inverted Pyramid
onstat –g iov
AIO I/O vps:
class/vp s io/s totalops
msc 0 i
0.0
26
aio 0 s
0.2
1080
aio 1 i
0.2
1072
aio 2 s
0.2
884
aio 3 i
0.2
647
aio 4 i
0.2
252
aio 5 i
0.2
24
pio 0 i
0.0
2
lio 0 i
0.0
3
dskread dskwrite
0
0
883
188
874
189
852
28
831
13
832
16
811
12
0
2
0
3
dskcopy
0
0
0
0
0
0
0
0
0
wakeups
26
849
830
699
769
712
673
3
4
io/wup
1.0
1.3
1.3
1.3
1.1
1.2
1.2
0.7
0.8
errors
0
0
0
0
0
0
0
0
0
Watching The Inverted Pyramid
onstat –g iov
AIO I/O vps:
class/vp s io/s totalops
msc 0 i
0.0
26
aio 0 s
0.2
1080
aio 1 i
0.2
1072
aio 2 s
0.2
884
aio 3 i
0.2
647
aio 4 i
0.2
252
aio 5 i
0.2
24
pio 0 i
0.0
2
lio 0 i
0.0
3
dskread dskwrite
0
0
883
188
874
189
852
28
831
13
832
16
811
12
0
2
0
3
dskcopy
0
0
0
0
0
0
0
0
0
wakeups
26
849
830
699
769
712
673
3
4
io/wup
1.0
1.3
1.3
1.3
1.1
1.2
1.2
0.7
0.8
errors
0
0
0
0
0
0
0
0
0
Errors indicate a lack of resources in the I/O subsystem
Especially important if using KAIO
Watching The Inverted Pyramid
onstat –g iov
AIO I/O vps:
class/vp s io/s totalops
msc 0 i
0.0
26
aio 0 s
0.2
1080
aio 1 i
0.2
1072
aio 2 s
0.2
884
aio 3 i
0.2
647
aio 4 i
0.2
252
aio 5 i
0.2
24
pio 0 i
0.0
2
lio 0 i
0.0
3
S
I
dskread dskwrite
0
0
883
188
874
189
852
28
831
13
832
16
811
12
0
2
0
3
dskcopy
0
0
0
0
0
0
0
0
0
wakeups
26
849
830
699
769
712
673
3
4
io/wup
1.0
1.3
1.3
1.3
1.1
1.2
1.2
0.7
0.8
errors
0
0
0
0
0
0
0
0
0
indicates in System call (VP is currently busy)
indicates the VP is currently idle
Monitoring I/O Resources by File
onstat -g iof
AIO
gfd
3
4
5
6
global files:
pathname
/dev/tsinfo1
/dev/tsinfo2
/dev/tsinfo0
/dev/tsinfo3
totalops
1506
776
22205
55092
dskread dskwrite
1393
113
3
773
7994
14211
54102
990
dskread/dskwrite
Number of read/write system calls
io/s
Average I/O operations per second
io/s
0.0
0.0
0.4
1.1
Network Resources By Client Type
onstat -g ntd
Client Type
sqlexec
srvinfx
oncheck
onmonitor
ontape
Calls
yes
yes
yes
yes
yes
Accepted
4744
92
410
4
5
Rejected
26
0
0
0
0
Read
3911350
0
114668
136
66113
Write
3882491
0
114851
137
66117
Client Type
The type of client connecting to the IDS
Accepted/Rejected
Number of times a successful/unsuccessful connection occurred
Read/Write
The number of times the engine received/sent data to the clients
Network Resources By User
onstat -g ntu
Individual thread network information (basic):
netscb type
thread name
sid
fd poll
3fc97bc soctcp sqlexec
5269
66
5
3f857bc soctcp sqlexec
5268
64
5
3fa97bc soctcp sqlexec
5267
62
5
3e737bc soctcp sqlexec
5264
52
5
3c7f7bc soctcp sqlexec
5262
29
5
reads
38
1
41
1586
357
writes q-nrm q-exp
38 0/ 1 0/ 0
1 0/ 1 0/ 0
41 0/ 1 0/ 0
1585 0/ 1 0/ 0
357 0/ 1 0/ 0
sid
User session id
type
Indicates the communication protocol used for this thread
reads/writes
The number of OnLine packets received/sent by the OnLine
Show System Memory Usage
onstat –g seg
• Shows the amount of memory current
allocate, used and free
• Block of memory are in 4KB units
Segment Summary:
id
key
93001
1386432513
93002
1386432514
9509
1386432519
Total:
-
addr
10a000000
10ad00000
10eb00000
-
size
13631488
31457280
8388608
53477376
ovhd
424320
1696
992
-
class
R
V
V
-
blkused
3160
4685
123
7968
blkfree
168
2995
1925
5088
Useful onstat Options
• onstat –P
• onstat –g tpf
• onstat –g ppf
Buffers by Table
Actions by Thread
Actions by Table
Monitoring Different Caches
• Buffer Cache
– Stores commonly accessed data/index pages from
disk
• Data Dictionary
– Cache used to store commonly accessed system
catalog information
• Data Distributions
– Cache to store update statistics medium/high data
used by the optimizer to build query plans
• Stored Procedure Cache
– Caches stored procedure code
• Extensible Caches
Buffer Cache Sizing (onstat –p)
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
78201
141506
1538858 94.92
14480
23668
298884
95.16
• System Read Cache
– Increasing buffers improves read cache rate
– 90% or better is a good read cache
• System Write Cache
– Increasing buffers may not help the write cache
– LRU parameters and CKPTINTVL greatly affect the
write cache
Buffer Pool Stats by Table
onstat –P
partnum
1048707
1048710
1048714
1049081
1049472
total
10
4
3
257
5462
btree
6
1
1
0
0
data
2
1
1
255
5459
other
2
2
1
2
3
dirty
0
0
0
0
4635
Totals:
9000
52
5747
3201
4636
Percentages:
Data 72.86
Btree 25.58
Other 01.57
• Partnum
– Numeric link to
objects name
table/index/fragment
• Total
– Number of pages
currently buffered for
this table/fragment
• Data
– Number of data
page currently
buffered for this
table/fragment
Buffer Pool Stats by Table
onstat –P
partnum
1048707
1048710
1048714
1049081
1049472
total
10
4
3
257
5462
btree
6
1
1
0
0
data
2
1
1
255
5459
other
2
2
1
2
3
dirty
0
0
0
0
4635
Totals:
9000
52
5747
3201
4636
Percentages:
Data 72.86
Btree 25.58
Other 01.57
Buffer Pool System Statistics
• Data
– Percentage of the
buffer pool holding
data pages
• Btree
– Percentage of the
buffer pool holding
index pages
• Other
Ensure the ratio of System Data/Btree
pages looks reasonable for your
workload
– Control pages, such
as, reserve pages,
bitmap
Data Dictionary Cache
onstat -g dic
Dictionary Cache: Number of lists: 31, Maximum list size: 10
list# size refcnt dirty? heapptr
table name
-------------------------------------------------------11 1 1 no a2ecf88 d1@olympia:jmiller.t2
25
1
0
no
a2ec330 d1@olympia:informix.systables
Total number of dictionary entries: 2
• Shows the tables in the data dictionary
• Total number of current entries
– Currently 2
• Total number of available cache locations
– Currently 31 * 10 = 310
Data Distributions Cache
onstat –g dsc
Cache used to store update statistics
medium or high information
Distribution Cache:
Number of lists
PC_POOLSIZE
Number of entries
Number of entries in use
: 31
: 50
:2
:0
Distribution Cache Entries:
list# id ref_cnt dropped? heap_ptr
distribution name
----------------------------------------------------------------15
0
0
0
abe2c20
stores@oly:customer.lname
7
0
0
0
abe3820
stores@oly:customer.company
Total number of distribution entries: 2.
Stored Procedure Cache
onstat –g prc
Stored Procedure Cache:
Number of lists
PC_POOLSIZE
Number of entries
Number of inuse entries
: 31
: 50
:3
:0
Stored Procedure Cache Entries:
list# id ref_cnt dropped? heap_ptr
procedure name
-------------------------------------------------------------4
1
0
0
abe3c20
stores7@olympia:informix.systdist
22
2
0
0
a2f0c58
sysmaster@olympia:informix.bitval
14
3
0
0
a2f0458
sysmaster@olympia:informix.l2date
Total number of procedure entries: 3.
Extensible Caches
•
•
•
•
•
•
•
•
User Defined Routines
Routine Cache
Type Cache
Cast Cache
OpClass
Access Method Cache
Secondary Access Method Cache
User Defined Aggregate Cache
Actions by Table
onstat -g ppf
Partition profiles
partnum lkrqs lkwts
9437341 11
0
9437394 205
0
10485999 15
0
11534359 5
0
6291561 0
0
Table
dlks
0
0
0
0
0
Locks
touts
0
0
0
0
0
isrd
0
63
0
0
1854
iswrt
4
11
6
1
0
isrwt
0
0
0
0
0
isdel
0
8
0
0
0
SQL like Actions
bfrd
26
318
29
18
3605
bfwrt
14
129
18
8
0
seqsc
0
6
0
0
103
Buffers
Actions by Table
onstat -g ppf
Partition profiles
partnum lkrqs lkwts
9437341 11
0
9437394 205
0
10485999 15
0
11534359 5
0
6291561 0
0
dlks
0
0
0
0
0
touts
0
0
0
0
0
isrd
0
63
0
0
1854
iswrt
4
11
6
1
0
isrwt
0
0
0
0
0
isdel
0
8
0
0
0
bfrd
26
318
29
18
3605
bfwrt
14
129
18
8
0
seqsc
0
6
0
0
103
• partnum
Decimal number linked to systables:partnum
• lkrqs
LocK ReQuestS
Number of times a lock has been acquired on the table
• lkwts
LocK WaiTS
Number of times a user had to wait when acquiring a lock on this table
• Dlks
DeadLocKS
Actions by Table
onstat -g ppf
Partition profiles
partnum lkrqs lkwts
9437341 11
0
9437394 205
0
10485999 15
0
11534359 5
0
6291561 0
0
• isrd
dlks
0
0
0
0
0
touts
0
0
0
0
0
isrd
0
63
0
0
1854
iswrt
4
11
6
1
0
isrwt
0
0
0
0
0
isdel
0
8
0
0
0
bfrd
26
318
29
18
3605
bfwrt
14
129
18
8
0
ISam Reads
Number of times a rows has been read from this table
• iswrt
ISam WriTes
Number of calls to insert a row into this table
• isrwt
ISam ReWriTes
Number of calls to update a row in this table
• Isdel
ISam DELetes
Number of calls to delete a row from this table
seqsc
0
6
0
0
103
Actions by Table
onstat -g ppf
Partition profiles
partnum lkrqs lkwts
9437341 11
0
9437394 205
0
10485999 15
0
11534359 5
0
6291561 0
0
• bfrd
dlks
0
0
0
0
0
touts
0
0
0
0
0
isrd
0
63
0
0
1854
iswrt
4
11
6
1
0
isrwt
0
0
0
0
0
isdel
0
8
0
0
0
bfrd
26
318
29
18
3605
bfwrt
14
129
18
8
0
seqsc
0
6
0
0
103
BuFfer ReaDs
Number of individual page reads from this table
• bfwrt
BuFfer WRites
Number of times a page in this table has been modified
• seqsc
SeQuential SCans
Number of sequential table scans
Actions by User/Thread
onstat -g tpf
tid lkreqs
42 35
48 35110
3873 4646
3876 498
3648 1946
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
• User Thread
• Locks
• Log Records
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
• SQL Like Actions
• Transactions
• Buffers
Actions by User/Thread
onstat -g tpf
tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus
42
35
0
0 0 408 0
0
0
0
11
0
0 207 128 856
48 35110 0
0 0 32
0
0
0
0
14
0
0 638 2294 0
3873 4646 0
0 0 0
1469 89
0
0
0
0
0 6683 1080 0
3876 498
0
0 0 0
93
25
0
2
0
0
0 535 200
0
3648 1946 0
0 0 68
6266 42
4
1
5
0
0 4238 495
0
• tid
lsmx seq
120
0
208
0
0
2
0 16
4488 10
Thread ID
– Each user can have one or more threads, the TID has
a many-to-one relationship to the user's Session ID
Actions by User/Thread
onstat -g tpf
tid lkreqs
42 35
48 35110
3873 4646
3876 498
3648 1946
• lgrs
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
LoG Records
The number of log records created by this thread
• lsus
Log Space Used
The number of bytes of logical log space used by this thread
• lsmx
Log Space Maximum
– The space used by the thread's largest transaction
Actions by User/Thread
onstat -g tpf
tid lkreqs
42 35
48 35110
3873 4646
3876 498
3648 1946
• isct
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
ISam CommiT
The number of commits
• isrb
ISam RollBack
The number of times this thread has rolled back a
transaction
• lx
Long Transactions
The number of long transactions perpetrated by this thread
Actions by User/Thread
onstat -g tpf
tid lkreqs
42 35
48 35110
3873 4646
3876 498
3648 1946
• isrd
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
ISam Reads
Number of times a row has been read by this thread
• Iswr
ISam WRites
Number of calls to insert a row by this thread
• Isrw
ISam ReWrites
Number of calls to update a row by this thread
• Isdl
ISam DeLetes
Number of calls to delete a row by this thread
Actions by User/Thread
onstat -g tpf
tid lkreqs
42
35
48 35110
3873 4646
3876 498
3648 1946
• lkreqs
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
LocK REQuestS
Number of times a lock has been acquired by this thread
• lkwts
LocK WaiTS
Number of times a user had to wait when acquiring a lock
• dlksDeadLocKS
Number of Deadlock errors encountered by this user
• to
lock TimeOuts
Number of times a user waited on a lock, but failed to acquire
the lock in the time allotted
Actions by User/Thread
onstat -g tpf
tid lkreqs
42 35
48 35110
3873 4646
3876 498
3648 1946
lkw
0
0
0
0
0
dl
0
0
0
0
0
to
0
0
0
0
0
lgrs
408
32
0
0
68
isrd
0
0
1469
93
6266
iswr
0
0
89
25
42
isrw
0
0
0
0
4
isdl
0
0
0
2
1
isct
11
14
0
0
5
isrb
0
0
0
0
0
lx bfr
0 207
0 638
0 6683
0 535
0 4238
bfw lsus lsmx seq
128 856 120
0
2294 0 208
0
1080 0
0
2
200
0
0 16
495
0 4488 10
• Monitor the ratio of all SQL like actions over buffer actions.
This gives an indication of how much work is done for each
row processed
(isrd + iswr + isrw + isdl) / (bfr + bfw)
• For OLTP users this ratio will be low. For DW users
this ratio should be higher.
• A high ratio can mean indexing or optimizer issues
Questions