… = 1 innodb_log_buffer_size=128M innodb_max_dirty_pages_pct=80 innodb_file_format=barracuda innodb_file_per_table innodb_read_io_threads = 8 innodb_write_io…_instances=256 innodb_buffer_pool_instances=16 # MySQL 5.5 and 5.6 loose-innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash…
Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
April 4, 2013 By 17 Comments
… i converted databases from MyISAM to InnoDB, mysql tunning script gives below results for temp table , server has 4G ram, which…table_size & tmp_table_size) , what values you experts are recomended ? TEMP TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 21559 temp tables…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…sec fetching rows mysql tables in use 1, locked 1 9417 lock struct(s), heap size 915896, 696679 row lock(s) MySQL thread …explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table …
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… if the problem still existed. The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the… Data dictionary to the mysql error log, which looks something like this: =========================================== 090420 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== ————————————– TABLE: name SYS_FOREIGN… root page 47, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb_support_xablogpercona.commanual innodb_sync_spin_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread_concurrencyblogpercona.commanual innodb…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…Innodb table but getting MyISAM because Innodb was disabled for some reason was very error prone gotcha. Note this is as far as MySQL…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_size from information_schema.tables t join information_schema.statistics i using (table_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table… old size. If you use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
…. Using InnoDB’s new Barracuda file format (available since InnoDB plugin 1.1 or MySQL 5.5) you can now leverage table compression by… choose a KEY_BLOCK_SIZE that is the same value of the InnoDB page size of 16KB? As the fine MySQL manual states: This setting may still be useful for tables with many…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… works. I have one table with size 13Gb what was filled during about 2.5 years. The table is: CREATE TABLE `link_out104` ( `domain… is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB each) and size of data 7492026403… the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup – you know whom ask for!
