Hi

I've got some problems with my db server for two weeks. It is used as a report server, data are imported nightly from production server in form of packed dbschema and flat unl files thru ftp , simple script creates sctructure, load data, then create indexes, trggers, etc. update statistics, at the end drop old db replacing new one, it took about 6hours, now it take over 21!!!.
I've checked hardware and software and I haven't found any problems,
except one in online.log - "VPCLASS 'cpu' not enough physical procs for affinity" (qnty of cpu not changed!!!), so I've tried to tune onconfig, it succeeded to me to go down below 10 hours with all dbimport, but i have no idea what do next.

Pls, take a look fo my parametrs:
Server:
2xXeon 2,8GHz, 4GB RAM
Array 300GB (raid5)
1x80GB (ide) tmpdb
Linux fc7 2.6.22.4
Informix IDS 10.00.UC5E
onconfig (cut):
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /usr/informix/.data/chunkroot001
# Path for device containing root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 1024000 # Size of root dbspace (Kbytes)

# 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)

PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 2000 # Physical log file size (Kbytes)


TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default)
TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)

IFX_EXTEND_ROLE 1 # To control the usage of EXTEND role.
SERVERNUM 0 # Unique id corresponding to a OnLine instance
DBSERVERNAME pol2_inf # Name of default database server
DBSERVERALIASES pol2_inf # List of alternate dbservernames
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
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
NOAGE 0 # Process aging
AFF_SPROC 1 # Affinity start processor, zmiana by ML, bylo 2
AFF_NPROCS 2 # Affinity number of processors
# Shared Memory Parameters
LOCKS 500000 # Maximum number of locks
NUMAIOVPS 128 # Number of IO vps
PHYSBUFF 128 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)
CLEANERS 20 # Number of buffer cleaner processes
SHMBASE 0x44000000 # Shared memory base address
SHMVIRTSIZE 5000000 # initial virtual shared memory segment size
SHMADD 32768 # Size of new shared memory segments
EXTSHMADD 8192 # Size of new extension shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0
CKPTINTVL 600 # Check point interval (in sec)
TXTIMEOUT 0x258 # Transaction timeout (in sec)
STACKSIZE 64 # Stack size (Kbytes)

OFF_RECVRY_THREADS 10 # Default number of offline worker threads
ON_RECVRY_THREADS 10 # Default number of online worker threads

RA_PAGES # Number of pages to attempt to read ahead
RA_THRESHOLD # Number of pages left before next group

DBSPACETEMP # Default temp dbspaces
# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES # Maximum number of decision support queries
DS_TOTAL_MEMORY 4000000 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DS_NONPDQ_QUERY_MEM 512 # Non PDQ query memory (Kbytes)
DATASKIP off # List of dbspaces to skip

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/informix/etc/evidence.sh # System Alarm program path

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 51200 # Per pool per index (Kbytes)

#Timeout for clien/t connection request
LISTEN_TIMEOUT 10 # Timeout (in Seconds)


IFX_FOLDVIEW 0 # fold multiple tables or union all view with ansi joins
BUFFERPOOL size=2K,buffers=200000,lrus=8,lru_min_dirty=70.000 000,lru_max_dirty=80.000000

onstat -p
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cache d
12711604 39904436 372481463 96.74 7832662 7939186 147913736 94.71

isamtot open start read write rewrite delete com mit rollbk
324966596 153923 5543381 21203023 122476519 11697 599 527 726 0

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 104 40602.52 5203.85 86 213

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seq scans
3224305 0 186754776 0 0 62 3219 946 6

ixda-RA idx-RA da-RA RA-pgsused lchwaits
211766 2312 3979518 4133847 257709

onstat -F
Fg Writes LRU Writes Chunk Writes
104 1056232 6771524

address flusher state data
60d79544 0 I 0 = 0X0
60d79a70 1 I 0 = 0X0
60d79f9c 2 I 0 = 0X0
60d7a4c8 3 I 0 = 0X0
60d7a9f4 4 I 0 = 0X0
60d7af20 5 I 0 = 0X0
60d7b44c 6 I 0 = 0X0
60d7b978 7 I 0 = 0X0
60d7bea4 8 I 0 = 0X0
60d7c3d0 9 I 0 = 0X0
60d7c8fc 10 I 0 = 0X0
60d7ce28 11 I 0 = 0X0
60d7d354 12 I 0 = 0X0
60d7d880 13 I 0 = 0X0
60d7ddac 14 I 0 = 0X0
60d7e2d8 15 I 0 = 0X0
60d7e804 16 I 0 = 0X0
60d7ed30 17 I 0 = 0X0
60d7f25c 18 I 0 = 0X0
60d7f788 19 I 0 = 0X0
states: Exit Idle Chunk Lru


onstat -m
Message Log File: /usr/informix/online.log
15:20:42 Checkpoint Completed: duration was 0 seconds.
15:20:42 Checkpoint loguniq 55716, logpos 0x23d018, timestamp: 0xb5ab8610

15:20:42 Maximum server connections 20
15:30:42 Checkpoint Completed: duration was 0 seconds.
15:30:42 Checkpoint loguniq 55716, logpos 0x241018, timestamp: 0xb5ab87b4

15:30:42 Maximum server connections 20
15:40:42 Checkpoint Completed: duration was 0 seconds.
15:40:42 Checkpoint loguniq 55716, logpos 0x27b018, timestamp: 0xb5ab9d2b

15:40:42 Maximum server connections 20
15:50:42 Fuzzy Checkpoint Completed: duration was 0 seconds, 3 buffers not flushed.
15:50:42 Checkpoint loguniq 55716, logpos 0x27f070, timestamp: 0xb5aba553

15:50:42 Maximum server connections 20
16:00:42 Fuzzy Checkpoint Completed: duration was 0 seconds, 3 buffers not flushed.
16:00:42 Checkpoint loguniq 55716, logpos 0x283070, timestamp: 0xb5aba7ba

16:00:42 Maximum server connections 20


I worry about FG writes >0 (104) and flushed, i noticed that the create index and update statistics take 70% of whole dbimport.

Regards
Mikah

Please:
- describe what caused this behavior (from 6 to 21 hours)
- post "onstat -d" output
- post "onstat -l" output
- do not use fuzzy checkpoints
- do not use RAID5 (see www.baarf.com and read carefully)
- if you have 64bit fedora, use 64bit informix
- if you can, use v11.50 (64 bit) - you can set it to do autotuning
- in v11 there is recommendation for much bigger physical log (your is only 2000kB and you can make it much bigger - e.g. 2000000kB)
- your SHMVIRTSIZE is to big for your physical memory (you configured 5000000 kB = 4.76GIG which does not fit in your physical memory)
- if you can, use 2 different onconfig's: first when importing data and second when creating indexes and when working as warehouse. In first case, put almost all physical memory in buffers and lower SHMVIRTSIZE (i.e.
for DW use much lower buffers and much bigger SHM i.e.
- if you have IDS Enterprise Edition you can use PDQ settings to improve DW queries
- use multiple TEMP dbspaces (even if they are on the same physical device)
- if you plan to use processor affinity, look in machine notes if it supported. If it is supported, you can't use all physical processors for IDS CPU vp's - then nothing left for other things (e.g. OS ) - I suggest not to use affinity, but use 4 CPU vp's (for start)

And, post this question to www.iiug.org forum - there are plenty of experienced Informix gurus. I'm sure you'll find right solution there
Also, visit iiug conference next year
HTH