RE: UNIX-Box slow down while execute stored procedure
Start with ...
When you run the procedure from dbaccess on the server how long does it
take?
Do you have the explain plan for the sqls in the procedure? (set explain on;
update statistics for your_procname;)
MW
> -----Original Message-----
> From: owner-informix-list@iiug.org
> [mailto:owner-informix-list@iiug.org]On Behalf Of Kai
> Sent: Tuesday, 21 October 2003 11:15 a.m.
> To: informix-list@iiug.org
> Subject: UNIX-Box slow down while execute stored procedure
>
>
> Hi,
>
> Sorry for my "special english".
>
> We are on a DGUX-Box with IDS 7.31 ( 4 P3/700 / 2 GB Mem )
>
> We have a problem with a stored procedure (ca. 1500 lines) for
> reportings.
> The short version of procedure:
>
> CREATE TEMP TABLE xxx ... WITH NO LOG FRAGMENT BY ROUND ROBIN;> (testet with 2 and 4 tempdbs on different dics)
> INSERT INTO xxx .... SELECT FROM second DATABASE via SYNONYMS; (ca 5
> 000 rows per month)> ca. 20x UPDATE xxx SELECT FROM second DATABASE via SYNONYMS....
> FOREACH SELECT RETURN ...
>
> The procedure runs, when a user starts an Report (via Crystal Reports
> over "onsoctcp").
>
> Informix is configured for an OLTP - Environment (onconfig below)
> there are up to 100 (OLTP)Users. If the Procedure runs for up to 8
> month of data (up to 40 000 rows) there is no problem (5-8 min).
> when report starts e.g. for 9 Month AND when it is the "normal
> worktime" the unix-System "freeze" up to an hour. If then the
> procedure returns the results to Crytal Reports , Unix is comming up
> to normal reaction. If the procedure runs in evening time (up to 30
> OLTP Users) the procedure produce syscalls, disk i/o esp. on the
> tempdbs-Disks but there is no problem with the OLTP-Users and
> unix-Sessions.
>
> So it is hard to find out what the problem is ...
> The Unix-System is at the "problem time" too slow to start everything
> like onstats, nsar etc.
>
> We playing with PDQ:
> MAX_PDQPRIORITY 100, 50, 1; SET PDQPRIORITY 20, 3, 0 (in the
> procedure)
> - nothings helps.
>
> All ideas are more than welcome.
>
> Thanks in advance for your help on
> the subject.
>
> Kai
>
> onconfig:
>
> Configuration File: /usr/local/informix/etc/onconfig
> # Root Dbspace Configuration
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH /usr/opt/informix/dbspaces/dbs_root #ntaining root> dbspace
> ROOTOFFSET 100 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 245000 # 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)> # Physical Log Configuration
> PHYSDBS logdbs # Location (dbspace) of physical log
> PHYSFILE 20000 # Physical log file size (Kbytes)> # Logical Log Configuration
> LOGFILES 64 # Number of logical log files
> LOGSIZE 8000 # Logical log size (Kbytes)> # Diagnostics
> MSGPATH /usr/local/informix/online.log # System message log
> file path
> CONSOLE /dev/console # System console message path> ALARMPROGRAM /usr/opt/informix/lfb/alarm # Alarm program path
> SYSALARMPROGRAM /usr/local/informix/etc/evidence.sh # System Alarm
> program path
> TBLSPACE_STATS 1> # System Archive Tape Device
> ###TAPEDEV /dev/null
> TAPEDEV /usr/opt/informix/dbspaces/tape_arc
> TAPEBLK 256 # Tape block size (Kbytes)
> TAPESIZE 20000000 # Maximum amount of data to put on
> tape (Kbytes)> # Log Archive Tape Device
> LTAPEDEV /dev/null # Log tape device path
> LTAPEBLK 256 # Log tape block size (Kbytes)
> LTAPESIZE 20000000 # Max amount of data to put on log
> tape (Kbytes)
> # Optical> STAGEBLOB # Informix Dynamic Server/Optical
> staging area
> # System Configuration
> SERVERNUM 0 # Unique id
> DBSERVERNAME xxxxx # Name of default database server
> DBSERVERALIASES xxxxxx # List of alternate dbservernames
> NETTYPE ipcshm,3,199,CPU # Configure poll thread(s) for> nettype
> NETTYPE tlitcp,3,199,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
> NUMCPUVPS 3 # 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 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors> # Shared Memory Parameters
> LOCKS 2560000 # Maximum number of locks
> BUFFERS 350000 # Maximum number of shared buffers
> NUMAIOVPS 6 # Number of IO vps
> PHYSBUFF 512 # Physical log buffer size (Kbytes)
> LOGBUFF 512 # Logical log buffer size (Kbytes)> LOGSMAX 64 # Maximum number of logical log files
> CLEANERS 127 # Number of buffer cleaner processes
> SHMBASE 0x20000000 # Shared memory base address
> SHMVIRTSIZE 131072 # initial virtual shared memory> segment size
> SHMADD 24576 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 1800 # Check point interval (in sec)
> LRUS 127 # Number of LRU queues
> LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning> limit
> LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)> # System Page Size
> # BUFFSIZE - Dynamic Server no longer supports this configuration
> parameter.
> # To determine the page size used by Dynamic Server 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