WP站 MYSQL 经常挂 my.cnf 怎么设置合适-呆子

    210704 19:48:56 [Note] Plugin ‘FEDERATED’ is disabled.
    210704 19:48:56 InnoDB: The InnoDB memory heap is disabled
    210704 19:48:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    210704 19:48:56 InnoDB: Compressed tables use zlib 1.2.7
    210704 19:48:56 InnoDB: Using Linux native AIO
    210704 19:48:56 InnoDB: Initializing buffer pool, size = 4.0G
    InnoDB: mmap(4395630592 bytes) failed; errno 12
    210704 19:48:56 InnoDB: Completed initialization of buffer pool
    210704 19:48:56 InnoDB: Fatal error: cannot allocate memory for the buffer pool
    210704 19:48:56 [ERROR] Plugin ‘InnoDB’ init function returned error.
    210704 19:48:56 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    210704 19:48:56 [ERROR] Unknown/unsupported storage engine: InnoDB
    210704 19:48:56 [ERROR] Aborting

    210704 19:48:56 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

    210704 20:01:00 [Note] Plugin ‘FEDERATED’ is disabled.
    210704 20:01:00 InnoDB: The InnoDB memory heap is disabled
    210704 20:01:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    210704 20:01:00 InnoDB: Compressed tables use zlib 1.2.7
    210704 20:01:00 InnoDB: Using Linux native AIO
    210704 20:01:00 InnoDB: Initializing buffer pool, size = 4.0G
    210704 20:01:00 InnoDB: Completed initialization of buffer pool
    210704 20:01:00 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 5049713926
    210704 20:01:00InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 5049732334
    InnoDB: 1 transaction(s) which must be rolled back or cleaned up
    InnoDB: in total 0 row operations to undo
    InnoDB: Trx id counter is 6161600
    210704 20:01:00InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
    67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    InnoDB: Last MySQL binlog file position 0 27237979, file name ./mysql-bin.000025
    InnoDB: Starting in background the rollback of uncommitted transactions
    210704 20:01:01InnoDB: Rolling back trx with id 616149B, 0 rows to undo
    210704 20:01:01InnoDB: Waiting for the background threads to start

以上是错误日志后面手动重启的
配置为 E3-1270 v332G内存 SWAP分了32G

下面是my.cnf的设置

    [client]
    #password = your_password
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    datadir = /usr/local/mysql/var
    skip-external-locking
    key_buffer_size = 512M
    max_allowed_packet = 1M
    table_open_cache = 2048
    sort_buffer_size = 8M
    net_buffer_length = 8K
    read_buffer_size = 8M
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 128M
    thread_cache_size = 256
    query_cache_size = 256M
    tmp_table_size = 256M

    #skip-networking
    max_connections = 1000
    max_connect_errors = 100
    open_files_limit = 65535

    log-bin=mysql-bin
    binlog_format=mixed
    server-id = 1
    expire_logs_days = 7

    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    innodb_buffer_pool_size = 4096M
    innodb_additional_mem_pool_size = 2M
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [myisamchk]
    key_buffer_size = 4096M
    sort_buffer_size = 8M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout

热议
推荐楼 呆子 昨天20:53

什么配置小鸡
不如用云MYSQL

配置为 E3-1270 v332G内存 SWAP分了32G

2楼 本人马保国 昨天20:47

什么配置小鸡
不如用云MYSQL

4楼 walterclozet 昨天21:35

这配置不低了

5楼 呆子 昨天21:36

这配置不低了

配置理论肯定够了 但是搞不懂 mysql 为什么还经常挂

6楼 ApkB 昨天21:41

内存使用情况呢?32G杜甫不至于4G内存空间也分不出来啊

7楼 呆子 昨天21:50

Linux内存运行个几天 都会跑到90%多吧
刚刚把innodb_buffer_pool_size改成1024 不知道会不会好点
@ApkB

8楼 heihai 昨天22:02

两台机机,一台搞高性能数据库,ip白名单开放数据库端口给另一台web服务器

9楼 PPS 昨天22:13

加个 Redis 做缓存

10楼 ApkB 昨天22:20

Linux内存运行个几天 都会跑到90%多吧
刚刚把innodb_buffer_pool_size改成1024 不知道会不会好点
@ApkB…

嗯,这样会好点。什么程序占用那么多内存,限制一下。

12楼 walterclozet 7小时前

配置理论肯定够了 但是搞不懂 mysql 为什么还经常挂

要不每天3-4点自动重启一次?

13楼 yjlml 7小时前

同病相怜啊
我也在搞着这个问题还是没找到解决的办法

14楼 dua 7小时前

請根據你的 硬體規格 CPU RAM 修改 配置你的 PHP MySQL 看起來 你的配置沒有優化好啊~~~~

15楼 呆子 24分钟前

請根據你的 硬體規格 CPU RAM 修改 配置你的 PHP MySQL 看起來 你的配置沒有優化好啊~~~~ …

php我调整成静态模式了SQL也稍微改了点按照之前的情况至少挂的间隔时间长了点

16楼 呆子 23分钟前

同病相怜啊
我也在搞着这个问题还是没找到解决的办法

服务器只有一个站的话 我之前开启swap 就没问题了现在服务器配置高 几个站在一起就不行了

17楼 liugogal 20分钟前

能缓存就缓存能静态就静态,wp就这样,甚至对着搜索页面f5都能刷死,几乎无视配置。

18楼 呆子 17分钟前

能缓存就缓存能静态就静态,wp就这样,甚至对着搜索页面f5都能刷死,几乎无视配置。 …

需要登录没办法开启Redis缓存不然登录就出问题

申明:本文内容由网友收集分享,仅供学习参考使用。如文中内容侵犯到您的利益,请在文章下方留言,本站会第一时间进行处理。

发表回复