List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 2 1999 12:36am
Subject:How exactly does INSERT DELAYED work?
View as plain text  
>>>>> "Jules" == Jules Bean <jmlb2@stripped> writes:

Jules> Some of you may have followed a thread here a few days ago where I
Jules> (wrongly) claimed that mysql didn't allow multiple reads on the same
Jules> table.

Jules> The situation that was confusing me into thinking that was like this:

Jules> I have, sometimes, a background process which is firing lots of
Jules> single-row inserts into my database's most important table.  When this
Jules> process is running, if two time-consuming selects come in for that
Jules> table, they will almost inevitably be separated, in the query queue, by
Jules> at least one write.  Since that write is waiting on a write lock, the
Jules> two read queries will not be processed together - the one which is ahead
Jules> of the write will complete, then the write will run, then the second
Jules> will complete.

Jules> Monty suggested that I should use INSERT DELAYED for my inserts, so they
Jules> wait for the reads to finish first.

Jules> So, I have read the manual section on INSERT DELAYED, and now I'm back
Jules> with a question.

Jules> The manual contains this fragment:

Jules> After every delayed_insert_limit rows are written, the handler checks
Jules> whether or not any SELECT statements are still pending. If so, it allows
Jules> these to execute before continuing. 

Jules> Now, that is exactly the behaviour I want, except... it seems to imply
Jules> to me that if I never quite get round to writing the next
Jules> 'delayed_insert_limit' rows, the delayed handler will persist with
Jules> unflushed rows.

Jules> I.e. suppose delayed_insert_limit is 100.  I insert 346 rows in rapid
Jules> succession.  When the handler checks at 100, 200 and 300, there are
Jules> selects still pending.  Then I don't insert any more rows for (say) 20
Jules> minutes.  Do my 346 stay unflushed for this time?  Or is there also a
Jules> timeout?

They stay unflushed for this time if you have running selects the
whole time.  Note that if you issue an INSERT without DELAYED, or do a 
UPDATE or DELETE, then new selects will wait and the INSERT DELAYED
will eventually be scheduled

Jules> Jules

Regards,
Monty
Thread
How exactly does INSERT DELAYED work?Jules Bean1 Sep
  • How exactly does INSERT DELAYED work?Michael Widenius2 Sep
  • Re: How exactly does INSERT DELAYED work?Jules Bean2 Sep
    • Re: How exactly does INSERT DELAYED work?Michael Widenius2 Sep
  • Re: How exactly does INSERT DELAYED work?Jules Bean2 Sep
    • Re: How exactly does INSERT DELAYED work?Michael Widenius2 Sep