Re: How to not lock anything?
On Tue, Dec 15, 2009 at 11:58 AM, Keith Murphy wrote: > Writers do block readers. Just at the row level vs the table level of > MyISAM. It's just much less likely for writers to block readers. No, they don't. Not unless you use an extreme isolation level. InnoDB uses multi-version concurrency to allow readers to work on the previous version while writers are updating. http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
Writers do block readers. Just at the row level vs the table level of MyISAM. It's just much less likely for writers to block readers. keith On Tue, Dec 15, 2009 at 11:57 AM, Perrin Harkins wrote: > On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso > wrote: > > 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. > > Problem solved: use InnoDB. Writers don't block readers and vice versa. > > - Perrin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com > > -- Chief Training Officer Paragon Consulting Services 850-637-3877
Re: How to not lock anything?
On Mon, Dec 14, 2009 at 8:32 PM, D. Dante Lorenso wrote: > 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. Problem solved: use InnoDB. Writers don't block readers and vice versa. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
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
Re: How to not lock anything?
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
RE: How to not lock anything?
> > I am using MySQL currently, but am starting to think that > > maybe I don't > > really need to use an RDBMS I just thought of another thing: It is always faster to insert into a table without indexes than inserting into a table with indexes. I also think it might be faster to clear the table, insert your data, and then put the indexes back. Try those to see if they help. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to not lock anything?
> I am using MySQL currently, but am starting to think that > maybe I don't > really need to use an RDBMS You can use flat file storage, but that would be inefficient. I guess you could try to create a custom system and keep all the records in RAM. That would require some decent coding to make it efficient. How big is your full dataset? If it is small, the NDB engine stores indexed columns in RAM. That might work for you. MyISAM tables is probabably going to be the easiest to implement and it will still be quite fast. Unless you really need something fancy, I think you should go with it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to not lock anything?
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 -- 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=arch...@jab.org
