Search Results for: show index statistics

When Does InnoDB Update Table Statistics? (And When It Can Bite)

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 statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB […]

InnoDB TABLE/INDEX stats

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | rows | estimated number of all rows […]

How (not) to find unused indexes

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.  This method is flawed – here’s the first reason why:

The cardinality of status index is woeful, but provided that the application […]

Google’s user_statistics V2 port and changes

Recently Google published V2 release of patches, one of them user_statistics we use in our releases. New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes: New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc) New command SHOW CLIENT_STATISTICS, which shows statistics per client’s hostname, not per user […]

SHOW INNODB STATUS walk through

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about […]

InnoDB’s multi-versioning handling can be Achilles’ heel

I believe InnoDB storage engine architecture is great for a lot of online workloads, however, there are no silver bullets in technology and all design choices have their trade offs. In this blog post I’m going to talk about one important InnoDB limitation that you should consider. InnoDB is a multiversion concurrency control (MVCC) storage […]