List:General Discussion« Previous MessageNext Message »
From:Javier Diaz Date:July 5 2004 5:48pm
Subject:RE: Concurrency Question
View as plain text  
Hi

I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the
information in these tables is important. On the other hand there is nothing
I can do from the point of view of the number of transactions. Each process
run its own set of INSERTs and UPDATEs statements, so I can not reduce the
number of transactions being executed.

Looking to the MySQL documentation:
  >> Since the rotation speed of a disk is typically at most 167
revolutions/second, that constrains the number of commits to the same
167th/second 
  >> if the disk does not fool the operating system

And that we are doing a LOT MORE INSERTs by second,  I'm afraid maybe the
only solution is go back to MyISAM :-(

By the way this figure of 167 revolutions/second is based on what kind of
hard disk?

thanks
Javier

-----Original Message-----
From: Marc Slemko [mailto:identd@stripped]
Sent: 05 July 2004 17:58
To: Javier Diaz
Cc: mysql@stripped; alec.cawley@stripped
Subject: Re: Concurrency Question


On Mon, 5 Jul 2004 16:07:58 +0100 , Javier Diaz <jdiaz@stripped>
wrote:
> 
> 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?

That all depends on how you are using transactions.  If you are trying
to do each of these operations in a separate transaction, then
definitely that will be a problem since transactions inherently have a
certain cost to them since they need to commit changes to durable
storage.

If this is the case, then a horribly ugly "now you don't have
durability any more in your transactions" hack you could try is
setting innodb_flush_log_at_trx_commit to 2, see the docs for details.
 Be warned that doing so means you can loose committed transactions if
the machine crashes.

________________________________________________________________________
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
________________________________________________________________________
Thread
Concurrency QuestionJavier Diaz2 Jul
  • Re: Concurrency QuestionAlec.Cawley2 Jul
  • Re: Concurrency QuestionEgor Egorov6 Jul
RE: Concurrency QuestionJavier Diaz5 Jul
  • Re: Concurrency QuestionMarc Slemko5 Jul
  • Re: Concurrency QuestionJosh Chamas5 Jul
RE: Concurrency QuestionJavier Diaz5 Jul
  • Re: Concurrency QuestionMarc Slemko5 Jul
RE: Concurrency QuestionPaul DuBois5 Jul
RE: Concurrency QuestionMarvin Wright12 Jul
  • RE: Concurrency QuestionJoshua Chamas12 Jul
Re: Concurrency QuestionHeikki Tuuri13 Jul