… deck, is now available. You can watch the recording and get the slides here. I got a lot of questions, and… all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that…
Comment: SHOW INNODB STATUS walk through
May 15, 2012 By 40 Comments
… Peter, I have trouble getting the monitores working – i created the different tables for the innodb (Standard, Lock,Tablespace,Table) and the…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a… lock is a lock on the gap between index records. Thanks to this gap lock, when you run the same query twice, you get… to get the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… from getting field list from all tables) Reads though would not be blocked, ie there is no MYISAM like table lock priority… reason (other than code design) for read lock to wait for other read lock in order to be set. As a workaround…
Comment: InnoDB's gap locks
March 29, 2012 By 14 Comments
Peter, The first example is READ COMMITTED. I’ve changed the isolation level to get that example of a phantom read. With REPEATABLE READ the rows and gaps gets locked and then you get the lock wait timeout.
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 26, 2012 By 20 Comments
Peter, by ‘Even with FLUSH TABLES WITH READ LOCK you are getting in some cases a snapshot that is not recoverable’ I… from that. Essentially, we are using FLUSH TABLES WITH READ LOCK, and then run ‘lvcreate -s’ to create a snapshot, mount… using ‘service mysql stop’ instead of FLUSH TABLES WITH READ LOCK to get reliable snapshots from a donor box.
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…_set_client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000… TABLE regularly during normal operation. I also hope if you get some empty tables in your mysqldump –single-transaction backups you… are potential solutions for this problem ? you can use mysqldump –lock-all-tables instead which does not have this problem at…
Comment: ext4 vs xfs on SSD
March 25, 2012 By 23 Comments
… – it’s put a lot more pressure on an exclusive lock by removing other bottlenecks. Hence when we hit contention on… thread count grows. ext4 performance shows that it uses exclusive locking as well, but it is not degrading like XFS is… count – each thread runs at 100% CPU utilsation, and XFS gets up to 3x as much throughput as ext4 does. Other…
Comment: Instrumentation and the cost of Foreign Keys
April 19, 2012 By 9 Comments
So, locking and subsequent blocking is one thing, but as you said, … is written. For example, someone creates an account, that account gets assigned an ID, then a transaction is created which references… benchmarks with and without FKs to see if the additional locking (blocking aside) has any real concern?
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 25, 2012 By 20 Comments
… WITH READ LOCK. 1. copy meta data files, like frm file. 2. copy data files other than InnoDB. 3. get the binlog… do a hot backup without using FLUSH TABLES WITH READ LOCK: 1. do not do any ddl operations in the progress… use InnoDB as our engine, no other engines used. 3. get binlog file and binlog postion information from xtrabackup_binlog_pos…
