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

David K. Killough (killougd@ix.netcom.com) wrote: : 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. Okay, this is true, but whether this is a real problem for you depends on what you do. A few points: - If you have several tables fragmented by date, you can distribute the I/O by putting the current months of each table on different disks. For example, let's just say you have 24 disks, 24 tables, 2 years worth of data (hey, this is hypothetical, I can make it as easy as I want :) You fragment table1 starting with disk1, ending with disk 24; fragment table2 starting with disk2, ending with disk1, fragment table3 starting with disk3, ending with disk2. Now each of your "current month" fragments is on a different disk. - Besides, you're using striping, so it really isn't all on the same disk anyway. - What you DO lose (maybe) is some of the parallelism. If, in fact, most of your activity is against the current month, then you don't get parallel scans if the current month is all in one dbspace. - Someone else mentioned that you might not get fragment elimination if you use an expression in your fragmentation expression. This might have been a bug in that version; I recommend you try it yourself. - It seems to me that the facility of doing what you want to do (namely archiving your old data and dropping it easily) probably will outweigh any performance degradation you might experience. And you can lessen your degradation by good planning. : 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. With AIO/KAIO, this should no longer be a problem with your page cleaners. It could still be a problem with the speed of your disk, if all the checkpoint page flushing was going to a single disk. However, if you distribute the current months of different tables as mentioned before, and you lower your LRUMAXDIRTY and LRUMINDIRTY to lessen checkpoint flushing (and you're using striping), I don't think this will be a problem. (But if it is, don't quote me ;) June ---- June Tong Informix Software ---- ---- Senior Consultant (650) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.