{"id":1854,"date":"2013-01-10T11:21:21","date_gmt":"2013-01-10T11:21:21","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=1854"},"modified":"2015-02-26T14:08:07","modified_gmt":"2015-02-26T14:08:07","slug":"mysql-variable-innodb_flush_method-summarized","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-variable-innodb_flush_method-summarized","title":{"rendered":"MySQL variable innodb_flush_method – summarized"},"content":{"rendered":"

innodb_flush_method variable specifies how InnoDB opens and flushes log and data files. In Innodb optimization, setting the variable innodb_flush_method tweaks the performance most of the times but there are cases of otherwise though. If innodb_flush_method is set to O_DIRECT it’d avoid double buffering and reduce swap pressure and helps improving performance.
\n
\ninnodb_flush_method has been covered at a lot of places and this is a quick reference to myself.<\/p>\n

<\/p>\n

innodb_flush_method:<\/h1>\n

<\/small>
\n\"\"<\/a><\/p>\n

innodb_flush_method On non-Windows systems:<\/strong><\/p>\n

1. fdatasync: <\/strong>
\n– default
\n– InnoDB uses fsync() to flush both data and log files (though fdatasync is specified).
\n– fdatasync() flushes only data while fsync() also flushes metadata along with file’s data ( & thus causes more IO).
\n– fsync() causes double buffering. i.e. operating system buffers at least some of the data in its own cache even though InnoDB is managing it in it’s own buffers.
\n– When innodb_file_per_table is set, writes on multiple tables causes multiple fsync() calls as they cannot be combined in single IO.<\/p>\n

2. O_DIRECT:<\/strong>
\n– Applies only for data files and not logs.
\n– O_DIRECT uses fsync() method to flush files to disk and ensures no double buffering on system.
\n– All reads and writes goes directly to disk.
\n– This setting disbles OS’s double buffering & read ahead while flushing.
\n– Implemented by setting O_DIRECT flag of fcntl() or directio() on Solaris.
\n– If innodb_file_per_table is not set, performance suffers due to serialized IO caused by inode-level mutex.<\/p>\n

3. O_DSYNC:<\/strong>
\n– Applies only to log files and not data files.
\n– Sets O_SYNC flag of open() and makes all writes synchronous.
\n– O_SYNC doesn’t disable double buffering \/ caching at system level.
\n– O_DSYNC flushes only data while O_SYNC flushes both data and metadata.<\/p>\n

innodb_flush_method On Windows systems:<\/strong><\/p>\n

1. async_unbuffered:<\/strong>
\n– unbuffered IO => data has been written \/ flushed before continue next.
\n– InnoDB to use unbuffered I\/O for most writes.
\n– If innodb_flush_log_at_trx_commit = 2, InnoDB uses buffered I\/O to the log files.
\n– This causes InnoDB to use Windows native Async IO for both Reads\/Writes.
\n– For older version of Windows, InnoDB uses it’s own Async IO.<\/p>\n

2. unbuffered:<\/strong>
\n– Same as async_unbuffered except here native async. IO is not used.<\/p>\n

3. Normal:<\/strong>
\n– This option tells to use neither unbuffered IO nor native async. IO.<\/p>\n","protected":false},"excerpt":{"rendered":"innodb_flush_method variable specifies how InnoDB opens and flushes log and data files. In Innodb optimization, setting the variable innodb_flush_method tweaks the performance most of the times but there are cases…\n","protected":false},"author":1,"featured_media":1857,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[358,357,355,354,427,356],"class_list":{"0":"post-1854","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-mysql","8":"category-mysql-articles","9":"tag-innodb","10":"tag-innodb-flush-method","11":"tag-innodb-optimization","12":"tag-innodb_flush_method","13":"tag-mysql","14":"tag-o_direct"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1854"}],"version-history":[{"count":20,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1854\/revisions"}],"predecessor-version":[{"id":2054,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1854\/revisions\/2054"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/1857"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}