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. Thanks Javier` -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 July 2004 10:42 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question Javier Diaz <[EMAIL PROTECTED]> wrote on 02/07/2004 10:28:32: > We have some tables to record different data of the activity in our website. > The number of INSERT, DELETE and UPDATE operations in these tables is huge > (it could be more than 3000 a second). So far we don't have any speed > problems, all these tables are optimised using the right indexes and > everything is working ok. But now we are writing a Report Tool to get some > stats and figures from these tables. We have been doing a few tests and any > SELECT query taking more than one second or a few simultaneous SELECT, and > we have a real mess, lots of LOCKS. We definitely can not afford to slow > down the web site, and we have been thinking in a few possible solutions > > 1- Create a duplicate once a day of each of the tables we need to connect > from the Report Tool. We can do this in the period of less activity in the > site. > This a safest solution because we will be running all the SELECT's > against the duplicates and there are no risks to cause problems in the site > but we will > have the inconvenience that we can not get latest figures only the > previous days. Sounds like a nasty kludge to me. I really wouldn't be keen on it at all. > > 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good > enough It strikes me that this is what InnoDB is designed for. In your situation, this is the first thing I would try. > 3- Replication of these tables to another server ?? This would also work: it just uses more resources (another computer of nearly equal power) and more development time (scripts to stop and start replication. If you can borrow the replicated machine, you could test the InnoDB solution on that: change the tables on the replicated server to InnoDB and try running your report generator on that. If the replications doesn't fall behind, the main server will probably handle it. Alec ________________________________________________________________________ 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]
