…_buffer_size=512M I would recommend (if you stick with MyISAM) 1500M. See http://mysql.rjweb.org/doc.php/memory For InnoDB, innodb_buffer_pool_size = 5500M might be optimal. (Plus key_buffer_size = 20M) If you switch to InnoDB, keep in mind the…
Post: Adjusting Innodb for Memory resident workload
March 25, 2009 By 11 Comments
… or most of their database (frequently Innodb) fits into memory. If all tables fit in Innodb buffer pool the performance for reads will be quite good however writes will still suffer because Innodb will do a… most optimal solution if you design the system from scratch but it is something which can be done without changing Innodb core…
Post: Real-Life Use Case for "Barracuda" InnoDB File Format
April 23, 2008 By 11 Comments
… new InnoDB file format but in this post I’d like to share some real-life example how compression in InnoDB plugin… huge innodb table with a set of TEXT fields. We’ve been trying to optimize this server before by playing with various innodb… 60Gb+ and all reads from this table were pretty random (buffer pool didn’t help). Few days ago we’ve decided to…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… (100W ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default)). The… jumps in flushing pages. Make the algorithm independent of innodb_io_capacity and innodb_max_dirty_pages_pct. (This is important for… mechanism that will limit the rate of changes in memory. Optimize the flushing of sequential neighbor pages in a way thatÂ…
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance… how to find the optimal combination ? First let me explain what happens on recovery and why large innodb_log_file_size slows… pages are up to date), number of unflushed pages in innodb buffer pool and its size as well as performance of IO subsystem…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… the join optimizations when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is… are the join optimizations when the workload is IO bound. For the purpose of benchmarking IO bound workload, the InnoDB buffer pool size is…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_…Innodb_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548 102948 76882 102672 76832 Innodb…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 10 Comments
… 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… to optimize the Checkpoint age, it is flushing to try to optimize IO and to obey the LRU in the buffer pool. Since buffers can…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…: 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… any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last way too…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…. The flushing neighborhoods feature was created as an optimization for hard drives, since InnoDB tries to combine writing as many pages as…/fio320 innodb_buffer_pool_size=24G innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer…
