…= none innodb_adaptive_flushing_method = keep_average innodb_file_per_table = true innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method = O_DIRECT innodb_log…defaults, as I understand from documentation TokuDB comes with good settings out-of-box, but I am ready to tune something if …
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… “self tuning” or prefer to expose tuning options to be available to the user. HT: I favor self tuning. PZ: I also like self tuning in…. On Windows, InnoDB uses native async I/O, and can perform many read-aheads at the same time. PZ: Another thing I‘d like… the user to tune the checkpointing values. HT: Hmm… we could tune the way InnoDB does the buffer pool flush. I think Yasufumi Kinoshita…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… of InnoDB flushing types is in your workload? There are a couple reasons: It defines a different I/O pattern on your I/O subsystem… the setting of innodb_adaptive_flushing, innodb_max_dirty_pages_pct, innodb_io_capacity, and innodb_log_file_size are important for tuning. Also, upgrading…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… by innodb_file_io_threads variable. For each helper thread you can see thread state – if thread is ready – waiting for i/o… writes 14073669 log i/o‘s done, 10.90 log i/o‘s/second Log section provides information about log subsystem of Innodb. You can… is smaller than allocated buffer pool size so you can tune it down. Even if free pages is zero as in…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…I see are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB… problems, or very slow I/O. In addition to unlikely circumstances, I sometimes see unlikely settings, …configuration alone. [1] I prefer to avoid the word “tuning,” because database tuning is an activity that…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
… as large as we’d like to. To avoid physical I/O all the time we need to get some reasonable amount… chosen strategy for this was to set innodb_flush_method=O_DIRECT. We could also tune the OS, but in my experience that…. Why is that happening? Look at SHOW INNODB STATUS: ===================================== 090128 8:29:03 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… increase innodb_io_capacity and decrease innodb_max_dirty_pages_pct so as to have fewer dirty pages. But I call that tuning by… works, instead of performing 1 random write, InnoDB will perform 8 random writes. I do not have a good explanation for why… the algorithm independent of innodb_io_capacity and innodb_max_dirty_pages_pct. (This is important for systems where I/O capacity may vary…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… random I/O can cause collateral damage: if the disk head is seeking all over for random I/O, then other I/O (including non-random I/O… it? What about InnoDB tuning? You might be wondering, what about InnoDB tuning? What is the best way to choose an innodb_buffer_pool_size… version is: ratio-based tuning is just as wrong for InnoDB as it is for MyISAM. Ratio-based tuning is invalid and wrong…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… are already doing this! 
I think I remember reports that PBXT (at an early stage) out performed InnoDB with INSERTS and UPDATES (but… performance is comparable with InnoDB, I am not entirely convinced that PBXT’s implementation of the log-based I/O is optimal at this…. While I try to only add tuning parameters that are absolutely necessary, PBXT is not specifically designed to be self-tuning, because I believe…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure…innodb_flush_method = O_DIRECT (the followings are XtraDB specific general settings) innodb_ibuf_active_contract = 1 innodb_adaptive_flushing = false innodb_adaptive_checkpoint = estimate <tuning…
