Re: tuning questions

Allen Jantzen wrote: > > I recently doubled the size of my database, and have been tuning for a > few days now. IIUG CDI Glimpse has been a tremendous help, especially > the posts of Art Kagel, David Williams, et al. My thanks to them. > > My environment: > HP 9000 J200 , 2 processors, 256 megs, HP-UX 10.01, database dedicated > Informix ODS 7.22.UC1 > Database around 16 gigs, 10 of which is used > 6 Dbspaces, all mirrored, 4 cooked 2 raw > KAIO just enabled KAIO only works for the RAW devices. Get those cooked devices onto RAW as soon as possible. > Basically 1/2 DSS and 1/2 OLTP Difficult to tune. > MULTIPROCESSOR 1 > NUMCPUVPS 1 If this is a dedicated DB server, then up this to 2. > SINGLE_CPU_VP 1 ....Thus 0. > LOCKS 100000 > BUFFERS 15000 > NUMAIOVPS 8 > PHYSBUFF 96 > LOGBUFF 48 > CLEANERS 16 > CKPTINTVL 300 > LRUS 16 > LRU_MAX_DIRTY 10 > LRU_MIN_DIRTY 5 > RA_PAGES 24 > RA_THRESHOLD 12 > PDQPRIORITY 50 Only for certain sessions I hope. Or are you talking about a configuration parameter? If so, your upgrade from 7.1x didn't go well. ;-) This parameter is ignored in 7.10.UD1 onwards. > A few questions: > > I cannot seem to get my onstat -F LRU write / Chunk Write ratio > any higher than 1:1 or 1.5:1. During the day my checkpoints average > only 3-4 seconds: > > 08:04:01 Checkpoint Completed: duration was 4 seconds. > 08:09:04 Checkpoint Completed: duration was 1 seconds. > 08:14:09 Checkpoint Completed: duration was 1 seconds. > 08:19:16 Checkpoint Completed: duration was 3 seconds. > 08:34:32 Checkpoint Completed: duration was 1 seconds.> > but at night when a lot of batch processing is occurring (beginning at > 3:30 AM for instance) they go up to 30-40 seconds, or even higher: > 03:15:39 Checkpoint Completed: duration was 1 seconds. > 03:31:10 Checkpoint Completed: duration was 19 seconds. > 03:36:44 Checkpoint Completed: duration was 30 seconds. > 03:42:34 Checkpoint Completed: duration was 47 seconds. > 03:47:33 Logical Log 5154 Complete. > 03:47:35 Process exited with return code 133: /bin/sh /bin/sh -c > /opt/informix/etc/log_full.sh 2 23 "Logical Log 515> 4 Complete." "Logical Log 5154 Complete." > 03:47:36 Logical Log 5154 - Backup Started > 03:48:10 Checkpoint Completed: duration was 33 seconds. > 03:48:10 Logical Log 5154 - Backup Completed > 03:53:47 Checkpoint Completed: duration was 34 seconds. That's the problem with tuning for both DSS and OLTP. One of them will have to suffer. It is normally DSS. See my comments at the end. A long checkpoint for DSS environments is normal. Remember, chunk writes are the most efficient. > (As an aside, why would a checkpoint not occur for 16 minutes between > 3:15 and 3:31 when my CKPTINTVL is 300???) No one did any work? > There are no FG writes. I have decreased LRU_MAX_DIRTY and > LRU_MIN_DIRTY and increased LRUS and CLEANERS, but no help. On a very active system, you can set LRU_MAX_DIRTY=1 and LRU_MIN_DIRTY=0 if you need to. So you have a lot more tuning to do yet, at 10 & 5. > My Bufwaits are around 3% of Bufreads and Bufwrites. My read and write > cache% are 99% and 97%. Fine. > How can I get my LRU write/Chunk write ratio to increase (and > therefore my checkpoint duration to drop)?? You won't get the checkpoint duration to drop much during DSS activity. For OLTP drop the LRU parameters further. Use RAW disk instead of COOKED. Make sure you create chunks across the disks, and not down the disks. Page cleaners will be allocated chunks in chunk number order. > My onstat -l physical logging pages/io is always higher than 40 while > my physical log bufsize is 48 - this seems great. However........ > my onstat -l logical logging pages/io is always lower than 5 while my > logical log bufsize is 24. The logical logging pages/io is way too > low. How can I increase this? Do more work! No, you are looking at the wrong end. Your ratio of pages/io to bufsize is 20%, when you are looking for about 75%. So reduce the size of the buffer to increase this ratio. Don't spend time on this. ;-) > My ontape -s lev 0 archives are taking a *LONG* time, like 5 or 6 > hours. What could be causing this? The tape block size? Find out what the tape drive supports, and set it as large as possible. Use RAW disk. Make sure temp dbspaces are on RAW disk. > Should I make other changes as a result of enabling KAIO? Yes, use RAW disk. > Should NUMAIOVPS go down to 1, even w/ cooked dbspaces?? No. The COOKED chunks still require AIO VPs. If all chunks are RAW, then yes, 1 or 2. > Would another CPU vp be a good idea? (2 physcial processors) Yes, if no apps are running. > Any insight greatly appreciated. Make sure you have read the release notes and configured the kernel as required. If your DSS and OLTP access is time based consider two configuration files. I.e. your day time access is predominantly OLTP while your night time access is mainly DSS. If you can shutdown the box at night and again in the morning you can use two configuration files. One configured for OLTP, pretty much the one you are using. Then another configured for DSS. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+