Re: InnoDB and lots of UPDATES

[Heikki Tuuri]

Steven,

- Original Message - 
From: Steven Roussey [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, September 03, 2003 5:31 AM
Subject: InnoDB and lots of UPDATES


 I have a question about InnoDB and how it would handle updates on the
 order of about 3,000-5,000 a second. The UPDATEs update a single record
 on a primary key. In MySQL, it does a table lock thus serializing the
 updates. There are a few selects, though on a couple of orders of
 magnitude less often. The table locks have the potential to cause
 problems at this volume.
 
 So to avoid the table locks, I have considered using InnoDB. However, it
 syncs to disk after every UPDATE and I don't think that will work.

why not run with

innodb_flush_log_at_trx_commit=2

or 0?

 The
 disk is a RAID 10 array of 6 15K drives (3x2).
 
 Any suggestions?
 
 --steve-

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: InnoDB and lots of UPDATES

[Steven Roussey]

 Use transaction:
 
 begin
 update ...
 update ...
 ...
 update ...
 commit;
 
 This way you will only have a syncs to disk at every commit instead of
every
 update.

This won't help -- I'm not doing a batch process. Each update is coming
from a different connection...

--steve-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnoDB and lots of UPDATES

[Steven Roussey]

I have a question about InnoDB and how it would handle updates on the
order of about 3,000-5,000 a second. The UPDATEs update a single record
on a primary key. In MySQL, it does a table lock thus serializing the
updates. There are a few selects, though on a couple of orders of
magnitude less often. The table locks have the potential to cause
problems at this volume.

So to avoid the table locks, I have considered using InnoDB. However, it
syncs to disk after every UPDATE and I don't think that will work. The
disk is a RAID 10 array of 6 15K drives (3x2).

Any suggestions?

--steve-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]