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?

score 3 · Accepted Answer · edited Jun 15 '20 at 09:05

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

Does MySQL's MyISAM use cache before insert?

1 Answers1

UPDATE 2013-02-19 13:37 EDT