mysql poolsize_mysql参数之innodb_buffer_pool_size大小设置

MYSQL无法启动报错日志，如下：

190801 11:24:29 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended

190801 11:30:39 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

190801 11:30:39 InnoDB: The InnoDB memory heap is disabled

190801 11:30:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins

190801 11:30:39 InnoDB: Compressed tables use zlib 1.2.3

190801 11:30:39 InnoDB: Initializing buffer pool, size = 10.0G

InnoDB: mmap(10989076480 bytes) failed; errno 12

190801 11:30:39 InnoDB: Completed initialization of buffer pool

190801 11:30:39 InnoDB: Fatal error: cannot allocate memory for the buffer pool

190801 11:30:39 [ERROR] Plugin ‘InnoDB’ init function returned error.

190801 11:30:39 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

190801 11:30:39 [ERROR] Unknown/unsupported storage engine: InnoDB

190801 11:30:39 [ERROR] Aborting

190801 11:30:39 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

190801 11:30:39 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended

190801 11:58:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

190801 11:58:24 InnoDB: The InnoDB memory heap is disabled

190801 11:58:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins

190801 11:58:24 InnoDB: Compressed tables use zlib 1.2.3

190801 11:58:24 InnoDB: Initializing buffer pool, size = 10.0G

InnoDB: mmap(10989076480 bytes) failed; errno 12

190801 11:58:24 InnoDB: Completed initialization of buffer pool

190801 11:58:24 InnoDB: Fatal error: cannot allocate memory for the buffer pool

190801 11:58:24 [ERROR] Plugin ‘InnoDB’ init function returned error.

190801 11:58:24 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

190801 11:58:24 [ERROR] Unknown/unsupported storage engine: InnoDB

190801 11:58:24 [ERROR] Aborting

解决办法

需要调整 MySQL 配置文件 my.cnf 中的 “innodb_buffer_pool_size”、”key_buffer_size” 的大小设置，适当的调大内存分配，一般调整为系统内存的一半

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 128M     #设置红色字体数值

innodb_write_io_threads = 4

innodb_read_io_threads = 4

myisam_sort_buffer_size = 16M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 500M

[myisamchk]

key_buffer_size = 16M      #设置红色字体数值

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

######################################################

MySQL [(none)]> show global variables like ‘innodb_buffer_pool_size’;

+————————-+———–+

| Variable_name           | Value       |

+————————-+———–+

| innodb_buffer_pool_size | 134217728 |

+————————-+———–+

1 row in set (0.00 sec)

MySQL [(none)]> show global status like ‘Innodb_buffer_pool_pages_data’;

+—————————————-+——-+

| Variable_name                                  | Value |

+—————————————-+——-+

| Innodb_buffer_pool_pages_data | 684    |

+—————————————-+——-+

1 row in set (0.01 sec)

MySQL [(none)]> show global status like ‘Innodb_buffer_pool_pages_total’;

+—————————————-+——-+

| Variable_name                                  | Value |

+—————————————-+——-+

| Innodb_buffer_pool_pages_total | 8192  |

+—————————————-+——-+

1 row in set (0.00 sec)

MySQL [(none)]> show global status like ‘Innodb_page_size’;

+———————–+——-+

| Variable_name        | Value |

+———————–+——-+

| Innodb_page_size | 16384 |

+———————-+——–+

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

当结果 < 95% 则减少 innodb_buffer_pool_size，

建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)

命令如：SET GLOBAL innodb_buffer_pool_size= 32423423：单位kb