Performance Problems

Hello again... We're using IDS 7.30.UC2 on an HP machine, D270 with 1 processor and 512 Mb RAM. There is only one instance up, wich have many databases. All databases were using the default GLS definitions, en_us.819. Due to a new developed system, built in 4J's, we had to convert one of the databases, so that the new database would be able to accept special characters. Then we exported the database and re-created it with pt_BR.819. The colums with data type char were re-created with data type nchar. All the configuration of the instance has not been changed, but since the creation of the new database, its performance has decreased too much. The processes are slowly than they were before. A simple select is taking much more time. The checkpoint time also was affected. The normal rate was 1-5 seconds, and now it's 6-15 seconds, with peaks of 30 seconds, even 60 seconds. I asked Informix Brasil if they'd seen something like this, and the answer was (obviously) no. They could not help. I wonder if someone has ever had this problem, or if somebody can give us some advice, because we don't know what happened. I'm attaching the onconfig file for this instance, I hope that someone can help us. Thanks in advance Paulo #*********************************************************************** *** # INFORMIX SOFTWARE, INC. # Title: onconfig.std # Description: INFORMIX-OnLine Configuration Parameters #*********************************************************************** *** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /informix/link_banco/rootsahlogixprod # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 200000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS logfisinforprod # Location (dbspace) of physical log PHYSFILE 8000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 74 # Number of logical log files LOGSIZE 6000 # Logical log size (Kbytes) # Diagnostics MSGPATH /tmp/inforprod.log # System message log file path CONSOLE /tmp/inforprod.log # System console message path ALARMPROGRAM /informix/etc/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/rmt/0m # Tape device path TAPEBLK 64 # Tape block size (Kbytes) TAPESIZE 4000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/rmt/0m # Log tape device path LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 4000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 2 # Unique id corresponding to a OnLineinstance DBSERVERNAME inforprsm # Name of default database server DBSERVERALIASES inforprip # List of alternate dbservernames NETTYPE ipcshm,1,300,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,1,80,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 1 # If non-zero, limit number of cpu vpsto one NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 700000 # Maximum number of locks BUFFERS 51200 # Maximum number of shared buffers NUMAIOVPS 10 # Number of IO vps PHYSBUFF 128 # Physical log buffer size (Kbytes) LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 100 # Maximum number of logical log files CLEANERS 4 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 50000 # initial virtual shared memory segmentsize SHMADD 16384 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 40 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine 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 workerthreads ON_RECVRY_THREADS 2 # Default number of online workerthreads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /dev/null # DR lost+found file path # CDR Variables CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu- vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) # Backup/Restore variables BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to readahead RA_THRESHOLD 24 # Number of pages left before next group # DBSPACETEMP: # OnLine equivalent of DBTEMP for SE. Thi

Paulo Amorim wrote: > > Hello again... > > We´re using IDS 7.30.UC2 on an HP machine, D270 with 1 processor and > 512 Mb RAM. > > There is only one instance up, wich have many databases. All databases > were using the default GLS definitions, en_us.819. > > Due to a new developed system, built in 4J´s, we had to convert one of > the databases, so that the new database would be able to accept special > characters. Then we exported the database and re-created it with > pt_BR.819. The colums with data type char were re-created with data > type nchar. > Ok . . . you dumped the data . . . > All the configuration of the instance has not been changed, but since > the creation of the new database, its performance has decreased too > much. The processes are slowly than they were before. A simple select > is taking much more time. The checkpoint time also was affected. The > normal rate was 1-5 seconds, and now it´s 6-15 seconds, with peaks of > 30 seconds, even 60 seconds. > I'm not seeing any mention of the 'update statistics' here. You did rerun it, didn't you?? If you didn't, then the optimizer probably doesn't have the needed information to choose the quickest query path. . . . . snipped . . . > OPTCOMPIND 2 # To hint the optimizer This might be part of the problem as well. Sometimes the 'lowest cost' solution is not necessarily the fastest. We've set OPTCOMPIND to 0. Enjoy! -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Paulo Amorim wrote: > Hello again... > > We´re using IDS 7.30.UC2 on an HP machine, D270 with 1 processor and > 512 Mb RAM. > > There is only one instance up, wich have many databases. All databases > were using the default GLS definitions, en_us.819. > > Due to a new developed system, built in 4J´s, we had to convert one of > the databases, so that the new database would be able to accept special > characters. Then we exported the database and re-created it with > pt_BR.819. The colums with data type char were re-created with data > type nchar. > > All the configuration of the instance has not been changed, but since > the creation of the new database, its performance has decreased too > much. The processes are slowly than they were before. A simple select > is taking much more time. The checkpoint time also was affected. The > normal rate was 1-5 seconds, and now it´s 6-15 seconds, with peaks of > 30 seconds, even 60 seconds. > > I asked Informix Brasil if they´d seen something like this, and the > answer was (obviously) no. They could not help. I wonder if someone has > ever had this problem, or if somebody can give us some advice, because > we don´t know what happened. > > I´m attaching the onconfig file for this instance, I hope that someone > can help us. > > Thanks in advance > Paulo > > # From what I have read on the NG some times "NULL"s play funny tricks on queries and cause performance problems, you can check your table to see if it accepts NULLs or not. That may make a difference. You can also add to your selects a WHERE veritable NOT NULL which may make a difference. PS: You may also check your index's to make sure they are OK. --- Compliments of QueriX -------------------------------------------------------------------------------------------------- QueriX 4GL Compilers are Informix 4GL Compatible, and Connection to other RDBMS such as Oracle. Hydra 4GL Compiler (Compatible with I4GL) Compile once, run everywhere Phoenix Windows GUI. (Front End to 4GL) Chimera Java GUI The only GUI you will ever need... (Front End to 4GL) Arachne Web Technology (Front End to 4GL on the Web) For more details visit: http://www.querix.com/ ---------------------------------------------------------------------------------------------------

Paulo Amorim schrieb: > Due to a new developed system, built in 4J´s, we had to convert one of > the databases, so that the new database would be able to accept special > characters. Then we exported the database and re-created it with > pt_BR.819. The colums with data type char were re-created with data > type nchar. Apart from the obvious answer "update statistics", there could be a problem with the "nchar" columns and queries with wildcards not using indexes. We had the same problem when we converted databases to German locales and CHAR columns to NCHAR columns. From then on, select statements like "SELECT * FROM patient WHERE name MATCHES "Schmid*" would show absolutely inacceptable performance. I found out with "SET EXPLAIN ON" that the index on the column "name" was never used in this setting. On a parallel database where the column type was still "CHAR", the performance was good and the index was used even though the German locale was in use. I opened up a case with Siemens Informix support, who finally responded that this was a known problem and was being worked on in newer releases. I'm still on IDS 7.30UC6 and UC10, where the problem remains unsolved. Reverting the column type back to CHAR will restore performance, but ORDER BY will not give you the correct sorting order for special characters. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard Spitz wrote: > > Paulo Amorim schrieb: > > > Due to a new developed system, built in 4J´s, we had to convert one of > > the databases, so that the new database would be able to accept special > > characters. Then we exported the database and re-created it with > > pt_BR.819. The colums with data type char were re-created with data > > type nchar. > > Apart from the obvious answer "update statistics", there could be a > problem with the "nchar" columns and queries with wildcards not using > indexes. > > We had the same problem when we converted databases to German locales > and CHAR columns to NCHAR columns. From then on, select statements > like "SELECT * FROM patient WHERE name MATCHES "Schmid*" would show > absolutely inacceptable performance. I found out with "SET EXPLAIN ON" > that the index on the column "name" was never used in this setting. > On a parallel database where the column type was still "CHAR", the > performance was good and the index was used even though the German > locale was in use. > > I opened up a case with Siemens Informix support, who finally > responded that this was a known problem and was being worked on in > newer releases. I'm still on IDS 7.30UC6 and UC10, where the problem > remains unsolved. I think this was bug# 99152 (fixed in the 7.31.UC4 release): 99152 INDEX ON NCHAR COLUMN IN AN 8-BIT GLS DATABASE SEQUENTIALLY SCANNED regards Markus > snip..

Markus Holzbauer wrote: > Richard Spitz wrote: > > > > Paulo Amorim schrieb: > > > > > Due to a new developed system, built in 4J´s, we had to convert one of > > > the databases, so that the new database would be able to accept special > > > characters. Then we exported the database and re-created it with > > > pt_BR.819. The colums with data type char were re-created with data > > > type nchar. > > > > Apart from the obvious answer "update statistics", there could be a > > problem with the "nchar" columns and queries with wildcards not using > > indexes. > > > > We had the same problem when we converted databases to German locales > > and CHAR columns to NCHAR columns. From then on, select statements > > like "SELECT * FROM patient WHERE name MATCHES "Schmid*" would show > > absolutely inacceptable performance. I found out with "SET EXPLAIN ON" > > that the index on the column "name" was never used in this setting. > > On a parallel database where the column type was still "CHAR", the > > performance was good and the index was used even though the German > > locale was in use. > > > > I opened up a case with Siemens Informix support, who finally > > responded that this was a known problem and was being worked on in > > newer releases. I'm still on IDS 7.30UC6 and UC10, where the problem > > remains unsolved. > > I think this was bug# 99152 (fixed in the 7.31.UC4 release): > > 99152 INDEX ON NCHAR COLUMN IN AN 8-BIT GLS DATABASE SEQUENTIALLY > SCANNED > > regards > Markus > > > > snip.. Have you done 'update statistics high for table (<colname>)' on that NCHAR column? This will enable the query optimer to use the index. For larger table, use 'medium' mode instead of high.