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:
>All,
>
>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 
>records.
>
>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


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. 
See http://dev.mysql.com/doc/refman/5.1/en/update.html
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 
http://dev.mysql.com/doc/refman/5.1/en/handler.html. It does accept a Where 
clause and can return the rows in index order.

Hope this helps.

Mike


>----------
>D. Dante Lorenso
>dante@stripped
>972-333-4139
>
>
>--
>----------
>D. Dante Lorenso
>dante@stripped
>972-333-4139
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
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