全球主机交流论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

CeraNetworks网络延迟测速工具IP归属甄别会员请立即修改密码
查看: 2520|回复: 21

[疑问] WP站 MYSQL 经常挂 my.cnf 怎么设置合适

[复制链接]
发表于 2021-7-4 20:36:28 | 显示全部楼层 |阅读模式
  1. 210704 19:48:56 [Note] Plugin 'FEDERATED' is disabled.
  2. 210704 19:48:56 InnoDB: The InnoDB memory heap is disabled
  3. 210704 19:48:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
  4. 210704 19:48:56 InnoDB: Compressed tables use zlib 1.2.7
  5. 210704 19:48:56 InnoDB: Using Linux native AIO
  6. 210704 19:48:56 InnoDB: Initializing buffer pool, size = 4.0G
  7. InnoDB: mmap(4395630592 bytes) failed; errno 12
  8. 210704 19:48:56 InnoDB: Completed initialization of buffer pool
  9. 210704 19:48:56 InnoDB: Fatal error: cannot allocate memory for the buffer pool
  10. 210704 19:48:56 [ERROR] Plugin 'InnoDB' init function returned error.
  11. 210704 19:48:56 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
  12. 210704 19:48:56 [ERROR] Unknown/unsupported storage engine: InnoDB
  13. 210704 19:48:56 [ERROR] Aborting

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

  15. 210704 20:01:00 [Note] Plugin 'FEDERATED' is disabled.
  16. 210704 20:01:00 InnoDB: The InnoDB memory heap is disabled
  17. 210704 20:01:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
  18. 210704 20:01:00 InnoDB: Compressed tables use zlib 1.2.7
  19. 210704 20:01:00 InnoDB: Using Linux native AIO
  20. 210704 20:01:00 InnoDB: Initializing buffer pool, size = 4.0G
  21. 210704 20:01:00 InnoDB: Completed initialization of buffer pool
  22. 210704 20:01:00 InnoDB: highest supported file format is Barracuda.
  23. InnoDB: Log scan progressed past the checkpoint lsn 5049713926
  24. 210704 20:01:00  InnoDB: Database was not shut down normally!
  25. InnoDB: Starting crash recovery.
  26. InnoDB: Reading tablespace information from the .ibd files...
  27. InnoDB: Restoring possible half-written data pages from the doublewrite
  28. InnoDB: buffer...
  29. InnoDB: Doing recovery: scanned up to log sequence number 5049732334
  30. InnoDB: 1 transaction(s) which must be rolled back or cleaned up
  31. InnoDB: in total 0 row operations to undo
  32. InnoDB: Trx id counter is 6161600
  33. 210704 20:01:00  InnoDB: Starting an apply batch of log records to the database...
  34. 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
  35. 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
  36. InnoDB: Apply batch completed
  37. InnoDB: Last MySQL binlog file position 0 27237979, file name ./mysql-bin.000025
  38. InnoDB: Starting in background the rollback of uncommitted transactions
  39. 210704 20:01:01  InnoDB: Rolling back trx with id 616149B, 0 rows to undo
  40. 210704 20:01:01  InnoDB: Waiting for the background threads to start
复制代码


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


下面是my.cnf的设置
  1. [client]
  2. #password        = your_password
  3. port                = 3306
  4. socket                = /tmp/mysql.sock

  5. [mysqld]
  6. port                = 3306
  7. socket                = /tmp/mysql.sock
  8. datadir = /usr/local/mysql/var
  9. skip-external-locking
  10. key_buffer_size = 512M
  11. max_allowed_packet = 1M
  12. table_open_cache = 2048
  13. sort_buffer_size = 8M
  14. net_buffer_length = 8K
  15. read_buffer_size = 8M
  16. read_rnd_buffer_size = 512K
  17. myisam_sort_buffer_size = 128M
  18. thread_cache_size = 256
  19. query_cache_size = 256M
  20. tmp_table_size = 256M

  21. #skip-networking
  22. max_connections = 1000
  23. max_connect_errors = 100
  24. open_files_limit = 65535

  25. log-bin=mysql-bin
  26. binlog_format=mixed
  27. server-id        = 1
  28. expire_logs_days = 7

  29. default_storage_engine = InnoDB
  30. innodb_file_per_table = 1
  31. innodb_data_home_dir = /usr/local/mysql/var
  32. innodb_data_file_path = ibdata1:10M:autoextend
  33. innodb_log_group_home_dir = /usr/local/mysql/var
  34. innodb_buffer_pool_size = 4096M
  35. innodb_additional_mem_pool_size = 2M
  36. innodb_log_file_size = 512M
  37. innodb_log_buffer_size = 8M
  38. innodb_flush_log_at_trx_commit = 1
  39. innodb_lock_wait_timeout = 50

  40. [mysqldump]
  41. quick
  42. max_allowed_packet = 16M

  43. [mysql]
  44. no-auto-rehash

  45. [myisamchk]
  46. key_buffer_size = 4096M
  47. sort_buffer_size = 8M
  48. read_buffer = 2M
  49. write_buffer = 2M

  50. [mysqlhotcopy]
  51. interactive-timeout


复制代码
 楼主| 发表于 2021-7-4 20:53:39 | 显示全部楼层
本人马保国 发表于 2021-7-4 20:47
什么配置小鸡
不如用云MYSQL

配置为 E3-1270 v3  32G内存 SWAP分了32G
发表于 2021-7-4 20:47:08 | 显示全部楼层
什么配置小鸡
不如用云MYSQL
发表于 2021-7-4 21:35:36 来自手机 | 显示全部楼层
这配置不低了
 楼主| 发表于 2021-7-4 21:36:20 | 显示全部楼层

配置理论肯定够了 但是搞不懂 mysql 为什么还经常挂
发表于 2021-7-4 21:41:44 | 显示全部楼层
内存使用情况呢?32G杜甫不至于4G内存空间也分不出来啊
 楼主| 发表于 2021-7-4 21:50:01 | 显示全部楼层
Linux内存运行个几天 都会跑到90%多吧
刚刚把innodb_buffer_pool_size改成1024 不知道会不会好点
@ApkB
发表于 2021-7-4 22:02:48 | 显示全部楼层
两台机机,一台搞高性能数据库,ip白名单开放数据库端口给另一台web服务器
发表于 2021-7-4 22:13:44 | 显示全部楼层
加个 Redis 做缓存
发表于 2021-7-4 22:20:08 | 显示全部楼层
呆子 发表于 2021-7-4 21:50
Linux内存运行个几天 都会跑到90%多吧
刚刚把innodb_buffer_pool_size改成1024 不知道会不会好点
@ApkB  ...

嗯,这样会好点。什么程序占用那么多内存,限制一下。
您需要登录后才可以回帖 登录 | 注册

本版积分规则

Archiver|手机版|小黑屋|全球主机交流论坛

GMT+8, 2024-3-29 23:02 , Processed in 0.096603 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表