Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Red face Unanswered: Poor performance

    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

  2. #2
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    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.
    Code:
    BUFFERPOOL	size=2K,buffers=1750000,lrus=8,lru_min_dirty=70.000 000,lru_max_dirty=80.000000
    SHMVIRTSIZE 128000
    for DW use much lower buffers and much bigger SHM i.e.
    Code:
    BUFFERPOOL	size=2K,buffers=100000,lrus=8,lru_min_dirty=70.000 000,lru_max_dirty=80.000000
    SHMVIRTSIZE 3500000
    - 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •