List:General Discussion« Previous MessageNext Message »
From:mos Date:December 15 2009 4:05pm
Subject:Re: How to not lock anything?
View as plain text  
At 07:32 PM 12/14/2009, you wrote:
>I am using MySQL currently, but am starting to think that maybe I don't 
>really need to use an RDBMS.  The data I am storing ends up getting 
>indexed with Sphinx because I have full-text indexes for about 40 million 
>I have an "items" table that is heavily updated with 40 million records 
>every 1 or 2 days and I need all those items indexed so they can be 
>searched.  The problem that I'm having is that the table is constantly 
>locked because an insert or delete is being performed.
>I am playing with InnoDB vs MyIsam and have been trying to figure out how 
>to get the best performance.  I actually don't care about dirty reads, 
>however, and wouldn't mind if all the 40 mm records could be 
>read/inserted/updated/deleted without any locking at all.  Are there known 
>solutions for the kind of storage I am looking for?  Anyone have any 
>pointers?  Is there a MySQL Storage Engine designed for this kind of 
>usage, or is there a another server that is commonly used along with MySQL 
>for this type of thing?
>-- Dante

    Here are a couple of recommendation for a MyISAM table.

1) Optimize the table. This gets rid off all of the holes left by deleted 
records. Then when rows are inserted it does NOT use a lock on the table.
2) Instead of actually deleting the rows, update a column Deleted='Y' so 
you don't physically delete the row because this would cause #1 to go back 
to using table locks when rows are inserted. If you can delay flagging 
these rows as deleted for a minute or so, then update these rows to 
Deleted="Y" every few minutes. This means only one lock to the table for 
processing hundreds of rows. You can also look into Low Priority updates. 
3) At night, either delete the rows with "Deleted=Y" and optimize the table 
or copy the table without the "Deleted=Y" to a new table. For 20 million 
rows this should take only a couple of minutes on a fast machine.
4) A little used feature of MyISAM table is the Handler command. It is more 
difficult to use because it is low level, but it allows you to read dirty 
rows from a table without waiting for locks.  See It does accept a Where 
clause and can return the rows in index order.

Hope this helps.


>D. Dante Lorenso
>D. Dante Lorenso
>MySQL General Mailing List
>For list archives:
>To unsubscribe:

How to not lock anything?D. Dante Lorenso15 Dec
  • RE: How to not lock anything?Neil Aggarwal15 Dec
    • RE: How to not lock anything?Neil Aggarwal15 Dec
  • Re: How to not lock anything?Jay Ess15 Dec
  • Re: How to not lock anything?mos15 Dec
  • Re: How to not lock anything?Perrin Harkins15 Dec
    • Re: How to not lock anything?Keith Murphy15 Dec
      • Re: How to not lock anything?Perrin Harkins15 Dec