… too much system resources. Here are some specific techniques to use. Do push concurrency too high Many developers will test script…. Unless it is really time critical process I would not use more than 4 parallel processes heavily writing to database. Introduce… with monopolizing replication thread. For example if I need to delete old data instead of DELETE FROM TBL WHERE ts
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
… this blog a dynamic innodb log file RECREATE did NOT seem to work if you are going to delete the log files later (or even do not move them). The end result was: I could only use mysqldump to import all the data into a clean/fresh innodb system! Good luck! Thomas
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
…(0.00 sec) root@localhost:test 14:35:22>delete from t where i=25; Query OK, 1 row…out my insert still be blocked. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207…2909, OS thread id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s),…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ… gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct…://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… supplemental repositories i.e. updates on CentOS. If you are using InnoDB, it is also important that your transaction logs (ib_logfile… able to restore up to before the first DELETE statement above. If you use my LVM snapshot script*, it also saves the… for the position of the DELETE statement so we can skip that after the snapshot restart. Using the below command and some…
Comment: InnoDB's gap locks
April 13, 2012 By 14 Comments
… with an example: “InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all… t; session2> start transaction; select * from t; I delete one row: session2> delete from t where i=1; session> commit; In session… using READ COMMITTED in spite of your chosen transaction isolation level. So lets try to remove the row 1 again: session1> delete…
Comment: InnoDB's gap locks
April 6, 2012 By 14 Comments
… that what he means by “it behaves as if you use READ COMMITTED for all write queries, in spite of your….com/questions/15854/innodb-row-locking-how-to-implement/15864#15864. I haven’t tested it with INSERT/DELETEs, but it should…
Comment: InnoDB's gap locks
April 4, 2012 By 14 Comments
Andy, With READ COMMITTED InnoDB doesn’t take additional locks on the gap, improving the … the gap. Compare this example with the following one using READ COMMITTED: session1> delete from t where i=25; Query OK, 1…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table, in index `par… InnoDB 400″ means thread is running inside Innodb kernel and still has 400 tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads to run inside Innodb…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… Running in innodb_force_recovery mode Innodb becomes read only for data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table… you may wish to use Innodb Recovery Toolkit which is also helpful in cases you’ve want to recover deleted rows or dropped…
