List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:November 30 2010 9:13am
Subject:Re: INSERT DELAYED and logging
View as plain text  
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

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