… SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 57 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock_mode X insert intention waiting Record lock, heap no…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…). What is a gap lock? A gap lock is a lock on the gap between index records. Thanks to this gap lock, when you run… locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock. This lock… bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode X locks rec but not gap RECORD LOCKS space id…
Comment: InnoDB's gap locks
April 5, 2012 By 14 Comments
… the affected index record has been locked but also the gap before and after that record with a shared gap lock preventing the insertion of data to other sessions.” so how to calculate the shared gap lock ? and another question, what the index…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This…) Adaptive hash index 4422068288 (2039977928 + 2382090360) Page hash 127499384 Dictionary cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… problem is index->lock mutex. InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation… global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys…
Comment: Index lock and adaptive search - next two biggest InnoDB problems
February 19, 2012 By 12 Comments
…_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…
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…
