List:General Discussion« Previous MessageNext Message »
From:Alejandro Bednarik Date:December 23 2010 7:17pm
Subject:Re: INSERT DELAYED and logging
View as plain text  
杨涛涛
*            Stop top posting.*
*
*
*thanks.*
*
*
On Thu, Dec 23, 2010 at 12:16 AM, 杨涛涛 <david.yang@stripped> wrote:

> 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
> > >
> >
>



-- 


Infrastructure Team

OLX Inc.

Buenos Aires - Argentina
Phone   : 54.11.4775.6696
Mobile : 54.911.50436059
Email: alejandrob@stripped

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