I am using the MEMORY storage engine but MySQL still writes to my disk...Why?

I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project.

For some reason, I have noticed that a large amount of disk write still occurs.

Is this because of Windows swapping the RAM to disk? How can I prevent this from happening?

Edit: Here are my global variables:

```
mysql> show global variables;
+---------------------------------------------------+--------------------------------------------------------------------------------
------------------------------+
| Variable_name | Value
|
+---------------------------------------------------+--------------------------------------------------------------------------------
------------------------------+
| auto_increment_increment | 1
|
| auto_increment_offset | 1
|
| autocommit | ON
|
| automatic_sp_privileges | ON
|
| back_log | 50
|
| basedir | C:/Program Files/MySQL/MySQL Server 5.5/
|
| big_tables | OFF
|
| binlog_cache_size | 32768
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format | STATEMENT
|
| binlog_stmt_cache_size | 4096
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1

You may want to watch how many MEMORY tables you are using. Even with a MEMORY table, there must be an open file handle to the .frm of the MEMORY table. Check your open_files_limit. You may need to increase that as well.

You also have a great disparity between read_buffer_size and read_rnd_buffer_size.

You have read_buffer_size at 64M and read_rnd_buffer_size at 4M.

I have never seen a config with read_buffer_size much bigger than read_rnd_buffer_size

You should make it as follows:

read_buffer_size = 16M
read_rnd_buffer_size = 64M

These things contribute to read I/O, not write I/O.

Here is something to consider:

Do you have other tables using MyISAM ???

Do you run JOIN queries that mix MEMORY tables and MyISAM tables ???

Do any temp tables exceed 128M ??

StackExchange Database Administrators Q#2868, answer score: 6