{"id":14416,"date":"2024-01-09T19:26:54","date_gmt":"2024-01-10T02:26:54","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=14416"},"modified":"2024-01-09T19:43:47","modified_gmt":"2024-01-10T02:43:47","slug":"innodb_flush_method","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/innodb_flush_method\/","title":{"rendered":"innodb_flush_method: Configure InnoDB Flush Method"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the InnoDB flush methods used to flush data from memory to disk and how to configure the InnoDB flush method using the innodb_flush_method<\/code> configuration option.<\/p>\n\n\n\n

Introduction to the InnoDB Flush Method<\/h2>\n\n\n\n

The InnoDB<\/a> flush method determines how data flushes from memory to disk. The InnoDB flush method highly impacts performance and data safety.<\/p>\n\n\n\n

MySQL uses the innodb_flush_method<\/code> global variable to determine the InnoDB flush method.<\/p>\n\n\n\n

InnoDB flush methods for Unix-like systems<\/h3>\n\n\n\n

The following table lists the main InnoDB flush methods on Unix-like systems, they can be a text string (fsync<\/code>) or a number (0<\/code>).<\/p>\n\n\n\n

Method<\/th>Number<\/th>Meaning<\/th><\/tr><\/thead>
fsync<\/code> <\/td>0<\/td>This method utilizes the operating system’s fsync()<\/code> function to ensure data is flushed to disk, enhancing data durability. However, it may impact performance due to disk I\/O latency. The fsync<\/code> is the default setting.<\/td><\/tr>
O_DSYNC<\/code> <\/td>1<\/td>This method is similar to O_DIRECT<\/code>, which bypasses the buffer cache but allows for metadata synchronization, providing a balance between durability and performance. It can be an effective choice if the file system supports O_DSYNC<\/code> but not O_DIRECT<\/code>.<\/td><\/tr>
O_DIRECT<\/code> <\/td>4<\/td>This method bypasses the operating system’s buffer cache, writing data directly to disk, potentially improving write performance by eliminating double buffering. It requires a file system with direct I\/O support. This option is available on some GNU\/Linux versions, FreeBSD, and Solaris.<\/td><\/tr>
O_DIRECT_NO_FSYNC<\/code><\/td><\/td>This method utilizes O_DIRECT<\/code>\u00a0for flushing I\/O but omits the\u00a0fsync()<\/code>\u00a0system call after each write operation.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

InnoDB flush methods for Windows<\/h3>\n\n\n\n

InnoDB uses the unbuffered<\/code> flush method on Windows. This method writes data directly to the Windows file system without caching.<\/p>\n\n\n\n

The unbuffered<\/code> flush method can improve performance for write-intensive applications. But it comes with a risk of data loss in case of crashes.<\/p>\n\n\n\n

Checking the InnoDB flush method<\/h2>\n\n\n\n

First, connect to the MySQL server<\/a>:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, check the innodb_flush_method<\/code> variable:<\/p>\n\n\n
show<\/span> global<\/span> variables<\/span> like<\/span> 'innodb_flush_method'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
On Windows, the output will look like:<\/p>\n\n\n
+---------------------+------------+<\/span>\n| Variable_name       | Value      |\n+---------------------+------------+<\/span>\n| innodb_flush_method | unbuffered |\n+---------------------+------------+<\/span>\n1 row in set<\/span> (0.02<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
On Unix-like systems, the output will be:<\/p>\n\n\n
+---------------------+-------+<\/span>\n| Variable_name       | Value |\n+---------------------+-------+<\/span>\n| innodb_flush_method | fsync |\n+---------------------+-------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting the InnoDB Flush Method<\/h2>\n\n\n\n
First, open the MySQL configuration file<\/a> and modify the InnoDB flush method:<\/p>\n\n\n
innodb_flush_method = O_SYNC<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to MySQL server<\/a>:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, show the variable innodb_flush_method<\/code>:<\/p>\n\n\n
show<\/span> global<\/span> variables<\/span> like<\/span> 'innodb_flush_method'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
\r\n+---------------------+---------+\r\n| Variable_name       | Value   |\r\n+---------------------+---------+\r\n| innodb_flush_method | O_DSYNC |\r\n+---------------------+---------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)\r<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • InnoDB flush methods determine how MySQL flushes data from memory to disk.<\/li>\n\n\n\n
  • Use the innodb_flush_method<\/code> configuration variable to configure the InnoDB flush method.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, save the file and restart the MySQL server<\/a>.<\/p>\n\n\n\n