主页 / dba / 问题 / 271261
Accepted
Rajesh Ranjan
Asked: 2020-07-21 00:09:56 +0800 CST

InnoDB 缓冲池没有显示我在 MariaDB 10.5 中配置的那么多

  • 772

我在具有 4G RAM 和 2 个 CPU 的 Centos7 上使用 MariaDB 10.5。

这是 my.cnf 配置

[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
max_allowed_packet=1G

event_scheduler = ON
innodb_file_per_table=ON
#innodb_file_format=Barracuda
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=6
innodb_log_file_size=1G
innodb_log_files_in_group=1
innodb_log_buffer_size=32M
innodb_log_write_ahead_size=32M
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=6
innodb_flush_method=o_direct


join_buffer_size=128M
sort_buffer_size=20M
read_rnd_buffer_size=128M
datadir=/var/lib/mysql
open_files_limit=10000
default-storage-engine=InnoDB
max_connections = 500

# CACHES AND LIMITS #
tmp-table-size                 = 20M
max-heap-table-size            = 32M
query-cache-type               = 1
thread-cache-size              = 50M
table-definition-cache        = 128M
table-open-cache               = 128M
query_cache_size = 50M
query_cache_limit=128M

但是当我看到使用它的 InnoDB 引擎状态时，SHOW ENGINE INNODB STATUS\G;它会给我以下信息，Buffer pool size 193296但是Total large memory allocated 3288334336

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3288334336
Dictionary memory allocated 19166328
Buffer pool size   193296
Free buffers       179141
Database pages     49186
Old database pages 18176

那么如果Buffer pool size 193296，剩余的内存分配在哪里呢？

谢谢！

mysql mariadb

1 个回答

  1. Best Answer

    随意提高价值是不安全的。它通常会导致交换，从而使性能变差。

    max_allowed_packet=1G  -- dangerously high; wastes RAM; set to 1% of RAM
innodb_buffer_pool_size=3G   --Since you have only 4GB, lower to 2G
innodb_buffer_pool_instances=6  -- only 1 per GB, so: 2
innodb_log_files_in_group=1   -- Use the standard of 2
innodb_log_write_ahead_size=32M  -- waste of RAM; leave at default (8K)
innodb_write_io_threads=128  -- too high
innodb_io_capacity=10000   -- do you have a super-duper SSD?  Else too high
join_buffer_size=128M    -- lower to 1% of RAM
read_rnd_buffer_size=128M  -- lower to 1% of RAM
max_connections = 500    -- the default will save RAM

    至于“缓冲池大小 193296”——即页面。乘以 16K 得到大约 3G。所以它并不“小”。

    • 1

相关问题