Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ?
For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? Additionally, what performance should I be able to acheive with innodb ? My server is currently Linux RH, 2 CPU's 3.2Ghz, 4 Gig of ram and SCSI drives. I think my queires are optimised, I've had as much as 8000-9000 inserts per second but this is when I start to run into machine load issues. Am I asking to much of the machine ? Marvin. -----Original Message----- From: Josh Chamas [mailto:[EMAIL PROTECTED] Sent: 05 July 2004 20:23 To: Javier Diaz Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question 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] ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
