RE: Huge performance problems Sun/Informix

The output from the following might be helpful. onstat -p onstat -D onstat -g iov During sequential scan.(find out who is active doing what) a couple of onstat -g act Are you using KAIO? What is your read ahead? When you say the system is 70-80 percent idle, and that means each CPU is only 8 percent busy, where are you getting the 8 percent. Is it a possibility that one cpu is pegged and the others are just twiddling their thumbs? I know we have run into situations where do to the lack of ability to parellize a query, we have had one cpu pegged and 3 idle hence vmstat shows 75% idle(4 CPU box). Hope to help Will >===== Original Message From Thomas Vogt <t.vogt@drgueldener.de> ===== >Hello everybody, > >we have huge performance problems on our 2-node Sun-Cluster with >Informix: > >Environment: > Sun E4500, 6 CPU's, 1GB Memory (primary host for database) > Sun E3500, 4 CPU's, 1GB Memory (secondary host for takeover) > 4 A5000-Diskarrays with 14 9,2GB disks each connected via FCAL > Solaris 2.6, Sun Cluster 2.1 > disk mirroring using Veritas Volume Manager (Raid 0/1) > Gigabit Network Adapters > Informix Dynamic Server 7.30UC9 > >Workload: for example: > > 400.000 inserts and updates per day in one non-fragmented table, row >size 265 bytes > via continuous batch processes on server. > 400.000 inserts of blob data (average blob size 10K) > (total sum per month are >2 Mio data rows and blobs) > > 100 OLTP users selecting small portions of data and blobs, updating >data table > > several batch programs for statistic analysis and invoice generation, >mainly > selecting from data table > >All dbspaces are located on raw devices. >Root-, physical log and logical log dbspaces are on separated disk. >Table dbspace is separated from index dbspaces on a stripe with 3 disks >each ( >stripe interleave is 16K which seems to be the same as Informix' >BIGREADS). >The blobs are stored in their own blobspaces with blob page size = 10K. > >Extract from onconfig: >============================================================= >NETTYPE tlitcp,2,100,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 ># on E4500 5 CPUs ># on E3500 3 CPUs >NUMCPUVPS 5 # Number of user (cpu) vps >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>to one > >NOAGE 1 # Process aging >AFF_SPROC 1 # Affinity start processor >AFF_NPROCS 5 # Affinity number of processors> ># Shared Memory Parameters > >LOCKS 1000000 # Maximum number of locks >BUFFERS 100000 # Maximum number of shared buffers >NUMAIOVPS 2 # Number of IO vps >PHYSBUFF 1024 # Physical log buffer size (Kbytes) >LOGBUFF 1024 # Logical log buffer size (Kbytes)>LOGSMAX 30 # Maximum number of logical log files >CLEANERS 10 # Number of buffer cleaner processes >SHMBASE 0xa000000 # Shared memory base address >SHMVIRTSIZE 32768 # initial virtual shared memory segment>size >SHMADD 32768 # Size of new shared memory segments >(Kbytes) >SHMTOTAL 0 # Total shared memory (Kbytes). >0=>unlimited >CKPTINTVL 900 # Check point interval (in sec) >LRUS 20 # Number of LRU queues >#># lowered for better checkpoint time >LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 3 # 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> >======================= > >Problems: it seems not possible to get more than 300K/s throughput from >individual disks > (more than 1.2MB/s from a 3 disk stripe) if we do a sequential scan >from the data > table with a result set of very few rows (<10). > But... monitoring the i/o with iostat shows that all disks are >only 10-20% busy > and doing just 70 reads per second. The whole system shows 70 to 80% >idle time, each > CPU is only 8% busy !! > > But.. a level 0 or level 1 backup of data dbspaces and blobspaces >with onbar > and Legato Networker results in a throughput between 10MB/s and >36MB/s for each > stripe !!!! > > > In the last months we had several dates with Sun and Informix >consultants. > > Sun says: there is no problem with the hardware, Informix read >requests are too small, > because best performance with A5000 arrays is achieved with 64K >requests. > > Informix says: there are no problems neither with Informix Server nor >with > our configuration. You have to do more reads in parallel > (on the client side). > >Is there anybody having similiar problems AND a solution ????? > >For us, one solution to this problem may be fragmention of the data >table by round robin policy. >This involves more questions: > - would that be the right choice for our OLTP environment ? > - our client software (written with C++-Builder from Borland) requires > an explicit rowid, no chance to refer to the existing primary key. > What about performance degradation using a virtual rowid > (create table .... WITH ROWID ) ? > - shall we fragment over several individual disks or over stripes ? > - detach indexes or not (I would prefer detaching without index >fragmentation)? > - what about PDQPRIORITY on the client side and on the server side ? > >I have checked all archives from comp.databases.informix back to Jan >1998 and I didn't find >any hints which may help to solve our problems. > >Many thanks for any help > >Thomas Vogt >Firmengruppe Dr. Gueldener ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------