主页 / dba / 问题 / 19164
Accepted
Abdul Manaf
Asked: 2012-06-13 23:27:39 +0800 CST

设置什么 innodb_buffer_pool 以及为什么..？

  • 772

我有 170GB 的InnoDB索引和数据。

我必须重新调整 innodb_buffer_pool 大小以获得更好的性能。InnoDB 表（索引+数据）的最大表大小为 28GB。

那么 innodb_buffer_pool 的最佳大小应该是多少。

更新

我们将把我们的本地数据库迁移到 ec2，因此将根据 innodb 的当前统计数据设置 RAM，这就是为什么我需要缓冲池的大小，这样我们就可以在那里拥有可用的 RAM。

File Per table 已启用。

我正在使用Linux机器。

mysql innodb

2 个回答

  1. Best Answer

    您拥有的最大表占总数据的 16.47% (28/170)。即使该表被高度写入和高度读取，也并非所有 28G 的表都在某一时刻加载到缓冲池中。您需要计算的是在任何给定时刻在当前 DB Server 上加载了多少 InnoDB 缓冲池。

    考虑到当前加载在当前数据库服务器的 InnoDB 缓冲池中的数据集，这是一种更精细的方法来确定新数据库服务器的 innodb_buffer_pool_size。

    在您当前的 MySQL 实例（您要从中迁移的服务器）上运行以下命令

    SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

    运行公式IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages。

    SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

    如果 IBPPctFull 为 95% 或更多，则应将 innodb_buffer_pool_size 设置为 DB 服务器 RAM 的 75%。

    如果 IBPPctFull 小于 95%，请运行以下公式IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05：

    SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

    IBPSize（以 GB 为单位）的数字是更接近您的实际工作数据集的数字。

    现在，如果 IBPSize 对于最大的 Amazon EC2 RAM 配置来说仍然太大，请为 Amazon EC2 数据库服务器使用 75% 的 RAM。

    • 29

  2. 我将此答案作为 Rolando 下面答案的补充信息提供。

    在服务器投入生产之前

    根据 MySQL 最常使用的最大表计算 innodb_buffer_pool_size。要根据数据库中的大小识别最大的表，您可以使用以下脚本：

    select table_schema, table_name, round(data_length/1024/1024,2) as size_mb 
from information_schema.tables where table_schema like 'my_database' 
order by size_mb desc;


+--------------+-------------------------+---------+
| table_schema | table_name              | size_mb |
+--------------+-------------------------+---------+
| heavybidder     | user                 |   522.55|
| heavybidder     | bid                  |   121.52|
| heavybidder     | item_for_sale        |    10.52|
| heavybidder     | account_user         |    5.02 |
| heavybidder     | txn_log              |    4.02 |
| heavybidder     | category             |    0.02 |
+--------------+-------------------------+---------+

    现在我们知道哪些表在我们的数据库中是最大的，我们需要确定哪些表是最常用的。为此，我会使用像 Jet Profiler (JP) 这样的分析程序来查看哪些表被访问最多。JP 会告诉你哪些表被最频繁地访问。这是JP该部分的屏幕截图

    在此处输入图像描述

    因此，考虑到这一点，我现在知道 user 和 bid 表占用大约 640MB 的磁盘空间，根据 JP，它们被非常频繁地使用，这意味着 MySQL 将把它们的索引和数据存储在缓冲池中，就像 Rolando在他的评论下面提到。

    为了确保 MySQL 有足够的内存来存储我最大和最常用的表的数据，我将 innodb_buffer_pool_size 定义为 640MB。

    还有一些额外的注意事项，但它们不适用于 innodb_buffer_pool_size。

    这是 32 位还是 64 位系统？在 32 位系统中，除非您激活 PAE，否则您将被限制为 4GB。在 Windows 中，这意味着运行 Windows Enterprise 或 Datacenter 版本。

    系统上运行的其他进程需要多少内存？在一个专用的 MySQL 服务器上，我会留出 5% 到 10% 给操作系统。在 Windows 中，您可以使用 Process Explorer 来分析内存使用情况。在 Linux 中，您有 sysstat、free、htop、top 和 vmstat。

    数据库是仅由 Innodb 表组成还是由 Innodb 和 MyISAM 混合组成？如果是两者的混合，那么我将为 key_cache、连接变量、查询缓存等留出内存。您可以稍后在服务器投入生产时计算您的 MyISAM 命中率。

    服务器投入生产后

    Innodb 目前的命中率是多少？

    1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)。

    什么是关键缓存命中率

    1 - (Key_reads/Key_read_requests)

    我通常会尝试使该比率尽可能接近 100%。

    您的表在缓冲池中的适合程度如何

    您还可以通过参考此链接查看您的表数据在 buffer_pool 中的适合程度，该链接提供了一种显示“给定表 (cnt) 的缓冲池中有多少页，其中有多少页是脏的 (dirty) ，以及索引在内存中的百分比是多少 (fit_pct)。" 仅适用于 Percona 服务器

    http://www.mysqlperformanceblog.com/2010/12/09/how-well-does-your-table-fits-in-innodb-buffer-pool/

    • 15

相关问题