Tech Tip Du Jour

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Obnoxio The Clown - 15 Jul 2008 23:41 GMT

Warning: I have been drinking.

http://obotheclown.blogspot.com/2008/07/tech-tip-du-jour-quick-performance.html

Signature

Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Reply to this Message

Captain Pedantic - 16 Jul 2008 22:55 GMT

Warning: I have been drinking.

http://obotheclown.blogspot.com/2008/07/tech-tip-du-jour-quick-performance.html

>> 3. Look at your data placement.
>> a) Check onstat -D for hot dbspaces. Look in the oncheck -pe output for
>> tables in those dbspaces, then look up the hex(partnum) of those tables
>> in sysmaster:systabnames and finally look in the onstat -g ppf for those
>> hex(partnums). Move naughty tables around.

Jesus! Tech Tip de 1994.
Warning: I have been drinking.

Reply to this Message

Obnoxio The Clown - 16 Jul 2008 23:15 GMT

Captain Pedantic said:

> Warning: I have been drinking.
>
[quoted text clipped - 9 lines]
> Jesus! Tech Tip de 1994.
> Warning: I have been drinking.

So you didn't know about the other stuff? Because most of that's been
around since 1994 as well.

:op

Signature

Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Reply to this Message

Captain Pedantic - 16 Jul 2008 23:53 GMT

Captain Pedantic said:

> Warning: I have been drinking.
>
[quoted text clipped - 9 lines]
> Jesus! Tech Tip de 1994.
> Warning: I have been drinking.

>> So you didn't know about the other stuff? Because most of that's been

around since 1994 as well.

The other stuff is still relevant today.

Reply to this Message

Obnoxio The Clown - 17 Jul 2008 00:04 GMT

Captain Pedantic said:

> Captain Pedantic said:
>> Warning: I have been drinking.
[quoted text clipped - 17 lines]
>
> The other stuff is still relevant today.

Obviously, not everyone is as sharp as you are. I got this (which has been
suitably anonymised) as part of a diagnostic yesterday:

c00000017d40c030 1 1 0 1103575 114235
c0000001800374a0 2 2 0 1813959 640462
c000000180037650 3 2 0 1444317 340926
c000000180037800 4 3 0 2073127 920772
c0000001800379b0 5 3 0 2024053 1000000
c000000180037b60 6 4 0 512066 784352
c000000180037d10 7 5 0 3162587 2915578
c000000180038030 8 6 0 3090302 2845817
c0000001800381e0 9 7 0 3249747 3001934
c000000180038390 10 8 0 3103808 2856052
c000000180038540 11 9 0 3069075 2823434
c0000001800386f0 12 10 0 3074443 2827789
c0000001800388a0 13 11 0 3073634 2826112
c000000180038a50 14 12 0 3005559 2758234
c000000180038c00 15 13 0 3093503 2905599
c000000180038db0 16 14 0 2041178 919
c000000180039030 17 15 0 2039366 824
c0000001800391e0 18 16 0 1909237 874
c000000180039390 19 17 0 1528277 927
c000000180039540 20 18 0 1527075 818
c0000001800396f0 21 19 0 1528290 919
c0000001800398a0 22 20 0 1527748 890
c000000180039a50 23 21 0 4101442 10073
c000000180039c00 24 22 0 62 0
c000000180039db0 25 23 0 62 0
c00000018003a030 26 24 0 1212772 1447
c00000018003a1e0 27 25 0 1212555 1389
c00000018003a390 28 26 0 1212592 1401
c00000018003a540 29 27 0 1214110 1408
c00000018003a6f0 30 28 0 1207947 1373
c00000018003a8a0 31 29 0 1213340 1410
c00000018003aa50 32 30 0 1211525 1424
c00000018003ac00 33 31 0 3547293 10272
c00000018003adb0 34 32 0 59 0
c00000018003b030 35 33 0 59 0
c00000018003b1e0 36 34 0 1329834 12613
c00000018003b390 37 35 0 13986213 137531
c00000018003b540 38 35 0 8764939 289743
c0000001800548a0 148 88 0 16 0
c000000180054a50 149 88 0 16 0
c000000180054c00 150 89 0 16 0
c000000180054db0 151 89 0 16 0
c000000180055030 152 90 0 16 0
c0000001800551e0 153 90 0 16 0
c000000180055390 154 35 0 16 0
c000000180055540 155 35 0 16 0
c0000001800556f0 156 91 0 8387318 17396
c0000001800558a0 157 92 0 3935544 23859
c000000180055a50 158 93 0 2927623 7458
c000000180055c00 159 121 0 2793503 4734

So for them, it was still relevant.

Signature

Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Reply to this Message

TBP - 17 Jul 2008 12:32 GMT

> Warning: I have been drinking.
>
> http://obotheclown.blogspot.com/2008/07/tech-tip-du-jour-quick-performance.html

2. Tune the engine.
...

c) Do you have any unexplained, random performance slowdowns? It could be the B-Tree Cleaner. onstat -C hot will tell how busy the
cleaner is, onstat -C will tell you how much time the cleaner has been active since the last engine reboot or onstat -z. If you are
spending a lot of time cleaning, then try increasing the threshold for cleaning by one or two orders of magnitude.

Well, I learnt something ...

one order of magnitude is 10 times bigger, i.e. 10 to the power 1
two orders of magnitude is 100 times bigger, i.e. 10 to the power 2

Sweet.

Soooooooo, what DOES onstat -C hot tell us :

Index Hot List
==============
Current Item 7 List Created 15:17:14
List Size 6 List expires in 291 sec
Hit Threshold 2000 Range Scan Threshold 200

Partnum Key Hits
0x00B00004 1 8406 *
0x00A00006 1 8390 *
0x00A00005 1 8274 *
0x00B00003 1 5907 *
0x00A00004 1 4794 *
0x00A00003 1 2724 *

So, not really that much - certainly I can't see from this that I have "badly configured BTSCANNER settings".

I think that onstat -C clean will give a better idea of the effectiveness of the settings :

> onstat -C clean | more

IBM Informix Dynamic Server Version 10.00.FC8 -- On-Line -- Up 20 days 23:18:03 -- 263776 Kbytes

Btree Cleaner Info

Index Cleaned Statistics
=========================
Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec
...
0x00600004 1 793 501 7159768 783 14290.95
0x00600005 1 563 14 279691 734 19977.93
0x00600006 1 427 14 308061 517 22004.36
0x00600007 1 8 5 54119 597 10823.80
0x00600008 1 1840 0 0 0 0.00
0x00600009 1 8 5 49415 501 9883.00
0x0060000a 1 723 1 49703 729 49703.00
0x0060000b 1 1582 0 0 0 0.00
0x0060000c 1 1344 254 5370704 8201 21144.50
0x0060000d 1 C 188 23195 484510382 9206 20888.57
0x0060000e 1 1313 385 5004046 403 12997.52
...

So, from the above, the settings are crap :D

For example for 0x0060000d 484510382 pages were examined (hmmm, about 924 Gig on a 2k system), and 9,206 items were deleted.

Bloody hell, crap :-/ I read nearly a terabyte of data, and deleted less than 10,000 index entries. Whoa there Lesley, don't buy
another disk array, increase the threshold!

So, with the default settings this is a bit concerning.

From 10.00.xC8 onwards it seems that the default threshold has increased from 500 to 5000 - a magnitude of 1 :D

BTSCANNER [num=scanner_threads,][threshold=committed_deleted],[rangesize=size]
BTSCANNER num=1,threshold=5000,rangesize=-1

So, what is a "good" setting for BTSCANNER - it depends :-/

I *think* you have to look at the bigger tables in the instance, and see how many deletes occur over a period of an hour say, and
then set the threshold to that value, so that you don't get huge sequential scans of the indexes all the time.

RANGESIZE I have a problem with - it works on the theory that you will not be deleting rows from disparate areas of the indexes
(i.e. from the beginning, middle and end). In a real world perspective I can't see how this can be a common case (sure deletion of
old data using a serial primary key perhaps, but what about all the other possible indexes?).

Anyways, hopefully something to ponder :D, and yes, orders of magnitude would seem to be appropriate for the THRESHOLD!

Reply to this Message

Obnoxio The Clown - 17 Jul 2008 12:51 GMT

TBP said:

>> Warning: I have been drinking.
>>
[quoted text clipped - 35 lines]
> So, not really that much - certainly I can't see from this that I have
> "badly configured BTSCANNER settings".

You didn't read the post carefully enough:

"onstat -C will tell you how much time the cleaner has been active since
the last engine reboot or onstat -z. If you are spending a lot of time
cleaning ..."

onstat -C gives output like this:

Btree Cleaner Info
BT scanner profile Information
==============================
Active Threads 1
Global Commands 2000000 Building hot list
Number of partition scans 3
Main Block 0x0C8EDB90
BTC Admin 0x0C6EDF30

BTS info id Prio Partnum Key Cmd
0x0C8EDCF0 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 <=== LOOK HERE
Number of index compresses 0
Number of deleted items 0
Number of index range scans 0
Number of index leaf scans 0
Number of index alice scans 0

If the "Time spent cleaning" seems unreasonable (more than a couple of
seconds per hour, really) then the B-Tree cleaner is probably thrashing.

> I think that onstat -C clean will give a better idea of the effectiveness
> of the settings :
[quoted text clipped - 43 lines]
> than 10,000 index entries. Whoa there Lesley, don't buy
> another disk array, increase the threshold!

That's good, too. Are you suggesting that my approach and reasoning are
not valid?

Signature

Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Reply to this Message

Art Kagel - 17 Jul 2008 16:22 GMT

Potato:

In your case I might go all the way and configure ALICE mode. It will do
MUCH more to reduce the work that the BTSCANNERS have to do on larger
indexes than even Range Scanning which you are planning to configure. Take
you config and add alice=12 to it. You are correct, the defaults are only
practical for very small databases and ones that do few large deletes.
Download the BTSCANNER presentation given at IIUG this year. It was very
good.

Art

> > Warning: I have been drinking.
> >
[quoted text clipped - 113 lines]
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Signature

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.

Reply to this Message

TBP - 18 Jul 2008 09:37 GMT

> Potato:
>
[quoted text clipped - 7 lines]
>
> Art

If you mean this one :

http://www.iiug.org/calendar/conf/cpc/slides_dvd/DVD/D14_Jamison_ALICE_and_Other
_BTSCANNER_Tips.pdf

I am struggling to get it :-/

Even logged in etc. etc.

Reply to this Message

Art Kagel - 18 Jul 2008 22:35 GMT

Got the presentations loaded onto the Oninit Web site now as well. Go to:

http://presentations.oninit.com/public/2008IIUG/D14_Jamison_ALICE_and_Other_BTSC
ANNER_Tips.pdf

For the BTScanner presentation.

Art

> > Potato:
> >
[quoted text clipped - 19 lines]
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Signature

Reply to this Message