Re: long checkpoints on informix

On 16 jul, 06:34, Ben Thompson <b...@nomonitorsoftspam.com> wrote: > Sakura.g...@gmail.com wrote: > > hi everyone > > i recently start to have some long and constant checkpoint > > i did lot of change but cant reduce it > > i have Informix Dynamic Server Version 9.40.FC2 > > and here is some parameter of the onconfig > > --------------------------------------------------------------------------------------------------------- > > > TBLSPACE_STATS 1 # Maintain tblspace statistics > > 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 1 # Process aging > > AFF_SPROC 0 # Affinity start processor > > AFF_NPROCS 0 # Affinity number of processors > > LOCKS 100000 # Maximum number of locks > > BUFFERS 200000 # Maximum number of shared buffers > > NUMAIOVPS 1 # Number of IO vps > > CLEANERS 127 # Number of buffer cleaner processes > > SHMBASE 0x10A000000L # Shared memory base address > > SHMVIRTSIZE 300000 # initial virtual shared memory> > segment size > > SHMADD 16384 # Size of new shared memory segments > > (Kbytes) > > CKPTINTVL 600 # Check point interval (in sec)(10 > > minutos) > > LRUS 127 # Number of LRU queues > > LRU_MAX_DIRTY 2.000000 # LRU percent dirty begin cleaning> > limit > > LRU_MIN_DIRTY 1.000000 # LRU percent dirty end cleaning limit > > DYNAMIC_LOGS 2 > > OFF_RECVRY_THREADS 10 # Default number of offline worker> > threads > > ON_RECVRY_THREADS 1 # Default number of online worker> > threads > > 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) > > RA_PAGES 32 # Number of pages to attempt> > to read ahead > > RA_THRESHOLD 30 # Number of pages left before> > next group > > MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority > > DS_MAX_QUERIES # Maximum number of decision support> > queries > > DS_TOTAL_MEMORY # Decision support memory (Kbytes) > > DS_MAX_SCANS 1048576 # Maximum number of decision support> > scans > > OPTCOMPIND 2 # To hint the optimizer > > ---------------------------------------------------------------------------------------------------------> > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 69174631 143507619 16152454169 99.57 10204375 98766167 29414237 > > 65.31 > > Some comments and things to try that may be helpful, may not. > > You're running 9.40.FC2 so you clearly have a 64-bit system. Which > platform? How much memory do you have? What are your processors? What is > the disc controller and RAID level? > > You've got 65% cached writes which is pretty low. Your other post shows > around 70 io operations/sec which is quite high so your system is busy. > > Your BUFFERS are just 200000 which is just 400Mb or 800Mb depending on > your page size, plus you've got 300Mb of shared memory. It's not a lot > really by today's standards and not for a 64-bit system. If you have the > memory, have you tried increasing these values? It would get your cached > writes up. > > You've got two CPU VPs and just one AIO VP. You could definitely have > more AIO VPs and maybe you could try two CPU VPs per processor (if you > have a multi-processor system on modern processors). > > You've got a lot of LRUs, presumably to keep checkpoints down, but > mostly chunk writes which is a little confusing. Maybe others can help > here. However performance is poor. Is your RAID set healthy? > > Your read-ahead values are quite high. Maybe you could reduce these to > cut down the number of pages read in. Obviously there is a trade-off > here with the number of disc access requests requests required so > perhaps a little tuning? > > You've got a lot of writes so presumably you've got an OLTP set up? > Perhaps try changing OPTCOMPIND to '0' (zero). > > Try monitoring using "onstat -F" during the checkpoint by running it > every second for analysis later. You have only posted truncated "onstat > -F" output. > > Also check locks using "onstat -k" and cross-reference with "onstat -u". > > Regards, Ben. SunOS sun4u sparc SUNW,Sun-Fire-V240 Memory size: 4096 Megabytes RAID5 > here. However performance is poor. Is your RAID set healthy? how can i check this ?

Sakura.ggxx@gmail.com wrote: > SunOS sun4u sparc SUNW,Sun-Fire-V240 > Memory size: 4096 Megabytes > RAID5 If Informix is the only service on the server then you're simply not using much of that 4GB RAM. I think Sun is 2k page size so you could easily set BUFFERS to 1250000 to utilise 2.5Gb RAM and SHMVIRTSIZE to 800000 or so (I have plucked these figures out of the air somewhat). This will improve your cached writes. However while this may make your server run better I think your real problem lies elsewhere. >> here. However performance is poor. Is your RAID set healthy? > how can i check this ? I know little about recent Sun hardware. If I was in your position I would check the front panel on the machine for red LEDs and follow the manufacturers documentation. Maybe someone else can offer better advice but you're now asking an operating system question and not an Informix question. RAID5 is not a good choice for a database system and performance will nosedive if one of the discs is out of operation. In fact if your RAID set is degraded you need to take urgent action now to protect your data such as making sure you have an up to date backup or replicated server as you cant afford to lose another disc. Regards, Ben.