…_memo_contains(mtr, dict_index_get_lock(index), 551 MTR_MEMO_X_LOCK)); 552 } else { 553 mtr_s_lock(dict_index_get_lock(index), mtr); ===> all other queries… lock on RW-latch at 0x2d881108 ‘&new_index->lock‘ 212 Mutex at 0x2d0777a8 ‘&log_sys->mutex’ 803 lock on RW-latch at 0x2d87c708 ‘&new_index->lock…
Comment: Index lock and adaptive search - next two biggest InnoDB problems
February 19, 2012 By 12 Comments
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… | 0.000004 | | init | 0.000008 | | Opening tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0.002255 | | After… about the same time was spent on rebuilding the index using fast index creation. So we have 36 seconds in total which… the execution time is proportional to the number of indexes, with fast index creation the time required to copy the data to…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
… output. “xx-lock on RW-latch at 0x7f2ff40a3dc0 created in file dict/dict0dict.c line 1627″ It is index->lock, viewing the source file (and it may be HISTORY table). This is the lock for each index tree. We may be able to disperse the lock using by the partitioning of…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…_row_length: 61 Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_increment: 1638401 Create_time… see: History list length 5 Total number of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS FOR EACH SESSION… memory: History list length 5 Total number of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS FOR EACH SESSION…
Post: Table locks in SHOW INNODB STATUS
June 8, 2010 By 1 Comment
… table locks instead. I’ve seen various theories ranging from lock escalation to using table locks in special cases, for example when no indexes are defined on the table. None of this is right. In fact Innodb uses Multiple Granularity Locking and there is always lock taken on the whole table before individual locks can be locked. Such locks are called intention lock, hence…
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
…-flushing/ Fix InnoDB index locking Figure out InnoDB adaptive locking issue, e.g: http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search…-transactional 
Make InnoDB page size setting per tables and per index Specify location of InnoDB tables (e.g: disk or SSD…
Comment: SHOW INNODB STATUS walk through
January 12, 2011 By 40 Comments
… for 28.000 seconds the semaphore: S-lock on RW-latch at 0x2ac843e8e770 ‘&new_index->lock‘ a writer (thread id 1265760576) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr/btr0cur.c line 487 Last…
Comment: Pitfalls of converting to InnoDB
March 28, 2007 By 16 Comments
… commits/rollsback. If enough time goes by, you get ER_LOCK_WAIT_TIMEOUT. Moral: keep transactions as short as possible. ER… implicit transaction of an insert/update as well as the index locks (especially foreign keys) acquired. Also, don’t forget that summary… like “update parent set column = column + 1 where primary_key = ##” lock parent’s row until the original statement is completed. Moral…
Comment: Index lock and adaptive search - next two biggest InnoDB problems
March 1, 2010 By 12 Comments
…. To add to alredy named index->lock and adaptive_search lock, there is single rollback segment and single lock on intrenal data dictionary. It is not as bad as for MyISAM table level lock, but named issues do not allow to scale on multi…
Comment: Tools
June 1, 2008 By 31 Comments
… PostgreSQL here) lacks a native way to watch the living locks at time t, in real time, as they appear (ie. to list all active locks (and not only the (misnamed) mysql’s lasts “deadlocks” (which… requests & users holding those active locks, and the duration of those requests, the list of tables/indexes/… locked by those requests, etc. ps…
