… of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests… 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: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8 innodb_buffer_pool_instances=1 innodb_adaptive_flushing_method=keep_average innodb…=100000001 -c config/MyConfig.properties -csvstats final-stats.csv -csvstream streaming-stats.csv -D requests=5000000 -D maxtime=…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
…by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel …command line client. Support for InnoDB 5.0 and InnoDB 5.1 builtin has been removed…cannot be removed. Bug fixed #1175860. xtrabackup –stats option would not work with server …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single Innodb node… statistics in a single table with a row for each stat; there are just a few hundred rows. When a player… your hotspots were really bad in standalone Innodb, you might consider relaxing the fsync: set innodb_flush_log_at_trx_commit to…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 7 Comments
… 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… done | INNODB_INDEX_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | index_name | index name | | fields | How many fields the index key has…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… have it in the INFORMATION_SCHEMA.INNODB_INDEX_STATS table and the ‘index_total_pages’ column. A page in Innodb is (usually) 16k, so some… (table_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) where t.table_schema like ‘sakila…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… the data on 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 CONCAT(index_statistics.index_name, index_statistics…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… innodb_sys_tables ON table_id = innodb_sys_tables.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: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go… the stats accuracy along running ANALYZE TABLE after initial table population and when there are significant changes makes sense. For Innodb as index stats… to this Innodb stats are less accurate by nature which means you can allow more data change while your index stats remain as good…
Comment: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… ON innodb_sys_indexes.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…
