I’m doing some statistics tracking using a myisam table and the table itself currently has 8 million rows with a 1.1GB MYD and a 700MB MYI. The server is a dual quadcore with 4GB ram. I have a key buffer size of 1GB. The table experiences frequent updates, writes and selects (an even distribution).
The table itself has 8 rows of mostly integer type except for a couple of char fields. Although I used MyISAM, the combination of select/update/inserts were faster than innodb.
Since the rows are set length, inserts are quite fast. At the same time, I used the delay_key_write option to speed up updates.
However, the index does update often as some of the columns that I sort by update (or get added to) frequently. I assume this invalidates the key buffer. For example, If i do a select count() where name like ‘te%’, the first time is slow, but on following querries its quite fast, even if i use a subpart of the key such as like ‘tes%’. However, 30 minutes from doing this, the initial select is slow again. Also, whenever the select count() query is slow, I see a tremendous amount drive read activity, which I assume reflects the index being loaded into memory.
What’s the best solution here? It seems I ideally should bump up the db server’s RAM and have the entire index file in memory. Should I place the index file on its own ram drive? Ram drives do have an additional file system overhead - I wish a hybrid Heap/MyISAM structure was possible where the entire index was manipulated in ram and the data was on file, requiring the index to be rebuilt on a database startup.
Is MySQL Cluster the best alternative here?