… are special cases when this is not the case. CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a…(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from… query plans depending on Innodb ordering of data by primary key inside indexes they can become significantly affected. Optimizer behavior may be…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are… days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to… patches to make Innodb IO scheduling more aggressive which is important for serious IO subsystems. Many Innodb IO settings were optimized on systems…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… which things do not: I used the following simple table for tests: CREATE TABLE `innodb` ( `id` int(10) unsigned NOT NULL, `a` int…`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; “myisam” is same table created with MyISAM storage engine used to show difference: MySQL Optimizer correctly knows Innodb tables is…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
… information: http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/ There are some… like FLUSH TABLES, ALTER, RENAME, OPTIMIZE or REPAIR can cause threads to wait on “Waiting for tables“, “Waiting for table” and “Waiting for table flush…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
…, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a… storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because… it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details…
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 for a query. If a table‘s… InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES…
Post: Real-Life Use Case for "Barracuda" InnoDB File Format
April 23, 2008 By 11 Comments
… only table customer has on this server is one huge innodb table with a set of TEXT fields. We’ve been trying to optimize this server before by playing with various innodb parameters…’ve decided to go further with our optimizations and I’ve tried to convert this table using COMPRESSED row format. This time…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does… some space allocated in system tablespace. So if you create Innodb tables you better store some data in them otherwise there will… optimizations in this space. To keep test alligned to my previous experiments I was running with table_open_cache=64 and tried innodb…
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
…_enter_innodb–; ENTER; } retry: if (entered_thread < innodb_thread_concurrency) { entered_threads++; thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets; ENTER; } if (innodb_thread… less than number of CPUs and you’re using mainly Innodb tables you might be unable to use all of your CPUs… 5.0 innodb_commit_concurrency limits number of threads which can be active inside Innodb kernel at commit stage. The optimal value for…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
… create 30,000 tables in InnoDB. That time is now a memory. We have customers with a lot more tables than a mere… near this many tables in the MySQL test suite. So, in fleshing out the test cases for this and innodb_dict_size…. Writing a simple test program that creates 30,000 tables in a similar InnoDB configuration as default MySQL is pretty easy (easier…
