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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
