IDS Forum

Re: Table Fragmentation

Posted By: FRANK
Date: Thursday, 4 March 2010, at 2:30 p.m.

In Response To: Re: Table Fragmentation (FRANK)

Joe,

8 million rows is not a big deal. No challenge at all.

I think People prefer new challenges, that may be the reason why the table
with 8 million partitions got born.
Luckily, we can handle it well!

Frank

On Thu, Mar 4, 2010 at 2:16 PM, FRANK <yunyaoqu@gmail.com> wrote:

> Joe,
>
> Very sure!
>
> We have 8 million partitions, NOT 8 million rows. Again 8 million
> PARTITIONS!!
>
> We have 256 rows in the table.
>
> Actually, the performance is getting better now....
> It only takes less than 2 seconds to do all types of fragmentation
> operations... no locks need.
> Fast enough?
>
> Frank
>
> On Thu, Mar 4, 2010 at 1:14 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:
>
> > So, let me understand you here, you have a total of 256 rows, inserted
> into
> > a
> > table with 8 million partitions? You sure you don't mean that you have 8
> > million rows in a table with 256 partitions?
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > FRANK
> > Sent: Thursday, March 04, 2010 12:06 PM
> > To: ids@iiug.org
> > Subject: Re: Table Fragmentation [19220]
> >
> > Plugge, Joe R,
> >
> > We have a table that has 8 million partitions ( lot more than you have !)
> > and it has no more than 256 rows in life time. It only takes 3 seconds to
> > do all types of fragment manipulations.
> >
> > Do you want to know how we did it?
> >
> > Franks
> >
> > On Wed, Mar 3, 2010 at 4:21 PM, Plugge, Joe R. <JRPlugge@west.com>
> wrote:
> >
> > > We have this table that even when totally empty, it chews through tons
> of
> > > locks on the detach:
> > >
> > > CREATE RAW TABLE monitor_min_sum (
> > >
> > > client INTEGER NOT NULL,
> > >
> > > program INTEGER NOT NULL,
> > >
> > > site VARCHAR(25,0),
> > >
> > > phone CHAR(10) NOT NULL,
> > >
> > > calldate DATETIME YEAR TO MINUTE NOT NULL,
> > >
> > > calls INTEGER NOT NULL
> > > )
> > > FRAGMENT BY EXPRESSION
> > >
> > > PARTITION p201003050700 ((calldate >= datetime(2010-03-05 07:00) year
> to
> > > minute ) AND (calldate <= datetime(2010-03-05 07:59) year to minute ) )
> > IN
> > > dbs3,
> > >
> > > PARTITION p201003050600 ((calldate >= datetime(2010-03-05 06:00) year
> to
> > > minute ) AND (calldate <= datetime(2010-03-05 06:59) year to minute ) )
> > IN
> > > dbs3,
> > >
> > > PARTITION p201003050500 ((calldate >= datetime(2010-03-05 05:00) year
> to
> > > minute ) AND (calldate <= datetime(2010-03-05 05:59) year to minute ) )
> > IN
> > > dbs3,
> > >
> > > PARTITION p201003050400 ((calldate >= datetime(2010-03-05 04:00) year
> to
> > > minute ) AND (calldate <= datetime(2010-03-05 04:59) year to minute ) )
> > IN
> > > dbs3 .......
> > >
> > > ..... about 800 PARTITIONS ...
> > >
> > > EXTENT SIZE 64 NEXT SIZE 64 LOCK MODE ROW;
> > >
> > > CREATE UNIQUE INDEX uix_monitor_min_sum ON monitor_min_sum (
> > >
> > > client ASC,
> > >
> > > program ASC,
> > >
> > > site ASC,
> > >
> > > phone ASC,
> > >
> > > calldate ASC
> > > ) USING btree;
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Kern
> > > Doe
> > > Sent: Wednesday, March 03, 2010 3:07 PM
> > > To: ids@iiug.org
> > > Subject: Re: Table Fragmentation [19197]
> > >
> > > As always, this is a very informative explanation I should take notes
> > too.
> > > Thanks Art.
> > > Emiliano, re-strategize your partition now, think ahead for the last
> > > partition
> > > scheme "partpositionnew". I assume you will also partition by month
> same
> > > way
> > > as the old ones.
> > >
> > > For any reason where you still want to try timing your detach process
> > with
> > > the
> > > old partition scheme, try to turn pdq on, it should speed up a little
> > bit.
> > >
> > > set pdppriority 50;
> > > alter fragment ...
> > >
> > > ----- Original Message ----
> > > From: Art Kagel <art.kagel@gmail.com>
> > > To: ids@iiug.org
> > > Sent: Wed, March 3, 2010 2:25:50 PM
> > > Subject: Re: Table Fragmentation [19189]
> > >
> > > Emiliano,
> > >
> > > Detaching a fragment should generally be very fast, as others have
> > > indicated. However, if you have a non-fragmented index or an index that
> > is
> > > fragmented on a different strategy, then detaching the fragment will
> > cause
> > > the entire index to have to be rebuilt from scratch (which is faster
> than
> > > if
> > > the engine just went through and deleted all of the keys you are about
> to
> > > detach). That operation is likely what's slowing you down.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > See you at the 2010 IIUG Informix Conference
> > > April 25-28, 2010
> > > Overland Park (Kansas City), KS
> > > www.iiug.org/conf
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other
> > > organization with which I am associated either explicitly, implicitly,
> or
> > > by
> > > inference. Neither do those opinions reflect those of other individuals
> > > affiliated with any entity with which I am affiliated nor those of the
> > > entities themselves.
> > >
> > > On Wed, Mar 3, 2010 at 10:21 AM, Kern Doe <kern_doe@yahoo.com> wrote:
> > >
> > > > Assuming you have your table partitioned properly by month per your
> > > saying,
> > > > correct, it should take seconds (or at least in my experience). I
> think
> > > the
> > > > one hour you encountered was mostly for waiting for an available
> > > exclusive
> > > > lock - which may be hard for an active large table.
> > > > You may want to try timing again on a real test environment and make
> > sure
> > > > there is absolutely no access to that table.
> > > > Kern --
> > > >
> > > > ----- Original Message ----
> > > > From: Emiliano Romero <eromero@sitrack.com>
> > > > To: ids@iiug.org
> > > > Sent: Wed, March 3, 2010 8:53:52 AM
> > > > Subject: Table Fragmentation [19170]
> > > >
> > > > Hi! I have an historic table (5 months) with about 30 millions record
> > > > per month. Our primary problem is that deleting old data takes too
> long
> > > > (we have some indexes in that table). So I start making some tests
> with
> > > > fragmentation.
> > > > I fragment my table with a date expression (monthly), using
> partitions,
> > > > so here are some question I have about fragmentation.
> > > >
> > > > - It takes like 1 hour to detach a 30 million row partition (1
> month).
> > > > Are these normal times? Any idea how can I get better times with that
> > > > amount of data? Everywhere I read people talk about seconds to detach
> a
> > > > partition.
> > > >
> > > > - Attaching and Detaching result in an exclusive lock in my table. Is
> > > > there some way to avoid an exclusive lock?
> > > >
> > > > I use IBM Informix Dynamic Server Version 11.50.FC5
> > > >
> > > > Thanks and Regards
> > > >
> > > > Emiliano Romero
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001517447dbadfe0450480ea7247
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --000e0cd5cd6a75a1f20480fd74fd
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --00504502ad5f0d395c0480fe70bb
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--000e0cd14b189a950f0480fea046

Messages In This Thread

• Table Fragmentation
  Emiliano Romero -- Wednesday, 3 March 2010, at 8:53 a.m.
  • Re: Table Fragmentation
    Kern Doe -- Wednesday, 3 March 2010, at 10:21 a.m.
    • Re: Table Fragmentation
      Emiliano Romero -- Wednesday, 3 March 2010, at 10:58 a.m.
    • Re: Table Fragmentation
      Emiliano Romero -- Wednesday, 3 March 2010, at 10:59 a.m.
      • Re: Table Fragmentation
        John Miller iii -- Wednesday, 3 March 2010, at 12:02 p.m.
        • Re: Table Fragmentation
          Emiliano Romero -- Wednesday, 3 March 2010, at 12:06 p.m.
          • RE: Table Fragmentation
            Plugge, Joe R. -- Wednesday, 3 March 2010, at 12:10 p.m.
      • Re: Table Fragmentation
        Kern Doe -- Wednesday, 3 March 2010, at 2:58 p.m.
        • Re: Table Fragmentation
          Emiliano Romero -- Wednesday, 3 March 2010, at 3:21 p.m.
    • Re: Table Fragmentation
      Art Kagel -- Wednesday, 3 March 2010, at 2:25 p.m.
      • Re: Table Fragmentation
        Kern Doe -- Wednesday, 3 March 2010, at 4:07 p.m.
        • RE: Table Fragmentation
          Plugge, Joe R. -- Wednesday, 3 March 2010, at 4:21 p.m.
          • Re: Table Fragmentation
            FRANK -- Thursday, 4 March 2010, at 1:06 p.m.
            • RE: Table Fragmentation
              Plugge, Joe R. -- Thursday, 4 March 2010, at 1:14 p.m.
              • Re: Table Fragmentation
                FRANK -- Thursday, 4 March 2010, at 2:16 p.m.
                • Re: Table Fragmentation
                  Art Kagel -- Thursday, 4 March 2010, at 2:29 p.m.
                • Re: Table Fragmentation
                  FRANK -- Thursday, 4 March 2010, at 2:30 p.m.
                  • RE: Table Fragmentation
                    Plugge, Joe R. -- Thursday, 4 March 2010, at 2:44 p.m.
                    • Re: Table Fragmentation
                      FRANK -- Thursday, 4 March 2010, at 2:58 p.m.
                      • RE: Table Fragmentation
                        Plugge, Joe R. -- Thursday, 4 March 2010, at 3:17 p.m.
                    • Re: Table Fragmentation
                      Kern Doe -- Thursday, 4 March 2010, at 4:04 p.m.
                • Re: Table Fragmentation
                  Art Kagel -- Thursday, 4 March 2010, at 2:31 p.m.
  • Re: Table Fragmentation
    FRANK COMPUTER -- Saturday, 27 March 2010, at 1:50 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.