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