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

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