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
da...@larkspark.com
972-333-4139
--
----------
D. Dante Lorenso
da...@lorenso.com
972-333-4139
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
