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 the same information and app for both servers. They are exactly the same, just one of them is SSD and another is HARD.

When I run a simple script that inserts 1,000,000 rows into the database they take a similar time to run. Sometimes HARD is faster!

I want to know does MySQL's MyISAM use any caching, so I can get the 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

• Ata: WOW , I think you have great mind on Database ! Do you have any book on performance ?
• Ata: 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 ?