List:General Discussion« Previous MessageNext Message »
From:杨涛涛 Date:December 23 2010 3:16am
Subject:Re: INSERT DELAYED and logging
View as plain text  
Hi.
  I think if there are not some concurrency visitors, you should not use it.
Otherwise, just put it.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0608@stripped



2010/12/1 Wagner Bianchi <wagnerbianchijr@stripped>

> I'll provide it to, bear with me, pls...
>
> Best regards.
> --
> WB
>
>
> 2010/11/30 Johan De Meersman <vegivamp@stripped>
>
> > Interesting, but I feel the difference is rather small - could you rerun
> > with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
> > might be interesting to see.
> >
> > Yes, I'm to lazy to do it myself, what did you think :-p
> >
> >
> > On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi <
> wagnerbianchijr@stripped
> > > wrote:
> >
> >> Friends, I did a benchmark regarding to this subject.
> >> Please, I am considering your comments.
> >> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
> >>
> >> Best regards.
> >> --
> >> WB
> >>
> >>
> >> 2010/11/30 Wagner Bianchi <wagnerbianchijr@stripped>
> >>
> >> Maybe, the table in use must be a table that is inside cache now - SHOW
> >>> OPEN TABLES, controlled by table_cache, I mean.
> >>>
> >>> Well, if the amount of data trasactioned is too small as a simple
> INSERT,
> >>> you don't have to be worried, I suggest. If you partition the table, we
> must
> >>> a benchmark to know the performance relation of a INSERT and compress
> data
> >>> into Archive Storage Engine or the insertion data into a partitioned
> table.
> >>>
> >>> Best regards.
> >>> --
> >>> WB
> >>>
> >>>
> >>> 2010/11/30 Johan De Meersman <vegivamp@stripped>
> >>>
> >>> I would assume that it's slower because it gets put on the delay thread
> >>>> anyway, and thus executes only whenever that thread gets some
> attention. I'm
> >>>> not sure wether there are other influencing factors.
> >>>>
> >>>> I should also think that "not in use" in this context means "not
> locked
> >>>> against inserts", so the MyISAM insert-while-selecting at the end of
> a
> >>>> continguous table may well apply.
> >>>>
> >>>> No guarantees, though - I'm not that hot on this depth.
> >>>>
> >>>>
> >>>>
> >>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades <WLGades@stripped>
> wrote:
> >>>>
> >>>>> What I'm confused by though, is this line.
> >>>>>
> >>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the
> table
> >>>>> is not
> >>>>> otherwise in use."  What's the definition of "in use"?  Does a
> logging
> >>>>> table
> >>>>> do that given that it's pretty much append-only/write-only?
> >>>>>
> >>>>> Waynn
> >>>>>
> >>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman <
> >>>>> vegivamp@stripped>wrote:
> >>>>>
> >>>>> > No, I think it's a good idea to do INSERT DELAYED here -
> it's only
> >>>>> logging
> >>>>> > application, and it's generally more important to not slow
> down the
> >>>>> > application for that. It's only ever into a single table,
> so
> there's
> >>>>> only
> >>>>> > going to be a single delay thread for it anyway.
> >>>>> >
> >>>>> > Archive tables are a good idea, agreed, but I suspect that
> inserts
> >>>>> into
> >>>>> > that are going to be slower than into regular MyISAM
> because of the
> >>>>> > compression, so why not use that overhead to (slightly)
> speed up
> your
> >>>>> > end-user experience instead ?
> >>>>> >
> >>>>> > You can always partition the table based on the log date
> or
> whatever,
> >>>>> if
> >>>>> > your table risks getting too big.
> >>>>> >
> >>>>> >
> >>>>> >
> >>>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
> >>>>> wagnerbianchijr@stripped
> >>>>> > > wrote:
> >>>>> >
> >>>>> >> Well,  analyze if you need to create an excessive
> overhead into
> the
> >>>>> MySQL
> >>>>> >> Server because a simple INSERT. What you must have a
> look is it:
> >>>>> >>
> >>>>> >>   - How much data this connection is delivering to
> MySQL's
> handlers?
> >>>>> >>   - A word DELAYED in this case is making MySQL
> surfer?
> >>>>> >>
> >>>>> >> Perhaps, you are sophisticating something that do not
> need it.
> >>>>> Besides it,
> >>>>> >> analyzing your "log table", I imagine this table can be
> an Archive
> >>>>> table
> >>>>> >> instead of MyISAM. Log tables or history tables can be
> controlled
> by
> >>>>> >> Archive
> >>>>> >> Storage Engine to have more compressed data. Although,
> Archive
> >>>>> Storage
> >>>>> >> Engine only supports SELECT and INSERT. Maybe, a good
> deal to you,
> >>>>> get rid
> >>>>> >> of you INSERT DELAYED:
> >>>>> >>
> >>>>> >>
> >>>>> >>   - ALTER TABLE <tbl_name> ENGINE = ARCHIVE;
> >>>>> >>
> >>>>> >>
> >>>>> >> Best regards.
> >>>>> >> --
> >>>>> >> WB
> >>>>> >>
> >>>>> >>
> >>>>> >> 2010/11/29 WLGades <WLGades@stripped>
> >>>>> >>
> >>>>> >> > I'm adding a table to our site that logs all page
> loads.  In the
> >>>>> past,
> >>>>> >> when
> >>>>> >> > I built this, I used MyISAM and INSERT DELAYED.  I
> went back to
> >>>>> look at
> >>>>> >> the
> >>>>> >> > documentation to see if I should still do this,
> and saw this
> >>>>> (taken from
> >>>>> >> >
> http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
> >>>>> >> >
> >>>>> >> > Note that INSERT DELAYED is slower than a normal
> INSERT if the
> >>>>> table is
> >>>>> >> not
> >>>>> >> > otherwise in use. There is also the additional
> overhead for the
> >>>>> server
> >>>>> >> to
> >>>>> >> > handle a separate thread for each table for which
> there are
> >>>>> delayed
> >>>>> >> rows.
> >>>>> >> > This means that you should use INSERT DELAYED only
> when you are
> >>>>> really
> >>>>> >> sure
> >>>>> >> > that you need it.
> >>>>> >> >
> >>>>> >> > Does that mean that I shouldn't use it if all I'm
> doing is
> INSERT
> >>>>> >> > (essentially an append-only table), with only very
> occasional
> >>>>> SELECTs?
> >>>>> >>  In
> >>>>> >> > addition, the last time I took this approach for
> logging, it
> >>>>> worked well
> >>>>> >> > until the table got to 65M+ rows, when it would
> crash every now
> >>>>> and
> >>>>> >> then.
> >>>>> >> >  I
> >>>>> >> > know I can archive off the table on a per
> month/quarter basis as
> >>>>> well.
> >>>>> >> >
> >>>>> >> > Waynn
> >>>>> >> >
> >>>>> >>
> >>>>> >
> >>>>> >
> >>>>> >
> >>>>> > --
> >>>>> > Bier met grenadyn
> >>>>> > Is als mosterd by den wyn
> >>>>> > Sy die't drinkt, is eene kwezel
> >>>>> > Hy die't drinkt, is ras een ezel
> >>>>> >
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Bier met grenadyn
> >>>> Is als mosterd by den wyn
> >>>> Sy die't drinkt, is eene kwezel
> >>>> Hy die't drinkt, is ras een ezel
> >>>>
> >>>
> >>>
> >>
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>

Thread
INSERT DELAYED and loggingWLGades30 Nov
  • Re: INSERT DELAYED and loggingWagner Bianchi30 Nov
    • Re: INSERT DELAYED and loggingJohan De Meersman30 Nov
      • Re: INSERT DELAYED and loggingWLGades30 Nov
        • Re: INSERT DELAYED and loggingJohan De Meersman30 Nov
          • Re: INSERT DELAYED and loggingWagner Bianchi30 Nov
            • Re: INSERT DELAYED and loggingWagner Bianchi30 Nov
              • Re: INSERT DELAYED and loggingJohan De Meersman30 Nov
                • Re: INSERT DELAYED and loggingWagner Bianchi30 Nov
                  • Re: INSERT DELAYED and logging杨涛涛23 Dec
                    • Re: INSERT DELAYED and loggingAlejandro Bednarik23 Dec