New to this forum. I’ve been appreciating Percona’s work for a couple of months and we do get very impressive performance results using XtraDB on a couple of projects So good job !
Right now, our problem is that we can’t understand a particular behaviour of MySQL engine.
Our project is making lots (>2K/sec) of inserts in a specific table.
The problem is that, when running a pretty big SELECT query on another table, INSERTS on the other table get locked
There absolutely no link to our “inserts table” in the SELECT query.
Here is our cnf config :
Quote:
Any idea?
We also noticed very intensive disk usage (compared to MyISAM) on the “inserts” table.
But both these tables are located on the same RAID right?
Kh3ops wrote on Sat, 19 March 2011 18:29
How did you measure it?
The fact that you are going to get more writes when using InnoDB instead of MyISAM is that InnoDB needs to fulfill the Durability part of the ACID transaction requirements, which requires more writes to disk.
And especially since you have the default setting for innodb_flush_log_at_trx_commit (which is the 1 as it says in your commented row in your config file).
The setting 1 means that if you don’t explicitly use transactions then each insert will be a separate transaction and hence it needs to flush the transaction log for each write.
Usually this is not a problem performance wise with a RAID controller with cache, but if your SELECT saturates the RAID controller/disk setup then these extra writes would lock up the inserts since it will not continue until they have been performed.
A common tradeoff to get better performance is to set it to:
innodb_flush_log_at_trx_commit = 2
that limits the writes to disk to about 1 per second, but the drawback is that you can also loose up to one second of transactions in the event of power failure/OS crash.
So good job !