Re: refragging a table
Gotchya. Thanks.
Hey, I was reading the old performance tuning book, and noticed they made mention of a supposedly EXCELLENT way of fragmenting a serial and or an integer field, using mod.
something like mod(token, 3) = 0 in dbs1
mod (token 3) = 1 in dbs2
Does anyone know where I can get further info on that ?
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Yunyao (Frank) Qu <Yunyao.Qu@noaa.gov>
To: Floyd Wellershaus <fwellers@yahoo.com>
Cc: bozon <curtis@crowson1.com>; informix-list@iiug.org
Sent: Tuesday, July 18, 2006 4:53:39 PM
Subject: Re: refragging a table
Normally, it is NOT a good idea to put all fragments in one dbs.
Distributing fragments into different disks would improve performance.
IDS 10 introduced the "Partition" that allow you to put more
fragments in one dbs to reduce the numbers of dbs needed.
Thanks,
Frank
Floyd Wellershaus wrote:
> Umm, why would I want to put all the fragments in the same dbspace ?
> Doesn't that defeat the main purpose of fragmenting ?
>
>
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
>
> email: fwellers@yahoo.com <mailto:fwellers@yahoo.com>
>
> Home: 703-430-0805
>
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/
>
>
> ----- Original Message ----
> From: bozon <curtis@crowson1.com>
> To: informix-list@iiug.org
> Sent: Tuesday, July 18, 2006 2:44:25 PM
> Subject: Re: refragging a table
>
> I believe the dbspaces need to be there. If you are running on 10 you
> can put all of the fragments in the same dbspace. If you aren't maybe
> this would be a good enough reason to upgrade. ;-)
>
> Floyd Wellershaus wrote:
> > Apparently that table is joined on a lot by that field. That's why
> we did it.
> > I called Informix, and although I don't have an explanation yet,
> they said I would need to totally refragment the table in order to fix
> this.
> >
> > Believe it or not, we are still considering refragging on the token
> field.
> > But, instead of making the last clause simply a token > xxxx, we can
> make the last clause just like the other clauses. This way, all we
> should have to do is add another fragment to put the latest tokens in.
> We'd have to keep an eye on the tokens though.
> >
> > A thought is, can you create a table with enough fragments to last
> 10 years, but don't actually create the dbspaces until we need them or
> do all the dbspaces need to be there at the start ?
> >
> > Also, I notice that my calculations when figuring size didn't pan
> out. The row size is 122. So doing the math, figuring on a 4 byte slot
> entry per page, and a usable page size of 4028, I figured that we can
> fit 31 rows on a page. In actuallity we can fit much more than that.
> > I assume the difference is due to the varchar fields in the table.
> The oncheck -pt must report max page size to include the max if all
> varchars are maxed out, but in reality that rarely happens. I guess we
> need to plan for the worst though, but it's a waste of space, when
> planning out fragments.
> >
> > Thanks for any advice.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ========================
> > -<<Floyd Wellershaus>>-
> > Database Administrator
> > Unix Administrator
> >
> >
> >
> > email: fwellers@yahoo.com
> >
> >
> > Home: 703-430-0805
> >
> >
> > Cell: 703-477-6045
> > ========================
> >
> >
> > http://www.one.org/
> >
> >
> >
> > ----- Original Message ----
> > From: bozon <curtis@crowson1.com>
> > To: informix-list@iiug.org
> > Sent: Tuesday, July 18, 2006 10:11:57 AM
> > Subject: Re: refragging a table
> >
> >
> > Is there a better field to fragment on? Something that is evenly
> > distributed among all of your records now and in the future, It being
> > used in queries a lot is also helpful.
> >
> >
> >
> > Floyd Wellershaus wrote:
> > > We have a table that is fragmented by the token, which is a serial
> datatype.
> > > The last fragment has grown too big so we want to make more fragments.
> > >
> > > Can I just get the max(token) and add a fragment with the expression:
> > > (( token > maxtoken ) AND (token <= some other number) )
> > > or do I have to totally refrag the table ?
> > >
> > > Thanks,
> > > floyd
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ========================
> > > -<<Floyd Wellershaus>>-
> > > Database Administrator
> > > Unix Administrator
> > >
> > >
> > >
> > > email: fwellers@yahoo.com
> > >
> > >
> > > Home: 703-430-0805
> > >
> > >
> > > Cell: 703-477-6045
> > > ========================
> > >
> > >
> > > http://www.one.org/
> > > --0-1694373946-1153217090=:82272
> > > Content-Type: text/html
> > > X-Google-AttachSize: 1328
> > >
> > > <html><head><style type="text/css"><!-- DIV {margin:0px}
> --></style></head><body><div style="font-family:times new roman, new
> york, times, serif;font-size:12pt"><DIV></DIV>
> > > <DIV>We have a table that is fragmented by the token, which is a
> serial datatype.</DIV>
> > > <DIV>The last fragment has grown too big so we want to make more
> fragments.</DIV>
> > > <DIV> </DIV>
> > > <DIV>Can I just get the max(token) and add a fragment with
> the expression:</DIV>
> > > <DIV>(( token > maxtoken ) AND (token <= some
> other number) ) </DIV>
> > > <DIV>or do I have to totally refrag the table ?</DIV>
> > > <DIV> </DIV>
> > > <DIV>Thanks,</DIV>
> > > <DIV>floyd</DIV>
> > > <DIV> </DIV>
> > > <DIV><BR>
> > > <DIV><BR>
> > > <DIV><BR>
> > > <DIV><BR>
> > > <DIV>========================<BR>-<<Floyd
> Wellershaus>>-<BR>Database Administrator<BR>Unix
> Administrator</DIV><BR>
> > >
> <DIV><BR>email:
> <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> > > <DIV>Home:
> 703-430-0805</DIV><BR>
> > >
> <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> > > <DIV><A
> href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV
> <http://www.one.org/%22%3Ehttp://www.one.org/%3C/A%3E%3C/DIV%3E%3C/DIV%3E%3C/DIV%3E%3C/DIV%3E%3C/DIV>>;
> > > <DIV></DIV></div></body></html>
> > > --0-1694373946-1153217090=:82272--
> >
> > _______________________________________________
> > Informix-list mai
Floyd Wellershaus wrote: > Gotchya. Thanks. > > Hey, I was reading the old performance tuning book, and noticed they > made mention of a supposedly EXCELLENT way of fragmenting a serial and > or an integer field, using mod. > > something like mod(token, 3) = 0 in dbs1 > mod (token 3) = 1 in dbs2 > > Does anyone know where I can get further info on that ? > <SNIP> It's a normal hash fragmentation scheme, that's in the Guide to SQL Syntax manual. It does load balance and roughly evenly spread the IOs. It is VERY efficient for rapid data insertion from multiple clients. However, there can be no fragment elimination so much of the savings at query time are traded away for the insert throughput. Art S. Kagel