OBSERVATION #1
Performance implications should become quickly apparent if the index pages of monitor.result
have to experience two things:
- Experience 1) Swapping of the RAM making up the MyISAM Key Cache
- Experience 2) Rotation in and out of the MyISAM Key Cache (sized by key_buffer_size)
Experience 1 is virtually unavoidable. As for Experience #2, it could result in needed index pages being purged out of the MyISAM Key Cache in the face of more recent queries against other MyISAM tables. Those needed index pages more be brought back by querying the corresponding table. The two experiences togther could make for a slower-than-expected query on relatively small tables.
However, you could minimize or neutralize any ill effects of swapping by assigning the index of monitor.result
by creating a dedicated MyISAM Key Cache. It will be a cache that will contain only index pages from monitor.result
.
How do you do that ???
First, recall that you mentioned that the index usage for monitor.result
was 2.29MB. You can create that dedicated key cache with that size with a little headroom, say 2.5MB. Let us create that key cache like this:
SET GLOBAL monitor_result_private_cache.key_buffer_size = 1024 * 512 * 5;
CACHE INDEX monitor.result IN monitor_result_private_cache;
LOAD INDEX INTO CACHE monitor.result;
This will perform the following steps:
- Create the Key Cache
- Assign the Key Cache to the MyISAM Table using LOAD INDEX INTO CACHE
- Load the Index Pages of the Assigned MyISAM Table into its Corresponding Cache
It would conveniently keep the index pages of that table from ever leaving the cache. The only table index pages would leave is if INSERTs into monitor.result
increases the contents beyond 2.5MB. You must choose enough headroom to accommodate many INSERTs into monitor.result
.
OBSERVATION #2
I also noticed the index you laid out for monitor.result
:
PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`)
If any of your queries against monitor.result
resemble something like this:
SELECT resoultion,value FROM monitor.result
WHERE server = 200 AND refid = 50 AND ... ;
You can speed queries by reordering the PRIMARY KEY
CREATE TABLE IF NOT EXISTS `monitor`.`result` (
`server` SMALLINT UNSIGNED NOT NULL ,
`ref_id` SMALLINT UNSIGNED NOT NULL ,
`request` TINYINT UNSIGNED NOT NULL ,
`recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`resolution` TINYINT NOT NULL ,
`value` MEDIUMINT UNSIGNED NOT NULL ,
PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`) )
ENGINE = MyISAM
or adding a UNIQUE index
CREATE TABLE IF NOT EXISTS `monitor`.`result` (
`server` SMALLINT UNSIGNED NOT NULL ,
`ref_id` SMALLINT UNSIGNED NOT NULL ,
`request` TINYINT UNSIGNED NOT NULL ,
`recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`resolution` TINYINT NOT NULL ,
`value` MEDIUMINT UNSIGNED NOT NULL ,
PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`),
UNIQUE KEY uniqndx1 (`server`, `ref_id`, `request`, `recorded`)
ENGINE = MyISAM
If you add a UNIQUE index, you must double the dedicate keycache accordingly.
OBSERVATION #3
You mentioned a SATA drive. Good choice for archival, low-update historical data. Any MyISAM table on a SATA drive which has a dedicated keycache should not be bothered by the index lookup but the data retrival time from the drive would be up to you to benchmark to see if you could live with the running times.
Here is an alternative:
Create an index that has every column
CREATE TABLE IF NOT EXISTS `monitor`.`result` (
`server` SMALLINT UNSIGNED NOT NULL ,
`ref_id` SMALLINT UNSIGNED NOT NULL ,
`request` TINYINT UNSIGNED NOT NULL ,
`recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`resolution` TINYINT NOT NULL ,
`value` MEDIUMINT UNSIGNED NOT NULL ,
PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`, `resolution`, `value`) )
ENGINE = MyISAM
What does this do? It provides data retrieval of entire rows strictly from the index. Combining this with a dedicated keycache, you will essentially has the whole table in RAM. All queries would be fulfilled by the index and never touch the table, REGARDLESS of the table being on SAS, SATA, SSD, or even stone.
UPDATE 2012-01-26 18:18 EDT
Question 1: You may want to look into memcached. I believe that there is a version InnoDB with a memcached plugin. At least, that's what I heard.
Question 2: InnoDB is for transactional tables. If you have archive data, compressed MyISAM tables should fill the bill. In fact, you could look into the ARCHIVE storage engine.
Question 3: Storing an index on disk (MyISAM,InnoDB) is always standard and cannot be changed. You have use special commands or run special queries to preload caches.
Question 4: RAID-10 for high-writes, SSD for high-reads. Watch your disk surface temperatures !!!
Question 5: if the table is strictly for holding historical info, no need for overkill. As long as it is a table rarely read, no need for special caching considerations.
In most cases, crashing a MyISAM table is nothing more than throwing mud on the MyISAM file header. It keeps a running count of the number of times the file has been opened. It increments upon opening and decrements upon closing.
You may want to look at the value for open-files-limit. This is an option that you cannot dynamically set with mysqld running. It can be configured at startup. What is interesting is its explanation in the MySQL Documentation:
Changes the number of file descriptors available to mysqld. You should
try increasing the value of this option if mysqld gives you the error
Too many open files. mysqld uses the option value to reserve
descriptors with setrlimit(). If the requested number of file
descriptors cannot be allocated, mysqld writes a warning to the error
log.
mysqld may attempt to allocate more than the requested number of
descriptors (if they are available), using the values of
max_connections and table_open_cache to estimate whether more
descriptors will be needed.
By default, mysqld sets this values based on the OS and how many maximum open file handles mysqld believes the OS will give to it.
You can take a risk and raise that number in /etc/my.cnf and restart mysql. As mentioned in the MySQL Documentation, a warning will be posted.
Talk to your sysadmin to see if your OS can have its file handle limit raised.
Once you can get it raised, restart mysql and see if the open_files_limit was raised as well.
Best Answer
Plenty of good reasons to move to InnoDB.
MyISAM has no crash recovery mechanism. A power break may render your tables useless. They can be corrupted (I've seen this happen plenty of times) and you may be able to salvage data -- or you may not be able to salvage data (and I've witnessed this as well multiple times). With InnoDB you have crash recovery mechanism.
MyISAM has no "hot backup", with the possible exception of file system/lvm/disk snapshots, which do require you to flush all tables to disk. You say your databases are extremely small, so this may not appeal to you too much; but the time it takes to flush your entire database to disk can be quite long for larger datasets. You have multiple ways of hot-backing up an InnoDB database.
MyISAM has poor index-write performance. Because of its lack of crash recovery mechanism, any write to an idnex page is immediately flushed to disk. I once consulted to a company where they had this MyISAM table being written to intensively -- it was so hard on the disk they had to put it on its very own server. Changed to InnoDB and Voila! All was relaxed: the transaction log takes care of all that for you.
MyISAM is very poor on highly concurrent apps. You can't write to the same table from two connections at the same time. This quickly leads to a bottleneck on heavy traffic sites, for example.
Likewise, once you do get a write lock on a table, no one else is then able to read from it (with a minor exception for specific INSERT types). With InnoDB you can have concurrent writes and non blocking reads.
MyISAM is hardly under any type of development. I don't recall any blog post by Oracle or anyone else saying "we've just improved foobar on MyISAM". InnoDB is under intensive development. You're likely to get new features and bugs fixes in InnoDB; not in MyISAM.
The above off the top of my head. I'm sure plenty more reasons will pop up once I submit; but these provide the general feeling for the disadvantages of MyISAM.
But - you said your databases are extremely small. Why then would it be so time consuming to migrate to InnoDB?