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

呆子

[ol]

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:00  InnoDB: 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:00  InnoDB: 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:01  InnoDB: Rolling back trx with id 616149B, 0 rows to undo

210704 20:01:01  InnoDB: Waiting for the background threads to start[/ol]复制代码

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


下面是my.cnf的设置
[ol]

[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

[/ol]复制代码

本人马保国

什么配置小鸡
不如用云MYSQL

呆子

本人马保国 发表于 2021-7-4 20:47

什么配置小鸡
不如用云MYSQL
配置为 E3-1270 v3  32G内存 SWAP分了32G