 | |
Re: Slow database creation and loading
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Search this Thread | Display Modes |
#11
 
| |||
| |||
|
Re: Slow database creation and loading -
06-21-2007
, 09:42 AM
|
I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing it can run unattended... please tes all first on a test box... as informix: set lrumin and maxdirty to 99 in $ONCONFIG bounce the engine. dbaccess sysmaster <<! -- WARNING CHECK THE CODE may contain a bug.. create procedure generatechkpt() define dirty decimal (4,3); while (1=1) select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod )) into dirty from syslrus ; if (dirty < 0.75 ) then system "sleep 1"; else system "onmode -c"; end if end while ; end procedure; execute procedure generatechkpt(); ! run your load when done do onmode -c set lrumin and maxdirty back to what it was and bounce your engine. regarding onconfig: grab 1 GB for bufferecache at least; BUFFERS 250000 # Maximum number of shared buffers PHYSFILE 40000 # Physical log file size (Kbytes) i do not want a checkpoint when this becomes 75 % full so use onparams to set the size to 1 GB or 1.5 GB. You should be safe since you are on 7.31.UD7. if all is raw and KAIO set NUMAIOVPS to 4 max or to 2. PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes) contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). it does work in deluxe as someone else stated, and yes you can increase your buffer cache it will help!!!!! also the above hack spl will help. i also see > Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- -->> checkpoint request.. how long are your checkpoints??? can your disks cope??? i sure hope no raid 5; ask Art why. Superboer. On 19 jun, 04:19,johneevo<johne... (AT) gmail (DOT) com> wrote: On Jun 11, 3:07 am, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hi Superboer, Thanks for the reply. You should be able to speed this up. I have been told that we have 4 GB of memory on this box. BUFFERS 75000 # Maximum number of shared buffers 4 GB available and only 75000*4k=300MB of buffer cache.. i would make this bigger and therefor increase PHYSFILE 40000 # Physical log file size (Kbytes) NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 Any suggestions on what the increase the the BUFFERS to. And I guess there is some ratio that the BUFFERS to PHYSFILE should be set to. If this is correct would you mind tell me what that ratio is? Are you using kernel io??? onstat -g ioa will tell. Yes it appears that we are using kernel io if I am reading the onstat - g ioa otput correctly. The kio lines have the majority the reads and writes. if so decrease NUMAIOVPS if using cooked files then consider raw please Any suggestions on that the decrease the NUMAIOVPS to? Or is this just a trail and error type of tuning? PHYSBUFF 64 # Physical log buffer size (Kbytes) bigger. LOGBUFF 64 # Logical log buffer size (Kbytes) bigger. I'm sorry, but once again, any suggestions on what to increase these buffers to? During the load it may be interesting to see what the db is doing, so an onstat -p may help... Here is the onstat -p output while the load is running. This was taken while the HPL portion was running. Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- Up 01:47:2 6 -- 873952 Kbytes Blocked:CKPT Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 isamtot open start read write rewrite delete commit rollbk 53729641 130660 148793 12402936 35365788 3679 13512 5474 139 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 3789.28 409.72 356 725 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 709578 0 15464370 0 0 580 1856 11850 ixda-RA idx-RA da-RA RA-pgsused lchwaits 79 1 255074 255136 57787 also you state dbimport/???? if load/unload, make sure that indexes are created after data is loaded. I'll check on this, but I believe these are rather small tables. The contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). Also you could consider generating your own checkpoints setting lrumax and min dirty to 99, check buffer cache if 75 % dirty then do a onmode -c (i have cut load times have in half using this......) I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing the boat completely with your comment here. John |
#12
| |||
| |||
|
|
Yes, if in deluxe mode, you are not really getting the full benefit of HPL, just some parallel input files. |
#13
| |||
| |||
|
|
The HPL process (and I found out we are using the Express mode not the Deluxe mode like I first thought) is taking about 4 hrs to load the 10GB of data, then another 3 hrs to create rowids (we needed to |
create rowids i assume you set pdpriority and have that configured
properly...
3 hours are LONGGGG
post the onconfig also check your disks if they are 100 % busy..
also how long are checkpoints taking???
some time ago i used HPL in express mode and reloaded a 100 GB table
on a sun which took only 1 hour.
again dependant on disks memory cpu etc.
Superboer.
On 21 jun, 15:42, johneevo <johne... (AT) gmail (DOT) com> wrote:
Quote:
|
On Jun 19, 3:05 am, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hi Superboer, Thanks for helping me with this. Last night I changed the BUFFERS to 400000 and increased LRUS and CLEANERS to 99 for each and this did not speed things up. So tonight I am going to try also make some of the changes that you suggested, lowering NUMAIOVPS from 36 to 4 and increasing the PHYSBUFF and LOGBUFF from 64 to 512 for each I'm not ready yet to try the "generatechkpt" stored proc (although I might be soon...) I figure I'll just try a few things at a time and see what if any affect they have. I should probably give a little more info on the overall time things are taking to run, since me first post probably gave the impression that the HPL process is taking 10+ hrs. The HPL process (and I found out we are using the Express mode not the Deluxe mode like I first thought) is taking about 4 hrs to load the 10GB of data, then another 3 hrs to create rowids (we needed to remove them before running HPL since these tables are fragmented), 1 hr to enable contraints etc. and then 2hrs for table stats. Do you still think we can speed this up, or is this the best we can get using the version that we have? John I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing it can run unattended... please tes all first on a test box... as informix: set lrumin and maxdirty to 99 in $ONCONFIG bounce the engine. dbaccess sysmaster <<! -- WARNING CHECK THE CODE may contain a bug.. create procedure generatechkpt() define dirty decimal (4,3); while (1=1) select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod )) into dirty from syslrus ; if (dirty < 0.75 ) then system "sleep 1"; else system "onmode -c"; end if end while ; end procedure; execute procedure generatechkpt(); ! run your load when done do onmode -c set lrumin and maxdirty back to what it was and bounce your engine. regarding onconfig: grab 1 GB for bufferecache at least; BUFFERS 250000 # Maximum number of shared buffers PHYSFILE 40000 # Physical log file size (Kbytes) i do not want a checkpoint when this becomes 75 % full so use onparams to set the size to 1 GB or 1.5 GB. You should be safe since you are on 7.31.UD7. if all is raw and KAIO set NUMAIOVPS to 4 max or to 2. PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes) contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). it does work in deluxe as someone else stated, and yes you can increase your buffer cache it will help!!!!! also the above hack spl will help. i also see > Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- -->> checkpoint request.. how long are your checkpoints??? can your disks cope??? i sure hope no raid 5; ask Art why. Superboer. On 19 jun, 04:19,johneevo<johne... (AT) gmail (DOT) com> wrote: On Jun 11, 3:07 am, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hi Superboer, Thanks for the reply. You should be able to speed this up. I have been told that we have 4 GB of memory on this box. BUFFERS 75000 # Maximum number of shared buffers 4 GB available and only 75000*4k=300MB of buffer cache.. i would make this bigger and therefor increase PHYSFILE 40000 # Physical log file size (Kbytes) NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 Any suggestions on what the increase the the BUFFERS to. And I guess there is some ratio that the BUFFERS to PHYSFILE should be set to. If this is correct would you mind tell me what that ratio is? Are you using kernel io??? onstat -g ioa will tell. Yes it appears that we are using kernel io if I am reading the onstat - g ioa otput correctly. The kio lines have the majority the reads and writes. if so decrease NUMAIOVPS if using cooked files then consider raw please Any suggestions on that the decrease the NUMAIOVPS to? Or is this just a trail and error type of tuning? PHYSBUFF 64 # Physical log buffer size (Kbytes) bigger. LOGBUFF 64 # Logical log buffer size (Kbytes) bigger. I'm sorry, but once again, any suggestions on what to increase these buffers to? During the load it may be interesting to see what the db is doing, so an onstat -p may help... Here is the onstat -p output while the load is running. This was taken while the HPL portion was running. Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- Up 01:47:2 6 -- 873952 Kbytes000 Blocked:CKPT Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 isamtot open start read write rewrite delete commit rollbk 53729641 130660 148793 12402936 35365788 3679 13512 5474 139 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 3789.28 409.72 356 725 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 709578 0 15464370 0 0 580 1856 11850 ixda-RA idx-RA da-RA RA-pgsused lchwaits 79 1 255074 255136 57787 also you state dbimport/???? if load/unload, make sure that indexes are created after data is loaded. I'll check on this, but I believe these are rather small tables. The contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). Also you could consider generating your own checkpoints setting lrumax and min dirty to 99, check buffer cache if 75 % dirty then do a onmode -c (i have cut load times have in half using this......) I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing the boat completely with your comment here. John |
#14
| |||||
| |||||
|
|
if you set lrumax and mindirty to 99 you have to generate your own checkpoints!!!! if you do not want to generate checkpoints you may want to set lrumaxdirty to 10 and set mindirty to say 5 or smaller. |
tonights load and see who things go.
Quote:
|
400000 for BUFFERS means 4 * 400000 = 1.6 GB of buffer cache.... assume you have that memory. |
instances on this box (one for development, the other for testing/
deployment),
only the testing/deployment db gets refreshed nightly. I am wondering
if that is to much memory to grab, but so far nobody has
complained about the performance going down after I made this change.
Quote:
|
4 hours for 10 GB is afwul. what are your disks doing???? create rowids i assume you set pdpriority and have that configured properly... |
export MAX_PDQPRIORITY=100 PSORT_NPROCS=6 PDQPRIORITY=100
FET_BUF_SIZE=32767
Then after the import we turn PDQ off.
Quote:
|
3 hours are LONGGGG post the onconfig also check your disks if they are 100 % busy.. also how long are checkpoints taking??? |
how long the checkpoints are taking.
Here is our config. NOTE: I changed the NUMAIOVPS, PHYSBUFF and
LOGBUFF values this morning and the
server wont be bounced until tonight before the import starts.
#************************************************* *************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.insurnace
# Description: INFORMIX-OnLine Configuration Parameters
#
#************************************************* *************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /usr/informix/dblinks/infrootlv1
# Path for device containing root
dbspace
ROOTOFFSET 4 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 2097147 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored
root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 40000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 14 # Number of logical log files
LOGSIZE 10000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /usr/informix/online.insurance # System message log
file path
CONSOLE /usr/informix/console.insurance # System console
message path
ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path
# System Archive Tape Device
TAPEDEV /dev/null # Tape device path
TAPEBLK 1024 # Tape block size (Kbytes)
TAPESIZE 12000000 # Maximum amount of data to put on
tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 1024 # Log tape block size (Kbytes)
LTAPESIZE 4096000 # Max amount of data to put on log
tape (Kbytes)
# Optical
STAGEBLOB # INFORMIX-OnLine/Optical staging area
# System Configuration
SERVERNUM 0 # Unique id corresponding to a OnLine
instance
DBSERVERNAME insurance_online # Name of default database server
DBSERVERALIASES insurance_remote # List of alternate dbservernames
NETTYPE soctcp,3,600,CPU # Added for Inf. TS 2/24/97
#NETTYPE onsoctcp,3,600,NET # Changed for Inf. CSA 5/3/06
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in
distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No =
0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-
processor
NUMCPUVPS 3 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps
to one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters
LOCKS 750000 # Maximum number of locks
# BUFFERS 75000 # Maximum number of shared buffers
BUFFERS 400000 # Maximum number of shared buffers JE
6/19/07
#NUMAIOVPS 36 # Number of IO vps changed CSA
05/3/06
NUMAIOVPS 4 # Number of IO vps changed JE 06/21/07
#PHYSBUFF 64 # Physical log buffer size (Kbytes)
#LOGBUFF 64 # Logical log buffer size (Kbytes)
PHYSBUFF 512 # Physical log buffer size (Kbytes)
JE 6/21/07
LOGBUFF 512 # Logical log buffer size (Kbytes) JE
6/21/07
LOGSMAX 40 # Maximum number of logical log files
#CLEANERS 32 # Number of buffer cleaner processes
CLEANERS 99 # Number of buffer cleaner processes JE
6/20/07
SHMBASE 0x30000000 # Shared memory base address
#SHMVIRTSIZE 131072 # initial virtual shared memory
segment size
SHMVIRTSIZE 524288 # initial virtual shared memory
segment size
SHMADD 32768 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 1200 # Check point interval (in sec)
# LRUS 4 # Number of LRU queues
# Test config for LRUS. From NG posting seems buffers to LRUS should
be a
# ratio of 2560 BUFFERS to 1 LRUS.
LRUS 99 # Number of LRU queues JE 6/20/07
#LRUS 19 # Number of LRU queues
LRU_MAX_DIRTY 10 # LRU percent dirty begin cleaning
limit
LRU_MIN_DIRTY 5 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark
percentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)
# System Page Size
# BUFFSIZE - OnLine no longer supports this configuration parameter.
# To determine the page size used by OnLine on your
platform
# see the last line of output from the command, 'onstat -
b'.
# Recovery Variables
# OFF_RECVRY_THREADS:
# Number of parallel worker threads during fast recovery or an offline
restore.
# ON_RECVRY_THREADS:
# Number of parallel worker threads during an online restore.
OFF_RECVRY_THREADS 10 # Default number of offline worker
threads
ON_RECVRY_THREADS 1 # Default number of online worker
threads
# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer
flushes (in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file
path
# Backup/Restore variables
BAR_ACT_LOG /tmp/bar_act_insurance.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
# Read Ahead Variables
RA_PAGES # Number of pages to attempt to read
ahead
RA_THRESHOLD # Number of pages left before next
group
# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
# that the OnLine SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that
exist
# when the OnLine system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will
create
# temporary files in /tmp instead.
DBSPACETEMP temp_space1:temp_space2 # Default temp dbspaces
# DUMP*:
# The following parameters control the type of diagnostics information
which
# is preserved when an unanticipated error condition (assertion
failure) occurs
# during OnLine operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
DUMPDIR /tmp # Preserve diagnostics in this
directory
DUMPSHMEM 1 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this
aborts OnLine)
DUMPCNT 1 # Number of shared memory or gcore
dumps for
# a single user's session
FILLFACTOR 90 # Fill factor for building indexes
# method for OnLine to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get
time from O
S(slow)
# Parallel Database Queries (pdq)
#MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
MAX_PDQPRIORITY 75 # Maximum allowed pdqpriority - CSA
DS_MAX_QUERIES 20 # Maximum number of decision support
queries - C
SA
DS_TOTAL_MEMORY 524288 # Decision support memory (Kbytes) -
CSA
DS_MAX_SCANS 1048576 # Maximum number of decision support
scans
DATASKIP off # List of dbspaces to skip
# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# "repeatable read", optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 2 # To hint the optimizer
ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1
= ABORT,
2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0
CDR_LOGBUFFERS 2048 # size of log reading buffer pool
(Kbytes)
CDR_EVALTHREADS 1,1 # evaluator threads (per-cpu-
vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
queue (Kb
ytes)
SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program
path
TBLSPACE_STATS 1
ISM_DATA_POOL ISMData # If the data pool name is changed, be
sure to
ISM_LOG_POOL ISMLogs
OPT_GOAL -1
DIRECTIVES 1
RESTARTABLE_RESTORE off
CDR_LOGDELTA 30 # % of log space allowed in queue
memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
none, 9 ma
x)
BAR_DEBUG_LOG /tmp/bar_dbug.log # ON-Bar Debug Log - not in /tmp
please
Quote:
|
some time ago i used HPL in express mode and reloaded a 100 GB table on a sun which took only 1 hour. |
When we started with HPL the loading of the data was only taking
seconds but re-enabling the indexes and constraints is where things
slowed down.
John
#15
| |||||
| |||||
|
|
This how we are setting pdqpriority from the shell file: export MAX_PDQPRIORITY=100 PSORT_NPROCS=6 PDQPRIORITY=100 FET_BUF_SIZE=32767 |
(i do assume if you create a backup you use onbar -b -w or external
backup??)
Quote:
|
I apologize but I don't know how to check how busy the disks are nor on ex sun |
sar -d <sampletime> < nr samples>
on aix afaicr there is a utitlity called topas which also displays
iostats....
aix should also have sar and if i am not mistaken iostat???
Quote:
|
how long the checkpoints are taking. how long checkpoints are taking, have a look in your log file (onstat - |
Quote:
|
1 hour?!?!?! Did that include indexes and constraints? No only data load, indexes where created later using pdq etc. |
HPL attempts to set pdq, but you do not have full controll.
Superboer.
Quote:
|
When we started with HPL the loading of the data was only taking seconds but re-enabling the indexes and constraints is where things slowed down. John |
#16
| |||
| |||
|
|
(i do assume if you create a backup you use onbar -b -w or external backup??) |
onbar. Since this is our development/test server I don't think
we are backing up the logs. On our test server the backups go to /dev/
null.
Quote:
|
on ex sun iostat -dx <sampletime> < nr samples> or sar -d <sampletime> < nr samples on aix afaicr there is a utitlity called topas which also displays iostats.... aix should also have sar and if i am not mistaken iostat??? how long the checkpoints are taking. how long checkpoints are taking, have a look in your log file (onstat - m) |
I will run sar
during the next import on Saturday night. I'll check the checkpoints
then also.
Quote:
|
1 hour?!?!?! Did that include indexes and constraints? No only data load, indexes where created later using pdq etc. I would create a script and set pdq etc after HPL. HPL attempts to set pdq, but you do not have full controll. |
Our tables are being created with the indexes, constraints, etc
and then HPL takes care of disabling and re-enabling them. But if HPL
is setting its own PDQ, then it might not be setting using
optimal settings of index creation. If I could find an easy way to
muck with the SQL file created by
dbexport each night to move index, constraint and trigger creations to
another SQL file then maybe we could speed things up.
#17
| |||
| |||
|
|
On Jun 22, 3:06 am, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hi Superboer, |
spliting the table and index
creation into seperate sql files. Then after creating the tables
running HPL, then setting PDQ
and then creating the indexes. So now it will run in 8 hours and 40
minutes instead of 10 hours
and 24 minutes. I would still like to get this faster.
Quote:
|
on aix afaicr there is a utitlity called topas which also displays iostats.... aix should also have sar and if i am not mistaken iostat??? how long the checkpoints are taking. how long checkpoints are taking, have a look in your log file (onstat - m) Sorry I should have mentioned that we are running aix on an RS-6000. I will run sar during the next import on Saturday night. I'll check the checkpoints then also. |
while the import was running.
05:00:01 %usr %sys %wio %idle
06:00:01 21 1 0 78
07:00:00 4 0 0 96
08:00:01 0 0 0 100
08:20:01 1 0 0 99
08:40:01 0 0 0 99
09:00:00 0 0 0 99
09:20:00 0 0 1 99
09:40:00 0 0 0 99
10:00:01 1 0 0 99
10:20:01 2 1 0 97
10:40:01 0 0 0 99
11:00:00 1 0 0 99
11:20:00 2 1 1 96
11:40:00 3 1 0 96
12:00:01 5 1 1 94
12:20:01 1 0 1 98
12:40:01 8 0 0 92
13:00:01 3 1 1 95
13:20:01 1 0 0 99
13:40:01 18 4 7 71
14:00:00 2 1 1 96
14:20:00 6 1 1 93
14:40:00 5 1 1 94
15:00:00 17 1 2 80
15:20:00 25 0 0 74
15:40:00 34 3 6 57
16:00:00 24 1 3 72
16:20:00 0 0 0 99
16:40:00 0 0 0 100
17:00:00 1 0 0 99
17:20:00 0 0 0 100
17:40:00 0 0 0 100
18:00:01 0 0 0 100
19:00:00 0 0 0 100
20:00:01 0 0 0 99
21:00:02 9 9 2 79
22:00:02 18 3 1 78
Average 6 1 1 92
tty: tin tout avg-cpu: % user % sys % idle %
iowait
2.0 76.0 9.0 2.1 88.0
0.9
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk0 2.9 65.1 3.8 142438018 458150353
hdisk1 2.1 59.1 2.9 86629487 458128613
hdisk5 7.2 288.9 16.0 749891204 1913831081
hdisk6 1.9 65.5 4.5 283490318 319931240
hdisk8 5.2 237.9 12.4 1642809150 550714601
hdisk3 5.0 227.0 10.7 1558397822 533924883
hdisk4 1.8 68.8 4.8 301438330 332516919
hdisk7 4.7 213.5 10.1 1461735242 506336724
hdisk9 4.1 147.2 16.4 782876350 574582217
hdisk10 6.3 244.0 12.1 346583380 1903228001
hdisk11 3.6 340.4 11.6 1673723910 1464929775
hdisk12 7.2 113.4 19.5 513698446 531469423
hdisk13 7.0 108.4 19.1 492614050 506336724
hdisk14 5.1 170.9 17.6 999561254 575746368
hdisk15 3.6 131.6 14.1 894433394 319094627
hdisk16 7.2 115.8 19.6 516760878 550714601
hdisk17 3.4 136.2 14.7 921304710 334455264
hdisk2 2.6 277.7 9.0 1095040122 1464929775
cd0 0.0 0.0 0.0 0 0
Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:36:41
-- 221180
8 Kbytes
Message Log File: /usr/informix/online.insurance
21:55:25 Checkpoint loguniq 9895, logpos 0x925098
21:55:56 Checkpoint Completed: duration was 5 seconds.
21:55:56 Checkpoint loguniq 9895, logpos 0x94a018
21:56:00 Checkpoint Completed: duration was 0 seconds.
21:56:00 Checkpoint loguniq 9895, logpos 0x94b018
21:56:15 Checkpoint Completed: duration was 2 seconds.
21:56:15 Checkpoint loguniq 9895, logpos 0x975018
21:57:59 Checkpoint Completed: duration was 0 seconds.
21:57:59 Checkpoint loguniq 9895, logpos 0x977018
22:00:08 Checkpoint Completed: duration was 32 seconds.
22:00:08 Checkpoint loguniq 9895, logpos 0x97b17c
22:00:32 Checkpoint Completed: duration was 7 seconds.
22:00:32 Checkpoint loguniq 9895, logpos 0x97c138
#18
| ||||||||
| ||||||||
|
|
Once you get that all worked out, post the following if it's still not good: onstat -p (including the time since stats were zero'd). I'm not sure if I included the time since stats wer zero'd (I ran |
what you meant or were looking for with that statement.
Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:47:33
-- 221180
8 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
1570999 8775316 28181244 94.43 1135204 2834114 3322217 65.83
isamtot open start read write rewrite delete
commit rollbk
53795341 130822 148858 12409836 35421742 3680 13513
5476 139
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 4259.90 417.96 372 762
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
848420 0 15624669 0 0 640 1869 11852
ixda-RA idx-RA da-RA RA-pgsused lchwaits
42 1 253189 253220 42055
Quote:
|
onstat -D Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- |
8 -- 2211808 Kbytes
Blocked:CKPT
Dbspaces
address number flags fchunk nchunks flags owner name
a0058150 1 1 1 1 N informix rootdbs
a008ff30 2 1 2 1 N informix histdbs
a0f96018 3 1001 3 1 N informix
fragdbs1
a0f960d8 4 1001 4 1 N informix
fragdbs3
a0f96198 5 1001 5 1 N informix
fragdbs5
a0f96258 6 1001 6 1 N informix
fragdbs2
a0f96318 7 1001 7 1 N informix
fragdbs4
a0f963d8 8 1001 8 1 N informix
fragdbs6
a0f96498 9 1 9 1 N informix histidx
a0f96558 10 1 10 1 N informix
fragidx1
a0f96618 11 1 11 1 N informix
fragidx2
a0f966d8 12 1 12 1 N informix
fragidx3
a0f96798 13 1 13 1 N informix
fragidx4
a0f96858 14 1 14 1 N informix
fragidx5
a0f96918 15 1 15 1 N informix
fragidx6
a0f969d8 16 2001 16 1 N T informix
temp_space1
a0f96a98 17 2001 17 1 N T informix
temp_space2
a0f96b58 18 1001 18 5 N informix
indexdbs
a0f96c18 19 1 21 1 N informix datadbs
19 active, 2047 maximum
Chunks
address chk/dbs offset page Rd page Wr pathname
a0058210 1 1 1 90491 997000 /usr/informix/dblinks/
infrootlv1
a00832f0 2 2 1 1177229 111776 /usr/informix/dblinks/
infhistlv1
a00833f0 3 3 1 1199160 147996 /usr/informix/dblinks/
infbdatalv1
a00834f0 4 4 1 1199219 147981 /usr/informix/dblinks/
infbdatalv3
a00835f0 5 5 1 1199237 147995 /usr/informix/dblinks/
infbdatalv5
a00836f0 6 6 1 1199235 147996 /usr/informix/dblinks/
infbdatalv2
a00837f0 7 7 1 1199236 147994 /usr/informix/dblinks/
infbdatalv4
a00838f0 8 8 1 1199174 147992 /usr/informix/dblinks/
infbdatalv6
a00839f0 9 9 1 62455 72056 /usr/informix/dblinks/
infhidxlv1
a0083af0 10 10 1 17057 17098 /usr/informix/dblinks/
inffidxlv1
a0083bf0 11 11 1 16365 16405 /usr/informix/dblinks/
inffidxlv2
a0083cf0 12 12 1 1576 1577 /usr/informix/dblinks/
inffidxlv3
a0083df0 13 13 1 1445 1459 /usr/informix/dblinks/
inffidxlv4
a0083ef0 14 14 1 25 23 /usr/informix/dblinks/
inffidxlv5
a008f630 15 15 1 19 17 /usr/informix/dblinks/
inffidxlv6
a008f730 16 16 1 5 878 /usr/informix/dblinks/
inftemplv1
a008f830 17 17 1 6 886 /usr/informix/dblinks/
inftemplv2
a008f930 18 18 1 274158 351676 /usr/informix/dblinks/
infidxlv1
a008fa30 19 18 1 152513 186007 /usr/informix/dblinks/
infidxlv2
a008fb30 20 18 1 305521 313197 /usr/informix/dblinks/
infidxlv3
a008fc30 21 19 1 3 0 /usr/informix/dblinks/
infdatalv1
a008fd30 22 18 1 2 1 /usr/informix/dblinks/
infidxlv4
a008fe30 23 18 1 2 1 /usr/informix/dblinks/
infidxlv5
23 active, 2047 maximum
Quote:
|
onstat -P |
-- 221180
8 Kbytes
partnum total btree data other resident dirty
0 199025 198583 6 436 0 985
1048578 2 1 1 0 0 0
1048579 8 4 4 0 0 0
1048580 1 1 0 0 0 0
1048582 1 1 0 0 0 0
1048584 4 2 2 0 0 0
1048595 1 1 0 0 0 0
1048606 1 1 0 0 0 0
1048627 30 12 17 1 0 0
1048628 11 6 4 1 0 0
1048629 37 14 23 0 0 0
1048630 2 1 1 0 0 0
1048631 2 1 1 0 0 0
1048633 2 1 1 0 0 0
1048634 1 1 0 0 0 0
1048636 1 1 0 0 0 0
1048637 32 7 24 1 0 0
1048638 1 1 0 0 0 0
1048640 2 1 1 0 0 0
1048641 9 6 2 1 0 0
1048646 1 1 0 0 0 0
1048647 1 1 0 0 0 0
1048648 1 1 0 0 0 0
1048650 1 1 0 0 0 0
1048651 98 20 77 1 0 0
1048652 2 2 0 0 0 0
1048654 44 18 25 1 0 0
1048655 5 3 1 1 0 0
1048672 4 2 2 0 0 0
1048838 5 0 5 0 0 0
1048839 11 0 11 0 0 0
1048841 28 0 28 0 0 0
1048842 28 0 28 0 0 0
1049257 4 2 2 0 0 0
1049276 3 2 1 0 0 0
1049278 2 1 1 0 0 0
1049279 1 1 0 0 0 0
1049281 2 1 1 0 0 0
1049284 2 1 1 0 0 0
1049285 1 1 0 0 0 0
1049288 1 1 0 0 0 0
1049294 1 1 0 0 0 0
1049295 1 1 0 0 0 0
1049296 1 1 0 0 0 0
1049299 1 1 0 0 0 0
1049303 3 3 0 0 0 0
1049305 1 0 1 0 0 0
1049306 116 8 108 0 0 0
1049310 69 3 66 0 0 0
1049311 230 5 220 5 0 0
1049314 39 4 34 1 0 0
1049315 173 5 163 5 0 0
1049316 1 1 0 0 0 0
1049319 23 3 19 1 0 0
1049320 236 0 236 0 0 0
1049323 1 0 1 0 0 0
1049328 13 13 0 0 0 0
2097158 28 0 28 0 0 0
2097159 10 0 10 0 0 0
3145738 28 0 28 0 0 0
3145766 4 4 0 0 0 0
3145767 7 7 0 0 0 0
3145770 10 0 10 0 0 0
3145773 6 0 6 0 0 0
3145774 36 0 36 0 0 0
3145779 1 1 0 0 0 0
3145786 7 7 0 0 0 0
3145787 10 10 0 0 0 0
3145788 26 26 0 0 0 0
3145820 28 0 28 0 0 0
3145824 19 0 19 0 0 0
4194314 28 0 28 0 0 0
4194342 4 4 0 0 0 0
4194343 6 6 0 0 0 0
4194346 10 0 10 0 0 0
4194349 6 0 6 0 0 0
4194350 36 0 36 0 0 0
4194355 1 1 0 0 0 0
4194362 7 7 0 0 0 0
4194363 10 10 0 0 0 0
4194364 26 26 0 0 0 0
4194396 28 0 28 0 0 0
4194400 19 0 19 0 0 0
5242890 28 0 28 0 0 0
5242918 4 4 0 0 0 0
5242919 7 7 0 0 0 0
5242922 10 0 10 0 0 0
5242925 6 0 6 0 0 0
5242926 36 0 36 0 0 0
5242931 1 1 0 0 0 0
5242938 7 7 0 0 0 0
5242939 10 10 0 0 0 0
5242940 26 26 0 0 0 0
5242972 28 0 28 0 0 0
5242976 19 0 19 0 0 0
6291466 28 0 28 0 0 0
6291494 4 4 0 0 0 0
6291495 7 7 0 0 0 0
6291498 10 0 10 0 0 0
6291501 6 0 6 0 0 0
6291502 36 0 36 0 0 0
6291507 1 1 0 0 0 0
6291514 7 7 0 0 0 0
6291515 10 10 0 0 0 0
6291516 26 26 0 0 0 0
6291548 28 0 28 0 0 0
6291552 19 0 19 0 0 0
7340042 28 0 28 0 0 0
7340070 4 4 0 0 0 0
7340071 8 8 0 0 0 0
7340074 10 0 10 0 0 0
7340077 6 0 6 0 0 0
7340078 36 0 36 0 0 0
7340083 1 1 0 0 0 0
7340090 7 7 0 0 0 0
7340091 11 11 0 0 0 0
7340092 27 27 0 0 0 0
7340124 28 0 28 0 0 0
7340128 19 0 19 0 0 0
8388618 28 0 28 0 0 0
8388646 4 4 0 0 0 0
8388647 7 7 0 0 0 0
8388650 10 0 10 0 0 0
8388653 6 0 6 0 0 0
8388654 36 0 36 0 0 0
8388659 1 1 0 0 0 0
8388666 7 7 0 0 0 0
8388667 10 10 0 0 0 0
8388668 27 27 0 0 0 0
8388700 28 0 28 0 0 0
8388704 19 0 19 0 0 0
9437190 7 7 0 0 0 0
9437191 6 6 0 0 0 0
9437192 6 6 0 0 0 0
9437193 6 6 0 0 0 0
9437194 6 6 0 0 0 0
9437195 7 7 0 0 0 0
9437196 4 4 0 0 0 0
9437197 5 5 0 0 0 0
9437205 211 211 0 0 0 211
9437206 6 6 0 0 0 6
9437207 13 13 0 0 0 13
9437259 21 21 0 0 0 0
9437260 23 23 0 0 0 0
9437261 18 18 0 0 0 0
9437262 13 13 0 0 0 0
9437263 18 18 0 0 0 0
9437264 15 15 0 0 0 0
9437265 18 18 0 0 0 0
9437266 18 18 0 0 0 0
9437267 18 18 0 0 0 0
9437268 17 17 0 0 0 0
9437269 17 17 0 0 0 0
9437270 8 8 0 0 0 0
9437271 3 3 0 0 0 0
9437272 7 7 0 0 0 0
9437273 3 3 0 0 0 0
9437274 4 4 0 0 0 0
9437275 7 7 0 0 0 0
9437276 6 6 0 0 0 0
9437277 7 7 0 0 0 0
9437278 3 3 0 0 0 0
9437279 3 3 0 0 0 0
10485762 53 53 0 0 0 0
11534338 46 46 0 0 0 0
12582914 8 8 0 0 0 0
13631490 9 9 0 0 0 0
16777218 2 1 1 0 0 1
17825794 2 1 1 0 0 0
18874370 2 2 0 0 0 0
18874373 8 8 0 0 0 0
18874374 6 6 0 0 0 0
18874375 7 7 0 0 0 0
18874376 2 2 0 0 0 0
18874378 2 2 0 0 0 0
18874380 2 2 0 0 0 0
18874382 2 2 0 0 0 0
18874385 2 2 0 0 0 0
18874386 2 2 0 0 0 0
18874387 2 2 0 0 0 0
18874388 2 2 0 0 0 0
18874389 2 2 0 0 0 0
18874390 2 2 0 0 0 0
18874393 2 2 0 0 0 0
18874394 2 2 0 0 0 0
18874395 2 2 0 0 0 0
18874396 2 2 0 0 0 0
18874397 2 2 0 0 0 0
18874398 2 2 0 0 0 0
18874399 1 1 0 0 0 0
18874400 2 2 0 0 0 0
18874401 2 2 0 0 0 0
18874402 1 1 0 0 0 0
18874403 2 2 0 0 0 0
18874404 2 2 0 0 0 0
18874407 2 2 0 0 0 0
18874408 2 2 0 0 0 0
18874409 2 2 0 0 0 0
18874410 2 2 0 0 0 0
18874411 2 2 0 0 0 0
18874412 2 2 0 0 0 0
18874413 2 2 0 0 0 0
18874414 2 2 0 0 0 0
18874415 2 2 0 0 0 0
18874416 2 2 0 0 0 0
18874417 2 2 0 0 0 0
18874418 1 1 0 0 0 0
18874419 2 2 0 0 0 0
18874422 2 2 0 0 0 0
18874423 2 2 0 0 0 0
18874424 2 2 0 0 0 0
18874425 2 2 0 0 0 0
18874426 2 2 0 0 0 0
18874427 2 2 0 0 0 0
18874428 2 2 0 0 0 0
18874429 2 2 0 0 0 0
18874430 2 2 0 0 0 0
18874431 2 2 0 0 0 0
18874435 2 2 0 0 0 0
18874436 2 2 0 0 0 0
18874450 4 4 0 0 0 0
18874451 1 1 0 0 0 0
18874452 2 2 0 0 0 0
18874453 2 2 0 0 0 0
18874454 2 2 0 0 0 0
18874455 2 2 0 0 0 0
18874456 4 4 0 0 0 0
18874458 2 2 0 0 0 0
18874459 1 1 0 0 0 0
18874460 10 10 0 0 0 0
18874461 23 23 0 0 0 0
18874462 17 17 0 0 0 0
18874463 17 17 0 0 0 0
18874464 16 16 0 0 0 0
18874466 2 2 0 0 0 0
18874467 2 2 0 0 0 0
18874471 2 2 0 0 0 0
18874472 2 2 0 0 0 0
18874474 2 2 0 0 0 0
18874475 2 2 0 0 0 0
18874477 55 55 0 0 0 0
18874478 56 56 0 0 0 0
18874479 52 52 0 0 0 0
18874480 59 59 0 0 0 0
18874481 55 55 0 0 0 0
18874482 57 57 0 0 0 0
18874483 69 69 0 0 0 0
18874484 57 57 0 0 0 0
18874485 12 12 0 0 0 0
18874486 7 7 0 0 0 0
18874487 9 9 0 0 0 0
18874488 7 7 0 0 0 0
18874489 10 10 0 0 0 0
18874490 9 9 0 0 0 0
18874491 9 9 0 0 0 0
18874492 8 8 0 0 0 0
18874493 3 3 0 0 0 0
18874494 4 4 0 0 0 0
18874495 3 3 0 0 0 0
18874496 7 7 0 0 0 0
18874497 7 7 0 0 0 0
18874498 7 7 0 0 0 0
18874499 7 7 0 0 0 0
18874500 6 6 0 0 0 0
18874509 2 2 0 0 0 0
18874510 4 4 0 0 0 0
18874517 2 2 0 0 0 0
18874518 2 2 0 0 0 0
18874519 40 40 0 0 0 0
18874520 16 16 0 0 0 0
18874521 20 20 0 0 0 0
18874523 2 2 0 0 0 0
18874528 10 10 0 0 0 0
18874529 1 1 0 0 0 0
18874530 2 2 0 0 0 0
18874531 57 57 0 0 0 0
18874532 55 55 0 0 0 0
18874533 55 55 0 0 0 0
18874534 56 56 0 0 0 0
18874535 31 31 0 0 0 0
18874540 2 2 0 0 0 0
18874541 2 2 0 0 0 0
18874542 2 2 0 0 0 0
18874543 2 2 0 0 0 0
18874544 19 19 0 0 0 0
18874545 7 7 0 0 0 0
18874546 144 144 0 0 0 0
18874547 302 302 0 0 0 0
18874548 8 8 0 0 0 0
18874549 10 10 0 0 0 0
18874550 324 324 0 0 0 0
18874551 252 252 0 0 0 0
18874552 249 249 0 0 0 0
18874553 24 24 0 0 0 0
18874554 325 325 0 0 0 0
18874555 4 4 0 0 0 0
18874556 4 4 0 0 0 0
18874557 72 72 0 0 0 0
18874558 72 72 0 0 0 0
18874559 7 7 0 0 0 0
18874560 906 906 0 0 0 0
18874561 1610 1610 0 0 0 0
18874562 1421 1421 0 0 0 0
18874563 16 16 0 0 0 0
18874564 4177 4177 0 0 0 0
18874565 3449 3449 0 0 0 0
18874566 3405 3405 0 0 0 0
18874567 3470 3470 0 0 0 0
18874568 3545 3545 0 0 0 0
18874569 342 342 0 0 0 0
18874570 4179 4179 0 0 0 0
18874571 3643 3643 0 0 0 0
18874572 4164 4164 0 0 0 0
18874573 3645 3645 0 0 0 0
18874574 20 20 0 0 0 0
18874575 3664 3664 0 0 0 0
18874576 2 2 0 0 0 0
18874577 2 2 0 0 0 0
18874578 2 2 0 0 0 0
18874579 2 2 0 0 0 0
18874580 1394 1394 0 0 0 0
18874592 1218 1218 0 0 0 0
18874593 1202 1202 0 0 0 0
18874594 652 652 0 0 0 0
18874595 1234 1234 0 0 0 0
18874596 76 76 0 0 0 0
18874597 1115 1115 0 0 0 0
18874598 13 13 0 0 0 0
18874599 1390 1390 0 0 0 0
18874600 1391 1391 0 0 0 0
18874601 49 49 0 0 0 0
18874602 68 68 0 0 0 0
18874603 61 61 0 0 0 0
18874604 24 24 0 0 0 0
18874605 87 87 0 0 0 0
18874606 4 4 0 0 0 0
18874607 87 87 0 0 0 0
18874608 87 87 0 0 0 0
18874609 17326 17326 0 0 0 0
18874610 15397 15397 0 0 0 0
18874611 14757 14757 0 0 0 0
18874612 14605 14605 0 0 0 0
18874613 14938 14938 0 0 0 0
18874614 1407 1407 0 0 0 0
18874615 40 40 0 0 0 0
18874618 15199 15199 0 0 0 0
18874619 14450 14450 0 0 0 0
18874620 17283 17283 0 0 0 0
18874621 17327 17327 0 0 0 0
18874622 11 11 0 0 0 0
18874623 4 4 0 0 0 0
18874624 153 153 0 0 0 0
18874633 142 142 0 0 0 0
18874984 2 2 0 0 0 0
18875003 2 2 0 0 0 0
18875006 2 2 0 0 0 0
18875015 2 2 0 0 0 0
18875074 10 10 0 0 0 0
18875075 7 7 0 0 0 0
18875123 6 6 0 0 0 0
18875126 2 2 0 0 0 0
18875132 2 2 0 0 0 0
18875142 2 2 0 0 0 0
18875143 2 2 0 0 0 0
18875144 2 2 0 0 0 0
18875145 2 2 0 0 0 0
18875146 2 2 0 0 0 0
18875153 2 2 0 0 0 0
18875156 2 2 0 0 0 0
18875157 2 2 0 0 0 0
Totals: 400000 397625 1920 455 0 1216
Percentages:
Data 0.48
Btree 99.41
Other 0.11
Quote:
|
onstat -g iov |
-- 221180
8 Kbytes
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
errors
kio 0 i 101.0 689815 574070 115745 0 1380514
0.5 0
kio 1 i 83.9 572828 497323 75505 0 1044176
0.5 202
kio 2 i 63.4 432942 368645 64297 0 786676
0.6 0
msc 0 i 0.6 4366 0 0 0 4365
1.0 0
aio 0 i 0.0 89 37 0 0 90
1.0 0
aio 1 i 0.0 1 0 0 0 1
1.0 0
aio 2 i 0.0 1 0 0 0 1
1.0 0
aio 3 i 0.0 0 0 0 0 1
0.0 0
pio 0 i 0.0 0 0 0 0 1
0.0 0
lio 0 i 0.0 0 0 0 0 1
0.0 0
Quote:
|
onstat -F |
-- 221180
8 Kbytes
Fg Writes LRU Writes Chunk Writes
0 43169 1091716
address flusher state data
a005a518 0 I 0 = 0X0
a005aa18 1 I 0 = 0X0
a005af18 2 I 0 = 0X0
a005b418 3 I 0 = 0X0
a005b918 4 I 0 = 0X0
a005be18 5 I 0 = 0X0
a005c318 6 I 0 = 0X0
a005c818 7 I 0 = 0X0
a005cd18 8 I 0 = 0X0
a005d218 9 I 0 = 0X0
a005d718 10 I 0 = 0X0
a005dc18 11 I 0 = 0X0
a005e118 12 I 0 = 0X0
a005e618 13 I 0 = 0X0
a005eb18 14 I 0 = 0X0
a005f018 15 I 0 = 0X0
a005f518 16 I 0 = 0X0
a005fa18 17 I 0 = 0X0
a005ff18 18 I 0 = 0X0
a0060418 19 I 0 = 0X0
a0060918 20 I 0 = 0X0
a0060e18 21 I 0 = 0X0
a0061318 22 I 0 = 0X0
a0061818 23 I 0 = 0X0
a0061d18 24 I 0 = 0X0
a0062218 25 I 0 = 0X0
a0062718 26 I 0 = 0X0
a0062c18 27 I 0 = 0X0
a0063118 28 I 0 = 0X0
a0063618 29 I 0 = 0X0
a0063b18 30 I 0 = 0X0
a0064018 31 I 0 = 0X0
a0064518 32 I 0 = 0X0
a0064a18 33 I 0 = 0X0
a0064f18 34 I 0 = 0X0
a0065418 35 I 0 = 0X0
a0065918 36 I 0 = 0X0
a0065e18 37 I 0 = 0X0
a0066318 38 I 0 = 0X0
a0066818 39 I 0 = 0X0
a0066d18 40 I 0 = 0X0
a0067218 41 I 0 = 0X0
a0067718 42 I 0 = 0X0
a0067c18 43 I 0 = 0X0
a0068118 44 I 0 = 0X0
a0068618 45 I 0 = 0X0
a0068b18 46 I 0 = 0X0
a0069018 47 I 0 = 0X0
a0069518 48 I 0 = 0X0
a0069a18 49 I 0 = 0X0
a0069f18 50 I 0 = 0X0
a006a418 51 I 0 = 0X0
a006a918 52 I 0 = 0X0
a006ae18 53 I 0 = 0X0
a006b318 54 I 0 = 0X0
a006b818 55 I 0 = 0X0
a006bd18 56 I 0 = 0X0
a006c218 57 I 0 = 0X0
a006c718 58 I 0 = 0X0
a006cc18 59 I 0 = 0X0
a006d118 60 I 0 = 0X0
a006d618 61 I 0 = 0X0
a006db18 62 I 0 = 0X0
a006e018 63 I 0 = 0X0
a006e518 64 I 0 = 0X0
a006ea18 65 I 0 = 0X0
a006ef18 66 I 0 = 0X0
a006f418 67 I 0 = 0X0
a006f918 68 I 0 = 0X0
a006fe18 69 I 0 = 0X0
a0070318 70 I 0 = 0X0
a0070818 71 I 0 = 0X0
a0070d18 72 I 0 = 0X0
a0071218 73 I 0 = 0X0
a0071718 74 I 0 = 0X0
a0071c18 75 I 0 = 0X0
a0072118 76 I 0 = 0X0
a0072618 77 I 0 = 0X0
a0072b18 78 I 0 = 0X0
a0073018 79 I 0 = 0X0
a0073518 80 I 0 = 0X0
a0073a18 81 I 0 = 0X0
a0073f18 82 I 0 = 0X0
a0074418 83 I 0 = 0X0
a0074918 84 I 0 = 0X0
a0074e18 85 I 0 = 0X0
a0075318 86 I 0 = 0X0
a0075818 87 I 0 = 0X0
a0075d18 88 I 0 = 0X0
a0076218 89 I 0 = 0X0
a0076718 90 I 0 = 0X0
a0076c18 91 I 0 = 0X0
a0077118 92 I 0 = 0X0
a0077618 93 I 0 = 0X0
a0077b18 94 I 0 = 0X0
a0078018 95 I 0 = 0X0
a0078518 96 I 0 = 0X0
a0078a18 97 I 0 = 0X0
a0078f18 98 I 0 = 0X0
states: Exit Idle Chunk Lru
Quote:
|
onstat -R |
-- 221180
8 Kbytes
99 buffer LRU queue pairs priority levels
# f/m pair total % of length LOW MED_LOW MED_HIGH HIGH
0 f 4041 98.0% 3961 0 3902 56 3
1 m 2.0% 80 0 80 0 0
2 f 4040 98.0% 3960 0 3880 74 6
3 m 2.0% 80 0 80 0 0
4 f 4040 98.0% 3960 0 3911 46 3
5 m 2.0% 80 0 80 0 0
6 f 4040 98.0% 3960 0 3897 61 2
7 m 2.0% 80 0 80 0 0
8 f 4041 98.0% 3961 0 3901 59 1
9 m 2.0% 80 0 80 0 0
10 f 4041 98.0% 3960 0 3894 62 4
11 m 2.0% 81 0 80 1 0
12 f 4041 98.0% 3961 0 3893 62 6
13 m 2.0% 80 0 80 0 0
14 f 4041 98.0% 3961 0 3888 67 6
15 m 2.0% 80 0 79 1 0
16 f 4041 98.0% 3961 0 3906 49 6
17 m 2.0% 80 0 80 0 0
18 f 4040 98.0% 3960 0 3894 63 3
19 m 2.0% 80 0 80 0 0
20 f 4041 98.0% 3961 0 3893 63 5
21 m 2.0% 80 0 80 0 0
22 f 4040 98.0% 3960 0 3893 62 5
23 m 2.0% 80 0 78 2 0
24 f 4041 98.0% 3961 0 3898 60 3
25 m 2.0% 80 0 79 1 0
26 f 4041 98.0% 3961 0 3916 45 0
27 m 2.0% 80 0 79 1 0
28 f 4041 98.0% 3961 0 3904 53 4
29 m 2.0% 80 0 80 0 0
30 f 4040 98.0% 3959 0 3907 47 5
31 m 2.0% 81 0 81 0 0
32 f 4041 98.0% 3961 0 3886 67 8
33 m 2.0% 80 0 80 0 0
34 f 4040 98.0% 3960 0 3894 58 8
35 m 2.0% 80 0 80 0 0
36 f 4041 98.0% 3961 0 3904 53 4
37 m 2.0% 80 0 80 0 0
38 f 4040 98.0% 3960 0 3906 50 4
39 m 2.0% 80 0 79 1 0
40 f 4040 98.0% 3960 0 3904 47 9
41 m 2.0% 80 0 79 1 0
42 f 4040 98.0% 3960 0 3896 56 8
43 m 2.0% 80 0 78 2 0
44 f 4040 98.0% 3960 0 3900 59 1
45 m 2.0% 80 0 80 0 0
46 f 4041 98.0% 3961 0 3901 54 6
47 m 2.0% 80 0 80 0 0
48 f 4040 98.0% 3960 0 3893 62 5
49 m 2.0% 80 0 80 0 0
50 f 4039 98.0% 3960 0 3908 50 2
51 m 2.0% 79 0 79 0 0
52 f 4040 98.0% 3959 0 3892 63 4
53 m 2.0% 81 0 81 0 0
54 f 4040 98.0% 3960 0 3889 66 5
55 m 2.0% 80 0 80 0 0
56 f 4040 98.0% 3960 0 3913 43 4
57 m 2.0% 80 0 80 0 0
58 f 4040 98.0% 3959 0 3888 67 4
59 m 2.0% 81 0 79 2 0
60 f 4040 98.0% 3960 0 3900 55 5
61 m 2.0% 80 0 79 1 0
62 f 4040 98.0% 3960 0 3917 41 2
63 m 2.0% 80 0 79 1 0
64 f 4040 98.0% 3960 0 3913 43 4
65 m 2.0% 80 0 80 0 0
66 f 4040 98.0% 3960 0 3891 65 4
67 m 2.0% 80 0 80 0 0
68 f 4040 98.0% 3960 0 3893 61 6
69 m 2.0% 80 0 80 0 0
70 f 4041 98.0% 3960 0 3915 44 1
71 m 2.0% 81 0 80 1 0
72 f 4040 98.0% 3959 0 3895 57 7
73 m 2.0% 81 0 81 0 0
74 f 4040 98.0% 3959 0 3919 36 4
75 m 2.0% 81 0 80 1 0
76 f 4040 98.0% 3960 0 3911 45 4
77 m 2.0% 80 0 80 0 0
78 f 4040 98.0% 3960 0 3897 59 4
79 m 2.0% 80 0 80 0 0
80 f 4041 98.0% 3961 0 3885 72 4
81 m 2.0% 80 0 80 0 0
82 f 4040 98.0% 3960 0 3893 61 6
83 m 2.0% 80 0 80 0 0
84 f 4041 98.0% 3961 0 3881 75 5
85 m 2.0% 80 0 80 0 0
86 f 4040 98.0% 3960 0 3893 63 4
87 m 2.0% 80 0 80 0 0
88 f 4041 98.0% 3961 0 3898 59 4
89 m 2.0% 80 0 80 0 0
90 f 4040 98.0% 3960 0 3904 51 5
91 m 2.0% 80 0 79 1 0
92 f 4041 98.0% 3961 0 3916 43 2
93 m 2.0% 80 0 80 0 0
94 f 4040 98.0% 3960 0 3893 60 7
95 m 2.0% 80 0 79 1 0
96 f 4041 98.0% 3961 0 3899 57 5
97 m 2.0% 80 0 80 0 0
98 f 4040 98.0% 3959 0 3889 63 7
99 m 2.0% 81 0 81 0 0
100 f 4041 98.0% 3960 0 3896 60 4
101 m 2.0% 81 0 81 0 0
102 f 4040 98.0% 3959 0 3894 57 8
103 m 2.0% 81 0 80 1 0
104 f 4041 98.0% 3960 0 3891 61 8
105 m 2.0% 81 0 81 0 0
106 f 4039 98.0% 3959 0 3902 50 7
107 m 2.0% 80 0 78 2 0
108 f 4040 98.0% 3960 0 3886 68 6
109 m 2.0% 80 0 80 0 0
110 f 4040 98.0% 3960 0 3901 55 4
111 m 2.0% 80 0 80 0 0
112 f 4041 98.0% 3960 0 3901 54 5
113 m 2.0% 81 0 80 1 0
114 f 4040 98.0% 3959 0 3884 68 7
115 m 2.0% 81 0 80 1 0
116 f 4040 98.0% 3959 0 3880 69 10
117 m 2.0% 81 0 80 1 0
118 f 4041 98.0% 3961 0 3905 49 7
119 m 2.0% 80 0 80 0 0
120 f 4040 98.0% 3960 0 3889 66 5
121 m 2.0% 80 0 80 0 0
122 f 4040 98.0% 3960 0 3894 50 16
123 m 2.0% 80 0 79 1 0
124 f 4041 98.0% 3961 0 3903 48 10
125 m 2.0% 80 0 79 1 0
126 f 4041 98.0% 3961 0 3896 61 4
127 m 2.0% 80 0 80 0 0
128 f 4041 98.0% 3960 0 3899 60 1
129 m 2.0% 81 0 81 0 0
130 f 4040 98.0% 3960 0 3892 58 10
131 m 2.0% 80 0 80 0 0
132 f 4041 98.0% 3959 0 3895 56 8
133 m 2.0% 82 0 81 1 0
134 F 4039 98.0% 3960 0 3882 70 8
135 m 2.0% 79 0 78 1 0
136 f 4041 98.0% 3961 0 3899 55 7
137 m 2.0% 80 0 79 1 0
138 f 4041 98.0% 3961 0 3884 70 7
139 m 2.0% 80 0 80 0 0
140 f 4039 98.0% 3960 0 3901 52 7
141 m 2.0% 79 0 79 0 0
142 f 4041 98.0% 3961 0 3894 62 5
143 m 2.0% 80 0 79 1 0
144 f 4040 97.9% 3955 0 3889 56 10
145 m 2.1% 85 0 85 0 0
146 f 4040 98.0% 3960 0 3886 70 4
147 m 2.0% 80 0 80 0 0
148 f 4040 98.0% 3960 0 3904 52 4
149 m 2.0% 80 0 80 0 0
150 f 4040 98.0% 3960 0 3909 47 4
151 m 2.0% 80 0 80 0 0
152 f 4041 98.0% 3961 0 3902 55 4
153 m 2.0% 80 0 79 1 0
154 f 4040 98.0% 3960 0 3900 55 5
155 m 2.0% 80 0 80 0 0
156 f 4041 98.0% 3961 0 3895 59 7
157 m 2.0% 80 0 80 0 0
158 f 4040 98.0% 3960 0 3909 47 4
159 m 2.0% 80 0 80 0 0
160 f 4040 98.0% 3960 0 3892 59 9
161 m 2.0% 80 0 80 0 0
162 f 4040 98.0% 3960 0 3903 53 4
163 m 2.0% 80 0 79 1 0
164 f 4041 98.0% 3961 0 3897 62 2
165 m 2.0% 80 0 78 2 0
166 f 4040 98.0% 3960 0 3902 52 6
167 m 2.0% 80 0 80 0 0
168 f 4040 98.0% 3960 0 3913 38 9
169 m 2.0% 80 0 80 0 0
170 f 4040 98.0% 3960 0 3881 74 5
171 m 2.0% 80 0 80 0 0
172 f 4041 98.0% 3961 0 3899 57 5
173 m 2.0% 80 0 79 1 0
174 f 4041 98.0% 3961 0 3872 79 10
175 m 2.0% 80 0 79 1 0
176 f 4040 98.0% 3960 0 3886 68 6
177 m 2.0% 80 0 80 0 0
178 f 4041 98.0% 3961 0 3866 92 3
179 m 2.0% 80 0 80 0 0
180 f 4040 98.0% 3959 0 3890 60 9
181 m 2.0% 81 0 81 0 0
182 f 4040 98.0% 3960 0 3888 68 4
183 m 2.0% 80 0 79 1 0
184 f 4040 97.9% 3957 0 3889 67 1
185 m 2.1% 83 0 83 0 0
186 f 4040 98.0% 3960 0 3899 54 7
187 m 2.0% 80 0 80 0 0
188 f 4040 98.0% 3960 0 3896 59 5
189 m 2.0% 80 0 79 1 0
190 f 4041 98.0% 3960 0 3886 68 6
191 m 2.0% 81 0 81 0 0
192 f 4040 98.0% 3960 0 3890 67 3
193 m 2.0% 80 0 79 1 0
194 f 4040 98.0% 3960 0 3897 59 4
195 m 2.0% 80 0 80 0 0
196 f 4040 98.0% 3960 0 3906 53 1
197 m 2.0% 80 0 80 0 0
7944 dirty, 399993 queued, 400000 total, 524288 hash buckets, 4096
buffer size
start clean at 10% (of pair total) dirty, or 404 buffs dirty, stop at
5%
0 priority downgrades, 0 priority upgrades
Quote:
|
onstat -g iof |
-- 221180
8 Kbytes
AIO global files:
gfd pathname totalops dskread dskwrite io/s
3 infrootlv1 108738 9564 99174 15.8
4 infhistlv1 85367 77406 7961 12.4
5 infbdatalv1 105075 87047 18028 15.2
6 infbdatalv3 103737 87114 16623 15.0
7 infbdatalv5 97479 87132 10347 14.1
8 infbdatalv2 105271 87130 18141 15.3
9 infbdatalv4 98409 87131 11278 14.3
10 infbdatalv6 97818 87057 10761 14.2
11 infhidxlv1 78190 72655 5535 11.3
12 inffidxlv1 18155 17057 1098 2.6
13 inffidxlv2 17416 16365 1051 2.5
14 inffidxlv3 1682 1576 106 0.2
15 inffidxlv4 1553 1445 108 0.2
16 inffidxlv5 35 25 10 0.0
17 inffidxlv6 27 19 8 0.0
18 inftemplv1 458 5 453 0.1
19 inftemplv2 462 6 456 0.1
20 infidxlv1 298669 274618 24051 43.3
21 infidxlv2 164296 152513 11783 23.8
22 infidxlv3 325403 305521 19882 47.2
23 infdatalv1 3 3 0 0.0
24 infidxlv4 3 2 1 0.0
25 infidxlv5 3 2 1 0.0
Quote:
|
And the update ONCONFIG (or onstat -c output). We'll see if we can help more. Art S. Kagel |
#19
| |||
| |||
|
|
export MAX_PDQPRIORITY=100 PSORT_NPROCS=6 PDQPRIORITY=100 FET_BUF_SIZE=32767 |
i do not see any i/o on your tempdbspaces....before you start do a
onstat -z
can you try setting DBSPACETEMP env var:
export DBSPACETEMP= temp_space1,temp_space2
then dbaccess
set pdqpriority 100;
select current from systables where tabname = systables;
create index......
select current from systables where tabname = systables;
create index....
select current from systables where tabname = systables;
....
etc.
do post a onstat -D.
also a onstat -g mgm may help; dono we can then rule out that pdq is
used or not....
you still may see an i/o problem which is hidden by sar... if kaio is
used you may not see
that it is waiting on i/o.
maybe topas can tell you a bit more.
Sorry that is all that comes to mind at the moment...
Superboer.