At 07:32 PM 12/14/2009, you wrote:
>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
>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?
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.
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.
>D. Dante Lorenso
>D. Dante Lorenso
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1