Load a MySQL innodb database into memory

Question

I have a MySQL innodb database at 1.9GB, showed by following command.

SELECT table_schema "Data Base Name"
     , sum( data_length + index_length ) / 1 048 576 
       as "Data Base Size in MB"
     , sum( data_free )/ 1 048 576  
       as "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ; 

+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| database_name      |        1959.73437500 |   31080.00000000 | 

My questions are:

1. Does it mean if I set the innodb_buffer_pool_size to 2GB or larger, the whole database can be loaded into memory so much fewer read from disk requests are needed?

2. What does the free space of 31GB mean?

3. If the maximum RAM can be allocated to innodb_buffer_pool_size is 1GB, is it possible to specify which tables to loaded into memory while keep others always read from disk?

Thanks in advance.

Answer 1

1. Not exactly. InnoDB buffer pool are used to buffer reads and writes. if most of your access is read, most if it will be cached and fewer disks access will be needed.
2. could be this bug, it's not documented very well but I think data_free is the available space inside the innodb files (if you write more than this InnoDB will have to enlarge the data file(s)).
3. no, but InnoDB will cache the data that you access most automatically, so it should have an optimal effect anyway.

consider using memcached as a cache layer to eliminate database access altogether if you need better performance.

Answer 2

1. It's better to worry about having enough memory to cache the indexes in ram, and leave the data on disk. Database performance suffers greatly if the indexes have to be read from disk each time - far more than the overhead of later retrieving the desired data from disk.
2. InnoDB data files are created at a fixed size, with the option to autoextend them (create extra files) if they get full. You can see what the per-file size is with show variables like 'innodb_data_file_path'. The free space reported is how much of the current data files is unused. In your case, you've got 2gigs of data stored in (most likely) 32gigs of InnodB data files, leaving 30gig available.
3. Is there any reason you want to bypass InnoDB's own cacheing logic to pin specific tables in ram? The cache will naturally tend to keep the most frequently accessed data in ram already, and performance will no doubt suffer if you force it to keep less-used data instead of the most popular.