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
dante@larkspark.com
972-333-4139


--
----------
D. Dante Lorenso
dante@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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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
>dante@larkspark.com
>972-333-4139
>
>
>--
>----------
>D. Dante Lorenso
>dante@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=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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. =A0The 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to not lock anything?

--0015174769c8e3b0e1047ac74d1e
Content-Type: text/plain; charset=ISO-8859-1

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=bmurphy@paragon-cs.com
>
>


--
Chief Training Officer
Paragon Consulting Services
850-637-3877

--0015174769c8e3b0e1047ac74d1e--

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-rea d.html

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org