… innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…going down. What you might see instead is flushing tables regularly or reducing table cache … 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0…Internal hash tables (constant factor + variable factor) Adaptive hash index 4422068288 (2039977928 + 2382090360) Page …
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… this (alas, 8 pages flushed). That is, instead of flushing 8 pages what we would expect to be flushed, InnoDB flushes 1 needed page and… reasons why innodb_adaptive_flushing is not able to keep up. What if we disable flushing of neighbor pages (we have the option innodb_flush_neighbor…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… intensive writes, InnoDB is using LRU list flushing. So why is it important to know what the mix of InnoDB flushing types is in… are flush list bound, then for MySQL 5.1/InnoDB-plugin or MySQL 5.5 the setting of innodb_adaptive_flushing, innodb_max_dirty_pages_pct, innodb_io_capacity, and innodb_log_file…
Post: Which adaptive should we use?
September 15, 2009 By 14 Comments
… = 8 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 0 innodb_ibuf_active_contract = 1
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
… innodb_buffer_pool_size=24G, innodb_log_file_size=2000M (innodb_log_files_in_group=2), and innodb_flush_log_at_trx_commit=2. Also, innodb_adaptive_flushing (ON) / innodb_adaptive_checkpoint (estimate…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… inserts is pretty much insert buffer efficiency. Adaptive hash index is hash index Innodb builds for some pages to speed up row… depending on your innodb_flush_log_at_trx_commit value your log writes may be more or less expensive. If innodb_flush_logs_at… IO submited by Innodb – pages to be flushed via LRU pages – dirty pages which were not accessed long time, flush list – old pages…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 5 Comments
… innodb flush more dirty pages. However, I can’t help but wonder if some tweaks could be made to the page flushing algorithm… tablespaces) *cough ext3 cough*. innodb_adaptive_flushing: An Innodb plugin/5.5 setting that tries to be smarter about flushing more aggressively based on the number of dirty pages and the rate of transaction log growth. innodb_adaptive_flushing_method: (Percona…
Post: Adaptive checkpointing
November 13, 2008 By 9 Comments
… aged dirty blocks and their age nears the max age, InnoDB flushes the dirty blocks with its best, but the oldest “age… checkpoint age. <innodb_adaptive_checkpoint(new patched parameter) & innodb_io_capacity> The last is the case of using innodb_adaptive_checkpoint instead of innodb_max_dirty…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… be performed. But InnoDB‘s adaptive hash indexes require that the prefix is stored in every record R. An adaptive hash index tries… Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty pages per flush. That means that under a heavy write load, a new flush… get good performance is to set innodb_flush_log_at_trx_commit=2, that is, to flush the log to the disk only…
