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

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