List:General Discussion« Previous MessageNext Message »
From:Wagner Bianchi Date:November 30 2010 12:09pm
Subject:Re: INSERT DELAYED and logging
View as plain text  
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
>

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