V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
MrMike
V2EX  ›  MySQL

InnoDB: Cannot allocate memory for the buffer pool 如何处理?

  •  
  •   MrMike · 2017-08-31 21:02:58 +08:00 · 4823 次点击
    这是一个创建于 2401 天前的主题,其中的信息可能已经有所发展或是发生改变。
    操作系统是 Centos 6.9, MySQL 版本是 5.6,CPU2 核,内存 2G,网站与数据在同一个服务器上,网站服务器是 apache 2。

    my.cnf 配置如下:

    [mysqld]

    port=3306
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-external-locking
    skip-name-resolve
    back_log=384
    key_buffer_size=16M
    max_allowed_packet=4M
    thread_stack=256k
    sort_buffer_size=6M
    read_buffer_size=4M
    thread_cache_size=64
    query_cache_size=64M
    tmp_table_size=256M
    max_connections=5000
    wait_timeout=60
    thread_concurrency=4
    skip-networking
    innodb_buffer_pool_size=64M
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=2M
    innodb_thread_concurrency=8
    tmp_table_size=64M
    read_rnd_buffer_size=16M
    lower_case_table_name=1
    symbolic-links=0
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    一直启动不了 mysql,报错代码:

    2017-08-31 21:01:11 11339 [Warning] Buffered warning: Performance schema disabled (reason: init failed).

    2017-08-31 21:01:11 11339 [Note] Plugin 'FEDERATED' is disabled.
    2017-08-31 21:01:11 11339 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2017-08-31 21:01:11 11339 [Note] InnoDB: The InnoDB memory heap is disabled
    2017-08-31 21:01:11 11339 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2017-08-31 21:01:11 11339 [Note] InnoDB: Memory barrier is not used
    2017-08-31 21:01:11 11339 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2017-08-31 21:01:11 11339 [Note] InnoDB: Using Linux native AIO
    2017-08-31 21:01:11 11339 [Note] InnoDB: Using CPU crc32 instructions
    2017-08-31 21:01:11 11339 [Note] InnoDB: Initializing buffer pool, size = 64.0M
    InnoDB: mmap(68681728 bytes) failed; errno 12
    2017-08-31 21:01:11 11339 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
    2017-08-31 21:01:11 11339 [ERROR] Plugin 'InnoDB' init function returned error.
    2017-08-31 21:01:11 11339 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    2017-08-31 21:01:11 11339 [ERROR] Unknown/unsupported storage engine: InnoDB
    2017-08-31 21:01:11 11339 [ERROR] Aborting

    2017-08-31 21:01:11 11339 [Note] Binlog end
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'partition'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_DELETED'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_METRICS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMPMEM'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMP_RESET'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_CMP'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_LOCKS'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'INNODB_TRX'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'ARCHIVE'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'BLACKHOLE'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'CSV'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'MEMORY'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'MRG_MYISAM'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'MyISAM'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'sha256_password'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'mysql_old_password'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'mysql_native_password'
    2017-08-31 21:01:11 11339 [Note] Shutting down plugin 'binlog'
    2017-08-31 21:01:11 11339 [Note] /usr/sbin/mysqld: Shutdown complete

    按照网上的类似方法,一直减少 innodb_buffer_pool_size=64M,还是不能启动。。
    6 条回复    2017-08-31 23:37:05 +08:00
    ToughGuy
        1
    ToughGuy  
       2017-08-31 22:10:35 +08:00
    你这配置文件, 不懂就别瞎调整,xxx_buffer_size 哪有调那么大的。

    你用默认配置文件就好, 然后适当的加大一下 innodb_buffer_pool_size, 如果还不能启动的话检查系统是不是被你改过了(内核参数)。

    echo > /etc/sysctl.conf && reboot

    然后再试试。
    gstqc
        2
    gstqc  
       2017-08-31 22:17:10 +08:00 via iPhone
    MySQL 有 mini 配置模板,先把配置重置为官方的 mini 配置再看
    snnn
        3
    snnn  
       2017-08-31 22:37:53 +08:00 via Android
    把数据目录下所有文件全删了。重新初始化
    MrMike
        4
    MrMike  
    OP
       2017-08-31 22:53:38 +08:00
    @ToughGuy
    @gstqc
    @snnn 谢了,我还原了默认的版本 my.cnf 。然后设置了 swap,就可以了。
    snnn
        5
    snnn  
       2017-08-31 23:18:54 +08:00 via Android
    @MrMike 你要明白为什么。。。。。。
    你出错是因为你改了某个配置的值。而且你肯定应该改这个值
    MrMike
        6
    MrMike  
    OP
       2017-08-31 23:37:05 +08:00
    @snnn 并不是我改过某个值,我安装好后,就报错了。后来修改的内容,也都是通过网络查询的,只是没有解决到问题。谢谢你的回复。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3309 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 00:04 · PVG 08:04 · LAX 17:04 · JFK 20:04
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.