Re: Online 5.03 real slow performance
In article <5j8qq8$m5h@cssun.mathcs.emory.edu>, Thuc Nguyen
<thucn@email.utcourts.gov> writes
>
>Hi everyone,
>
>I have spent a few days looking at the REAL slow performance problems
>and have tried LRU_MAX-DIRTY/LRU_MIN_DIRTY,LTXHWM/LTXEHWM,
>and BUFFERS adjustment. Nothing worked. The users complained that it
>took them 30min to 1 hour to bring up a record, that should be less than a
>few seconds, especially in the waiting line. I have included the tbconfig
>and kernal parameters info to see if anyone there can correct it. Is there
>something else I am missing. I have also reviewed the index issue.
>
Yes
1. Run UPDATE STATISTICS !!!!
2. Get the user to run the application then do onstat -u on the
server repeatedly. Watch to see if nreads goes up a lot. If so
they are clearly reading > 1 record. This probably means that an
inded is not missing/not being used.
3. Get the sql the user is running - the software providing the
database connection (ODBC/Native Driver) should provide a way to
get the sql being executed.
4. Run dbaccess -> Query Language then run
set explain on
next run the SQL from step 4 and then look at the sexplain.out
file produced. See what is happening.
5. RUn netstat -i and check you are not getting > 2-3% errors on the
network (at most). There should be column Ierrs (Input errors)/
Oerrs (Output errors).
6. Run vmstat 3 3 and check no paging is happening.
7. Run sar -d 3 3 and check what %busy is on each disk
8 Run sar -u 3 3 and check %idle is >10%.
>Online 5.03
>
># Root Dbspace Configuration
>
>ROOTNAME rootdbs>ROOTPATH /dev/ifmx/coris1
>ROOTOFFSET 1000
>ROOTSIZE 50000>
># Disk Mirroring Configuration
>
>MIRROR 0
>MIRRORPATH
>MIRROROFFSET 0>
># Physical Log Configuration
>
>PHYSDBS rootdbs
>PHYSFILE 4000>
># Logical Log Configuration
>
>LOGFILES 10
>LOGSIZE 2000>
># Message Files
>
>MSGPATH /usr/informix/onlog.coris
>CONSOLE /dev/null>
># Archive Tape Device
>
>TAPEDEV /dev/rmt/1m
>TAPEBLK 1024
>TAPESIZE 2000000>
># Logical Log Backup Tape Device
>
>LTAPEDEV /dev/rmt/0m
>LTAPEBLK 1024
>LTAPESIZE 2000000>
># Identification Parameters
>
>SERVRNUM 3
>DBSERVERNAME vernl_coris>
># Shared Memory Parameters
>
>RESIDENT 1>USERS 50
>TRANSACTIONS 100
>LOCKS 90000
>BUFFERS 10000>TBLSPACES 240
>CHUNKS 8
>DBSPACES 8
>PHYSBUFF 32
>LOGBUFF 32>LOGSMAX 20
>CLEANERS 4
>SHMBASE 0x0>CHPTINTVL 300
>LRUS 4
>LRU_MAX_DIRTY 40
>LRU_MIN_DIRTY 20
>LTXHWM 60
>LTXEHWM 75>
># Machine - and Product Specific Parameters
>
>DYNSHMSZ 0
>GTRID_CMP_SZ 32
>DEADLOCK_TIMEOUT 60
>TXTIMEOUT 300>SPINCNT 0
>STAGEBLOB
>
># System Page Size
>
>BUFFSIZE 2048
>
>HP-UX O/S 9.04u model# 817 with 67 meg, 3 gig of disk capacity
>reserved for database, LD-tape, HD-tape, 1 controller, and 1 CPU.
>
>Kernel Configurable Parameters
>
>DELAYCLOSETIMER 500
>NSTREVENT 50
>NSTRPUSH 16
>NUM_DKHS 0
>STRCTLSZ 1024
>STRMSGSZ 4096
>acctresume 4
>acctsuspend 2
>bootspinlocks 64
>bufpages 0
>dst 1
>eqmemsize 10
>file_pad 10
>fs_async 0
>iomemsize 40960
>ipx_max_sockets 100
>maxdsiz 67108864
>maxfiles 60
>maxfiles_lim 1024
>maxssiz 8388608
>maxswapchunks 256
>maxtsiz 67108864
>maxuprc 100
>maxusers 32
>maxvgs 10
>minswapchunks 1
>msgmap 258
>msgmax 32768
>msgmnb 32768
>msgmni 50
>msgseg 7168
>msgssz 32
>msgtql 256
>nbuf 0
>ncallout 1064
>ncp_max_clients 250
>ncp_max_engines 50
>netisr_priority -1
>netmemmax 0
>nfile 670
>nflocks 200
>ninode 1114
>nni 2
>nproc 1000
>npty 60
>nstlbe 1024
>nswapdev 10
>nswapfs 10
>nvt_connections 60
>pfail_enabled 1
>public_shlibs 1
>reboot_option 1
>semaem 16384
>semmap 10
>semmni 64
>semmns 128
>semmnu 30
>semume 10
>semvmx 32767
>shmmax 67108864
>shmmni 100
>shmseg 12
>spx_max_connections 30
>spx_max_sockets 100
>swapmen_on 1
>swchunk 2048
>timeslice