…: ## 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_io…
Comment: Percona Server vs MySQL on Intel 320 SSD
February 23, 2012 By 21 Comments
…. MySQL 5.5 server dirty page flushing respects the innodb_io_capacity setting, Percona server flushing doesn’t. Set innodb_io_capacity too low and… also normally benefit from setting innodb_purge_threads to 1 and, if doing lots of multithreading, setting innodb_buffer_pool_instances to 8 as…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you …is easy to check though. Run “FLUSH TABLES” and see whenever memory …Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages…
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 done…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… what kind of flushing is used. There is a variable 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: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… 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… may want to make MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other performance benefits as…
Post: What is stored InnoDB buffer pool
September 28, 2008 By 8 Comments
…INNODB_BUFFER_POOL_CONTENT; +———–+——-+——–+———+———-+————+———–+————–+————–+—————-+—————–+————–+——————+ | BLOCK_NUM | SPACE | OFFSET | RECORDS | DATASIZE | FLUSH_TYPE | FIX_COUNT | LRU_POSITION | PAGE…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_buffer_pool_pages_datablogpercona.commanual Innodb_buffer_pool_pages_dirtyblogpercona.commanual Innodb_buffer_pool_pages_flushedblogpercona.commanual Innodb_buffer_pool_pages_freeblogpercona.commanual Innodb_buffer_pool_pages_latchedblogpercona.commanual Innodb_buffer_pool_pages…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
… (64GB) with Innodb Buffer pool configured to be just a bit over 20GB. Innodb recovery was taking long hours with reasonably sized Innodb log files… without Innodb bothering to flush anything (I only could see reads while recovery was going with large buffer pool) plus large distinct amount of pages…
Comment: When EXPLAIN estimates can go wrong!
October 15, 2011 By 7 Comments
… extended-status | grep -i innodb_buffer_pool_pages | Innodb_buffer_pool_pages_data | 141 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 10098 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 10239 | After…
