Re: checkpoint duration

Willie L. Meeks wrote: > > I have a serious problem with checkpoint duration which cause my posting > process to almost double in duration because of the amount of time wasted > on checkpoints. Here is a a sample of the duration take directly from the snip.... > Regards, > > Willie Meeks Hmmmm...Your physical log is 75MBytes big, so when it hits 3/4 full, it's time for a checkpoint (hence the irregularly spaced, though frequent checkpoints). Checkpoint duration is a function of how long it takes to flush dirty pages out to disk(s) and clear the Phys Log. I assume since it's a nonlogged, DSS system, you're loading lots of stuff for later query. If so, then you need to reconfig to optimize for batch loading. I would: - Massively expand the Physical log, so as to trigger checkpoints less often - make it a gig or two at least... Striping will improve bandwidth if available... - Expand PHYSBUFF, say to 4096 - Set LRU's to 127 - for a big system, why bother with less? - Set LRU_MAX_DIRTY to 90 - Let the dirty pages pile up in memory, who cares, if you write them out frequently, there's a good chance you'll have to rewrite them again later. - Set LRU_MIN_DIRTY to 89 - no point in wasting a lot of effort on LRU writes I'd probably bump BUFFERS up and SHMVIRTSIZE down (1.3 GB looks way big....) You could probably benefit by tweaking the PDQ parameters and PSORT_NPROCS environment variable, but I'd do that second. The idea is to plow through the whole load doing few, if any checkpoints, and if you have to, do them with big chunk writes. Force a checkpoint manually when done, it'll probably be a doozy. What are you running on, CPU count and type, and real memory? Are you beating the devil out of one disk, or writing to lots of them? This is a good application for drive arrays with big, static front end cache (i.e. EMC or somesuch...), then you could configure to make virtually all I/O asynchronous, and your app runs at memory speed. Greg