Re: sysmaster slow
--0-1611837224-1106318424=:47941
Content-Type: text/plain; charset=us-ascii
HI Art,
>>Anyway, you can move the temp table IO's off root by adding one or more
of the non-temp dbspaces to DBSPACETEMP. <<
I am not sure I know what you mean here. move NON-temp dbspaces to DBSPACETEMP ?
btw, I have 3 dbspaces listed in dbspacetemp.
Onstat -g ppf gets output for me, so I assume the tablestats are on. That is neat. Does it tell you io per table ? Where can I read more about that ? for instance, how do I know what tablename is by the partnum ?
Thanks.
"Art S. Kagel" <KAGEL@bloomberg.net> wrote:
Floyd Wellershaus wrote:
As Martin pointed out, logged temp tables (including those created by an
INTO TEMP clause) are created in ROOTDB if there are not 'regular' dbspaces
listed in DBSPACETEMP, however, given the low number of writes on the two
chunks prod_root_c[12], I'd say that's not the problem, nor is logical log
IO. Anyway, you can move the temp table IO's off root by adding one or more
of the non-temp dbspaces to DBSPACETEMP. I agree with Martin that you
should turn on table level stats if not on already and monitor onstat -g ppf.
Art S. Kagel
> Can anyone tell me why my rootdbs is being hit so hard ?
> Below is a partial output, sorted by io, of an onstat -g iof.
>
> When I try and query tables in sysmaster, it takes forever, and am wondering if the heavily used rootdbs has something to do with it. Is there a way of finding out what is going on in that rootdbs ?
>
> Thanks,
> IBM Informix Dynamic Server Version 9.40.FC3 -- On-Line -- Up 2 days 20:00:12 -- 4585232 Kbytes> 171 prod_root_c2 2771643 2757214 14429 175.6
> 3 prod_root_c1 2141522 2138057 3465 135.7
> 191 prod_stpx2c1 861237 854256 6981 54.6
> 201 /dev/prod_stx_c1 553963 550476 3487 35.1
> 202 /dev/prod_stx_c2 306934 299433 7501 19.4
> 102 /dev/prod_tmp3c1 303168 14233 288935 19.2
> 9 prod_stpf6c1 298403 296762 1641 18.9
> 160 prod_gsid_c4 294784 292434 2350 18.7
> 74 /dev/prod_tmp1c1 291313 25583 265730 18.5
> 138 prod_gsid_c1 283731 281398 2333 18.0
> 100 /dev/prod_tmp2c1 262428 26478 235950 16.6
> 86 prod_stpf3c1 257166 247665 9501 16.3
> 159 prod_gsid_c3 243128 241057 2071 15.4
> 85 prod_stpf2c1 233882 231671 2211 14.8
> 48 prod_tsub_c4 226468 120469 105999 14.3
> 87 prod_stpf4c1 216517 210033 6484 13.7
> 199 /dev/prod_stu_c1 203175 200798 2377 12.9
> 11 prod_stpf9c1 204380 199285 5095 12.9
> 143 prod_gsid_c2 200564 198569 1995 12.7
> 7 prod_stpf7c1 194700 193566 1134 12.3
> 84 prod_stpf1c1 185105 181710 3395 11.7
> 8 prod_stpf5c1 161562 160275 1287 10.2
> 212 prod_gsid_c5 135067 132928 2139 8.6
> 10 prod_stpf8c1 129790 129127 663 8.2
> 208 prod_slog_c1 98419 98000 419 6.2
> 273 prod_dtlf6_c2 95255 95255 0 6.0
> 195 prod_rptq_c1 90133 32219 57914 5.7
> 68 prod_ssub_c3 89936 57358 32578 5.7
> 252 prod_dtlf1_c1 84076 83846 230 5.3
> 274 prod_dtlf6_c3 80845 80743 102 5.1
> 257 prod_dtlf2_c2 79902 79902 0 5.1
> 217 prod_gsid_c6 80946 78658 2288 5.1
> 256 prod_dtlf2_c1 79018 79018 0 5.0
> 254 prod_dtlf1_c3 79160 79160 0 5.0
> 275 prod_dtlf6_c4 77842 74610 3232 4.9
> 253 prod_dtlf1_c2 76954 76954 0 4.9
> 259 prod_dtlf2_c4 73451 73451 0 4.7
>
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Work: 703-733-4126
Pager: 703-705-9241
Email Pager: 7037059241@my2way.com
Home: 703-430-0805
Cell: 703-477-6045
========================
--0-1611837224-1106318424=:47941
Content-Type: text/html; charset=us-ascii
<DIV>HI Art,</DIV>
<DIV>>>Anyway, you can move the temp table IO's off root by adding one or more <BR>of the non-temp dbspaces to DBSPACETEMP. <<</DIV>
<DIV> </DIV>
<DIV>I am not sure I know what you mean here. move NON-temp dbspaces to DBSPACETEMP ? </DIV>
<DIV>btw, I have 3 dbspaces listed in dbspacetemp.</DIV>
<DIV> </DIV>
<DIV>Onstat -g ppf gets output for me, so I assume the tablestats are on. That is neat. Does it tell you io per table ? Where can I read more about that ? for instance, how do I know what tablename is by the partnum ?</DIV>
<DIV> </DIV>
<DIV>Thanks.<BR><BR><B><I>"Art S. Kagel" <KAGEL@bloomberg.net></I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Floyd Wellershaus wrote:<BR><BR>As Martin pointed out, logged temp tables (including those created by an <BR>INTO TEMP clause) are created in ROOTDB if there are not 'regular' dbspaces <BR>listed in DBSPACETEMP, however, given the low number of writes on the two <BR>chunks prod_root_c[12], I'd say that's not the problem, nor is logical log <BR>IO. Anyway, you can move the temp table IO's off root by adding one or more <BR>of the non-temp dbspaces to DBSPACETEMP. I agree with Martin that you <BR>should turn on table level stats if not on already and monitor onstat -g ppf.<BR><BR>Art S. Kagel<BR><BR>> Can anyone tell me why my rootdbs is being hit so hard ?<BR>> Below is a partial output, sorted by io, of an onstat -g iof. <BR>> <BR>> When I try and query tables in sysmaster, it takes forever, and am wondering if the heavily used rootdbs has something to do with it. Is there
a way of finding out what is going on in that rootdbs ?<BR>> <BR>> Thanks,<BR>> IBM Informix Dynamic Server Version 9.40.FC3 -- On-Line -- Up 2 days 20:00:12 -- 4585232 Kbytes<BR>> 171 prod_root_c2 2771643 2757214 14429 175.6<BR>> 3 prod_root_c1 2141522 2138057 3465 135.7<BR>> 191 prod_stpx2c1 861237 854256 6981 54.6<BR>> 201 /dev/prod_stx_c1 553963 550476 3487 35.1<BR>> 202 /dev/prod_stx_c2 306934 299433 7501 19.4<BR>> 102 /dev/prod_tmp3c1 303168 14233 288935 19.2<BR>> 9 prod_stpf6c1 298403 296762 1641 18.9<BR>> 160 prod_gsid_c4 294784 292434 2350 18.7<BR>> 74 /dev/prod_tmp1c1 291313 25583 265730 18.5<BR>> 138 prod_gsid_c1 283731 281398 2333 18.0<BR>> 100 /dev/prod_tmp2c1 262428 26478 235950 16.6<BR>> 86 prod_stpf3c1 257166 247665 9501 16.3<BR>> 159 prod_gsid_c3 243128 241057 2071 15.4<BR>> 85 prod_stpf2c1 233882 231671 2211 14.8<BR>> 48 prod_tsub_c4 226468 120469 105999 14.3<BR>> 87 prod_stpf4c1 216517 210033 6484
13.7<BR>> 199 /dev/prod_stu_c1 203175 200798 2377 12.9<BR>> 11 prod_stpf9c1 204380 199285 5095 12.9<BR>> 143 prod_gsid_c2 200564 198569 1995 12.7<BR>> 7 prod_stpf7c1 194700 193566 1134 12.3<BR>> 84 prod_stpf1c1 185105 181710 3395 11.7<BR>> 8 prod_stpf5c1 161562 160275 1287 10.2<BR>> 212 prod_gsid_c5 135067 132928 2139 8.6<BR>> 10 prod_stpf8c1 129790 129127 663 8.2<BR>> 208 prod_slog_c1 98419 98000 419 6.2<BR>> 273 prod_dtlf6_c2 95255 95255 0 6.0<BR>> 195 prod_rptq_c1 90133 32219 57914 5.7<BR>> 68 prod_ssub_c3 89936 57358 32578 5.7<BR>> 252 prod_dtlf1_c1 84076 83846 230 5.3<BR>> 274 prod_dtlf6_c3 80845 80743 102 5.1<BR>> 257 prod_dtlf2_c2 79902 79902 0 5.1<BR>> 217 prod_gsid_c6 80946 78658 2288 5.1<BR>> 256 prod_dtlf2_c1 79018 79018 0 5.0<BR>> 254 prod_dtlf1_c3 79160 79160 0 5.0<BR>> 275 prod_dtlf6_c4 77842 74610 3232 4.9<BR>> 253 prod_dtlf1_c2 76954 76954 0 4.9<BR>> 259 prod_dtlf2_c4 73451 73451 0 4.7<BR>>
<BR></BLOCKQUOTE><BR><BR><DIV>
<DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV>
<DI