Re: Alta tasa de insercion/ big rate of insertion

Mediador wrote: > > I all (hola a todos ) > > 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 deberian > 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 registro > es de 300 bytes. > ' Cual es la forma de enfrentar el problema de tener una tabla con tan alta > tasa de insercion y poder consultar eficientemente esta informaci'n ? > > 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 run > 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. > 'What is the way of solution the challenge of a table with big rate of > insertion and index exist? You did not specify the Informix version you are using, however, I think that the problem is hardware. You are adding 800,000 rows at 300 bytes per row plus 4 indexes. This means that each row added affects 5 data pages, assuming a 75% cache hit rate on writes that is 3 million pages of I/Os. Probably your update program pushes the cache rate temporarily up to a max of 82% (only six rows fit on a page so best case you will do one I/O rather than 6) so that makes it 400,000 pages or about 1.6 million disk blocks. This is probably saturating your controllers or disk array. Do you use DG Clarion Disk Arrays? or physical disks? Have you upgraded the controllers to fast/wide SCSI-III? Do you have cache on the disk controllers? Are you using RAID5? individual disks? mirrors? or stripping? How long does the update job run (this will help estimate your actual I/O throughput)? How many buffers are configured in the engine (if you do not have enough then queries will always be waiting for buffers and performance will suffer)? How often does the engine checkpoint during normal load, during the update job run? What is the CKPTINTVL? How large is the physical log? How long are the checkpoints (for V5 you'll have to guesstimate? Heavy update activity can cause abnormally high checkpoint rates such that the engine is in a checkpoint condition more seconds out of each minute than it is doing work (ie a checkpoint every 3 minutes lasting 2 minutes, the fix is a larger physical log, decreasing MAX_DIRTY and MIN_DIRTY, and increase the CKPTINTVL). I hope that the suggestions help, if not give us the information requested and someone will likely recognise the problem. Art S. Kagel