…: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G 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
… blobs. It is easy to check though. Run “FLUSH TABLES” and see whenever memory usage goes down. …INNODB STATUS and look for memory information block, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 132183490560; in additional pool…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 5 Comments
… into Innodb updates buffer pool pages in memory and records page operations in the transaction (redo) log. Behind the scenes those updated (dirty) buffer pool pages are flushed down the to the tablespace. If Innodb stops (read: crashes) with dirty buffer pool pages, Innodb recovery must be…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… in the database and may not be as buffer pool flushes are not predictable. As Innodb is scanning log files from last checkpoint time… have small buffer pool you will have a lot of pages being flushed from buffer pool and so you may end up with flushes being more… page is already in the buffer pool at this stage. Of course it affects time needed to flush buffer pool when recovery is complete but…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
How does Buffer Pool size affects Innodb Performance ? I always expected the effect to be positive, Innodb with large buffer pool to performing better. Including Recovery… so flush list could grow unrestricted without Innodb bothering to flush anything (I only could see reads while recovery was going with large buffer pool…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… affect how InnoDB works internally? Let me show some details. Internally, InnoDB uses two lists for flushing (writing pages from the Innodb buffer pool memory to… in SHOW STATUS, innodb_buffer_pool_pages_flushed, but it shows the sum of flushing from both the LRU list and the flush list. In Percona…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of Innodb on Solaris? HT: Yes, if your ‘write working set’ fits in 16 GB. I mean, if the database buffer pool can buffer… is: set innodb_flush_method=O_DIRECT in my.cnf, to prevent double buffering. Configure as much memory as possible to InnoDB‘s buffer pool. For… the post on this this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL bugs opened about…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool is… other needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit… do anything. On Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default)). The problem here… 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…
