?? Are table fragments going to work for me ??
Posted in 1997
I am preparing an 140GB database using Dynamic Server 7.2. We need the database available 100% of the time. We will use it 24x7x365. We need to store the last 25 months; the oldest month needs to be archived to tape and deleted from each table to make room for the next month. I think that I should create a fragment for each month for each table. When the oldest month needs to be archived and removed, I would detach the fragment for that month, archive it to tape, clear it out, and set it up for the next month. Simple. However, the Informix documentation says that this is not a good idea (fragmenting by date) because it would cause the current month (likely the most active) to be stored on a single dbspace. The recommended strategy for fragments is to distribute the data across dbspaces so that the reads and writes are more evenly balanced. Our computer (Sequent NUMA-Q) is being configured with RAID-5 (4+1 parity, stripes, and mirrors) We are creating 40 4.4GB dbspaces. My disk reads and writes should already be very balanced. My fragmentation strategy has more to do with being able to archive while keeping the table completely available. Based on my experience with Online 5.0, I do have a concern about having an imbalance in the writes across dbspaces: I found that during a checkpoint that shared memory is flushed back to the dbspaces/chunks? by page cleaners. Each que was assigned its own exclusive cleaner. If a que had more to flush than another, it was prolonging the checkpoint more than if the amounts to flush were equal. By balancing the writes across dbspaces, I was able reduce a 40 second checkpoint to 7 seconds. My new 7.2 database has 25 months of history in more than 25 tables. If the page cleaner behaivor has not changes between 5.0 and 7.2 then I need to distrubute the current month for each table in different dbspaces, to address the needed balancing. If this is not a problem then I would rather put an entire month in a single dbspace, so that each month I could clean a whole dbspace. Any advice would be GREATLY appreciated. Thanks in advance ....Dave Killough