List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 16 2000 2:01pm
Subject:Using all threads
View as plain text  

>>>>> "Steven" == Steven Roussey <sroussey@stripped> writes:

Steven> Hi all,
Steven> We use delayed insert for logging. Bizarre things happen every once in a
Steven> while. I once saw MySQL use all the threads for insert delayed handlers, but
Steven> I can not confirm that is what happened to us today.

Steven> MySQL has died every couple of hours on us today, and then I noticed
Steven> something went wrong on a cron job in the morning.

Steven> Cron job does this:

Steven> Renames logtable to logtable_20000114.
Steven> Create new logtable.

Which MySQL version are you using?
If you are using INSERT DELAYED on a linux system, you need one off the latest 
MySQL version as these fixes a bug in Linuxthread.
Note also that in earlier MySQL version ALTER TABLE combinate with
DELAYED didn't always work properly;  This should be ok in the 3
latest versions.

Anyway, if you are using MySQL 3.23.8 then and mainly do INSERT +
SELECT, you probably don't need to do INSERT DELAYED anymore as SELECT
and INSERT can now be run at the same time if you don't have any holes
after deleted rows in the table (when the holes are filled with new
rows, the inserts will again be run concurrently with SELECTS.)

Do you get any problems (execpt for performance problems) when not

Steven> ...

Steven> Today something went wrong when creating the new log table and I did not
Steven> notice its absence. I think the insert delayed handlers were filling up or
Steven> something for all these inserts for a table that does not exist until the
Steven> there were no more connections available. Does this sound plausible? Is this
Steven> the correct behavior?

I have never tested INSERT DELAYED when you rename the table under
it's feats;  I don't think this will work properly;  MySQL should not
crash, but you will loose all queued rows.

The only way to fix this now, is to ensure that when you rename the
log file you don't have any queued rows in the delay handler.

I shall look at doing an atomic multi-table-rename next week to make
it possible to drop a new table in place of an old one in one command.

Using all threadsSteven Roussey16 Jan
  • Re: Using all threadsSasha Pachev16 Jan
    • RE: Using all threadsSteven Roussey16 Jan
      • Re: Using all threadsBrian T. Allen16 Jan
  • Using all threadsMichael Widenius16 Jan