… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are not prefix compressed they have very good compression potential. I also would like to see some stats about compressed tables… days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can…. Note however there is still significant amount of reads (the stats were flushed between query runs) which does not make sense… to accommodate needs of your temporary table. Do not forget about it especially if running only Innodb tables so you may think there…
Comment: InnoDB TABLE/INDEX stats
February 6, 2013 By 7 Comments
I have installed mysql5.6.10, following error is seen in .err InnoDB: Error: Table “mysql”.”innodb_table_stats” not found. When Mysql services is started, after deleting ib log and data files. Please help
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… 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_name, index_name) where t.table_schema like ‘sakila%’ group by t.table…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… INNODB_INDEX_STATS to get the list of unused indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table_name)NOT IN(SELECT…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-rollback-on-timeout innodb-rollback-segments=16 innodb-stats-auto-update=0 innodb-stats-on-metadata=0 innodb-stats…-lock=0 innodb-status-file innodb-strict-mode innodb-thread-concurrency=0 innodb-thread-concurrency-timer-based innodb-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… version (pre-5.1.61) and a single test, innodb_plugin.innodb_bug53674. The test is chosen for no particular reason except…_show.cc:5522) ==9090== by 0x74E4DF: get_schema_tables_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C… ones either show the same issues, show similar issues (other InnoDB stat counters), show non-issues or my analysis errors (like the…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table… number of tmp tables created in memory vs on-disk, percentage of queries that needed full scan, InnoDB IO stats, etc. One thing…
Comment: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
….table_id = innodb_sys_tables.table_id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb_sys_tables…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual innodb_stats_on_metadatablogpercona.commanual innodb_stats_sample_pagesblogpercona.commanual innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb_support_xablogpercona.commanual innodb_sync_spin_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread…
