…, replication etc. Q: Is it possible to move MyISAM to Innodb through plain ALTER TABLE statements? Yes. This is exactly how… execution plan either by updating statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features in new version. Innodb statistics can especially be…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…-current) innodb_io_capacity = 4000 innodb_support_xa = false innodb_file_per_table = true innodb_buffer_pool_size = 16G innodb_read_io_threads = 8 innodb_write… the variable to tune the contention. innodb_stats_update_need_lock = 0 (default 1) It skip the updating statistics which needs the lock. (it… for updates from user threads on the all of other CPUs. XtraDB can increase the purge_threads from the next release. innodb…
Comment: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 7, 2011 By 7 Comments
…_auto_update=0, innodb_stats_on_metadata should also be 0 to disable the feature. I agree with you, knowing the previous statistics surely is useful especially when looking for a decent cardinality for your indexes. @Steve, Adding this options may disable the statistics update…
Comment: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
…_innodb.cc info_low 9684 ret = dict_stats_update(ib_table, stats_upd_option,FALSE); 5 storage/innobase/row/row0mysql.cc row_update_statistics_if_needed 980 dict_stats_update(table…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…’s quite easy to collect statistics about index usage in Percona Server (and others) using the User Statistics patch. By enabling ‘userstat…_schema.tables t join information_schema.statistics i using (table_schema, table_name) join information_schema.innodb_index_stats s using (table…: 27 index_total_size: 442368 … Now we just need to update our droppable_indexes view to use that information: mysql> drop…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…, it is interesting to see what statistic we can get from workload that …COUNT_UPDATE: 8584 SUM_TIMER_UPDATE: 3453842535354 MIN_TIMER_UPDATE: 7587746 AVG_TIMER_UPDATE: 402357649 MAX_TIMER_UPDATE: 78153671549…/sb/ibdata1 EVENT_NAME: wait/io/file/innodb/innodb_data_file COUNT_READ: 118218 COUNT_WRITE…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
…=on –max-requests=0 –max-time=300 run …. per-request statistics: min: 0.68ms avg: 0.88ms max: 306.80ms approx… run again now running the same update statement on the different cluster node: per-request statistics: min: 0.69ms avg: 1.12ms…_name=DPE binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 wsrep_sst_auth…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
… statistics are calculated automatically, check the following blog post for more information: http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/ There are some variables to tune how that statistics are calculated but…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… busy application. Recently I have started the development of an updated version of the patch. The basic idea is the same… temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL currently allows you to see many per-session statistics for operations…: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT COUNT(1) FROM t2; Important…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… disk, if full scan was done, etc) as well as InnoDB statistics (such as IO read operations, the number of unique pages…, enables us to have the statistics available after the query is actually executed, while no such statistics are available for queries that….1956 2.4% 89 0.0022 1.00 0.01 UPDATE wp_options # 7 0x7AEDF19FDD3A33F1 0.1381 1.7% 909 0…
