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.
