… id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id… 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… 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…/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_array_wait_event,rw_lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search_build_page_hash_index,btr_search_info_update…_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read_ra nge_first,handler…_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read_ra nge_first,handler…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But …lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock…
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
…/disaster-mysql-5-5-flushing/ Fix InnoDB index locking Figure out InnoDB adaptive locking issue, e.g: http://www.mysqlperformanceblog.com/2010/02/25/index-lock…-transactional 
Make InnoDB page size setting per tables and per index Specify location of InnoDB tables (e.g: disk or SSD…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… even MySQL 5.1. I surely would like to see it sooner at least in more flexible community version. Relaxing Locks Row… where clause (think for example about running not-indexed UPDATE which has to lock all rows in Innodb right now) I however… areas in Innodb than buffer-pool which had it locking relaxed in latest MySQL 5.0 Hopefully the patches he has provided…
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… updating indexes by insertion. Let’s see if having more secondary indexes in the table makes any difference: mysql> SET expand_fast_index_creation… InnoDB picks such an index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug #49120 Fast Index Creation page in…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 10 Comments
… your mysql session, set /tmp/lock_waits as your pager and let’s see if there are any lock waits: mysql> pager /tmp/lock_waits PAGER set to ‘/tmp/lock_waits’ mysql> show innodb status\G ——- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 72 index `GEN_CLUST_INDEX` of table…
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 MySQL. Added the…
