List:General Discussion« Previous MessageNext Message »
From:Jay Ess Date:December 15 2009 9:59am
Subject:Re: How to not lock anything?
View as plain text  
D. Dante Lorenso 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?
Double buffering :
Have two identical tables.
Update to the non active and when ready make this table the active.
Now do the same updates to the old now nonactive table while the new 
active table can be read pretty much without disturbance.
Make the two tables reside on separate disks if you dont have enough IO.
Sure its dirty but it works.

If you entirally rebuild your datasets from scratch use this approach :
Create an empty table from live table definition (CREATE TABLE tmp 
SELECT * FROM livetable limit 0;)
Now rebuild your dataset to table tmp.
Drop live table.
Rename tmp table to live table name.

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