Where is the MySQL variable - innodb_flush_method?

Question

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.

Answer 1

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.

• Sithu: Thank you, Rolando. I found that have_innodb=YES and #skip-innodb. So, InnoDB engine is already available. When I added innodb_flush_method=O_DIRECT under the [mysqld] section, I could not restart mysql. I got the error "Windows could not start the mysql service on Local Computer. Error 1067 the process terminated unexpectedly". When I removed that line, I could restart the server. I could not add that variable anywhere in the file. Any idea?
• RolandoMySQLDBA: OMG it just hit me. This is a Windows question. For the Windows version of MySQL, O_DIRECT is not supported. It is available under Linux only.
• ypercubeᵀᴹ: Yep: "On Windows, the flush method is always async_unbuffered and cannot be changed."
• RolandoMySQLDBA: Another FacePalm Moment : mamapop.com/wp-content/uploads/2012/02/…