Re: ER Error Enterprise Replication

bozon wrote:
Enterprise Replication is giving the following message when I do a
simple cdr list server

ndl12:ndl12a common_reporting$ cdr list server
SERVER ID STATE STATUS QUEUE CONNECTION
CHANGED
-----------------------------------------------------------------------
cdr error: select bytesqueued from syscdrqueued
SQL code -1215 :Value exceeds limit of INTEGER precision

ISAM 0 :

I think it is because I have more than 2 gigs queued to send. I am
experimenting with it and tried to push all of my data to an empty
database but it seems that it is going very slowly. I have a 1 gig
ethernet connection and will have 10 gigs in production. I know I
don't have it tuned correctly because I know I don't know what I am
doing.

If you use 'cdr sync' to push all of the data, then you will be coordinating the 'push' with the allowable size of the queue, and thus avoid spooling to disk. You would also have the advantage of dynamically increasing the queue size for the duration of the sync.

Also - if you use 'cdr sync' to populate a new node, the data in the table is not logged to perform the prorogation. If you do dummy updates, then the logical log will be impacted.

Generally speaking, spooling (stable storage of the queue) is not a good thing as it means that additional work is done to 1) read the replicated transaction and 2) to remove the replicated transaction when it has been ACKed. You might consider having multiple sbspaces for the stable queue - some logged (for smaller transactions) and some unlogged (for larger transactions).



So any help figuring out how to tune, monitor or diagnose what is
going on would be appreciated. All comments welcome, since I don't
know that much about ER, I am sure any comment will be helpful, except
comments about me being an idiot ( I already know this so you are just
wasting your time. ;-) ) I will open a case with informix because this
is pretty crappy behavior that could be fixed easily enough.

Below onstat -c output that shows version and settings.

Thanks for any help

ndl12:ndl12a common_reporting$ onstat -c

IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 1
days 22:49:03 -- 4004260 Kbytes

Configuration File: /usr/local/informix/etc/onconfig.ndl12a
#
# onconfig file for instance ndl12a
# servernums in stage should be 200+ a=1, b=2, c=3, ..
#

# instance type: EN

SERVERNUM 201 # Unique id corresponding to a OnLine instance
DBSERVERNAME ndl12a # Name of default database server

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
# ROOTPATH /usr/local/informix_dev/eease_en/root_001
# ROOTPATH /usr/local/informix_dev/ndl12a/root_001
ROOTPATH /usr/local/informix_dev/ndl12a/root_001
# Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
# -c3 using 80,000 for EN, should do same for SED, which is currently
200,000
# (or vice-versa).
ROOTSIZE 800000 # Size of root dbspace (Kbytes)0

# Disk Mirroring Configuration Parameters

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored
root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)

# Physical Log Configuration

# -c3 for install PHYSDBS physdbs # Location (dbspace)
of physical log
# -c3 for install PHYSFILE 2097046 # Physical log file
size (Kbytes)
PHYSDBS physdbs # Location (dbspace) of physical log
# -d3 take value from prod
# PHYSFILE 2097046 # Physical log file size (Kbytes)
PHYSFILE 599894 # Physical log file size (Kbytes)

# Logical Log Configuration

# -c3 for install LOGFILES 82 # Number of logical
log files
# -c3 for install LOGSIZE 2000 # Logical log size (Kbytes)
LOGFILES 64 # Number of logical log files
LOGSIZE 4000 # Logical log size (Kbytes)
LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT)

# Tablespace Tablespace Configuration in Root Dbspace

TBLTBLFIRST 1600 # First extent size (Kbytes) (0 = default)
TBLTBLNEXT 800 # 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 /usr/local/eease/log/online_ndl12a.log # System message log
file path
# -c3 try this out
CONSOLE /usr/local/eease/log/online_ndl12a.console # System console
message path
# CONSOLE /dev/null

# To automatically backup logical logs, edit alarmprogram.sh and set
# BACKUPLOGS=Y
ALARMPROGRAM /usr/local/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 /dev/null # Tape device path
TAPEBLK 5120 # Tape block size (Kbytes)
TAPESIZE 0 # Maximum amount of data to put on tape (Kbytes)

# Log Archive Tape Device

LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 5120 # Log tape block size (Kbytes)
LTAPESIZE 0 # Max amount of data to put on log tape (Kbytes)

# Optical

STAGEBLOB # Informix Dynamic Server staging
area

# System Configuration

DBSERVERALIASES # List of alternate dbservernames
NETTYPE soctcp,15,100,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-
processor
#v9#NUMCPUVPS 1 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps
to one

#v9#NOAGE 0 # Process aging
#v9#AFF_SPROC 0 # Affinity start processor
#v9#AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

# 2008.6.18 -c3 for rkumbhar
# LOCKS 2000000 # Maximum number of locks
LOCKS 4000000 # Maximum number of locks
PHYSBUFF 512 # Physical log buffer size (Kbytes)
LOGBUFF 512 # Logical log buffer size (Kbytes)
CLEANERS 128 # Number of buffer cleaner processes
SHMBASE 0x44000000L # Shared memory base address
SHMVIRTSIZE 1500000 # initial virtual shared memory segment
size
SHMADD 100000 # Size of new shared memory segments (Kbytes)
EXTSHMADD 100000 # Size of new extension shared memory segments
(Kbytes)
SHMTOTAL 6000000 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
TXTIMEOUT 300 # Transaction timeout (in sec)
STACKSIZE 256 # Stack size (Kbytes)

# 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 1
# -c3 XXX
# LTXHWM 40
# LTXEHWM 50
LTXHWM 60
LTXEHWM 75

# 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 20 # Default number of offline worker threads
ON_RECVRY_THREADS 2 # 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/local/informix_arch/recovery/ndl12a/dr.lostfound # DR
lost+found file path
DRIDXAUTO 0 # DR automatic index repair. 0=off, 1=on

# CDR Variables
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 32768 # Maximum amount of memory for any CDR
queue (Kbytes)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)
CDR_SERIAL 0 # Serial Column Sequence

CDR_DBSPACE cdr_cat_dbspace # dbspace for syscdr database
CDR_QHDR_DBSPACE cdr_qhdr_dbspace # CDR queue dbspace (default same
as catalog)
CDR_QDATA_SBSPACE cdr_qdata_sbspace # List of CDR queue smart blob
spaces

# CDR_MAX_DYNAMIC_LOGS
# -1 => unlimited
# 0 => disable dynamic log addition
# >0 => limit the no. of dynamic log additions with the specified
value.
# Max dynamic log requests that CDR can make within one server
session.

CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default

# Backup/Restore variables
BAR_ACT_LOG /usr/informix/bar_act.log # ON-Bar Log file - not in /
tmp please
BAR_DEBUG_LOG /usr/informix/bar_dbug.log # ON-Bar Debug Log - not in /
tmp please
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 20
BAR_XFER_BUF_SIZE 31
RESTARTABLE_RESTORE ON
BAR_PROGRESS_FREQ 0

# Informix Storage Manager variables
ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs

# Read Ahead Variables
#RA_PAGES # Number of pages to attempt to read ahead
#RA_THRESHOLD # Number of pages left before next group
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 24 # 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 tempdbs001:tempdbs002:tempdbs003:tempdbs004
# 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 /usr/local/informix_arch/dumpdir/ndl12a # Preserve
diagnostics in this directory
DUMPSHMEM 0 # 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 OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES 128 # Maximum number of decision support
queries
DS_TOTAL_MEMORY 200000 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DS_NONPDQ_QUERY_MEM 50000 # Non PDQ query memory (Kbytes)
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

DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0)

ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT,
2 = WAIT
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

SBSPACENAME # Default smartblob space name - this is where
blobs
# go if no sbspace is specified when the smartblob is
# created. It is also used by some datablades as
# the location to put their smartblobs.
SYSSBSPACENAME # Default smartblob space for use by the
Informix
# Server. This is used primarily for Informix Server
# system statistics collection.

BLOCKTIMEOUT 3600 # Default timeout for system block
SYSALARMPROGRAM /usr/local/informix/etc/evidence.sh # System
Alarm program path

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1

ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or
anything but 1)


#Create Index Online Shared Memory usage limitation
ONLIDX_MAXMEM 25600 # Per pool per index (Kbytes)

#Timeout for client connection request
LISTEN_TIMEOUT 10 # Timeout (in Seconds)

BUFFERPOOL
size=2K,buffers=750000,lrus=30,lru_min_dirty=50.000000,lru_max_dirty=60.000000

# -c3 removed certain noage.
VPCLASS cpu,num=4,noage
VPCLASS aio,num=75
VPCLASS soc,num=15

STMT_CACHE 2 # on all sessions
STMT_CACHE_SIZE 5120 # 5 MB
STMT_CACHE_HITS 2 # 2 stmt uses b/f cached
STMT_CACHE_NOLIMIT 1 # allow inserts past cache size
STMT_CACHE_NUMPOOL 20 # 20 pools

BTSCANNER num=2,prio=high,rangesize=120000,threshold=50000
PLOG_OVERFLOW_PATH
SBSPACETEMP
MAX_INCOMPLETE_CONNECTIONS 1024
FASTPOLL 1
VP_MEMORY_CACHE_KB 0
AUTO_AIOVPS 1
DIRECT_IO 0
DEF_TABLE_LOCKMODE page
SHMVIRT_ALLOCSEG 0.000000
SHMNOACCESS
AUTO_CKPTS 1
RTO_SERVER_RESTART 0
BAR_DEBUG 0
BAR_BSALIB_PATH
BACKUP_FILTER
RESTORE_FILTER
DD_HASHSIZE 31
DD_HASHMAX 10
DS_HASHSIZE 31
DS_POOLSIZE 127
PC_HASHSIZE 31
PC_POOLSIZE 127
USELASTCOMMITTED NONE
MAX_FILL_DATA_PAGES 0
EXT_DIRECTIVES 0
IFX_FOLDVIEW 0
AUTO_REPREPARE 1
EXPLAIN_STAT 1
SECURITY_LOCALCONNECTION 0
UNSECURE_ONSTAT 0
ADMIN_USER_MODE_WITH_DBSA 0
ADMIN_MODE_USERS
SSL_KEYSTORE_LABEL
PLCY_POOLSIZE 127
PLCY_HASHSIZE 31
USRC_POOLSIZE 127
USRC_HASHSIZE 31
ENCRYPT_HDR 0
ENCRYPT_SMX 0
ENCRYPT_CDR 0
ENCRYPT_CIPHERS
ENCRYPT_MAC medium
ENCRYPT_MACFILE builtin
ENCRYPT_SWITCH 0,0
CDR_SUPPRESS_ATSRISWARN
HA_ALIAS
LOG_INDEX_BUILDS 0
SDS_ENABLE 0
SDS_TIMEOUT 10
SDS_PAGING
REDIRECTED_WRITES 0
FAILOVER_CALLBACK
TEMPTAB_NOLOG 0
STORAGE_FULL_ALARM 600,3
RAS_PLOG_SPEED 31329
RAS_LLOG_SPEED 2570
EILSEQ_COMPAT_MODE 0
QSTATS 0
WSTATS 0
JVPJAVAHOME
JVPHOME
JVPPROPFILE .jvpprops
JVPJAVALIB
JVPJAVAVM libjava.so
JVPCLASSPATH
AUTO_LRU_TUNING 1
BAR_PERFORMANCE 0
UPDATABLE_SECONDARY 0
JVPLOGFILE jvp.log
.

Relevant Pages

  • Re: ONCONFIG and AIX W/Smitty
    ... Informix Dynamic Server Configuration Parameters ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ... This is the list of dbspaces ...
    (comp.databases.informix)
  • ONCONFIG and AIX
    ... Informix Dynamic Server Configuration Parameters ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ... This is the list of dbspaces ...
    (comp.databases.informix)
  • Re: onconfig recommendations
    ... Old Server: HP something 4 way with 4GB RAM. ... Your system seems very unbalanced with regard to disk I/O capacity in ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ... This is the list of dbspaces ...
    (comp.databases.informix)
  • ER Error Enterprise Replication
    ... ndl12:ndl12a common_reporting$ cdr list server ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ... This is the list of dbspaces ...
    (comp.databases.informix)
  • oninit -i failure
    ... I'm trying to startup informix for the first time on a CentOS 4.5 ... server. ... # Disk Mirroring Configuration Parameters ... # BUFFSIZE - OnLine no longer supports this configuration parameter. ...
    (comp.databases.informix)