What's the unload cursor waiting on?
We recently moved from a physical to a virtual host (VMware) for a
data warehouse DB. The migration was used Tivoli Bare-Metal Restore
for O/S and onbar for Informix chunks. Nothing was changed -- same
onconfig, kernel parameters, etc. We updated statistics on all
databases. But a nightly unload of a large table to file (unload to/
select * from) now takes over twice as long. The session's stillrunning:
ses 831
IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up
04:16:26 -- 15048908 Kbytes
session #RSAM total
used dynamic
id user tty pid hostname threads memory
memory explain
831 statsetl - 20256 stats09 1 143360
135776 off
tid name rstcb flags curstk status
1103 sqlexec 1473bad88 Y--P--- 4544 cond
wait(netnorm)
Memory pools count 2
name class addr totalsize freesize #allocfrag
#freefrag
831 V 14756c040 139264 6744 170
10
831*O0 V 147587040 4096 840 1 1
name free used name free
used
overhead 0 6512 scb 0 144
opentable 0 3872 filetable 0 840
log 0 12096 temprec 0
5496
keys 0 688 ralloc 0
56368
gentcb 0 1600 ostcb 0
2968
sqscb 0 18208 sql 0 72
rdahead 0 8288 hashfiletab 0 552
osenv 0 2656 buft_buffer 0
5784
sqtcb 0 3232 fragman 0
6400
sqscb info
scb sqscb optofc pdqpriority sqlstats
optcompind directives
14743e2e0 1475dd028 0 0 0
2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
831 SELECT newstats_prd CR Not Wait 0 0 9.03
Off
Current statement name : unlcur
Current SQL statement :
select * from fact_table
Last parsed SQL statement :
select * from fact_table
The output file continues to grow at a snail's pace, but I'm not able
to discern any activity from any onstat commands due to the unload
except that on occasion the thread will appear on the wait queue
waiting for a condition id that we were never able to identify. We
know that there are substantial improvements to the script itself that
will boost performance. And other scripts for the ETL process have
taken about the same time as prior to the migration. Raw O/S level
tests of the target filesystem (on a much faster SAN) for both read
and write show much improved performance. But this simple table dump
script is dogging. How can we determine where the bottleneck is?
Running IDS 10.00.FC6 on RHEL4/2.6.9 kernel.
IO under VMWare is two to three times slower than bare metal. Supposedly
VMWare is working on the problem, but there is no solution that I am aware
of.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. 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 Fri, Feb 24, 2012 at 6:40 PM, red_valsen <red_valsen@yahoo.com> wrote:
> We recently moved from a physical to a virtual host (VMware) for a
> data warehouse DB. The migration was used Tivoli Bare-Metal Restore
> for O/S and onbar for Informix chunks. Nothing was changed -- same
> onconfig, kernel parameters, etc. We updated statistics on all
> databases. But a nightly unload of a large table to file (unload to/
> select * from) now takes over twice as long. The session's still> running:
>
> ses 831
>
> IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up
> 04:16:26 -- 15048908 Kbytes>
> session #RSAM total
> used dynamic
> id user tty pid hostname threads memory
> memory explain
> 831 statsetl - 20256 stats09 1 143360
> 135776 off
>
> tid name rstcb flags curstk status
> 1103 sqlexec 1473bad88 Y--P--- 4544 cond
> wait(netnorm)
>
> Memory pools count 2
> name class addr totalsize freesize #allocfrag
> #freefrag
> 831 V 14756c040 139264 6744 170
> 10
> 831*O0 V 147587040 4096 840 1 1
>
> name free used name free
> used
> overhead 0 6512 scb 0 144
> opentable 0 3872 filetable 0 840
> log 0 12096 temprec 0
> 5496
> keys 0 688 ralloc 0
> 56368
> gentcb 0 1600 ostcb 0
> 2968
> sqscb 0 18208 sql 0 72
> rdahead 0 8288 hashfiletab 0 552
> osenv 0 2656 buft_buffer 0
> 5784
> sqtcb 0 3232 fragman 0
> 6400
>
> sqscb info
> scb sqscb optofc pdqpriority sqlstats
> optcompind directives
> 14743e2e0 1475dd028 0 0 0
> 2 1
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers
> Explain
> 831 SELECT newstats_prd CR Not Wait 0 0 9.03
> Off
>
> Current statement name : unlcur
>
> Current SQL statement :
> select * from fact_table>
> Last parsed SQL statement :
> select * from fact_table>
> The output file continues to grow at a snail's pace, but I'm not able
> to discern any activity from any onstat commands due to the unload
> except that on occasion the thread will appear on the wait queue
> waiting for a condition id that we were never able to identify. We
> know that there are substantial improvements to the script itself that
> will boost performance. And other scripts for the ETL process have
> taken about the same time as prior to the migration. Raw O/S level
> tests of the target filesystem (on a much faster SAN) for both read
> and write show much improved performance. But this simple table dump
> script is dogging. How can we determine where the bottleneck is?
> Running IDS 10.00.FC6 on RHEL4/2.6.9 kernel.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>