MySQL 内存缓冲池 InnoDB Buffer Pool Sizes 深度解析

-- 查询InnoDB缓冲池的大小和使用情况
SELECT variable_name, value
FROM information_schema.GLOBAL_VARIABLES
WHERE variable_name = 'innodb_buffer_pool_size';
 
-- 设置InnoDB缓冲池的大小
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 设置为1GB
 
-- 查看InnoDB缓冲池状态
SHOW STATUS LIKE 'innodb_buffer_pool_pages_%';
 
-- 查看InnoDB缓冲池的配置和使用情况
SELECT
    total_pages,
    free_buffers,
    dirty_pages,
    pending_io_pages,
    pages_used_memory
FROM
    (SELECT
        SUM(page_size) AS total_pages,
        SUM(free_buffers) AS free_buffers,
        SUM(is_dirty) AS dirty_pages,
        SUM(is_old) AS pending_io_pages,
        (SUM(page_size) - SUM(free_buffers)) AS pages_used_memory
    FROM
        information_schema.innodb_buffer_page) AS stats;

这个代码实例展示了如何查询和设置InnoDB缓冲池的大小，以及如何查看其状态和使用情况的一些关键指标。这对于监控和调优MySQL数据库性能至关重要。