RE: ?? Are table fragments going to work for me ??

Dave Killough wrote: > 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 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 ---- Dave: The manuals say don't do it and the manuals are right... almost. We had this problem on our database - we wanted to use date-based = fragmentation, for exactly the same reasons as you do. Eventually we got = it working right. Vers 1: Using (call_date <=3D "19970930" AND call_date >=3D "19970901") This works OK, but if someone chooses to use DBDATE=3DDMY4-, conversion = errors occur. I believe this is/was a known bug. Vers 2: Using (call_date <=3D MDY(9,30,1997) AND call_date >=3D MDY(9,1,1997)) This solves the conversion error problem, but kills performance. By = having a function within the fragment expression, the optimiser did not = appear to be able to eliminate any fragments from a query. On a system = the size of ours 200+ GB, this was not good enough. Vers 3 (the solution): Since Informix represents dates internally as integers, and all date-based = calculations etc automatically translated to and from these integers, the = answer is to use these values in your fragment expression eg: (call_date <=3D 35702 AND call_date >=3D 35673) The DBspace/Date-Range equivalency is not as obvious from a dbschema = listing as the previous alternatives, but IT WORKS! Fragments are = correctly eliminated from queries (and queries do not need to use these = integers, only the ATTACH/ADD fragment expressions.) I have a little 4GL routine to perform the conversions either way for me, = and if anyone wants it, let me know. But the translation is simple = enough: SELECT TRUNC(DATE("19970930"),0), TRUNC(DATE("19970901"),0) FROM systables WHERE tabid =3D 1; (constant) (constant) 35702 35673 ... and alternatively: SELECT DATE(35702), DATE(35673) FROM systables WHERE tabid =3D 1; (constant) (constant) 19970930 19970901 As far as your load balancing question is concerned, we have a similar = set-up - lots of tables storing lots of history, with the "current month" = fragment for each table offset across the available DBspaces. You can get = a reasonable balance of performance and admin simplicity using this = methodology. I would recommend that you keep one more fragment than you require for = each table. That way at month-end you can have an empty fragment ready to = ADD, then DETACH the oldest, and have a month up your sleeve to archive = off the data at your leisure. Hope this is some help RET +------------------------------------------------------------------------+ | Richard Thomas (DBA) richard_thomas@yes.optus.com.au +61 2 9342 7188 | +------------------------------------------------------------------------+