…database to see which tables are referenced the most, and which are candidates for dropping… KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_B` ( `id`…table=”parent” column=”parent_id”/> table> tables> If you’re looking for other examples you can find…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… there is approximately 12GB of data in the largest table. You can find the individual SSB query definitions in my previous blog… this flight, Q4.3, which is faster using MyISAM than InnoDB. Like the queries in Flight #3 that are faster using MyISAM, Q4… user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` … use MySQL, for which we at Percona tend to find redundant indexes. … that are engine-dependent, like redundant suffixes for secondary keys in InnoDB….
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…, `TABLE_ID` is our table_id. `ID` here is the index identifier. We need it to find InnoDB pages which belong to the table‘s index. Which one? A table… matter is regardless at what position primary key fields are defined in CREATE TABLE statement internally they always go first in a…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…our boxes. You can find setup/config details …which is probably a bit closer to real life workloads. It’s still the AUTOCOMMIT mode, so SELECTs are…innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… 4096. This, however, is not the true limit. To find out what the actual limit is, we must delve… than NOW()). It also has certain limits which by any modern standard are purely arbitrary. One of those is the… 5.6 you can create a table with a few more columns in InnoDB! This led me on another idea……
Comment: How to recover table structure from InnoDB dictionary
May 8, 2013 By 4 Comments
1) innodb_file_per_table is OFF by default, which means all tables are in ibdata1. 2) if it was HW problem and ibdata1 …/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start . The biggest challenge here is to find index_id for each table – http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
… out which tools work with it and how. There’s still one noticeable omission: pt-table-sync…tries for all operations are configurable. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout …and tcpdump), so you shouldn’t have a problem finding queries to run and compare. pt-query-…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… to be investigated. You might find that there are InnoDB tables whose .frm files exist but have been dropped from InnoDB, for example. (The INFORMATION… desired information about the queries by any means necessary, I find out which are the most likely to give the greatest gains. I…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…are same as you previously could find in SHOW INNODB STATUS, there are however few which were not available before. In SHOW INNODB STATUS many values are…
