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 <wagnerbianch...@gmail.com>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 <wagnerbianch...@gmail.com> > > 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 <vegiv...@tuxera.be> >> >> 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 <wlga...@gmail.com> 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 <vegiv...@tuxera.be >>>> >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 < >>>> wagnerbianch...@gmail.com >>>> > > 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 <wlga...@gmail.com> >>>> >> >>>> >> > 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
