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 >> > >
