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
