Re: Physical Log Boundedness...

In article <E54x54.24r@nonexistent.com>, Jacob Salomon <jake@apparel.net> writes >Greg wrote: >> >>To clarify: by bounded, I mean being limited by the ability to write to >>the disk fast enough to not block the CPU waiting for the I/O to >>complete. >> >>> >>>Anybody got some good tricks to relieve Physical log boundedness when >>>doing massive Insert/Update/Deletes??? >>> >>> Thanks - Greg > >Greg, > >try using a huge physical log buffer. How useful this turns out to be >depends on other factors - like checkpoint frequency and general >page-flushing activity. Monitor the average number of pages per flush >with 'onstat -d'. Don't you mean onstat -l pages/io? Also move the phyiscal log out of the root dbspace into a seperate dbspace (Normally called phydbs) which consists of one chunk which ONLY contains the physical log and is on a seperate disk from the rest of the OnLine system. The root dbspace gets accessed a lot to update the reserved pages. Remember these track a) Copy of ONCONFIG file (PAGE_CONFIG) a) Checkpoints (PAGE_1CKPT and PAGE_2CKPT) b) Archives and data replication status (PAGE_1ARCH and PAGE_2ARCH) c) Free space in chunks (First chunk free list page) Make sure that Kernel Async I/O is being used - check $INFORMIXDIR/release/ONLINE* to see if you need kernel patches for it to work. The only other suggestion is to use a Logical Volume Manager to stripe the phyical log across >1 disk with a small stripe size E.g. 2/4K i.e. each flush of the all of the physical log buffers (remember there are 2 physical log buffers) is spread across several disks. I've never tried that but it could help if you get really desperate. > >You can lower general page-flushing activity with a long checkpoint >interval and high values for values for LRU_MAX_DIRTY and LRU_MIN_DIRTY. >However, when you do this, human users on the system will be subject to >periodic freeze-ups, when a checkpoint freezes the buffer pool while >flushing thousands of pages. > >But for massive updates/insert/delete, hey, this is just fine! > -- David Williams