I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint columns, 1 tinyint column, and 1 timestamp column. The index length usage is very low per row (about 12 bytes), but the database will be used to store a very large amount of data.
So the problem is the server will wind up having less physical RAM than the size of the index_length in MySQL for that table. What are the implications of having that happen? I know in theory Linux can swap the memory to the disk, but will that duplicate the disk usage (since there already exists a .MYI file)? What are the performance implications of not being able to store the entire index in RAM? Can I still expect sub 10ms selects with SATA II drives in RAID 1?
In response to the first comment for more info
My question was more theoretical than practical at the moment. The project I'm working on is well funded enough that technically we can afford the RAM costs, but I'd prefer to know the implications of not having enough RAM to cover the indexes. But anyway, I'll attempt to answer anyway.
Technically the project has unlimited RAM, so the only reason to have less of it is to keep costs down.
The data is stored in MyISAM tables for "historical" storage purposes, but exists in an NDBCluster for the first 24 hours or so (the NDB Cluster uses about 4x the index RAM than MyISAM).
I can certainly upgrade the RAM, but doing so adds a lot of complexity.
The answer to the amount of MB usage for the index is 2.29MB, but it is rather meaningless. Right now I am just testing the index usage for the data structure. The 2.29MB consists of 155,301 rows (about 15.5 bytes a row).
So there is only 1 table I actually care about. The rest of them are very small in size. The structure of the table is as follows:
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`) ) ENGINE = MyISAM
The reasons a "ref_id" column exists is to narrow what the data set is referring to down past the server level. So for instance, we may have stats about a user or a device on a server.
Why I would need so much RAM
It might seem like the table above wouldn't use that much RAM, and for most practical cases, it doesn't. I would like to store as much data as possible. I understand that I can store less data, but I'd like the resolution of the data to be as high as it can be. Disk space is cheap, so I'm not even remotely concerned with that cost, but RAM on the other hand can become expensive. Even though the business model makes it feasible to not have to worry about the RAM to any extent, I'd like to keep costs down wherever possible.
To put it in perspective, I'd like to store say at maximum 100 stats every minute for each server that is monitored. You can see the number of rows gets large quickly with a thousand servers (100×1000×1044×365 = 38,106,000,000). The budget per year on a thousand servers is $120,000 (a lot of RAM), but the whole point is to keep costs down.
Refining the question
I really appreciate the answers that have been provided so far, so I'll just get a little more specific to address my concerns more specifically.
The answers so far have lead me to think I need to just simply do some benchmarking on my own and see what comes of it (development for ya!). So really at this point the "problem" I face is that the RAM usage will inevitably be hundreds of gigabytes.
Question 1) So if I decide to go the route of putting an incredibly large amount of data into RAM, it will need to be spread across a bunch of servers. I do this already with NDBCluster, but NDBCluster uses almost 3 times as much RAM to store the identical data (15 bytes vs about 48 bytes). What is the accepted method for storing that much data in RAM in a cluster of servers? Should I implement some application level system for integrating with a bunch of MyISAM Servers?
Question 2) Is MyISAM even the right choice in Database Engines? I tested a bit with InnoDB and it seemed to use a lot more RAM than MyISAM for handling the index. What about non MySQL solutions?
Question 3) Is storing the index on a disk even worthwhile? At that point should I not even create an index if it won't be in RAM anyway (I seriously doubt it).
Question 4) If I go the route of not putting the data in RAM, what sort of disk setup is recommended for this project? A RAID of SSD's?
Question 5) Is it worthwhile at all to not include the value and resolution column in the index? How much of a waste of CPU time are we talking about assuming the index is on the disk and not in RAM?
Thank you so much any advice, I'll be sure to select an answer once these questions are answered (if possible)