Re: Alta tasa de insercion/ big rate of insertion

Art et al. I think he said in his post his version is Informix DSA 7.2. Somehow you didn't get the complete message. Yours, Nick ********************************* Nick Nobbe, Library of Congress=20 NLS/BPH mail: nnob@loc.gov ********************************* On Thu, 9 Oct 1997, Art S. Kagel wrote: } Mediador wrote: } >=20 } > I all (hola a todos ) } >=20 } > Tengo el siguiente problema : } > En una aplicacion de telefonia celular, una tabla tiene una tasa de } > insercion de 800.000 registros diarios y tiene 4 indices que se deberia= n } > utilizar para resolver querys sobre esta. El problema es que cuando } > comienza la insercion la maquina se degrada tremendamente y no se logra } > insertar los registros. ESta aplicacion funciona sobre una DATA GENERAL } > 3500 con 512 MB en RAM, suficiente disco y 2 procesadores de 275 MHZ. } > la tabla puede llegar a tener 20.000.000 de registros; el ancho de regi= stro } > es de 300 bytes. } > =BF Cual es la forma de enfrentar el problema de tener una tabla con ta= n alta } > tasa de insercion y poder consultar eficientemente esta informaci=F3n ? } >=20 } > I have the next problem : } > A phone wireless application, a big table has insertion rate of 800.000 } > records/day and have 4 index for to optimize querys on it. The problem = is } > what when input of records begin, the performance goes down. This app r= un } > over a DATA GENERAL 3500 with 512 mb RAM and 2 CPU of 275 MHZ.The table= may } > have 20.000.000 of records and the size of 1 record is 300 bytes. } > =BFWhat is the way of solution the challenge of a table with big rate o= f } > insertion and index exist? }=20 } You did not specify the Informix version you are using, however, I think= =20 } that the problem is hardware. You are adding 800,000 rows at 300 bytes= =20 } per row plus 4 indexes. This means that each row added affects 5 data=20 } pages, assuming a 75% cache hit rate on writes that is 3 million pages=20 } of I/Os. Probably your update program pushes the cache rate temporarily= =20 } up to a max of 82% (only six rows fit on a page so best case you will do= =20 } one I/O rather than 6) so that makes it 400,000 pages or about 1.6=20 } million disk blocks. This is probably saturating your controllers or=20 } disk array. Do you use DG Clarion Disk Arrays? or physical disks? Have= =20 } you upgraded the controllers to fast/wide SCSI-III? Do you have cache=20 } on the disk controllers? Are you using RAID5? individual disks?=20 } mirrors? or stripping? How long does the update job run (this will help= =20 } estimate your actual I/O throughput)? How many buffers are configured=20 } in the engine (if you do not have enough then queries will always be=20 } waiting for buffers and performance will suffer)? How often does the=20 } engine checkpoint during normal load, during the update job run? What=20 } is the CKPTINTVL? How large is the physical log? How long are the=20 } checkpoints (for V5 you'll have to guesstimate? Heavy update activity=20 } can cause abnormally high checkpoint rates such that the engine is in a= =20 } checkpoint condition more seconds out of each minute than it is doing=20 } work (ie a checkpoint every 3 minutes lasting 2 minutes, the fix is a=20 } larger physical log, decreasing MAX_DIRTY and MIN_DIRTY, and increase=20 } the CKPTINTVL). }=20 } I hope that the suggestions help, if not give us the information=20 } requested and someone will likely recognise the problem. }=20 } Art S. Kagel }=20