…innodb_log_buffer_size=128M innodb_max_dirty_pages_pct=80 innodb_file_format=barracuda innodb_file_per_table innodb_read_io_threads = 8 innodb…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… data because it uses random sampling to update the stats. You may consider disabling innodb_stats_on_metadata and increasing innodb-stats-sample-pages to get more accurate results. In recent Percona Server versions and MySQL 5.6 you can also store innodb stats in…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… he changed the value of innodb_stats_sample_pages from 8 to 256 but with no effect, however I think innodb_stats_sample_pages really would have no effect on the “number of rows estimation”, because page sampling is used to generate…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… 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-sample-pages=256 innodb-stats-update-need-lock=0 innodb-status-file innodb-strict-mode innodb-thread…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_rows_updatedblogpercona.commanual innodb_spin_wait_delayblogpercona.commanual innodb_stats_on_metadatablogpercona.commanual innodb_stats_sample_pagesblogpercona.commanual innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb…
Comment: MySQL on Amazon RDS part 2: Determining Peak Throughput
March 28, 2011 By 4 Comments
… 4 innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_spin_wait_delay 6 innodb_stats_on_metadata ON innodb_stats_sample_pages 8 innodb_strict_mode OFF innodb_support_xa ON innodb_sync_spin_loops 30 innodb_table_locks ON innodb_thread_concurrency 0 innodb…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…sampled over decent period of time. In the very start of printout Innodb will print: ===================================== 060717 3:07:56 INNODB… switch rate in your OS stats. The other important peice of …of pages in buffer pool, number of pages free, pages allocated by database pages and dirty pages…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… may therefore want to run this several days, or just sample different short windows during your normal production peak. In either… 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….statistics i using (table_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) where…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… following query: select table_schema, table_name, page_type, count(*) from information_schema.innodb_buffer_pool_content group by 1, 2, 3….) After a day or so of doing this, I re-sampled the buffer pool contents on the passive server. With all three samples stored in tables on my laptop, I wrote a query against these three sets of stats to find the…
