… As you can see in this case the query self joins the table so we observe 2 table instances (note – same… unlocking it straight after. Not so for Innodb. Unless table is being locked explicitly Innodb “converts” table lock to “no lock” hence…. You almost never will run into problems with Innodb table level locks because innodb will only set intentional level locks for everything…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… on a lot of short write transactions. innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The… to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings you will…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME=’salaries’; +———-+———+ | INDEX_ID | NAME | +———-+———+ | 26 | PRIMARY | | 27 | emp_no | +———-+———+ B) InnoDB…
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
… InnoDB/XtraDB code we invite you to join our development team to work on the following (but not limited to) problems: Fix InnoDB…/09/18/disaster-mysql-5-5-flushing/ Fix InnoDB index locking Figure out InnoDB adaptive locking issue, e.g: http://www.mysqlperformanceblog…-biggest-innodb-problems/ Make some InnoDB tables non-transactional 
Make InnoDB page size setting per tables and per index Specify location of InnoDB tables…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… tables with where clause and joins. It is important for tables which is being read to be Innodb – even if writes are… not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index … show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the…? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse as the statement started… gridlock one way or another. If you’re just using Innodb tables and you’re not actively changing users, stored procedures…
Post: Speaking on San Francisco Meetup 14-Dec : What's new in XtraDB/InnoDB-plugin 5.1+
November 29, 2010 By Leave a Comment
… coming SF Meetup on Dec-14 about new features in InnoDB in MySQL 5.1 and 5.5 and, what is… and do not mind to listen to me, welcome to join!
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql> select straight_join sum(lo_extendedprice… | 3776369 | | INNODB_BUFFER_POOL_READS | 191571 | | INNODB_DATA_PENDING_READS | 0 | | INNODB_DATA_READ | 3140882432 | | INNODB_DATA_READS | 191581 | | INNODB_PAGES_READ | 191570 | | INNODB_ROWS_READ… with innodb_old_blocks_time=0: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder on…
