Javier Diaz wrote:
Hi
We have changed all our tables to InnoDB and now the server is not able to
handle the load, even when we are not running the SELECTs statements against
these tables yet.
As I mentioned in my email we make a lots of INSERTS and UPDATES in these
tables (more than 3000 per second). So far using MyISAM everything was OK,
but now when we moved the tables to InnoDB (to be able to make Read/Write
operations in these tables) the performance was down completely and the
server can not handle it.
Does anyone have a rough idea when you change from MyISAM to InnoDB how the
performance is affected?
I would appreciate any ideas you can have, we really need this ASAP.
I would suggest load/capacity testing things very carefully in a test
environment before moving your system to InnoDB. InnoDB has very
different locking / disk i/o behavior than MyISAM as you have
discovered. It also seems to use about 2x the disk space for my tables
as it has something like a 19 byte overhead per record in the table.
Like others suggested, make sure you bundle as many transactions as
possible in one commit. Each commit will end up doing a disk write,
so using an auto-commit mode ( without BEGIN WORK ) will result in
one disk write per transaction. If you bundle many SQL statements
in one transaction, you can get a relative performance improvement,
say if you can get an average of 3 insert/updates per transaction,
then you have just increased your performance by a factor of 3
if your operations were disk bound in the first place which is likely
at 3000 inserts/updates per second.
That said, I also found that InnoDB can do some non-intuitive row level
locking that can result in dead locks, so when moving to many SQL operations
per transaction, you also have to test this carefully under load to make
sure that your code does not result in any dead locks.
Regards,
Josh
________________________________________________________________________
Josh Chamas, Founder | NodeWorks - http://www.nodeworks.com
Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com
http://www.chamas.com | Apache::ASP - http://www.apache-asp.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
