将MySQL innodb数据库加载到内存中 [英] Load a MySQL innodb database into memory

问题描述

我有一个1.9GB的MySQL innodb数据库，由以下命令显示.

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 | 

我的问题是:

1. 这是否意味着如果将innodb_buffer_pool_size设置为2GB或更大，那么整个数据库都可以加载到内存中，因此需要更少的磁盘请求读取次数?

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?

31GB的可用空间是什么意思?

What does the free space of 31GB mean?

如果可以分配给innodb_buffer_pool_size的最大RAM为1GB，是否可以指定要加载到内存中的表，同时保持其他表始终从磁盘读取?

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?

谢谢.

推荐答案

1. 不完全是. InnoDB缓冲池用于缓冲读取和写入.如果您的大部分访问都被读取，则大部分将被缓存并且需要更少的磁盘访问.
2. 可能是这个 bug ，它没有很好的记录，但是我认为data_free是innodb文件内的可用空间(如果您写的内容超过此数目，InnoDB将不得不扩大数据文件).
3. 否，但是InnoDB会自动缓存您最常访问的数据，因此无论如何它都应具有最佳效果.

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.

考虑使用 memcached 作为缓存层，以在需要更高性能时完全消除数据库访问.

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

这篇关于将MySQL innodb数据库加载到内存中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！