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

呆子

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. 
    
15. 210704 19:48:56 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
    
16. 
    
17. 210704 20:01:00 [Note] Plugin 'FEDERATED' is disabled.
    
18. 210704 20:01:00 InnoDB: The InnoDB memory heap is disabled
    
19. 210704 20:01:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
20. 210704 20:01:00 InnoDB: Compressed tables use zlib 1.2.7
    
21. 210704 20:01:00 InnoDB: Using Linux native AIO
    
22. 210704 20:01:00 InnoDB: Initializing buffer pool, size = 4.0G
    
23. 210704 20:01:00 InnoDB: Completed initialization of buffer pool
    
24. 210704 20:01:00 InnoDB: highest supported file format is Barracuda.
    
25. InnoDB: Log scan progressed past the checkpoint lsn 5049713926
    
26. 210704 20:01:00  InnoDB: Database was not shut down normally!
    
27. InnoDB: Starting crash recovery.
    
28. InnoDB: Reading tablespace information from the .ibd files...
    
29. InnoDB: Restoring possible half-written data pages from the doublewrite
    
30. InnoDB: buffer...
    
31. InnoDB: Doing recovery: scanned up to log sequence number 5049732334
    
32. InnoDB: 1 transaction(s) which must be rolled back or cleaned up
    
33. InnoDB: in total 0 row operations to undo
    
34. InnoDB: Trx id counter is 6161600
    
35. 210704 20:01:00  InnoDB: Starting an apply batch of log records to the database...
    
36. 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
    
37. 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
    
38. InnoDB: Apply batch completed
    
39. InnoDB: Last MySQL binlog file position 0 27237979, file name ./mysql-bin.000025
    
40. InnoDB: Starting in background the rollback of uncommitted transactions
    
41. 210704 20:01:01  InnoDB: Rolling back trx with id 616149B, 0 rows to undo
    
42. 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. 
   
6. [mysqld]
   
7. port                = 3306
   
8. socket                = /tmp/mysql.sock
   
9. datadir = /usr/local/mysql/var
   
10. skip-external-locking
    
11. key_buffer_size = 512M
    
12. max_allowed_packet = 1M
    
13. table_open_cache = 2048
    
14. sort_buffer_size = 8M
    
15. net_buffer_length = 8K
    
16. read_buffer_size = 8M
    
17. read_rnd_buffer_size = 512K
    
18. myisam_sort_buffer_size = 128M
    
19. thread_cache_size = 256
    
20. query_cache_size = 256M
    
21. tmp_table_size = 256M
    
22. 
    
23. #skip-networking
    
24. max_connections = 1000
    
25. max_connect_errors = 100
    
26. open_files_limit = 65535
    
27. 
    
28. log-bin=mysql-bin
    
29. binlog_format=mixed
    
30. server-id        = 1
    
31. expire_logs_days = 7
    
32. 
    
33. default_storage_engine = InnoDB
    
34. innodb_file_per_table = 1
    
35. innodb_data_home_dir = /usr/local/mysql/var
    
36. innodb_data_file_path = ibdata1:10M:autoextend
    
37. innodb_log_group_home_dir = /usr/local/mysql/var
    
38. innodb_buffer_pool_size = 4096M
    
39. innodb_additional_mem_pool_size = 2M
    
40. innodb_log_file_size = 512M
    
41. innodb_log_buffer_size = 8M
    
42. innodb_flush_log_at_trx_commit = 1
    
43. innodb_lock_wait_timeout = 50
    
44. 
    
45. [mysqldump]
    
46. quick
    
47. max_allowed_packet = 16M
    
48. 
    
49. [mysql]
    
50. no-auto-rehash
    
51. 
    
52. [myisamchk]
    
53. key_buffer_size = 4096M
    
54. sort_buffer_size = 8M
    
55. read_buffer = 2M
    
56. write_buffer = 2M
    
57. 
    
58. [mysqlhotcopy]
    
59. interactive-timeout
    
60. 
    
61. 
    

复制代码

呆子

本人马保国 发表于 2021-7-4 20:47
什么配置小鸡
不如用云MYSQL

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

本人马保国

什么配置小鸡
不如用云MYSQL

walterclozet

这配置不低了

呆子

walterclozet 发表于 2021-7-4 21:35
这配置不低了

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

ApkB

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

呆子

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

heihai

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

PPS

加个 Redis 做缓存

ApkB

呆子 发表于 2021-7-4 21:50
Linux内存运行个几天 都会跑到90%多吧
刚刚把innodb_buffer_pool_size改成1024 不知道会不会好点
@ApkB  ...

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