AW: selects on sysmaster are very slow
Posted in 2007
Topics: Logging & Checkpoints
Dear list,
after rebooting the server the query performs between 18 and 58 seconds
which is not too fast but much faster than 30 minutes. Interesting: Before
the reboot the query retrieved 14455 rows, after the reboot only 10747 rows.
> Can you please send us the output of both onstat -g sql SID /
> onstat -g ses SID> during the select?
IBM Informix Dynamic Server Version 9.40.FC4W2 -- On-Line -- Up 01:27:22
-- 6493184 Kbytes
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
40919 SELECT sysmaster CR Not Wait 0 0 9.03 Off
Current statement name : slctcur
Current SQL statement :
select * from systabnames
Last parsed SQL statement :
select * from systabnames
IBM Informix Dynamic Server Version 9.40.FC4W2 -- On-Line -- Up 01:27:22
-- 6493184 Kbytes
session #RSAM total used
dynamic
id user tty pid hostname threads memory memory
explain
40919 informix 1 21027 db1 1 69632 65936
off
tid name rstcb flags curstk status
40981 sqlexec 2427ba700 ---PR-- 11103 yield aiowait
Memory pools count 1
name class addr totalsize freesize #allocfrag
#freefrag
40919 V 2596e6040 69632 3696 116 7
name free used name free used
overhead 0 3248 scb 0 144
opentable 0 4680 filetable 0 1336
log 0 2184 temprec 0 10104
keys 0 384 ralloc 0 10184
gentcb 0 1632 ostcb 0 2864
sqscb 0 17576 sql 0 72
rdahead 0 1120 hashfiletab 0 552
osenv 0 3880 buft_buffer 0 2160
sqtcb 0 3184 fragman 0 488
sapi 0 72
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind
directives
261754028 26bb6f028 0 0 0 1
1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
40919 SELECT sysmaster CR Not Wait 0 0 9.03 Off
Current statement name : slctcur
Current SQL statement :
select * from systabnames
Last parsed SQL statement :
select * from systabnames
sid cumtime reason
40919 311891216,0000 condition
40919 60270460,00000 aio
40919 6584457,000000 mt ready
40919 2464614,000000 running
40919 14884,00000000 mutex
40919 10538,00000000 buffer
40919 10070,00000000 mt yield
Overall waitstates:
2844222607635 condition
28990175732,00 running
15850457074,00 buffer
12452310205,00 aio
11497394902,00 mt ready
4583042671,000 checkpoint
1376000545,000 mutex
771279106,0000 mt yield 0
22529029,00000 mt yield
2141490,000000 mt yield n
55340,00000000 lock
Regards,
Reinhard
> -----Ursprüngliche Nachricht-----
> Von: Bogdan Botez [mailto:bmbogdan@gmail.com]
> Gesendet am: Freitag, 30. März 2007 12:03
> An: Habichtsberg, Reinhard
> Betreff: Re: selects on sysmaster are very slow
>
> On 3/30/07, Habichtsberg, Reinhard
> <RHabichtsberg@arz-emmendingen.de> wrote:
> > Dear list,
> >
> > since 3 or 4 weeks selects on sysmaster esp. systabnames
> behave very, very
> > slow.
> >
> > A simple select:
> > select * from systabnames
> > into temp t1 with no log;> >
>
> Hello Reinhard,
>
> Can you please send us the output of both onstat -g sql SID /
> onstat -g ses SID> during the select? Also, from our experience, please make
> sure that the
> pdqpriority of that session is low.
>
> Regards,
> Bogdan BOTEZ.
>
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g