Jon J Ritson
IBM Information Management
I05
Tuesday, October 3, 2006 • 10:15 a.m. – 11:15 a.m.
Platform:
Implementation Tuning IDS
2
2
Introduction
•
What is this Tech Talk about?
•
“Understand what you mean by performance”
•
Providing an approach to review systems from a performance
perspective
•
Providing an insight in the different diagnostic information that is
available.
•
Providing guidelines for setting configuration parameters
•
A walk through important onstat outputs to relate the information back
to configuration parameters that have an impact on performance.
•
“ Understand what you mean by performance”
Performance means many different things to many different people. The first step is to
identify
specifics to be able to work on each issue and be able to improve the performance.
•
Providing an approach to review systems from a performance perspective.
I will discuss the different approaches you should take when trying to improve performance in
different situations, different factors can have impact on performance and we will have a look
at all of them
• Providing an insight in the different diagnostic information that is available.
An explanation on all the different logs and other resources that are available to provide a
picture of the status and activities on the database server that help us to benchmark
performance.
• Providing guidelines for setting configuration parameters.
We will go through the guidelines for specific configuration settings based on workload and
diagnostic information found with the ‘Onstat’ command.
• A walk through important Onstat outputs to relate the information back to configuration
parameters that have an impact on performance.
3
Topics
•
Who am I?
•
Introduction.
•
What is performance anyway?
•
What do you need to look at?
•
The engine – What is available?
•
The online.log – A mine of information.
•
Walk through an onstat -a!
•
Conclusion!
•
Questions?
•
Links!
4
4
Who am I?
•
Jon J Ritson (JJ)
•
IBM IM Informix Technical Support
•
System Down
•
Advanced Diagnosis
•
Training
•
Tech Talks
•
Expectation Setting
5
What is performance anyway?
•
Performance means many different things to many different
people.
•
What is your understanding of performance?
•
"Ability to run satisfactorily at peak load"
•
"Users can always do their OLTP work within a maximum of 5
seconds!"
•
"The CEO report must complete within 1 hour!"
•
"It runs too slow!"
•
What, specifically, do you need to achieve?
It is no good having vague reference to “make it run faster”.
You need specifics to do actual work on.
What do you need to achieve?
Set some goals
Top level goals
OLTP transaction improvement from 20 seconds to 1 second - great!
OLTP transaction improvement from 1 second to 0.5 second - so what
Set Your Current Baseline.
Use something that you can actually measure!
Gather relevant information.
Make discrete changes.
Do NOT throw loads of changes live, one could be really good, and one
could be a bit bad – which one???
Assess where you are in relation to your baseline
i.e. were the changes good or bad!
Assess where you are in relation to your goals.
6
6
What do you need to look at
•
The environment where the engine is running
•
CPU Usage
•
Swap Usage
•
Disk Usage
•
Network usage
•
What is being run against the engine
•
5,000 OLTP connections from a web server
•
Big MIS reports
•
SOA
•
Finally, look at the engine!
What do you need to look at?
The IDS engine runs within a machine, so no matter how much you try and
tune an engine you also need to look how that engine is running on the machine.
If the machine is used for other things, like using gzip to continually compress
20 1 Gig files in parrallell across NFS mounted disks, think about what resources
are actually being made available to the engine.
The IDS engine runs applications, so no matter how much you try and tune an engine you also need to look at how that
engine is being used by applications
Check out queries before going live
Don’t just add indexes or let developers add indexes.
Use temp tables “with no log”
Finally, look at the engine.
Outside the engine
Get an idea of the charateristics of your environment.
the number of CPUs that the machine has
the amount of RAM that the machine has
the configuration of swap
the physical layout of the discs that the machine has
the actual devices that the chunks are pointing to (i.e. files, block special devices or raw)
Set up appropriate monitoring (sar, vmstat, iostats etc. etc.) of
CPU utilisation
Memory utilisation
Swap utilisation
Disk utilisation at the H/W level
Disk utilisation at the O/S level
Use relevant sampling (e.g. sample once an hour over a 7 day period)
Set up graphs to get a "feel for machine usage
Busy periods "generally"
7
The engine – what is available?
•
What can you look at?
•
online log
•
onstat output
•
Historical onstat output
•
sysmaster queries / data
•
Historical shared memory dumps :o)
online log
Historical onstat output
Look to cron at the same intervals as the other "relevant
monitoring”
onstat -a; sleep 3600; onstat -z;
that way you can tie up the samples
N.B. This can be a lot of output / have enough
space!
sysmaster queries / data
Historical shared memory dumps
If you have a lot of disk space, consider taking shared memory
dumps to
8
8
The online.log
– a mine of information!
---21:40:08 Checkpoint loguniq 13797, logpos 0x1852580, timestamp: 0xba5eb98f 21:40:08 Maximum server connections 584…
21:43:40 Checkpoint loguniq 13797, logpos 0x189e664, timestamp: 0xba5f0a5a 21:43:40 Maximum server connections 584
---10:37:13 Physical Recovery Started at Page (1:909).
10:37:14 Physical Recovery Complete: 43 Pages Examined, 43 Pages Restored. 10:37:14 Logical Recovery Started.
10:37:14 10 recovery worker threads will be started.
10:37:17 Logical Recovery has reached the transaction cleanup phase. 10:37:17 Logical Recovery Complete.
1 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
---10:40:28 Checkpoint loguniq 492, logpos 0x2ce018, timestamp: 0x165f081 10:40:29 Level 0 Archive started on rootdbs
10:40:30 Archive on rootdbs Completed.
10:40:31 Checkpoint Completed: duration was 0 seconds.
10:40:31 Checkpoint loguniq 492, logpos 0x2d3018, timestamp: 0x165f0e5 10:40:31 Level 0 Archive started on dbspace_blob1
---9
The online.log
– a mine of information!
•
What can we glean from an online log?
•
How many users are connected when
•
The observed duration of checkpoints
•
Fuzzy checkpoints
•
The rate of log consumption
•
Backup duration and type (parallel or serial)
•
Engine shutdown / start-up method (-ky vs -uky)
•
Long transactions
•
etc. etc.
How many users are connected which can be graphed :
21:36:38 Maximum server connections 584
The observed duration of checkpoints which can be graphed :
21:40:08 Fuzzy Checkpoint Completed: duration was 1 seconds, 609 buffers not
flushed, timestamp: 0xba5eb98f.
<snip>
22:36:19 Fuzzy Checkpoint Completed: duration was 0 seconds, 940 buffers not
flushed, timestamp: 0xba65e9a8.
The rate of log consumption can be established :
21:36:38 Checkpoint loguniq 13797, logpos 0x17f03dc,
timestamp: 0xba5e5a33
<snip>
22:36:19 Checkpoint loguniq 13797, logpos 0x20e75c0, timestamp: 0xba65e9a8
From logpos 0x17f03dc to logpos 0x20e75c0 in one hour, which is 0x20e7 - 0x17f0 pages per hour.
Therefore a total of 2,295 log pages in one hour => about 5 Mb an hour.
Messages regarding invalid DBSPACETEMP settings could point to a duff user environment
Long transactions
If there are physical pages recovered after startup, then the engine is shutdown either with onmode –ky or
there was an engine crash
Use onmode –uky; as this terminates open transactions on shutdown
onmode –ky leaves transactions open to be rolled back on startup
If there is no
10
10
Walk through an onstat -a!
•
What is an ‘onstat –a’?
•
Purpose is to relate the information from an onstat –a back to
configuration parameters.
•
Not covering all output!
•
Provide a starting point for most of the sections, along with
equivalent onstat commands
•
Go through an onstat –a, get a feel for where to review, and then
go through it again.
This is the main section of this presentation, and the purpose here is to be able
to relate the information provided in an onstat –a back to configuration
parameters.
11
The status line
•
The status line:
IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 4
days 23:54:37 -- 41688 Kbytes
•
The version of onstat
10.00.FC4
•
Status of the engine
On-Line
•
How long the engine has been up Up 4 days 23:54:37
•
The amount of memory in use
41,668 K
•
Message Log File:
/usr4/logs/jaimef_100b/online.log
•
Configuration File:
/usrinformix/etc/onconfig.jaimef_100b
Note that this is the version of the onstat, and not of the oninit of the engine.
Note that statistics gathered are from the last onstat –z or from when the
engine was started.
Is this the configuration file you thought would be in use??
12
12
onstat –u
(PDQ,BUFFERS,LOGBUFF)
Userthreads
address flags sessid user tty wait tout locks nreads nwrites 5bc94018 ---P--D 1 informix - 0 0 0 507 5391 5bc94630 ---P--F 0 informix - 0 0 0 0 25255 5bc94c48 ---P--F 0 informix - 0 0 0 0 32108 5bc99568 ---P--- 16 informix - 0 0 0 0 522 5bc99b80 ---P--B 17 informix - 0 0 0 269583 778 ...
5bc9a7b0 Y--P--D 26 informix - a08fd2c 0 0 0 0 5bc9adc8 ---P--D 20 informix - 0 0 0 0 0 ...
5bc9b3e0 Y--P--- 23289 rppages - 5c6f60b8 0 1 5824 5824 5bcb3ff8 Y--P--- 23290 rppages - 5e02a328 0 4 3000 3076 5bcbcc20 Y--P--- 22589 rppages - 5f0799d8 0 1 2912 2912 5bcbf6c8 Y--P--- 23549 rppages - 5ef12800 0 1 232 224 5bc6caae Y--P--- 23505 rppages - 6b9595c0 0 1 4171 4116 6caafce0 Y--P--- 23469 rppages - 732ad2a8 0 4 2636 3076
113 active, 640 total, 629 maximum concurrent
Userthreads
Are user sessions are generally reading and writing about the same? How many locks do user sessions generally have?
Different userids or only one or two userids here? Do any of the sessions have more than one entry (PDQ)?
Several Bs in the first column? May be an issue with BUFFERS/LRUs Several Ls in the first column? May be an issue with locking Several Gs in the first column? May be an issue with LOGBUFF Several Ss in the first column? May be an issue with Mutexes Several Cs in the first column? Checkpoint waits! What are the active and highest concurrent sessions?
This is “getting a feel” time!
Check for tout -1 which is “set lock mode to wait”. If these sessions have locks and are also WAITING for locks they
will cause issues.
What do the flags mean? (or “why keep onmonitor?”)
onmonitor / status / userthreads / CTRL-W
Userthread Status contains four sets of flags that indicate the status
as follows:
The flag codes for position 1:
B waiting for a buffer
C waiting for a checkpoint
G waiting on write of the logical log buffer
L waiting for a lock
S waiting for a mutex
T waiting on a transaction
Y waiting on a condition
X waiting on a long transaction cleanup (rollback)
The flag codes for position 2:
13
onstat –F - Flushers
(CLEANERS)
•
Foreground Writes – page flush requested by user thread - If this is
ever not zero then buffer pool is too dirty. Review
LRU_MAX_DIRTY / LRU_MIN_DIRTY, LRUS, BUFFERS and / or
CKPTINTVL.
Fg Writes LRU Writes Chunk Writes 0 0 92721 address flusher state data
5bc94630 0 I 0 = 0X0 5bc94c48 1 I 0 = 0X0 5bc95260 2 I 0 = 0X0 5bc95878 3 I 0 = 0X0 5bc95e90 4 I 0 = 0X0 5bc964a8 5 I 0 = 0X0 5bc96ac0 6 I 0 = 0X0 5bc970d8 7 I 0 = 0X0
states: Exit Idle Chunk Lru
Fg writes
Foreground Writes – page flush requested by user thread - If this is ever not zero then buffer pool is too dirty. Review LRU_MAX_DIRTY / LRU_MIN_DIRTY, LRUS, BUFFERS and / or CKPTINTVL.
Foreground writes occur when the system can not find an empty buffer to read a page into.
It must then request that a dirty page be flushed before we can read a page.
This value should always be zero.
If it is not, then the buffer pool might be getting full of dirty pages or the flushers are not able to keep up with the
dirtying of buffers.
This could be caused because the number of buffers is too small, or LRU_MAX is configured too high, or maybe
because there is disk contention on the physical drive that the buffer pages are being flush to.
In any case, having foreground writes is an indication that tuning needs to be done.
LRU Writes
Writes done between checkpoints, triggered by LRU_MAX_DIRTY / LRU_MIN_DIRTY; okay but not as efficient as Chunk Writes
Basically LRU writes are done between checkpoints are are triggered by LRU_MIN/LRU_MAX settings while chunk
writes are done as part of the checkpoint.
Chunk Writes
Writes done as part of a checkpoint; sorted by page and the most efficient
Chunk writes are more efficient than LRU writes because chunk writes are sorted by physical page address and LRU
writes are not.
However since checkpoints can cause an impact on the OLTP user, OLTP shops will often try to reduce the size of the
check point by increasing the LRU writes.
This is done by lowering LRU_MIN/LRU_MAX.
14
14
onstat –R
(BUFFERPOOLS)
Buffer pool page size: 2048
8 buffer LRU queue pairs priority levels # f/m pair total % of length LOW HIGH
0 f 256 99.6% 255 255 0 1 m 0.4% 1 1 0 2 f 256 100.0% 256 256 0 3 m 0.0% 0 0 0 …
3 dirty, 2048 queued, 2048 total, 2048 hash buckets, 2048 buffer size start clean at 10.000% (of pair total) dirty, or 26 buffs dirty, stop at
5.000% …
Buffer priority downgrade-upgrade history since server boot # f/m downgrades upgrades
0 f 14 12 1 m 0 6
BUFFERPOOLS
One for each PAGESIZE in use
Now a bit more convoluted, in that EACH buffer pool has :
BUFFERPOOL
size=8K,buffers=2048,lrus=8,lru_min_dirty=5.00,lru_max_dirty=
10.00
Each buffer pool will have to reviewed independently,
Depending on hardware, to keep checkpoints to a "reasonable" level :
LRU_MAX_DIRTY should represent 4 Mb to 16Mb – bear in
mind MULTIPLE bufferpools, and your disks
LRUs should represent about 4Mb for OLTP, and increasing for
MIS systems (128Mb is pretty high)
RA_PAGES / RA_THRESHOLD is for ALL BUFFER sizes!
Keep this small (below 64k / 32k), otherwise you may thrash your
BUFFERPOOLs
15
onstat –t
(-tblspaces)
Tblspacesn address flgs ucnt tblnum physaddr npages nused npdata nrows nextns 8 c0000000033c6640 0 1 100001 1:14 683 652 0 0 10 106 c0000000033c8180 0 1 200001 2:4 500 500 0 0 10 117 c0000000033c9b40 0 1 300001 3:4 300 300 0 0 6 121 c0000000033c6a80 0 1 400001 4:4 50 4 0 0 1 …
onstat –t – Tblspaces
Quick way to see what tables are active
16
16
onstat –d
(NUMAIOVPS)
Dbspaces
add number flags fchunk nchunks pgsize flags owner name 0xX 1 0x40001 1 1 2048 N B informix rootdbs 0xX 2 0x40001 2 1 2048 N B informix dbspace_1 0xX 4 0x42001 4 1 2048 N TB informix dbspace_temp1 0xX 6 0x40011 6 1 2048 N BB informix dbspace_blob1 0xX 7 0x48001 7 1 2048 N SB informix dbspace_sblob1 ... Chunks
add chunk/dbs offset size free bpages flags pathname
0xX 1 1 0 16384 2518 PO-B /dev/vg02/rjjf_100a 0xX 2 2 16384 8192 1073 PO-B /dev/vg02/rjjf_100a 0xX 4 4 32768 4096 4043 PO-B /dev/vg02/rjjf_100a 0xX 6 6 40960 8192 ~8192 8192 POBB /dev/vg02/rjjf_100a 0xX 7 7 49152 8192 6345 6346 POSB /dev/vg02/rjjf_100a Metadata 1793 182 1793 ...
NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
Check for temporary dbspaces, are they real TEMP dbspaces? Are there
enough?
Check for a high number of chunks in one dbspace, as this really provides IDS
with problems;
for example backups are by dbspace, so I have one critical dbspace for rootdbs,
physical logs and logical logs, one temporary dbspace, and one data dbspace
-I will backup in serial my single dbspace with 13 chunks, -I can do no table
fragmentation, I can do no parrallel sorting from table reads etc. etc.
Check the number of chunks against the NUMAIOVPs, considering whether
KAIO is in use;
it is probably better just to note how many chunks there are here at the moment
and consider later onstat -g ioa (iof, ioq) output.
17
onstat -l
•
Physical and Logical logging
•
LOGBUFF size depends on logging of databases
•
Increase LOGBUFF to get above 75 % LOGBUFF for
pages/IO when busy
•
All logical logs should be the same size (LTXHWM uses
complete logs for “percentage”)!
•
Parallel backups switch to next log at end of each
dbspace; enogh logs?
•
LOGBUFF is also used for HDR Log Buffer sizing
Logical logs :
Are they all the same size???
This may seem petty but LTXHWM (long transaction high water mark) is based on whole
logical logs and does not consider the actual size of each lo
gical log. e.g. I have 100Mb logical log and then 5 10 Mb logical logs, I do a BEGIN WORK at
the beginning of the 100 Mb logical log and fill it,
and then I fill 3 10 Mb logical logs, hit LTXHWM (set to 50%), and start to roll back, but I
need about 90Mb to roll back and I have 20 Mb of av
ailable logical log space!!
Are they mostly 100% used?? When we finish a dbspace backup with onbar we switch to the
next logical log.
Are they mostly backed up?? How are the logical log backups done?
Where is the "L"ast checkpoint in relation to the "C"current logical log?? Recovery
If this is spanning several logical logs, then consider
- is the physical log to big?
- Is CKPTINTVL set to default?
Are the logical logs too small?
One could aim to complete a logical log as determined by the granularity of
complete disk failure - i.e.
all I can salvage are my Level 0 backups and log tapes.
So, one could suggest 15 mins to complete a logical log would be a rule of
thumb.
18
18
onstat –l
(Physical / Logical log)
Physical LoggingBuffer bufused bufsize numpages numwrits pages/io P-2 0 16 117 14 8.36
phybegin physize phypos phyused %used
1:263 1000 823 0 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 402 88 86 4.6 1.0
Subsystem numrecs Log Space used OLDRSAM 401 38460 SBLOB 1 44
address number flags uniqid begin size used %used 0xX 1 U-B---- 493 1:1263 1000 18 1.80 0xX 2 U---C-L 494 1:2263 1000 6 0.60 0xX 3 U-B---- 489 1:3263 1000 1000 100.00 0xX 4 U-B---- 490 1:4263 1000 1000 100.00 0xX 5 U-B---- 491 1:5263 1000 1000 100.00 0xX 6 U-B---- 492 1:6263 1000 765 76.50
Not much to say about PHYSBUFF
Logical logs :
LOGBUFF depends on logging mode of databases and also transactional activity
Increase LOGBUFF to get above 75 % LOGBUFF for pages/IO when busy
Are they all the same size???
This may seem petty but LTXHWM (long transaction high water mark) is based on whole logical logs
and does not consider the actual size of each
logical log.
e.g. I have 100Mb logical log and then 5 10 Mb logical logs, I do a BEGIN WORK at
the beginning of the 100 Mb logical log and fill it,
and then I fill 3 10 Mb logical logs, hit LTXHWM (set to 50%), and start to roll
back, but I need about 90Mb to roll back and I have 20 Mb of
available logical log space!!
Are they mostly 100% used?? When we finish a dbspace backup with onbar we switch to the next logical
log.
Are they mostly backed up?? How are the logical log backups done?
Where is the "L"ast checkpoint in relation to the "C"current logical log?? Recovery
If this is spanning several logical logs, then consider
- is the physical log to big?
- Is CKPTINTVL set to default?
Are the logical logs too small?
One could aim to complete a logical log as determined by the granularity of complete
disk failure - i.e.
all I can salvage are my Level 0 backups and log tapes.
19
onstat –p
(Profile)
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6063 22664 46537 86.97 722 841 4228 82.92
isamtot open start read write rewrite delete commit rollbk 41449 2187 7731 18709 366 13 11 62 0 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
6 0 0 1 0 0 4
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 22.37 10.94 22 496
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
35 0 17913 0 0 19 743 105
ixda-RA idx-RA da-RA RA-pgsused lchwaits 35 0 19 54 405
onstat -p
There is a significant amount of "general" information here, but before
changes are made, further investigation should be made into the behaviour
assumed :
Any entries in ovlock, ovuserthread or ovbuff should be addressed!
%Read and %Write cache rates should be high (between 90% and 100%),
if not then consideration should be made towards "why"?
Are there a lot of rollbacks compared to commits??
bufwaits (in relation to bufreads) could be an issue and should be aimed to be
reduced
-(LRUs, LRU_MAX_DIRTY, LRU_MIN_DIRTY, RA_PAGES,
RA_THRESHOLD, CLEANERS).
seqscans should be relatively "low"
20
20
onstat –g glo
•
Consider :
NOAGE, AFF_SPROC, AFF_NPROC
vs
VPCLASS cpu,num=2,aff=1-2,noage
MT global info:
Individual virtual processors:
vp pid class usercpu syscpu total 1 8506 cpu 7.38 5.64 13.02 2 8507 adm 0.76 2.01 2.77 3 8508 cpu 13.45 1.16 14.61 4 8509 lio 0.20 0.47 0.67 5 8510 pio 0.16 0.44 0.60 6 8511 aio 0.19 0.48 0.67 7 8512 msc 0.01 0.02 0.03 8 8513 aio 0.16 0.43 0.59 9 8514 shm 0.24 0.46 0.70 tot 22.55 11.11 33.66
Think about RESIDENT as well.
Virtual Processor Summary section
If there are multiple CPU VPs, does "last" CPU VP use more than half of the
highest?
If so add more CPU VPs
If not take some away!
Same with the other VP classes (i.e. AIO or SOC etc.)
Consider :
NOAGE, AFF_SPROC, AFF_NPROC
vs
21
onstat –g ath
Threads:
tid tcb rstcb prty status vp-class name
2 c000000003017028 0 2 sleeping forever 4lio lio vp 0 3 c00000000302f630 0 2 sleeping forever 5pio pio vp 0 4 c000000003050630 0 2 sleeping forever 6aio aio vp 0 5 c000000003071630 0 2 sleeping forever 7msc msc vp 0 6 c0000000030a2630 0 2 sleeping forever 8aio aio vp 1 7 c0000000030c39b0 c000000002643028 4 sleeping secs: 1 1cpu main_loop() 8 c000000003071910 0 2 running 1cpu soctcppoll 9 c000000003017380 0 2 running 9shm sm_poll 10 c00000000311a9a8 0 3 sleeping forever 1cpu soctcplst 11 c000000003145d90 0 3 sleeping forever 1cpu sm_listen 12 c000000003170a00 0 2 sleeping secs: 1 1cpu sm_discon 13 c000000003170ce0 c000000002643790 2 sleeping forever 1cpu flush_sub(0) 23 c00000000331c1f0 c0000000026472d0 3 sleeping forever 1cpu aslogflush 24 c00000000331cc20 c000000002647a38 1 sleeping secs: 14 3cpu btscanner_0 40 c0000000038c13c8 c000000002649070 4 sleeping secs: 1 1cpu onmode_mon 44 c00000000385b4d8 c000000002648908 2 cond wait bp_cond 1cpu bf_priosweep() 150 c0000000037ed288 c0000000026481a0 2 cond wait netnorm 1cpu sqlexec 152 c0000000036c5bd8 c00000000264b578 2 cond wait sm_read 3cpu sqlexec
All threads
Hopefully most user threads in
cont wait sm_read
cond wait netnorm
22
22
onstat –g seg
(RESIDENT)
Segment Summary:
id key addr size ovhd class blkused blkfree 2609 1391282179 c000000000347000 569344 768 M 138 1 10407 1391282177 c00000000101d000 20049920 430696 R* 4891 4 2808 1391282178 c00000000233c000 25165824 1512 V* 5062 1082 813 1391282180 c000000005d4a000 8388608 1000 V* 1 2047
Total: - - 54173696 - - 10092 3134
(* segment locked in memory)
Avoid multiple virtual segments
Consider RESIDENT -1 (All segments resident) if available memory!
AIX will allocate a bit at a time, even if RESIDENT 1 or -1.
Do not over allocate!!
HP KAIO will really like -1.
Over allocation is also a waste, review blkused / blkfree for the virtual
segments (8k blocks)
23
onstat –g dic and onstat –g prc
Dictionary Cache: Number of lists: 31, Maximum list size: 10
list# size refcnt dirty? heapptr table name
---0 2 ---0 no c---0---0---0---0---0---0---0---03521838 sysmaster@jaimef_1---0---0a_tcp:informix.sysptprof
Total number of dictionary entries: 28
---UDR Cache:
Number of lists : 31 PC_POOLSIZE : 127
UDR Cache Entries:
list# id ref_cnt dropped? heap_ptr udr name
---0 134 ---0 ---0 c---0---0---0---0---0---0---0---034---06838 syscdr@jaimef_1---0---0a_tcp:.destroy 3 27 0 0 c0000000033fac38 syscdr@jaimef_100a_tcp:.destroy 6 133 0 0 c000000003433438 syscdr@jaimef_100a_tcp:.assign 6 33 0 0 c000000003402838 syscdr@jaimef_100a_tcp:.destroy
onstat –g dic – Dictionary cache
DD_HASHSIZE
Default 31 is small
DD_HASHMAX
Default 10 is okay
DD_HASHSIZE
Suggest 151
DD_HASHMAX
Suggest 10
onstat –g prc – User Defined Routine cache (Procedures)
PC_HASHSIZE
Default 31
PC_POOLSIZE
Default 127 (is small)
PC_HASHSIZE
Suggest 151 (Aim for about 10 entries)
PC_POOLSIZE
Suggest 1510 (Total number of UDRs!)
Dictionary cache should hold number of “active table”s, bearing in mind 50
internal procedures per database!!
24
24
onstat –g cac
•
onstat –g cac – Other caches
•
Sizing of other caches are based on
•
DS_HASHSIZE
Default 31
•
DS_POOLSIZE
Default 127 is small!
•
Size these for the highest cache usage
•
Resolved Routine Cache
•
Distribution Cache
•
Extended Type Name Cache
•
Extended Type ID Cache
•
Cast Cache
•
Opclass Instance Cache
•
User-defined Aggregate Cache
•
Suggest setting DS_HASHSIZE to 151 (or as PC_HASHSIZE)
•
Suggest setting DS_POOLSIZE to 1510 (or as PC_POOLSIZE)
Set DS_HASHSIZE/DS_POOLSIZE to the same values as
PC_HASHSIZE/PC_POOLSIZE to have
the procedure cache and resolved routine cache in sync.
onstat -g cac
What are the main caches and how do you configure them?
Dictionary Cache
onstat -g dic
DD_HASHSIZE Should be a prime e.g. 503
DD_HASHMAX Should be 10 or more e.g. 4
Should be increased to cover ALL entries.
Review whether there alot of "dirty" entries, if so is there are "rogue" alter
table activity like "update statistcs"!
A new entry is generated for each remote table queried - so the more the better
Distribution Cache
onstat -g cac dst
onstat -g dsc
25
onstat –g ioa
(NUMAIOVP)
AIO global info: 7 aio classes 12 open files 64 max global files AIO I/O queues:
q name/id len maxlentotalops dskread dskwrite dskcopy sqli_db 0 0 0 0 0 0 0
kio 0 0 16 1483 1041 442 0 kio 1 0 16 5342 5059 283 0 …
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors kio 0 i 0.1 1403 1041 362 0 2611 0.5 0 kio 1 i 0.3 5226 5041 185 0 10271 0.5 0 …
AIO global files:
gfd pathname totalops dskread dskwrite io/s
3 rjaimef_100a 2394 2017 377 0.2 4 rjaimef_100a 1596 1487 109 0.1
onstat -g ioa
One of the biggest clues to I/O issues is the maxlen column, however there are several areas that need to be considered:
Is kernel AIO in use, in which case how many CPU VPs are there as there to perform KAIO
Is AIO in use, if so are raw disks being used or file system; beware of filesystem cache!
Is NUMAIOVPs set in the $ONCONFIG? If not a really bad allocation of two AIO VPs per
chunk is done.
Are *real* raw devices in use!
crw-rw----
1 informix informix
64 0x0200001 May 11 09:59
/dev/vg02/rjaimeu_731a
Note the "c"
at the begining of the output which stands for "character special"
Note the "r" which prefixes the logical volume name (on HP at least) for the raw device.
Note that "b" at the beginning will denote a "block special device" which is where file systems
are mounted on, this should NOT be used!
If there is only 1 CPU VP and kernel AIO is in use, then there will be performance issues, as the single CPU VP will
have to be doing all the work for I/O, computational, scheduling, network issues etc. etc.
So, consider disabling KAIO and having AIO VPs.
Looking at maxlen, we would like to see that at or below 16 - above 16 and there is probably room for tuning.
If there are figures above 16, then consider the following :
All instances should have at least 2 temporary dbspace, and busy systems can benefit with up to
4, this is an easy thing to do and can give very good returns on performance.
With AIO VPs, too few is bad, but too many can cause hangs! Review onstat -g glo output to see
where the effective use of AIO VPs tails off (e.g. how many before < 50% CPU usage of the
first).
26
26
onstat –g nta
(NETTYPE)
global network information:
#netscb connects read write q-free q-limits q-exceedalloc/max 7/ 11 86 1488 1641 1/ 1 135/ 10 0/ 0 2/ 2
Individual thread network information (basic):
netscb type thread name sid fd poll reads writes q-nrm q-pvt q-exp 000003138088 ipcshm sqlexec 96 0 0 13 16 0/ 0 0/ 0 0/ 0
Individual thread network information (times):
netscb thread name sid open read write address c000000003138088 sqlexec 96 14:35:53 14:36:03 14:36:03
c000000003101bb8 soctcplst 4 10:37:11 14:58:49 jaime|2151|soctcp
Network mailbox information:
box netscb thread name max received in box max in box full signal 5 c0000000030e1628 soctcppoll 10 188 0 2 0 yes
Client Type Calls Accepted Rejected Read Write sqlexec yes 86 0 54 62 No MaxConnect instances connected
All network information
Network connections on NET VPs
Multiple DBSERVERALIASES for multiple listeners
3rdparameter is the total number of connections not connections per poll thread
Rule of thumb :
1 poll thread per 300 to 500
1 Listener per 1,000
3
rdparameter total protocol connections plus “leeway”
NETTYPE and how to tune it! The continuing debate!!
Up until 9.40.xC8 and 10.00.xC5 the following should be used to calculate NETTYPE settings :
Run Network connections on NET VPs
Roughly 300 to 500 connections per poll thread.
Roughly 1000 connections per listener thread (i.e. DBSERVERALIASES for that protocol)
So, If I have 1,000 users connecting via soctcp, then I would suggest having :
NETTYPE
soctcp,3,1200,NET
and one DBSERVERALIAS running on sockets.
Note that the 3rd parameter is the TOTAL number of connections (plus a bit)
If I have 3,000 users connecting via soctcp, then I would suggest having :
NETTYPE
soctcp,6,3400,NET
and 3 DBSERVERALIASES running on sockets.
Note that the 3rd parameter is the TOTAL number of connections (plus a bit)
9.40.xC9 uses the 3rd parameter to multiply the number of poll threads for available connection memory.
Check out the q-exceed value and the q-limites:
global network information:
27
onstat –g ppf
(Partition Profiles)
Partition profilespartnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio 0xf 0 0 0 0 5 0 0 0 12367 0 0 89 0x14 0 0 0 0 9540 0 0 0 0 0 0 0 0x17 20 0 0 0 623 0 0 0 0 0 0 0 0x18 12 0 0 0 56 0 0 0 792 0 0 96 0x19 0 0 0 0 168 156 0 0 0 0 0 0 0x1a 3 0 0 0 3 0 0 0 0 0 0 0 0x1c 20 0 0 0 437 0 0 0 0 0 0 0 0x23 20 0 0 0 20 0 0 0 0 0 0 0 0x402 0 0 0 0 0 3 0 0 0 0 0 0 0x100001 0 0 0 0 0 0 0 0 0 0 0 0 0x100002 606 0 0 0 259 0 0 0 852 0 15 100 0x100003 6678 0 0 0 3084 0 0 0 7757 0 0 100 0x100004 757 0 0 0 361 0 0 0 788 0 0 97
Partition Profiles
Based on partition / fragment
Can be used to determine “busy” tables / indexes
Entries under seqsc may suggest missing index
lkwts / dlks / touts suggest potential for application improvements (hot rows
etc.)
Lkrqs Lock requests
28
28
Others not really covered!
•
onstat -g tpf – Thread Profiles
•
Loads of useful information here for each specific thread; number of
locks requested, log records created, sequential scans, etc.
•
onstat -g mgm – Memory Grant Manager
•
Anything under the “gates”? Resource limitation can be preventing
sessions running; PDQ set up correctly?
•
onstat -g env
•
check for odd environment variables!!!
•
onstat -g arc
•
Worth while just checking that a backup has been done!
Copy an instance to another machine :
ontape -s -L 0 -F -t STDIO | ssh host2 ".
/home/informix/.profile;ontape -r -t STDIO"
Set up HDR using STDIO
ontape -s -L 0 -F -t STDIO | remsh jaime
"FPATH=/usr/local/functions;set_engine > /dev/null; set_jaimef_100s; ontape
-p -t STDIO; onmode -d secondary jaimef_100p_tcp“
29
onstat –g ddr / rqm (ER)
Onstat –g ddrDDR Running
--# Event Snoopy Snoopy Replay Replay Current Current Buffers ID Position ID Position ID Position 1040 494 ab07c 494 18 494 ac000
onstat -g rqm brief
IBM Informix Dynamic Server Version 10.00.FC4 On-Line Up 04:38:32 --52904 Kbytes
Queue trg_send (all totals approximate) # Txns In Memory: 1
Server g_jf_100b_tcp (152) # txns in queue: 1
Queue control_send (all totals approximate) # Txns In Memory: 2
Better to have onstat –g cdr – a bit like an onstat –a for ER
onstat –g ddr
Check that you are “keeping up”
i.e. Snoopy, current and replayid are close
onstat –g rqm brief
If empty then “all is up to date”, otherwise a summary of outstanding
replication information both for data and control messages
onstat –g rqm sendq
Worthwhile understanding this as it provides detailed information for each
replicate from “this” server to each of the other servers.
30
30onstat -C
•
Btree Scanner
•
onmode -C
•
|-- -C--+---+---|
•
+-start---•
+-stop--count---+
•
+-kill--count---+
•
+-threshold--size-+
•
+-high---+
•
'-low---'
•
BTSCANNER
[num=scanner_threads,][priority=low|high,][threshold=committed_dele
ted],[rangesize=size]
•
BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
Element Purpose Key
Considerations
-C
Controls the B-tree scanner for cleaning indexes of deleted items
Additional Information:
There is no limit to the number of threads that can run at one time. However, there is a limit of 128 threads that can be
started at one time. If, for example, you wanted 150 threads to run, you could execute two commands: onmode -C 100
and onmode -C 50.
stop count
kill count
Stops or kills the B-tree scanner threads
Additional Information: Either of these threads
stops or kills the B-tree scanners. If a count you specify is higher than the number of threads currently running, the
current number is assumed. If you do not specify a number, then a count of 1 is assumed.
threshold size
Sets the minimum number of deleted items an index must encounter before placing a priority or
hot list
Additional Information: Once all indexes above the threshold are cleaned, then indexes below
the threshold are added to the hot list. The default threshold is 500.
onmode -C threshold -1 cleans every index in the database.
onmode -C threshold 0 cleans every index that has a dirty_hit count greater than 0.
high
Sets the priority of all B-tree scanner threads that are running
Additional Information: This
option sets the priority of the B-tree scanner threads to equal that of normal users.
low
Sets the priority of all B-tree scanner threads that are running
Additional Information: This
option sets the priority of the B-tree scanner threads lower than that of normal users. This command allows the B-tree
scanner to consume only spare system resources, ensuring that the threads will not use the CPU cycles of normal users.
The default priority is low.
BTSCANNER [num=scanner_threads,][priority=low|high,][threshold=committed_deleted],[rangesize=size]
BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
Here is what this paramater means:
num= the number of btree scanner threads to start when the engine starts up
priority= low | high
low means run the btree scanner below a normal user thread and only
give the btree scanner extra CPU cycles
high means run the btree scanner thread equal to that of a normal users
threshold=
31
onstat -C
Btree Cleaner Info BT scanner profile Information ==============================
Active Threads 1
Global Commands 20000 Building hot list Number of partition scans 192
Main Block 0xc0000000031650d8 BTC Admin 0xc000000002647a38 BTS info id Prio Partnum Key Cmd 0xc000000003165300 0 Low 0x00000000 0 40 Yield N Number of leaves pages scanned 0
Number of leaves with deleted items 0
Time spent cleaning (sec) 0
Number of index compresses 0
Number of deleted items 0
Number of index range scans 0
32
32
Conclusion
•
Understand where you are now
•
Understand what your goals are
•
Keep a history
•
Discrete changes and monitor effect
•
Remember outside the engine!
34
34
