Performance problem mystery

Environment: IDS 7.24UC7, Solaris 2.6, 512MB RAM, 6 Physical Processors, Sun Storage array with 22 singleton disks. No of users: 100 (Approx.) Hi everyone! I was wondering if anyone could shed some light on the subject or if anyone had experience this kind of behavior. I was tuning our production server and it was running fine using the following ONCONFIG parameters: NETTYPE tlitcp,1,,NET NETTYPE ipcshm,1,,CPU RESIDENT 1 MULTIPROCESSOR 1 NUMCPUVPS 3 SINGLE_CPU_VP 0 NOAGE 0 AFF_SPROC 1 AFF_NPROCS 3 LOCKS 64000 BUFFERS 10000 NUMAIOVPS 2 PHYSBUFF 128 CLEANERS 10 SHMVIRTSIZE 64000 CKPTINTVL 600 LRUS 20 LRU_MAX_DIRTY 10 LRU_MIN_DIRTY 5 Checkpoints at this point were in the 1-2 second duration. Then I tried setting NOAGE to 1. The machine gets rebooted every Friday evening and users don't access the system till Monday. Come Monday, users were complaining that the system was slow. When I took a look at the 'online.log', sure enough, checkpoints were long. See below: Fri Jun 11 22:07:14 1999 22:07:14 Event alarms enabled. 22:07:19 DR: DRAUTO is 0 (Off) 22:07:21 INFORMIX-OnLine Initialized -- Shared Memory Initialized. 22:07:21 Physical Recovery Started. 22:07:22 Physical Recovery Complete: 0 Pages Restored. 22:07:22 Logical Recovery Started. 22:07:25 Logical Recovery Complete.11 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks 22:07:25 Onconfig parameter NOAGE modified from 0 to 1. 22:07:25 Dataskip is now OFF for all dbspaces 22:07:25 On-Line Mode 22:07:25 Affinitied VP 3 to phys proc 2 22:07:25 Affinitied VP 4 to phys proc 3 22:07:25 Affinitied VP 1 to phys proc 1 22:07:26 Checkpoint Completed: duration was 0 seconds. Mon Jun 14 04:21:37 1999 07:34:32 Checkpoint Completed: duration was 1 seconds. 07:45:00 Checkpoint Completed: duration was 12 seconds. 07:57:27 Checkpoint Completed: duration was 132 seconds. 08:07:56 Checkpoint Completed: duration was 6 seconds. 08:18:14 Checkpoint Completed: duration was 13 seconds. 08:28:44 Checkpoint Completed: duration was 22 seconds. 08:41:34 Checkpoint Completed: duration was 163 seconds. I tried setting NOAGE back but to no avail. We were still experiencing slow checkpoints! Or is Informix itself that is slow? We even tried rebooting the server but it didn't work! I don't get it, this is the same configuration I have a week ago and everything was running fine. Nothing changed at the OS level, we checked all our system files. There has to be an explanation! By the way, there were only like 500-800 buffers dirty at checkpoint time because users can hardly do anything. Even connecting through dbaccess took forever! Mon Jun 14 09:50:47 1999 09:50:54 INFORMIX-OnLine Initialized -- Shared Memory Initialized. 09:50:54 Physical Recovery Started. 09:50:54 Physical Recovery Complete: 0 Pages Restored. 09:50:54 Logical Recovery Started. 09:51:00 Logical Recovery Complete. 0 Committed, 5 Rolled Back, 0 Open, 0 Bad Locks 09:51:00 Onconfig parameter NOAGE modified from 1 to 0. 09:51:00 Dataskip is now OFF for all dbspaces 09:51:00 On-Line Mode 09:51:00 Affinitied VP 1 to phys proc 1 09:51:00 Affinitied VP 4 to phys proc 3 09:51:00 Affinitied VP 3 to phys proc 2 09:51:01 Checkpoint Completed: duration was 1 seconds. 10:03:30 Checkpoint Completed: duration was 148 seconds. 10:14:57 Checkpoint Completed: duration was 77 seconds. I called up Informix and after dialing-up to our server, they still couldn't figure-out what's wrong. They did change some settings (See below) and everything was fine again though CACHE rate suffered, buffwaits increased etc... All my tuning done to the system went down the drain. Checkpoints though, returned back to normal. 15:45:18 Onconfig parameter CLEANERS modified from 10 to 8. 15:45:18 Onconfig parameter LRUS modified from 20 to 10. 15:45:18 Onconfig parameter LRU_MAX_DIRTY modified from 10 to 5. 15:45:18 Onconfig parameter LRU_MIN_DIRTY modified from 5 to 2. 15:45:18 Onconfig parameter NUMCPUVPS modified from 1 to 5. Now, I'm trying to write a report to my boss stating what happened. And, frankly, I don't know what to say. Thanks for listening. Zandy Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Extraordinarily long checkpoints occasionally is usually NOT an engine tuning problem. Remember that before the checkpoint can do anything it must wait for all processes in critical sections to finish what they are doing and release their latches. If a process that is in a critical section becomes stalled for any reason (like it is interactive and the user ^Z'd it in mid transaction) it will hold everything up. This may be what you are seeing. Look at the first two lines of the onstat report header when this happens again to see if you are actually in a CKPT or CKPT REQ. The latter means the engine is waiting to initiate a checkpoint. Look for sessions, in onstat -u, that are still performing writes to identify the culprit. Sometimes it is a COMMIT or ROLLBACK of a large transaction also. Art S. Kagel Lyzander Marantal wrote: > > Environment: IDS 7.24UC7, Solaris 2.6, 512MB RAM, 6 Physical > Processors, Sun Storage array with 22 singleton disks. > No of users: 100 (Approx.) > > Hi everyone! > > I was wondering if anyone could shed some light on the subject or if > anyone had experience this kind of behavior. > I was tuning our production server and it was running fine using the > following ONCONFIG parameters: > > NETTYPE tlitcp,1,,NET > NETTYPE ipcshm,1,,CPU > RESIDENT 1 > MULTIPROCESSOR 1 > NUMCPUVPS 3 > SINGLE_CPU_VP 0 > NOAGE 0 > AFF_SPROC 1 > AFF_NPROCS 3 > LOCKS 64000 > BUFFERS 10000 > NUMAIOVPS 2 > PHYSBUFF 128 > CLEANERS 10 > SHMVIRTSIZE 64000 > CKPTINTVL 600 > LRUS 20 > LRU_MAX_DIRTY 10 > LRU_MIN_DIRTY 5> > Checkpoints at this point were in the 1-2 second duration. > > Then I tried setting NOAGE to 1. The machine gets rebooted every Friday > evening and users don’t access the system till Monday. Come Monday, > users were complaining that the system was slow. When I took a look at > the “online.log”, sure enough, checkpoints were long. See below: > > Fri Jun 11 22:07:14 1999 > > 22:07:14 Event alarms enabled. > 22:07:19 DR: DRAUTO is 0 (Off) > 22:07:21 INFORMIX-OnLine Initialized -- Shared Memory Initialized. > 22:07:21 Physical Recovery Started. > 22:07:22 Physical Recovery Complete: 0 Pages Restored. > 22:07:22 Logical Recovery Started. > 22:07:25 Logical Recovery Complete.> 11 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks > 22:07:25 Onconfig parameter NOAGE modified from 0 to 1. > 22:07:25 Dataskip is now OFF for all dbspaces > 22:07:25 On-Line Mode > 22:07:25 Affinitied VP 3 to phys proc 2 > 22:07:25 Affinitied VP 4 to phys proc 3 > 22:07:25 Affinitied VP 1 to phys proc 1 > 22:07:26 Checkpoint Completed: duration was 0 seconds.> > Mon Jun 14 04:21:37 1999 > > 07:34:32 Checkpoint Completed: duration was 1 seconds. > 07:45:00 Checkpoint Completed: duration was 12 seconds. > 07:57:27 Checkpoint Completed: duration was 132 seconds. > 08:07:56 Checkpoint Completed: duration was 6 seconds. > 08:18:14 Checkpoint Completed: duration was 13 seconds. > 08:28:44 Checkpoint Completed: duration was 22 seconds. > 08:41:34 Checkpoint Completed: duration was 163 seconds.> > I tried setting NOAGE back but to no avail. We were still experiencing > slow checkpoints! Or is Informix itself that is slow? > We even tried rebooting the server but it didn’t work! I don’t get it, > this is the same configuration I have a week ago and everything was > running fine. Nothing changed at the OS level, we checked all our > system files. There has to be an explanation! > > By the way, there were only like 500-800 buffers dirty at checkpoint > time because users can hardly do anything. Even connecting through > dbaccess took forever! > > Mon Jun 14 09:50:47 1999 > 09:50:54 INFORMIX-OnLine Initialized -- Shared Memory Initialized. > 09:50:54 Physical Recovery Started. > 09:50:54 Physical Recovery Complete: 0 Pages Restored. > 09:50:54 Logical Recovery Started. > 09:51:00 Logical Recovery Complete.> 0 Committed, 5 Rolled Back, 0 Open, 0 Bad Locks > 09:51:00 Onconfig parameter NOAGE modified from 1 to 0. > 09:51:00 Dataskip is now OFF for all dbspaces > 09:51:00 On-Line Mode > 09:51:00 Affinitied VP 1 to phys proc 1 > 09:51:00 Affinitied VP 4 to phys proc 3 > 09:51:00 Affinitied VP 3 to phys proc 2 > 09:51:01 Checkpoint Completed: duration was 1 seconds. > 10:03:30 Checkpoint Completed: duration was 148 seconds. > 10:14:57 Checkpoint Completed: duration was 77 seconds.> > I called up Informix and after dialing-up to our server, they still > couldn’t figure-out what’s wrong. They did change some settings (See > below) and everything was fine again though CACHE rate suffered, > buffwaits increased etc... All my tuning done to the system went down > the drain. Checkpoints though, returned back to normal. > > 15:45:18 Onconfig parameter CLEANERS modified from 10 to 8. > 15:45:18 Onconfig parameter LRUS modified from 20 to 10. > 15:45:18 Onconfig parameter LRU_MAX_DIRTY modified from 10 to 5. > 15:45:18 Onconfig parameter LRU_MIN_DIRTY modified from 5 to 2. > 15:45:18 Onconfig parameter NUMCPUVPS modified from 1 to 5.> > Now, I’m trying to write a report to my boss stating what happened. > And, frankly, I don’t know what to say. > > Thanks for listening. > > Zandy > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.