 |
IDS Forum
Re: Performance issue after 732 to 94fc6 upgra....
Posted By: Khaled Bentebal
Date: Tuesday, 13 June 2006, at 11:01 a.m.
In Response To: Re: Performance issue after 732 to 94fc6 upgrade P (Doug Fossmeyer)
Hi Doug,
I noticed that 75% of the buffers are occupied by a single table accessed sequentially (its partnum is the following 15728642). Another table occupies 10% of the buffer cache (its partnum is 3147332). You can look at the onstat -P output for this info.
It is either a lack of indexes or most likely an optimisation problem.
1/ Try setting OPTCOMPIND to 0
2/ The resident parameter should be 0 or 1 and not : RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0)
3/ The affinity should be set to off : VPCLASS CPU,num=3,aff=1-3,noage
VPCLASS AIO,num=4,aff=0-3,noage
4/ Why are you using 4 AIO VPs? You should use one or 2 AIO Vps max in your case.
5/ Drop the statistics high and test.
6/ Increase the numbre of cleaners
I think that you have an optimisation problem since it is slow on some large tables. The optimizer is using the wrong routes.
Keep me posted.
Regards,
Khaled Bentebal
ConsultiX
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: http://www.consult-ix.fr
----- Original Message -----
From: "Doug Fossmeyer" <DougF@SpokaneSchools.org>
To: <ids@iiug.org>
Sent: Tuesday, June 13, 2006 2:02 AM
Subject: Re: Performance issue after 732 to 94fc6 upgra.... [6932]
>
> This is embarrassing, our email server (groupwise is configured to limit email
> to 30 k. I am having to post my issue to the list serve with that limitation,
> meaning that to post the onstat and dbschema requires 3, yes 3 emails.
>
> I hope someone can evaluate the content and provide some guidance. Aside from
> upgrading to FC7 we are at a loss as to why the large batch processes are
> taking so long. Recap: I zero'd out the statistics just before running the
> process. Nothing else is running at the same time. Our issue is that after the
> upgrade most OLTP and small batch processes are running fine, the processes
> with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS
> 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport,
> install seq Tools, Engine, Network steps, dbimport, manually update stats of
> system tables, update stats using dostats.
>
> Thanks in advance, Doug
>
> Part 1
>
> onstat -p
>
> IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:51:54
> -- 2325280 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 1692924 5680780 2148702238 99.92 98673 478066 9326835 98.94
>
> isamtot open start read write rewrite delete commit rollbk
> 1403470631 5711631 27131297 1288458477 714047 182811 21956 192676 0
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 25339.64 948.51 217 1608
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 38861 0 1361203201 0 0 77 374795 1127856
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 483892 201 25013 509073 4265
>
> onstat -d
>
> IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:11
> -- 2325280 Kbytes
>
> Dbspaces
> address number flags fchunk nchunks flags owner name
> c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs
> c00000008f581ce8 2 0x1 2 1 N informix syscat94
> c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941
> c00000008f584028 4 0x20001 13 11 N informix ifashrpy
> c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94
> c00000008f584328 6 0x2001 24 1 N T informix tempdbs6
> c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7
> c00000008f584628 8 0x2001 26 1 N T informix tempdbs8
> c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9
> c00000008f584928 10 0x20001 28 1 N informix tempdbs10
> c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941
> c00000008f584c28 12 0x20001 40 1 N informix plogdbs94
> c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp
> c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt
> c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2
> 15 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> c00000008e3fc028 1 1 0 15000 2826 PO-- /dbms/links/rootdbs9
> c00000008f57d710 2 2 0 1023744 966451 PO-- /dbms/links/syscatdbs94
> c00000008f57d8a8 3 3 0 1023744 3513 PO-- /dbms/links/ifasnrdbs941
> c00000008f57da40 4 3 256 1023744 3 PO-- /dbms/links/ifasnrdbs942
> c00000008f57dbd8 5 3 256 1023744 2029 PO-- /dbms/links/ifasnrdbs943
> c00000008f57dd70 6 3 256 1023744 27788 PO-- /dbms/links/ifasnrdbs944
> c00000008f57e028 7 3 256 1023744 53819 PO-- /dbms/links/ifasnrdbs945
> c00000008f57e1c0 8 3 256 1023744 17378 PO-- /dbms/links/ifasnrdbs946
> c00000008f57e358 9 3 256 1023744 235004 PO-- /dbms/links/ifasnrdbs947
> c00000008f57e4f0 10 3 256 1023744 1023441 PO-- /dbms/links/ifasnrdbs948
> c00000008f57e688 11 3 256 1023744 1023241 PO-- /dbms/links/ifasnrdbs949
> c00000008f57e820 12 3 256 1023744 1023741 PO-- /dbms/links/ifasnrdbs9410
> c00000008f57e9b8 13 4 0 1023744 8 PO-- /dbms/links/ifashrpy1
> c00000008f57eb50 14 4 256 1023744 0 PO-- /dbms/links/ifashrpy2
> c00000008f57ece8 15 4 256 1023744 89 PO-- /dbms/links/ifashrpy3
> c00000008f57ee80 16 4 256 1023744 470 PO-- /dbms/links/ifashrpy4
> c00000008f57f028 17 4 256 1023744 2 PO-- /dbms/links/ifashrpy5
> c00000008f57f1c0 18 4 256 1023744 9 PO-- /dbms/links/ifashrpy6
> c00000008f57f358 19 4 256 1023744 6 PO-- /dbms/links/ifashrpy7
> c00000008f57f4f0 20 4 256 1023744 3 PO-- /dbms/links/ifashrpy9
> c00000008f57f688 21 4 256 1023744 552910 PO-- /dbms/links/ifashrpy10
> c00000008f57f820 22 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy11
> c00000008f57f9b8 23 5 0 511872 51819 PO-- /dbms/links/llogdbs94
> c00000008f57fb50 24 6 256 1023744 1021639 PO-- /dbms/links/tempdbs6
> c00000008f57fce8 25 7 256 1023744 1023583 PO-- /dbms/links/tempdbs7
> c00000008f57fe80 26 8 256 1023744 1023591 PO-- /dbms/links/tempdbs8
> c00000008f580028 27 9 256 1023744 1023591 PO-- /dbms/links/tempdbs9
> c00000008f5801c0 28 10 256 1023744 1023675 PO-- /dbms/links/tempdbs10
> c00000008f580358 29 11 0 1023744 3 PO-- /dbms/links/ifasdevdbs1
> c00000008f5804f0 30 11 256 1023744 3817 PO-- /dbms/links/ifasdevdbs2
> c00000008f580688 31 11 256 1023744 1276 PO-- /dbms/links/ifasdevdbs3
> c00000008f580820 32 11 256 1023744 25494 PO-- /dbms/links/ifasdevdbs4
> c00000008f5809b8 33 11 256 1023744 22984 PO-- /dbms/links/ifasdevdbs5
> c00000008f580b50 34 11 256 1023744 143 PO-- /dbms/links/ifasdevdbs6
> c00000008f580ce8 35 11 256 1023744 0 PO-- /dbms/links/ifasdevdbs7
> c00000008f580e80 36 11 256 1023744 14017 PO-- /dbms/links/ifasdevdbs8
> c00000008f581028 37 11 256 1023744 133739 PO-- /dbms/links/ifasdevdbs9
> c00000008f5811c0 38 11 256 1023744 948330 PO-- /dbms/links/ifasdevdbs10
> c00000008f581358 39 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy12
> c00000008f5814f0 40 12 0 63750 197 PO-- /dbms/links/plogdbs94
> c00000008f581688 41 13 256 1023744 1023691 PO-- /dbms/links/ifasdbs94temp
> c00000008f581820 42 14 256 1023744 1004580 PO-- /dbms/links/ifasdev2dbspyt
> c00000008f5819b8 43 15 256 1023744 634273 PO-- /dbms/links/ifasdev2dbspyt2
> c00000008f581b50 44 15 256 1023744 0 PO-- /dbms/links/ifasdev2dbspyt3
> 44 active, 2047 maximum
>
> Expanded chunk capacity mode: disabled
>
> onstat -D
>
> IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:43
> -- 2325280 Kbytes
>
> Dbspaces
> address number flags fchunk nchunks flags owner name
> c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs
> c00000008f581ce8 2 0x1 2 1 N informix syscat94
> c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941
> c00000008f584028 4 0x20001 13 11 N informix ifashrpy
> c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94
> c00000008f584328 6 0x2001 24 1 N T informix tempdbs6
> c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7
> c00000008f584628 8 0x2001 26 1 N T informix tempdbs8
> c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9
> c00000008f584928 10 0x20001 28 1 N informix tempdbs10
> c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941
> c00000008f584c28 12 0x20001 40 1 N informix plogdbs94
> c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp
> c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt
> c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2
> 15 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset page Rd page Wr pathname
> c00000008e3fc028 1 1 0 481 2063 /dbms/links/rootdbs9
> c00000008f57d710 2 2 0 900 112 /dbms/links/syscatdbs94
> c00000008f57d8a8 3 3 0 194832 241 /dbms/links/ifasnrdbs941
> c00000008f57da40 4 3 256 566 20 /dbms/links/ifasnrdbs942
> c00000008f57dbd8 5 3 256 15582 667 /dbms/links/ifasnrdbs943
> c00000008f57dd70 6 3 256 18903 12243 /dbms/links/ifasnrdbs944
> c00000008f57e028 7 3 256 26 5 /dbms/links/ifasnrdbs945
> c00000008f57e1c0 8 3 256 8324 6 /dbms/links/ifasnrdbs946
> c00000008f57e358 9 3 256 43320 77 /dbms/links/ifasnrdbs947
> c00000008f57e4f0 10 3 256 0 0 /dbms/links/ifasnrdbs948
> c00000008f57e688 11 3 256 1 4 /dbms/links/ifasnrdbs949
> c00000008f57e820 12 3 256 0 0 /dbms/links/ifasnrdbs9410
> c00000008f57e9b8 13 4 0 5 0 /dbms/links/ifashrpy1
> c00000008f57eb50 14 4 256 0 0 /dbms/links/ifashrpy2
> c00000008f57ece8 15 4 256 0 0 /dbms/links/ifashrpy3
> c00000008f57ee80 16 4 256 0 0 /dbms/links/ifashrpy4
> c00000008f57f028 17 4 256 0 0 /dbms/links/ifashrpy5
> c00000008f57f1c0 18 4 256 0 0 /dbms/links/ifashrpy6
> c00000008f57f358 19 4 256 8 0 /dbms/links/ifashrpy7
> c00000008f57f4f0 20 4 256 0 0 /dbms/links/ifashrpy9
> c00000008f57f688 21 4 256 0 0 /dbms/links/ifashrpy10
> c00000008f57f820 22 4 256 0 0 /dbms/links/ifashrpy11
> c00000008f57f9b8 23 5 0 0 78681 /dbms/links/llogdbs94
> c00000008f57fb50 24 6 256 97045 117122 /dbms/links/tempdbs6
> c00000008f57fce8 25 7 256 82884 83875 /dbms/links/tempdbs7
> c00000008f57fe80 26 8 256 68811 69825 /dbms/links/tempdbs8
> c00000008f580028 27 9 256 68298 69322 /dbms/links/tempdbs9
> c00000008f5801c0 28 10 256 5 58 /dbms/links/tempdbs10
> c00000008f580358 29 11 0 0 0 /dbms/links/ifasdevdbs1
> c00000008f5804f0 30 11 256 0 0 /dbms/links/ifasdevdbs2
> c00000008f580688 31 11 256 0 0 /dbms/links/ifasdevdbs3
> c00000008f580820 32 11 256 0 0 /dbms/links/ifasdevdbs4
> c00000008f5809b8 33 11 256 0 0 /dbms/links/ifasdevdbs5
> c00000008f580b50 34 11 256 0 0 /dbms/links/ifasdevdbs6
> c00000008f580ce8 35 11 256 0 0 /dbms/links/ifasdevdbs7
> c00000008f580e80 36 11 256 0 0 /dbms/links/ifasdevdbs8
> c00000008f581028 37 11 256 0 0 /dbms/links/ifasdevdbs9
> c00000008f5811c0 38 11 256 0 0 /dbms/links/ifasdevdbs10
> c00000008f581358 39 4 256 0 0 /dbms/links/ifashrpy12
> c00000008f5814f0 40 12 0 0 15399 /dbms/links/plogdbs94
> c00000008f581688 41 13 256 4 8 /dbms/links/ifasdbs94temp
> c00000008f581820 42 14 256 3844 24807 /dbms/links/ifasdev2dbspyt
> c00000008f5819b8 43 15 256 966987 3563 /dbms/links/ifasdev2dbspyt2
> c00000008f581b50 44 15 256 4110741 0 /dbms/links/ifasdev2dbspyt3
> 44 active, 2047 maximum
>
> Expanded chunk capacity mode: disabled
>
> onstat -P
>
> IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:53:16
> -- 2325280 Kbytes
> partnum total btree data other dirty
> 0 138 0 0 138 4
> 1048578 3 1 1 1 0
> 1048579 9 5 4 0 0
> 1048580 4 2 1 1 0
> 1048581 3 1 1 1 0
> 1048582 4 2 1 1 0
> 1048583 3 1 1 1 0
> 1048585 4 1 1 2 0
> 1048598 2 1 0 1 0
> 1048599 2 1 0 1 0
> 1048602 5 2 2 1 0
> 1048603 2 1 0 1 0
> 1048608 3 1 0 2 0
> 1048613 3 1 1 1 0
> 1048615 3 1 1 1 0
> 1048623 1 0 0 1 0
> 1048624 4 3 0 1 0
> 2097201 10 4 6 0 0
> 2097207 2 1 1 0 0
> 2097224 94 16 78 0 0
> 2097250 178 0 178 0 0
> 2097251 8 8 0 0 0
> 2097252 44 44 0 0 0
> 2097254 3 3 0 0 0
> 2097260 1 0 1 0 0
> 2097261 1 1 0 0 0
> 2097265 81 0 81 0 0
> 2097266 6 6 0 0 0
> 2097267 32 32 0 0 0
> 2097277 6 0 6 0 0
> 2097281 11 11 0 0 0
> 2097284 145 0 145 0 0
> 2097287 5 5 0 0 0
> 2097293 29 0 29 0 0
> 2097294 13 13 0 0 0
> 2097295 3 3 0 0 0
> 2097358 20 0 20 0 0
> 2097359 3 3 0 0 0
> 2097360 6 5 0 1 0
> 3145730 107 46 59 2 2
> 3145731 66 22 42 2 2
> 3145732 41 12 27 2 1
> 3145733 16 8 6 2 1
> 3145734 7 3 2 2 1
> 3145735 8 3 3 2 2
> 3145736 3 1 1 1 0
> 3145737 5 2 1 2 1
> 3145739 1 1 0 0 0
> 3145740 7 3 4 0 0
> 3145743 5 3 2 0 0
> 3145744 7 5 2 0 0
> 3145745 10 6 4 0 0
> 3145746 4 2 2 0 0
> 3145747 3 3 0 0 0
> 3145748 4 3 1 0 0
> 3145749 1 1 0 0 0
> 3145750 1 1 0 0 0
> 3145751 4 3 1 0 0
> 3145752 5 3 2 0 0
> 3145753 291 76 213 2 1
> 3145754 10 4 4 2 1
> 3145755 1 1 0 0 0
> 3145756 4 2 1 1 0
> 3145759 1 1 0 0 0
> 3145760 1 1 0 0 0
> 3145762 3 2 1 0 0
> 3145764 2 1 1 0 0
> 3145765 1 1 0 0 0
> 3145771 1 1 0 0 0
> 3145774 25 20 3 2 0
> 3145775 3 3 0 0 0
> 3145777 81 0 81 0 0
> 3145778 12 12 0 0 0
> 3145779 1 0 1 0 0
> 3146110 1 0 1 0 0
> 3146149 2031 0 2031 0 0
> 3146188 5 0 5 0 0
> 3146191 5 5 0 0 0
> 3146228 540 0 537 3 0
> 3146703 4 0 4 0 0
> 3146706 13 13 0 0 0
> 3146794 1 0 1 0 0
> 3146796 2 2 0 0 0
> 3147073 1 0 1 0 0
> 3147076 9 0 8 1 0
> 3147077 1 1 0 0 0
> 3147084 131 0 130 1 0
> 3147086 3 3 0 0 0
> 3147087 4 4 0 0 0
> 3147089 2 0 2 0 0
> 3147091 4 4 0 0 0
> 3147099 1 0 1 0 0
> 3147100 27 26 0 1 0
> 3147101 2 2 0 0 0
> 3147103 125 0 124 1 0
> 3147105 2 2 0 0 0
> 3147111 1 1 0 0 0
> 3147113 23 0 23 0 0
> 3147114 23 23 0 0 0
> 3147116 2 0 2 0 0
> 3147117 3 3 0 0 0
> 3147118 3 3 0 0 0
> 3147120 14 0 14 0 0
> 3147121 9 9 0 0 0
> 3147123 31 0 31 0 0
> 3147124 21 21 0 0 0
> 3147125 14 14 0 0 0
> 3147131 7 0 7 0 0
> 3147132 10 10 0 0 0
> 3147135 4 0 4 0 0
> 3147136 5 5 0 0 0
> 3147144 1 0 1 0 0
> 3147145 2 2 0 0 0
> 3147150 6 0 5 1 0
> 3147151 1 1 0 0 0
> 3147169 1 0 1 0 0
> 3147183 5426 0 5426 0 0
> 3147203 1786 1786 0 0 0
> 3147205 1 1 0 0 0
> 3147208 19100 0 19100 0 0
> 3147211 1632 0 1632 0 0
> 3147214 69 69 0 0 0
> 3147230 62 0 61 1 0
> 3147233 10 0 10 0 0
> 3147332 94137 0 94137 0 0
> 3147344 3 0 2 1 0
> 3147348 1 0 0 1 0
> 3147355 5 0 4 1 0
> 3147356 8 0 7 1 0
> 3147480 1 0 1 0 0
> 3147481 3 3 0 0 0
> 3147488 3 3 0 0 0
> 3147651 2 0 2 0 0
> 3147654 3 3 0 0 0
> 3147834 1 0 1 0 0
> 3148051 1 0 1 0 0
> 3148123 8 0 6 2 0
> 3148124 1 1 0 0 0
> 3148205 673 0 672 1 0
> 3148209 3 3 0 0 0
> 3148369 624 0 624 0 0
> 3148471 85 85 0 0 0
> 3148489 3 3 0 0 0
> 3148502 598 598 0 0 0
> 3148506 11 11 0 0 0
> 3148521 96 96 0 0 0
> 3148526 4897 4897 0 0 0
> 3148527 2600 2600 0 0 0
> 3148559 6948 6945 0 3 0
> 3148560 2099 2070 0 29 0
> 3148561 9683 9636 0 47 0
> 4194496 2 0 1 1 0
> 4194498 8 0 7 1 0
> 4195008 12 7 5 0 0
> 4195015 2 1 1 0 0
> 4195032 4 3 1 0 0
> 6291457 2 0 0 2 1
> 6291458 30238 0 30230 8 0
> 6291459 1 0 0 1 0
> 6291460 1 0 0 1 0
> 6291461 1 0 0 1 0
> ....snip...
> 9437327 1 0 0 1 0
> 9437328 1 0 0 1 0
> 9437329 1 0 0 1 0
> 9437330 1 0 0 1 0
> 9437331 1 0 0 1 0
> 9437332 1 0 0 1 0
> 9437333 1 0 0 1 0
> 10485761 1 0 0 1 0
> 10485762 6 0 4 2 0
> 10485763 6 4 0 2 0
> 13631489 1 0 0 1 0
> 13631490 3 0 1 2 0
> 14680066 9475 0 9470 5 288
> 14680067 4086 4061 0 25 23
> 14680068 478 476 0 2 11
> 14680069 811 809 0 2 3
> 15728641 2 0 0 2 0
> 15728642 690224 0 690224 0 0
> 15728644 3216 3216 0 0 0
> 15728649 3549 3545 0 4 0
>
> Totals: 900000 41525 857540 935 395
>
> Percentages:
> Data 95.28
> Btree 4.61
> Other 0.10
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Messages In This Thread
- Performance issue after 732 to 94fc6 upgrade
Doug Fossmeyer -- Friday, 26 May 2006, at 1:20 p.m.
- RE: Performance issue after 732 to 94fc6 upgrade
Julio Gerardo Flores Olvera -- Friday, 26 May 2006, at 1:39 p.m.
- RE: Performance issue after 732 to 94fc6 upgrade
Marcus Haarmann -- Friday, 26 May 2006, at 1:45 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade
ART KAGEL, BLOOMBERG/ 731 LEXIN -- Friday, 26 May 2006, at 1:50 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade
Doug Fossmeyer -- Friday, 26 May 2006, at 5:42 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade
Doug Fossmeyer -- Monday, 12 June 2006, at 2:55 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade P
Doug Fossmeyer -- Monday, 12 June 2006, at 8:02 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade p
Doug Fossmeyer -- Monday, 12 June 2006, at 8:03 p.m.
- Re: Performance issue after 732 to 94fc6 upgrade p
Doug Fossmeyer -- Monday, 12 June 2006, at 8:04 p.m.
- RE: Performance issue after 732 to 94fc6 upgrade
Everett Mills -- Friday, 26 May 2006, at 3:23 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
