dbimport EXTREMELY slow
I am doing dbimport with IDS 10UC5 on AIX 5.3.
Watched,
large number(100,000+) of dirty pages were SLOWLY written to disk.
piece of online log,
20:08:47 Maximum server connections 0
20:08:47 On-Line Mode
20:15:15 Fuzzy Checkpoint Completed: duration was 56 seconds, 1
buffers not flushed.
20:15:15 Checkpoint loguniq 12, logpos 0xcb7050, timestamp: 0x54d481b
20:15:15 Maximum server connections 1
21:00:24 Fuzzy Checkpoint Completed: duration was 2409 seconds, 7
buffers not flushed.
21:00:24 Checkpoint loguniq 12, logpos 0xccb0b0, timestamp: 0x5eba28f
21:00:24 Maximum server connections 1
21:01:54 Checkpoint Completed: duration was 61 seconds.
21:01:54 Checkpoint loguniq 12, logpos 0xd21018, timestamp: 0x601652a
21:01:54 Maximum server connections 1
21:01:56 IBM Informix Dynamic Server Stopped.
See the above LONG check point!
config file attached.
Any suggestions are deeply appreciated!
Thanks
Quman
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /usr/informix/dev-links/int1/DBROOT_INT1 # Path fordevice containing root dbspace
ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 512000 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH /usr/informix/dev-links/int1/MDBROOT_INT1 # Path
for device containing mirrored root
MIRROROFFSET 4 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS dbphylog # Location (dbspace) of physical log
PHYSFILE 890000 # Physical log file size (Kbytes)#PHYSDBS rootdbs # Location (dbspace) of physical log
#PHYSFILE 20000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 140 # Number of logical log files#LOGSIZE 20000 # Logical log size (Kbytes)
LOGSIZE 160000 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT)
# Tablespace Tablespace Configuration in Root Dbspace
TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default)
TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)
# Security
# DBCREATE_PERMISSION:
# By default any user can create a database. Uncomment DBCREATE_PERMISSON to
# limit database creation to a specific user. Add a new DBCREATE_PERMISSION
# line for each permitted user.
DBCREATE_PERMISSION informix
# DB_LIBRARY_PATH:
# When loading a (C or C++) shared object (for a UDR or UDT), IDS checks that
# the user-specified path starts with one of the directory prefixes listed in
# the comma-separated list of prefixes in DB_LIBRARY_PATH. The string
# "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order for
# extensibility and IBM supplied blades to work correctly.
DB_LIBRARY_PATH $INFORMIXDIR/extend
# IFX_EXTEND_ROLE:
# 0 (or off) => Disable use of EXTEND role to control who can register
# external routines.
# 1 (or on) => Enable use of EXTEND role to control who can register
# external routines. This is the default behaviour.
#
IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.
# Diagnostics
MSGPATH /dbbackup/online_log/online_int1.log # System message
log file path
CONSOLE /dbbackup/online_log/console # System console message path
# To automatically backup logical logs, edit alarmprogram.sh and set
# BACKUPLOGS=Y
ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path
ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur
TBLSPACE_STATS 1 # Maintain tblspace statistics
# System Archive Tape Device
TAPEDEV /dbbackup/ontape/ontape_int1 # Tape device path
TAPEBLK 32 # Tape block size (Kbytes)#TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes)
TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path#LTAPEDEV /dbbackup/logbackup/logbackup_int1 # Log tape device path
LTAPEBLK 32 # Log tape block size (Kbytes)#LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)
LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server staging area
# System Configuration
SERVERNUM 10 # Unique id corresponding to a OnLine instance
DBSERVERNAME osdint1_tcp # Name of default database server
DBSERVERALIASES osdint1 # List of alternate dbservernames#NETTYPE # Configure poll thread(s) for nettype
NETTYPE ipcshm,1,50,CPU # Configure poll thread(s) for nettype
NETTYPE soctcp,2,100,NET # Configure poll thread(s) for nettype
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
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 32 # Number of buffer cleaner processes
SHMBASE 0x40000000L # Shared memory base address
SHMVIRTSIZE 307200 # initial virtual shared memory segment size
SHMADD 32768 # Size of new shared memory segments (Kbytes)
EXTSHMADD 8192 # Size of new extension shared memory
segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
TXTIMEOUT 300 # Transaction timeout (in sec)
STACKSIZE 64
# Dynamic Logging
# DYNAMIC_LOGS:
# 2 : server automatically add a new logical log when necessary. (ON)
# 1 : notify DBA to add new logical logs when necessary. (ON)
# 0 : cannot add logical log on the fly. (OFF)
#
# When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM,
# because the server can add new logical logs during long transaction rollback.
# However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM
# can be set to smaller values.
#
# If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values
# to avoid long transaction rollback hanging the server due to lack of logical
# log space, i.e. 50/60 or lower.
#
# In case of system configured with CDR, the difference between LTXHWM and
# LTXEHWM should be atleast 30% so that we could minimize log overrun issue.
DYNAMIC_LOGS 2
LTXHWM 30
LTXEHWM 60
# 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
Change your lru_min/max_dirty parameters from 20/40 to 1/5, also increase
CLEANERS to 64 (128 if you actually have 4K page dbspaces in addition to 2kpage
dbspaces.
Art
----- Original Message -----
From: Quman <ids@iiug.org>
At: 9/13 17:09:58
I am doing dbimport with IDS 10UC5 on AIX 5.3.
Watched,
large number(100,000+) of dirty pages were SLOWLY written to disk.
piece of online log,
20:08:47 Maximum server connections 0
20:08:47 On-Line Mode
20:15:15 Fuzzy Checkpoint Completed: duration was 56 seconds, 1
buffers not flushed.
20:15:15 Checkpoint loguniq 12, logpos 0xcb7050, timestamp: 0x54d481b
20:15:15 Maximum server connections 1
21:00:24 Fuzzy Checkpoint Completed: duration was 2409 seconds, 7
buffers not flushed.
21:00:24 Checkpoint loguniq 12, logpos 0xccb0b0, timestamp: 0x5eba28f
21:00:24 Maximum server connections 1
21:01:54 Checkpoint Completed: duration was 61 seconds.
21:01:54 Checkpoint loguniq 12, logpos 0xd21018, timestamp: 0x601652a
21:01:54 Maximum server connections 1
21:01:56 IBM Informix Dynamic Server Stopped.
See the above LONG check point!
config file attached.
Any suggestions are deeply appreciated!
Thanks
Quman
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /usr/informix/dev-links/int1/DBROOT_INT1 # Path fordevice containing root dbspace
ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 512000 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH /usr/informix/dev-links/int1/MDBROOT_INT1 # Path
for device containing mirrored root
MIRROROFFSET 4 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS dbphylog # Location (dbspace) of physical log
PHYSFILE 890000 # Physical log file size (Kbytes)#PHYSDBS rootdbs # Location (dbspace) of physical log
#PHYSFILE 20000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 140 # Number of logical log files#LOGSIZE 20000 # Logical log size (Kbytes)
LOGSIZE 160000 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT)
# Tablespace Tablespace Configuration in Root Dbspace
TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default)
TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)
# Security
# DBCREATE_PERMISSION:
# By default any user can create a database. Uncomment DBCREATE_PERMISSON to
# limit database creation to a specific user. Add a new DBCREATE_PERMISSION
# line for each permitted user.
DBCREATE_PERMISSION informix
# DB_LIBRARY_PATH:
# When loading a (C or C++) shared object (for a UDR or UDT), IDS checks that
# the user-specified path starts with one of the directory prefixes listed in
# the comma-separated list of prefixes in DB_LIBRARY_PATH. The string
# "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order for
# extensibility and IBM supplied blades to work correctly.
DB_LIBRARY_PATH $INFORMIXDIR/extend
# IFX_EXTEND_ROLE:
# 0 (or off) => Disable use of EXTEND role to control who can register
# external routines.
# 1 (or on) => Enable use of EXTEND role to control who can register
# external routines. This is the default behaviour.
#
IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.
# Diagnostics
MSGPATH /dbbackup/online_log/online_int1.log # System message
log file path
CONSOLE /dbbackup/online_log/console # System console message path
# To automatically backup logical logs, edit alarmprogram.sh and set
# BACKUPLOGS=Y
ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path
ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur
TBLSPACE_STATS 1 # Maintain tblspace statistics
# System Archive Tape Device
TAPEDEV /dbbackup/ontape/ontape_int1 # Tape device path
TAPEBLK 32 # Tape block size (Kbytes)#TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes)
TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path#LTAPEDEV /dbbackup/logbackup/logbackup_int1 # Log tape device path
LTAPEBLK 32 # Log tape block size (Kbytes)#LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)
LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server staging area
# System Configuration
SERVERNUM 10 # Unique id corresponding to a OnLine instance
DBSERVERNAME osdint1_tcp # Name of default database server
DBSERVERALIASES osdint1 # List of alternate dbservernames#NETTYPE # Configure poll thread(s) for nettype
NETTYPE ipcshm,1,50,CPU # Configure poll thread(s) for nettype
NETTYPE soctcp,2,100,NET # Configure poll thread(s) for nettype
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
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 32 # Number of buffer cleaner processes
SHMBASE 0x40000000L # Shared memory base address
SHMVIRTSIZE 307200 # initial virtual shared memory segment size
SHMADD 32768 # Size of new shared memory segments (Kbytes)
EXTSHMADD 8192 # Size of new extension shared memory
segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
TXTIMEOUT 300 # Transaction timeout (in sec)
STACKSIZE 64
# Dynamic Logging
# DYNAMIC_LOGS:
# 2 : server automatically add a new logical log when necessary. (ON)
# 1 : notify DBA to add new logical logs when necessary. (ON)
# 0 : cannot add logical log on the fly. (OFF)
#
# When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM,
# because the server can add new logical logs during long transaction
rollback.
# However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM
# can be set to smaller values.
#
# If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values
# to avoid long transaction rollback hanging the server due to lack of logical
# log space, i.e. 50/60 or lower.
#
# In case of system configured with CDR, the difference between LTXHWM and
# LTXEHWM should be atleast 30% so that we could minimize log overrun issue.
DYNAMIC_LOGS 2
LTXHWM 30
LTXEHWM 60
# 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_R
Quman said:
>
> I am doing dbimport with IDS 10UC5 on AIX 5.3.
>
> Watched,
>
> large number(100,000+) of dirty pages were SLOWLY written to disk.
>
> piece of online log,
>
> 20:08:47 Maximum server connections 0
> 20:08:47 On-Line Mode
> 20:15:15 Fuzzy Checkpoint Completed: duration was 56 seconds, 1
> buffers not flushed.
> 20:15:15 Checkpoint loguniq 12, logpos 0xcb7050, timestamp: 0x54d481b
>
> 20:15:15 Maximum server connections 1
> 21:00:24 Fuzzy Checkpoint Completed: duration was 2409 seconds, 7
> buffers not flushed.
> 21:00:24 Checkpoint loguniq 12, logpos 0xccb0b0, timestamp: 0x5eba28f
>
> 21:00:24 Maximum server connections 1
> 21:01:54 Checkpoint Completed: duration was 61 seconds.
> 21:01:54 Checkpoint loguniq 12, logpos 0xd21018, timestamp: 0x601652a
>
> 21:01:54 Maximum server connections 1
> 21:01:56 IBM Informix Dynamic Server Stopped.
>
> See the above LONG check point!
>
> config file attached.
>
> Any suggestions are deeply appreciated!
Run the dbimport with logging disabled.
Given the large number of pages you are flushing, why do you think the
checkpoint is long?
> Thanks
> Quman
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH /usr/informix/dev-links/int1/DBROOT_INT1 # Path for> device containing root dbspace
> ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 512000 # Size of root dbspace (Kbytes)>
> # Disk Mirroring Configuration Parameters
>
> MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH /usr/informix/dev-links/int1/MDBROOT_INT1 # Path
> for device containing mirrored root
> MIRROROFFSET 4 # Offset into mirrored device (Kbytes)>
> # Physical Log Configuration
>
> PHYSDBS dbphylog # Location (dbspace) of physical log
> PHYSFILE 890000 # Physical log file size (Kbytes)> #PHYSDBS rootdbs # Location (dbspace) of physical log
> #PHYSFILE 20000 # Physical log file size (Kbytes)
>
> # Logical Log Configuration
>
> LOGFILES 140 # Number of logical log files> #LOGSIZE 20000 # Logical log size (Kbytes)
> LOGSIZE 160000 # Logical log size (Kbytes)> LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT)
>
> # Tablespace Tablespace Configuration in Root Dbspace
>
> TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default)
> TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)>
> # Security
> # DBCREATE_PERMISSION:
> # By default any user can create a database. Uncomment DBCREATE_PERMISSON
> to
> # limit database creation to a specific user. Add a new
> DBCREATE_PERMISSION
> # line for each permitted user.
>
> DBCREATE_PERMISSION informix
>
> # DB_LIBRARY_PATH:
> # When loading a (C or C++) shared object (for a UDR or UDT), IDS checks
> that
> # the user-specified path starts with one of the directory prefixes listed
> in
> # the comma-separated list of prefixes in DB_LIBRARY_PATH. The string
> # "$INFORMIXDIR/extend" must be included in DB_LIBRARY_PATH in order for
> # extensibility and IBM supplied blades to work correctly.
>
> DB_LIBRARY_PATH $INFORMIXDIR/extend
>
> # IFX_EXTEND_ROLE:
> # 0 (or off) => Disable use of EXTEND role to control who can register
> # external routines.
> # 1 (or on) => Enable use of EXTEND role to control who can register
> # external routines. This is the default behaviour.
> #
> IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.>
> # Diagnostics
>
> MSGPATH /dbbackup/online_log/online_int1.log # System message
> log file path
> CONSOLE /dbbackup/online_log/console # System console message path
>
> # To automatically backup logical logs, edit alarmprogram.sh and set
> # BACKUPLOGS=Y
> ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path
> ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur
> TBLSPACE_STATS 1 # Maintain tblspace statistics>
> # System Archive Tape Device
>
> TAPEDEV /dbbackup/ontape/ontape_int1 # Tape device path
> TAPEBLK 32 # Tape block size (Kbytes)> #TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes)
> TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/null # Log tape device path> #LTAPEDEV /dbbackup/logbackup/logbackup_int1 # Log tape device path
> LTAPEBLK 32 # Log tape block size (Kbytes)> #LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)
> LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server staging area
>
> # System Configuration
>
> SERVERNUM 10 # Unique id corresponding to a OnLine instance
> DBSERVERNAME osdint1_tcp # Name of default database server
> DBSERVERALIASES osdint1 # List of alternate dbservernames> #NETTYPE # Configure poll thread(s) for nettype
> NETTYPE ipcshm,1,50,CPU # Configure poll thread(s) for nettype
> NETTYPE soctcp,2,100,NET # Configure poll thread(s) for nettype
> 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
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one>
> # Shared Memory Parameters
>
> LOCKS 200000 # Maximum number of locks
> PHYSBUFF 32 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)
> CLEANERS 32 # Number of buffer cleaner processes
> SHMBASE 0x40000000L # Shared memory base address
> SHMVIRTSIZE 307200 # initial virtual shared memory segment size
> SHMADD 32768 # Size of new shared memory segments (Kbytes)
> EXTSHMADD 8192 # Size of new extension shared memory
> segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> TXTIMEOUT 300 # Transaction timeout (in sec)
> STACKSIZE 64>
> # Dynamic Logging
> # DYNAMIC_LOGS:
> # 2 : server automatically add a new logical log when necessary. (ON)
> # 1 : notify DBA to add new logical logs when necessary. (ON)
> # 0 : cannot add logical log on the fly. (OFF)
> #
> # When dynamic logging is on, we can have higher values for
> LTXHWM/LTXEHWM,
> # because the server can add new logical logs during long transaction
> rollback.
> # However, to limit the number of new logical logs being added,
> LTXHWM/LTXEHWM
> # can be set to smaller values.
> #
> # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller
> values
> # to avoid long transaction rollback hanging the server due to lack of
> logical
> # log space, i.e. 50/60 or lower.
> #
> # In case of system configured with CDR, the difference between LTXHWM and
> # LTXEHWM should be atleast 30% so that we could minimize log overrun
> issue.
>
> DYNAMIC_LOGS 2
> LTXHWM 30
> LTXEHWM 60>
> # 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_REC
Related threads
- onbar -c -F in Windows Informix instance
- Anyone... SQLCODE=-668, ISAM error=-1
- Not using the 100% logical log page size alloacted to informix