CPU consumption and table size
A production IDS 11.50 instance on a Solaris Sun Fire T2000 (Niagara) was pegging its assigned CPUs near 100% during peak hours, causing transaction timeouts and long checkpoints; the poster suspected the growth of a large transactional table (~250 columns, 40-50 indexes) and asked how row count relates to CPU use. Art Kagel replied that IDS doesn't work well with T-class hardware threads, advising that CPU VPs be pinned to one thread per core (e.g. processors 0,3,6,...,21 via VPCLASS aff) or hardware threads disabled. The poster's follow-up questions about finding the top CPU-consuming queries and the impact of NET poll threads went unanswered, so no further resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
One IDS instance running on a production server consuming a lot of CPU
resources. From following given output of mpstat, it is visible that
processors 7 to 15 which are assigned to that instance get almost 100% busy in
peak transaction hours, and causes the transactions timeouts and long duration
checkpoints.
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 369 26 3670 1637 200 1998 99 240 139 0 2323 17 19 0 64
1 163 14 490 619 0 865 82 186 56 0 3622 49 14 0 37
2 526 12 446 867 0 1146 36 242 55 1 2792 22 13 0 66
3 284 9 183 583 0 760 26 177 42 0 2616 19 15 0 67
4 214 5 1078 537 0 782 19 147 37 0 1761 28 11 0 61
5 258 6 219 427 0 670 9 156 36 0 1448 12 8 0 80
6 36 4 581 324 0 539 7 129 30 0 1521 9 6 0 85
7 16 0 136 43 0 50 42 7 23 0 1378 97 3 0 0
8 1 0 15 18 0 24 16 0 2 0 26 100 0 0 0
9 3 0 51 15 0 20 14 0 6 0 217 99 1 0 0
10 2 0 16 17 0 19 14 0 2 0 164 98 1 0 1
11 3 0 1925 1844 1807 65 34 1 16 0 523 90 9 0 1
12 3 0 193 22 0 31 18 0 9 0 679 96 2 0 2
13 2 0 25 28 0 42 21 1 3 0 118 97 0 0 2
14 1 0 62 45 30 18 13 0 4 0 106 99 1 0 1
15 2 1 234 238 219 25 16 0 10 0 182 98 2 0 1
16 104 14 325 533 7 1079 22 177 59 0 2120 12 7 0 80
17 57 12 555 632 66 1183 15 178 56 0 1685 6 10 0 84
18 374 14 493 558 2 1197 14 173 72 0 1284 5 7 0 89
19 129 15 534 471 0 1027 12 151 63 0 949 9 6 0 85
20 89 11 410 416 0 945 15 153 54 0 1712 9 8 0 83
21 139 7 268 440 0 982 17 157 43 0 1433 9 7 0 84
22 69 25 285 482 0 1088 15 170 51 0 1149 8 6 0 87
23 182 7 1141 468 0 1161 24 141 44 0 3054 14 12 0 75
The degradation in performance heavy load on CPU is observed after great
increase in size of a transactional table. This table has around 250 columns
and 40-50 indexes. My question is how can we find relaton between
table-size(number of rows) and CPU utilization. Thanks.
regards,
Kamran
$ uname -a
SunOS CA-MCPDB2 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200Informix version: IBM Informix Dynamic Server Version 11.50.FC3W1
I'll say it again, IDS and Sun T-class (Niagra processors) machines do not
go well together. You CANNOT configure IDS to use all of the hardware
threads on any one core. IDS should at most use the first thread on each
core and preferably the hardware threads should be disabled on any machine
on which IDS is running!
So, if what you actually have is 8 cores with three hardware threads each,
you should pin the CPU VPs to processor numbers 0, 3, 6, 9, 12, 15, 18, & 21
using the aff options to the VPCLASS parameter in the ONCONFIG file.
Art
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.
On Wed, Oct 14, 2009 at 4:13 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote:
> Hi,
> One IDS instance running on a production server consuming a lot of CPU
> resources. From following given output of mpstat, it is visible that
> processors 7 to 15 which are assigned to that instance get almost 100% busy
> in
> peak transaction hours, and causes the transactions timeouts and long
> duration
> checkpoints.
>
> CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
> 0 369 26 3670 1637 200 1998 99 240 139 0 2323 17 19 0 64
> 1 163 14 490 619 0 865 82 186 56 0 3622 49 14 0 37
> 2 526 12 446 867 0 1146 36 242 55 1 2792 22 13 0 66
> 3 284 9 183 583 0 760 26 177 42 0 2616 19 15 0 67
> 4 214 5 1078 537 0 782 19 147 37 0 1761 28 11 0 61
> 5 258 6 219 427 0 670 9 156 36 0 1448 12 8 0 80
> 6 36 4 581 324 0 539 7 129 30 0 1521 9 6 0 85
> 7 16 0 136 43 0 50 42 7 23 0 1378 97 3 0 0
> 8 1 0 15 18 0 24 16 0 2 0 26 100 0 0 0
> 9 3 0 51 15 0 20 14 0 6 0 217 99 1 0 0
> 10 2 0 16 17 0 19 14 0 2 0 164 98 1 0 1
> 11 3 0 1925 1844 1807 65 34 1 16 0 523 90 9 0 1
> 12 3 0 193 22 0 31 18 0 9 0 679 96 2 0 2
> 13 2 0 25 28 0 42 21 1 3 0 118 97 0 0 2
> 14 1 0 62 45 30 18 13 0 4 0 106 99 1 0 1
> 15 2 1 234 238 219 25 16 0 10 0 182 98 2 0 1
> 16 104 14 325 533 7 1079 22 177 59 0 2120 12 7 0 80
> 17 57 12 555 632 66 1183 15 178 56 0 1685 6 10 0 84
> 18 374 14 493 558 2 1197 14 173 72 0 1284 5 7 0 89
> 19 129 15 534 471 0 1027 12 151 63 0 949 9 6 0 85
> 20 89 11 410 416 0 945 15 153 54 0 1712 9 8 0 83
> 21 139 7 268 440 0 982 17 157 43 0 1433 9 7 0 84
> 22 69 25 285 482 0 1088 15 170 51 0 1149 8 6 0 87
> 23 182 7 1141 468 0 1161 24 141 44 0 3054 14 12 0 75
>
> The degradation in performance heavy load on CPU is observed after great
> increase in size of a transactional table. This table has around 250
> columns
> and 40-50 indexes. My question is how can we find relaton between
> table-size(number of rows) and CPU utilization. Thanks.
> regards,
> Kamran
>
> $ uname -a
> SunOS CA-MCPDB2 5.10 Generic_125100-09 sun4v sparc SUNW,Sun-Fire-T200> Informix version: IBM Informix Dynamic Server Version 11.50.FC3W1
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747360a889f890475eb3c1c
Can we get top queries with respect to CPU consumption/usage? And there is how much impact of poll threads of type NET with CPU high usage, if any?