… good for raw performance, but there are limitations which MyISAM imposes that are difficult to …innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=…logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… in the AUTOCOMMIT mode are, by definition, read-only transactions, so the …of transactions, its applicability is quite limited. Namely, it requires the list …file_per_table = true innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 max_connections=5000 table_open_cache…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table definition…. what storage engine it is. We can ignore the upper limit on number of storage engines for MySQL and understanding the…
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…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…TABLE_DEFINITION_CACHE | 400 | 1400 | | PORT | 5530 | 5610 | | QUERY_CACHE_TYPE | ON | OFF | | REPORT_PORT | 5530 | 5610 | | PERFORMANCE_SCHEMA_MAX_FILE…
Post: Limiting InnoDB Data Dictionary
February 11, 2009 By 13 Comments
…tables definitions is not limited…table) it is disaster. For 100.000+ tables InnoDB is consuming gigabytes of memory, keeping definition in memory all time after table was once opened…table is used or not on MySQL level. We made it by checking MySQL table_cache. If table is placed in table_cache…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…Opening tables | select sql_no_cache * from other_table limit 1 | +—–+——+———–+——+———+——+—————-+————————————————+ I tried some alternative approaches to trick MySQL into removing smaller files with DROP TABLE…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Definition of foreign key which failed as well as the closest match which was located in parent table…use. Innodb tries to limit thread concurrency allowing only…cache is not enough. This is typically done by calling fsync() for modified files… queue 1 read views open inside InnoDB Main thread process …
