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

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