Re: chekcpoints, BUFFER settings, and optimization

In article <348FFD23.5D0B@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >David Kosenko wrote: >> >> "Art S. Kagel" <kagel@bloomberg.com> wrote: >> +Dave I have to disagree. I agree that additional cleaners are of >> +limited use during normal LRU writes but they are needed for fastest >> +checkpointing. Since at checkpoint time each chunk is assigned to >> +another cleaner thread until the threads are exhausted, and since that >> +one thread, as has already been pointed out, is only scheduling the >> +actual I/Os with either the AIO VPs or KAIO VPs, that thread will block >> +on each I/O that it schedules and single thread writes to your disks. >> +You must have multiple cleaners, even with a single CPU VP, since the >> +other cleaners can wake and schedule the I/Os that they are responsible >> +for while the first cleaner is blocked waiting for I/O service. >> >> The cleaner threads do not block on the i/o calls. There are NO blocking >> calls whatsoever in the CPU VPs. >> >> Besides, if a cleaner thread "blocked", it would not be yielding the cpu vp, >in >> which case having more cleaner threads than cpu vps would still not make any >> sense. I'm with Dave Williams on this one. > Sorry but I've change my mind and I'm now with Art. >I was not clear. Perhaps 'blocked' is not the correct word here. My >point is that the single cleaner thread has to wait for the issued I/O >to complete. It does relinquish the CPU VP to other threads so that I now think it does wait for the I/O to complete, see below. >other work is not blocked, but, only one disk is cleaned at a time. On >any significant system this is takes too long. > >I am not constrained to a single CPU VP but the sheer size of our >systems provide similar behavioral situations, observe. We have 32 SLOW >CPUs, 4 reserved for system processes and our proprietary IPC processes, >with 28 CPU VPs. KAIO on DG M88k is slow so we still use AIO VPs. Our >typical server has either 200,000 or 300,000 buffers in 128 LRUs we run >~120 AIO VPs (different machines have different peak load needs). We >must keep our checkpoints < 8 seconds or our data feed applications fall >behind real time performance requirements. To do this we keep MIN_DIRTY >= 0 and MAX_DIRTY = (1 or 2). I have tried cleaner values <= the number >of CPU VPs and checkpoint times are ALWAYS too long. We have found that >having 2-2.5 cleaner threads per CPU VP makes certain that a cleaner is >always ready for service when a VP has cycles available and checkpoints >are minimized. Because of the large number of LRUs we use (32 are too >few as contention for buffers increases and I have made clear my belief >that there is a bug in the LRU access hashing with 64 LRUs, someone else >reported similar problems to me with 96 LRUs, so since 128 works...) my >advice to use 1.5 cleaners per LRU is overkill for us but for 32 LRUs or >less I believe that that figure is probably correct. > >At any rate it is simple enough to test. Just choose a configuration >(1 cleaner or N cleaners) load some data and read the checkpoint times >from the log then change the value and try again. Let me modify my >recommendation slightly. I keep forgetting most installations do not >have 258 chunks. So I recommend 1.5 * min(#chunks,#LRUs) as the number >of cleaners keep the 2-2.5 * NUMCPUs idea for multiple CPUs in mind and >constrained by a maximum useful value of about 64 for multiple CPU >systems. I have not worked with single CPU installations enough to >guess at a maximum value but I STRONGLY suspect that it is MUCH greater >than one (1)! I know that this sound complex, but the configuration >that I use is the result of months of testing and the recommendations >are an attempt to extrapolate a formula from these results. > >If someone will try to apply my formula and Dave's recommendation to a >and single CPU machine and come up specific empirical recommendations >I would like to see the outcome. That would be the best way to settle >this debate. > I've tried on a single CPU machine at work, here are my findings. Initial Setup for tests ======================= Online 7.12.UC1 on an ICL Teamserver running DRS/NX. 3 chunks , 2K pagesize Dbspace File size Device rootdbs root1 5000 pages on /dev/c0d0s21 DIsk 1 dbs1 chk1 1500 pages on /dev/c0d0s21 Disk 1 chk2 chk2 1500 pages on /dev/c1d4s1 Disk 2 1 CPU VP, 2 AIO VPs. Database setup via dbaccess =========================== create database dlo # This is in the root dbs and have no logging create table t1 ( c1 char(2020) ) in dbs1; create table t2 ( c1 char(2020) ) in dbs2; Source code for test program ============================ 2.c #include <stdio.h> #include <sys/types.h> #include <time.h> main() { time_t t; int i,a; a=doit1(0); t=time(NULL); fprintf(stderr,"%s\\n",ctime(&t)); a=doit2(0); t=time(NULL); fprintf(stderr,"%s\\n",ctime(&t)); 1.4gl FUNCTION doit1() DATABASE dlo DELETE FROM t1 DELETE FROM t2 END FUNCTION FUNCTION doit2() DEFINE i INTEGER FOR i = 1 TO 1000 INSERT INTO t1 VALUES("11") INSERT INTO t2 VALUES("22") END FOR END FUNCTION Run this program once to populate the tables... Disk layout =========== oncheck -pe nows shows rootdbs = sysmaster+sysutil. chunk2 (chk2) = START LENGTH OTHER RESERVED PAGES 0 2 CHUNK FREE LIST 2 1 TBLSPACE TBLSPACE 3 50 dlo.t1 53 1008 FREE 1061 439 chunk3 (chk3) = START LENGTH OTHER RESERVED PAGES 0 2 CHUNK FREE LIST 2 1 TBLSPACE TBLSPACE 3 50 dlo.t2 53 1008 FREE 1061 439 Online configuration ==================== OK so we have 2 tables on 2 disks We have NUMAIOVPS 2 NUMCPUVPS 1 BUFFERS 2000 Physical Log Size 2000 LRU_MAX_DIRTY 99 LRU_MIN_DIRTY 98 This is a small instance so I've forced more I/O to occur at checkpoint time. Test procedure ============== 1. start online # Note this is from 'cold' # Since the program has been run once we have 'preallocated # our extents. Since we delete and reinsert the same rows # with no indexes, we have exactly the same I/O's occuring # to the same pages on both runs. 2. Run program above once. 3. onmode -c # Force checkpoint 4. onstat -m # Note checkpoint time. Repeat 2,3*4 two more times Timing with CLEANERS = 1 ======================== Program Start End Run Time Checkpoint Times Total ch