Does MySQL's MyISAM use cache before insert?

Question

I want to test two different Disks, one of them is HDD and another is SSD

I use exactly same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD.

When I run simple script that insert 1000000 rows in database, They take similar time to run (Some times HARD is faster !)

I want to know does MySQL's MyISAM use any caching, so I can get same time to insert or I have same disk.

If they use cache before insert , how can I disable that to test disk speed .

Answer 1

MyISAM does not cache data at all. It caches only indexes ( See my post What are the main differences between InnoDB and MyISAM? )

You could just set key_buffer_size to something absurbly small, like 8 (the minimum allowed). You may as well disable the query cache while you're at it (Setting query_cache_size to 0).

Just add these lines to /etc/my.cnf

[mysqld]
key_buffer_size = 8
query_cache_size = 0

and run service mysql restart

This will effectively disallow caching anything for MyISAM.

Give it a Try !!!

UPDATE 2013-02-19 13:37 EDT

You just commented:

Also if I understand correctly , you want to say InnoDB use Both INDEX AND Data in memory , so it should have faster speed than MyISAM , but I saw MyISAM insert speed is 10 time faster than InnoDB, why ?

Believe it or not, it is possible to tune MyISAM to outperform InnoDB

• Jul 06, 2012 : MySQL table with 100,000 records queried often
• May 03, 2012 : Which is faster, InnoDB or MyISAM?
• Sep 20, 2011 : Best of MyISAM and InnoDB