Mysql – Where is the MySQL variable – innodb_flush_method

configurationinnodbMySQLmysql-5

I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES.

But I could not find it in the configuration file for the MySQL Server – my.cnf.
I'm using XAMPP 1.6.3 with PHP (5.2.3) and MySQL (5.0.45). Its MySQL configuration file path is /xampp/mysql/bin/my.cnf
I searched the variable in the file, but no luck.
Where can I find it? Am I just overlooking?

UPDATE
I upgraded to XAMPP 1.8.1. Its configuration file path is /xampp/mysql/bin/my.ini
Still could not find that variable.

Best Answer

The only way for you to have it is to add it to your my.cnf.

Before you can add this option, you must make sure the InnoDB storage engine is available.

Please run these commands

SHOW VARIABLES LIKE 'have_innodb';
SHOW ENGINES;

If you see this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | NO       | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

then you have skip-innodb in your my.cnf. You should comment it out and restart mysql

What you want to see is this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

Add that option to the my.cnf

[mysqld]
innodb_flush_method=O_DIRECT

then run

service mysql restart

Afterwards, you should be able to see it if you run

mysql> show variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)

mysql>

UPDATE 2013-04-12 14:25 EDT

I just realized you are running the Windows version of MySQL. What was I thinking ?

As point by @yercube's comment, innodb_flush_method is not available for Windows. Here is the MySQL Documentation on it:

Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you need this performance enhancement, migrate your data to the Linux version of MySQL.

Related Question