…(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into t values(23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:44:37>insert into t values… STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables in use 1…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…’t measuring the cost of index maintenance. Transaction-id is auto-incremented, and so partitioning on transaction-id makes the insertions easy. Basically you… python version that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… running transaction, two identical statements get different values, because some other transaction has modified the table’s rows. For example: transaction1> START TRANSACTION…: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id…` trx id 72C lock_mode X locks gap before rec If you have lot of gaps locks in your transactions affecting the…
Comment: Emulating global transaction ID with pt-heartbeat
April 24, 2012 By 6 Comments
… slaves to master and knowing that mysql does not have a way match the transactions as ‘global transactions id‘.I agree that matching logs…
Comment: Percona XtraDB Cluster Feature 2: Multi-Master replication
April 2, 2012 By 16 Comments
… writesets. The reason for this is keeping track of global transaction ID and caching the writesets for potential IST. This overhead can… comparison, you probably should compare single Galera node with plain mysql with binlog turned on. Having said that, 20% may be…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
March 23, 2012 By 46 Comments
What can you say about http://bugs.mysql.com/bug.php?id=52020 Can you please explain how really locking in INSERT … ON DUPLICATE KEY UPDATE works. As I recall: Lock 1) Transaction…) Transaction 1 tries to locks row for UPDATE (goes into queue) It is reasonable not to release Lock 1, but why MySQL…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…, MariaDB, MySQL) also provide a solution. See “START TRANSACTION WITH CONSISTENT INNODB SNAPSHOT” in https://www.facebook.com/note.php?note_id=390420710932
Post: Emulating global transaction ID with pt-heartbeat
November 4, 2011 By 6 Comments
Global transaction IDs are being considered for a future version of MySQL. A global transaction ID lets you determine a server’s replication position reliably… transaction ID into pt-heartbeat, the replication heartbeat tool included with Percona Toolkit. It has some of the benefits of a global transaction ID, but it works on any version of MySQL. Now why would we want this…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… can cause a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing… log. In MySQL 5.1 and newer with the InnoDB plugin, it’s easy to figure out which transaction is blocking others… ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if…
Post: MVCC: Transaction IDs, Log Sequence numbers and Snapshots
December 19, 2007 By 3 Comments
MySQL Storage Engines implementing Multi Version Concurrency Control have several internal … you to understand MVCC a bit better. Transaction ID As the name says this is transaction identifier. It can be used by the… really need to store transaction ID for the row which is so old it is visible to old running transaction anyway. So what…
