On Informix 7.23, when deleting records on large tables it goes very
slow. What should we change to get better performance?

$ onstat -p

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 01:06:01 -- 130680
Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
44588 44727 15176613 99.71 3330 5595 10658 68.76

isamtot open start read write rewrite delete commit
rollbk
284423 731 4473 258214 753 2 2705 1295
0

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 726.41 2.63 4 12

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
1526 0 56023660 0 0 0 1 618

ixda-RA idx-RA da-RA RA-pgsused lchwaits
1424 9116 32210 42706 30

$ onstat -g glo

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 01:06:26 -- 130680
Kbytes

MT global info:
sessions threads vps lngspins
1 18 11 3

sched calls thread switches yield 0 yield n yield forever
total:7645421 47167 7600110 10098 12035
per sec:10 6 4 3 0

Virtual processor summary:
class vps usercpu syscpu total
cpu 2 747.94 1.90 749.84
aio 4 2.02 0.65 2.67
tli 1 0.00 0.00 0.00
lio 1 0.00 0.10 0.10
pio 1 0.80 0.01 0.81
adm 1 0.00 0.00 0.00
msc 1 0.15 0.00 0.15
total 11 750.91 2.66 753.57

Individual virtual processors:
vp pid class usercpu syscpu total
1 7183 cpu 12.12 1.49 13.61
2 7184 adm 0.00 0.00 0.00
3 7185 cpu 735.82 0.41 736.23
4 7186 lio 0.00 0.10 0.10
5 7187 pio 0.80 0.01 0.81
6 7188 aio 0.65 0.25 0.90
7 7189 msc 0.15 0.00 0.15
8 7190 aio 0.06 0.16 0.22
9 7191 aio 0.64 0.16 0.80
10 7192 aio 0.67 0.08 0.75
11 7193 tli 0.00 0.00 0.00
tot 750.91 2.66 753.57

$ onstat -g sch

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 01:06:59 -- 130680
Kbytes

VP Scheduler Statistics:

vp pid class semops busy waits spins/wait
1 7183 cpu 1 26 3946
2 7184 adm 0 0 0
3 7185 cpu 7941 8391 9854
4 7186 lio 1401 0 0
5 7187 pio 128 0 0
6 7188 aio 2225 0 0
7 7189 msc 17 0 0
8 7190 aio 1457 0 0
9 7191 aio 1049 0 0
10 7192 aio 825 0 0
11 7193 tli 2 2 1000

$ sar 1 5

UnixWare customs01 5 7.1.0 i386 03/28/05

12:44:46 %usr %sys %wio %idle %intr
12:44:47 50 0 0 50 0
12:44:48 50 0 0 50 0
12:44:49 50 0 0 50 0
12:44:50 50 0 0 50 0
12:44:51 46 0 12 42 0
Average 49 0 2 48 0


It's on dual processor Porliant ML350 with 512 MB memory
ONCONFIG:

# Physical Log Configuration
PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 100000 # Physical log file size (Kbytes)

# Logical Log Configuration
LOGFILES 9 # Number of logical log files
LOGSIZE 50000 # Logical log size (Kbytes)

# System Configuration
SERVERNUM 0 # Unique id corresponding to a OnLine instance
DBSERVERNAME customs01 # Name of default database server
DBSERVERALIASES customs # List of alternate dbservernames
NETTYPE ipcshm,1,350,CPU # 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
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 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
BUFFERS 50000 # Maximum number of shared buffers
NUMAIOVPS 4 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 10 # Maximum number of logical log files
CLEANERS 2 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 8000 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 900 # Check point interval (in sec)
LRUS 32 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 50 # 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 32 # Stack size (Kbytes)

# Read Ahead Variables
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 16 # Number of pages left before next group

FILLFACTOR 90 # Fill factor for building indexes

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 10 # Maximum allowed pdqpriority
DS_MAX_QUERIES 500 # Maximum number of decision support
queries
DS_TOTAL_MEMORY 65536 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support
scans
DATASKIP off # List of dbspaces to skip

OPTCOMPIND 0 # To hint the optimizer

ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
ABORT, 2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

Cristoff wrote:
> On Informix 7.23, when deleting records on large tables it goes very
> slow. What should we change to get better performance?
>
> $ onstat -p
>
> INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 01:06:01 -- 130680
> Kbytes

You're on 7.22, not 7.23, and you should not be on it still. 7.24 was
the Y2K-compliant version of IDS 7.2x. There's no guarantee that simply
changing the software would alter the performance of anything, you
should still be feeling a little uncomfortable about using quite such an
archaic version.

OTC gave some moderately good advice - at least, look at your indexing
strategies. Are you using fragmentation appropriately; do you have
enough disk drives?


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/