Unfortunately, I wasn't able to get permission to show schema for this implementation, so I dropped the thread. (I know I won't get help if I don't show schema).
We are still having the issue, though. We tried Rick's suggestion of shrinking the key_buffer_size--It didn't make any difference.
If you're interested, here's the information you asked for. Also, I should point out that we're loading these large MyISAM tables by staging incoming data through identical (but small) InnoDB loading tables. We do this to preserve transactional functionality in cases where a poorly formatted file is partially fed to our loader. The InnoDB usage is therefore minimal, but we still require the engine.
I think my original question stands, though. Given an infinite amount of memory, and a moderate dataset (say, 10GB), MS SQL server will slowly (and intelligently) cache large chunks of the data/indexes in RAM. It will do this until it doesn't need any more memory, it runs up to just under installed amount of RAM on the machine, or it hits an artificial ceiling set up by the DBA.
Does MySQL offer equivalent functionality, and how does one activate it? At present, my data directory is occupying over 20GB on disk. MySQLd, however, is occupying about just under 2GB of RAM. The server has 10GB of RAM sitting free and ready to go for MySQL, but it's just not being used. I know that this data is occasionally being table scanned, which would imply that it's had ample opportunity to be loaded into cache.
Thanks for your interest,
Paul B. Davis
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 340527
Server version: 5.1.45-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use information_schema;
Database changed
mysql> SELECT COUNT(*) FROM TABLES;
+----------+
| COUNT(*) |
+----------+
| 222 |
+----------+
1 row in set (0.09 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%cache%';
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 4294963200 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_definition_cache | 256 |
| table_open_cache | 512 |
| thread_cache_size | 16 |
+------------------------------+------------+
14 rows in set (0.08 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 536870912 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8589934592 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 6291456 |
| read_rnd_buffer_size | 8388608 |
| sort_buffer_size | 6291456 |
| sql_buffer_result | OFF |
+-------------------------+------------+
12 rows in set (0.08 sec)
mysql>