… = 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…
Post: Disaster: MySQL 5.5 Flushing
September 18, 2011 By 27 Comments
…, which InnoDB configured for this hardware innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 1900M innodb_log…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… server 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… large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are seeing individual 16KB paged I/O…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
… is slower than typical directly-attached server-grade RAID storage. Since they have a lot of tables, InnoDB uses over 3.5G… from competing. My chosen strategy for this was to set innodb_flush_method=O_DIRECT. We could also tune the OS, but in my experience…. Why is that happening? Look at SHOW INNODB STATUS: ===================================== 090128 8:29:03 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… unless you’re piping large blobs to Innodb in this case increase it a bit. innodb_flush_log_at_trx_commit=2 If… your application and default which is 8 is decent start innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases…
Post: Effect from innodb log block size 4096 bytes
January 3, 2011 By 18 Comments
…_group=2 innodb_log_file_size=4G innodb_adaptive_checkpoint=keep_average innodb_thread_concurrency=0 innodb_flush_method = O_DIRECT innodb_read_ahead = none innodb_flush_neighbor_pages = 0 innodb_write_io_threads=16 innodb_read…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_io_capacity=500 innodb_max…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…. You do not want OS to cache what Innodb is caching already. Innodb cache is more efficient compared to OS cache because… other things and if you will not make Innodb to bypass OS buffering Innodb TableSpace IO will wipe out cache because it… do anything. On Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be able to select…
Post: SSD, XFS, LVM, fsync, write cache, barrier and lost transactions
March 2, 2009 By 32 Comments
… innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT Actually most interesting one are innodb_flush_log_at_trx_commit=1 and innodb_flush_method = O_DIRECT (I tried also default innodb_flush_method, with the same result), using innodb_flush…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…_group=2 innodb_log_file_size=4G innodb_log_block_size=4096 innodb_thread_concurrency=0 innodb_flush_method = O_DIRECT innodb_read_ahead = none innodb_flush_neighbor_pages = 0 innodb_write_io_threads=8 innodb_read…
