… row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD LOCKS space id 19 page no 4 n bits 80 index `age` of table… no need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… bit of background – in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that…_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_increment: 1638401 Create_time: 2006-07-12 07:31…
Post: MySQL Slow query log in the table
May 31, 2007 By Leave a Comment
… table with a lot of indexes may be significant overhead, so you can instead create analyze_low_log table of similar structure with needed index… can create stored procedure, something like LAST_SLOW_QUERIES and use it instead, just remember unless you add extra indexes this table this… buffer of slow log queries which is used if log table is locked. One thing which would be rather handy is UDF…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… it is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and bringing box… than straight ALTER TABLE. P.S I do not know why this was not done when support for creating index by sorting was…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(…tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE…read and written. Created pages is empty pages created in buffer pool for…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Comment: MySQL Server Memory Usage
September 1, 2006 By 18 Comments
…_checksum’, ’0′ ‘Com_commit’, ’9558970′ ‘Com_create_db’, ’0′ ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table‘, ’31′ ‘Com_dealloc_sql’, ’0′ ‘Com… ’0′ ‘Com_load_master_data’, ’0′ ‘Com_load_master_table‘, ’0′ ‘Com_lock_tables‘, ’102′ ‘Com_optimize’, ’0′ ‘Com_preload_keys’, ’0′ ‘Com…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > for few hours so can… factors that developers should consider when deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates…
