Centos 6.5 падает периодически msql куда копать?

Question

[Denis Denisov]

Centos 6.5 падает периодически msql куда копать?

5.5.44 ver

151108 12:03:45 [ERROR] /usr/libexec/mysqld: Sort aborted: Error writing file '/tmp/MY01kSIv' (Errcode: 28)
151108 16:02:13 [Warning] IP address '218.30.67.89' could not be resolved: Name or service not known
151108 19:58:46 [Warning] IP address '58.215.79.221' could not be resolved: Name or service not known
151109 2:42:46 [Warning] IP address '202.207.7.68' could not be resolved: Name or service not known
151109 2:54:33 [Warning] IP address '42.193.1.36' could not be resolved: Name or service not known
151109 23:05:50 mysqld_safe Number of processes running now: 0
151109 23:05:50 mysqld_safe mysqld restarted
151109 23:05:54 [Note] /usr/libexec/mysqld (mysqld 5.5.44) starting as process 14379 ...
151109 23:05:54 [Note] Plugin 'FEDERATED' is disabled.
151109 23:05:54 InnoDB: The InnoDB memory heap is disabled
151109 23:05:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151109 23:05:54 InnoDB: Compressed tables use zlib 1.2.3
151109 23:05:54 InnoDB: Using Linux native AIO
151109 23:05:54 InnoDB: Initializing buffer pool, size = 128.0M
151109 23:05:55 InnoDB: Completed initialization of buffer pool
151109 23:05:55 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151109 23:05:55 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...
151109 23:05:57 InnoDB: Waiting for the background threads to start
151109 23:05:58 InnoDB: 5.5.44 started; log sequence number 27720663237
151109 23:05:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
151109 23:05:59 [Note] - '0.0.0.0' resolves to '0.0.0.0';
151109 23:05:59 [Note] Server socket created on IP: '0.0.0.0'.
151109 23:05:59 [Note] Event Scheduler: Loaded 0 events
151109 23:05:59 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.44' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi

151110 11:18:11 [Warning] IP address '202.207.7.68' could not be resolved: Name or service not known
151110 21:34:07 [Warning] IP address '222.186.3.96' could not be resolved: Name or service not known
151110 23:06:31 mysqld_safe Number of processes running now: 0
151110 23:06:31 mysqld_safe mysqld restarted
151110 23:06:31 [Note] /usr/libexec/mysqld (mysqld 5.5.44) starting as process 24343 ...
151110 23:06:32 [Note] Plugin 'FEDERATED' is disabled.
151110 23:06:32 InnoDB: The InnoDB memory heap is disabled
151110 23:06:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151110 23:06:32 InnoDB: Compressed tables use zlib 1.2.3
151110 23:06:32 InnoDB: Using Linux native AIO
151110 23:06:32 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
151110 23:06:32 InnoDB: Completed initialization of buffer pool
151110 23:06:32 InnoDB: Fatal error: cannot allocate memory for the buffer pool
151110 23:06:32 [ERROR] Plugin 'InnoDB' init function returned error.
151110 23:06:32 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
151110 23:06:32 [ERROR] Unknown/unsupported storage engine: InnoDB
151110 23:06:32 [ERROR] Aborting

151110 23:06:32 [Note] /usr/libexec/mysqld: Shutdown complete

151110 23:06:33 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
151111 00:05:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
151111 0:05:33 [Note] /usr/libexec/mysqld (mysqld 5.5.44) starting as process 27231 ...
151111 0:05:33 [Note] Plugin 'FEDERATED' is disabled.
151111 0:05:33 InnoDB: The InnoDB memory heap is disabled
151111 0:05:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151111 0:05:33 InnoDB: Compressed tables use zlib 1.2.3
151111 0:05:33 InnoDB: Using Linux native AIO
151111 0:05:33 InnoDB: Initializing buffer pool, size = 128.0M
151111 0:05:33 InnoDB: Completed initialization of buffer pool
151111 0:05:33 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151111 0:05:33 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...
151111 0:05:34 InnoDB: Waiting for the background threads to start
151111 0:05:35 InnoDB: 5.5.44 started; log sequence number 28663550439
151111 0:05:35 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
151111 0:05:35 [Note] - '0.0.0.0' resolves to '0.0.0.0';
151111 0:05:35 [Note] Server socket created on IP: '0.0.0.0'.
151111 0:05:35 [Note] Event Scheduler: Loaded 0 events

Answer 1

[Андрей]

/usr/libexec/mysqld: Sort aborted: Error writing file '/tmp/MY01kSIv' (Errcode: 28)

кончилось место.

Answer 2

[Denis Denisov]

[root@ns log]# ps -eo pid,user,pcpu,pmem,comm --sort vsize | tail -5 | tac
27231 mysql 154 0.7 mysqld
3001 named 0.0 0.0 named
23834 apache 1.2 0.2 httpd
23813 apache 0.6 0.2 httpd
23702 apache 1.0 0.2 httpd

Answer 3

[Anton Chernousov]

У вас там база основательно поехала.

1. ОБЯЗАТЕЛЬНО! Остановите базу и сделайте файловый бэкап, чем черт не шутит, чтобы можно было в дальнейшем анализировать ситуацию.

2. Теперь запуск в safe-режиме делайте и дамп базы выгрузите и залейте в пустую.

3. Если не запускается и в safe режиме, то это ахтунг и надо смотреть более подробно.

П.С.
Или памяти не хватает (Fatal error: cannot allocate memory for the buffer pool)... Но она от некорректного отключения все равно пытается само восстановиться восстановиться

Comments

[Denis Denisov]

У меня на сервере порядка 10 баз, две из них по 1 Gb (wordpress)
параметры сервера

Processor Intel Xeon E3 1225v2
Cores/Threads 4 cores/ 4 threads
Frequency 3.2 GHz+
RAM 32GB DDR3
Disks 3 x 120 GB SSD

[Anton Chernousov]

karlson1980: Попробуйте все-таки выгрузку-загрузку. С памятью должно быть теоретически все ОК раз у вас такой агрегат неплохой.

[Denis Denisov]

Anton Chernousov: ни разу подобного ни делал " запуск в safe-режиме делайте и дамп базы выгрузите и залейте в пустую." - можете на "пальцах" объяснить - гугл не помог

[Anton Chernousov]

Вот из этого мануала про запуск в safe-моде (база будет в однопользовательском режиме, чтобы наверняка):

th22.info/blog/sbros-parolya-root-v-mysql

Потом выгружаем дамп всех баз данных командой:

mysqldump -u root -p --all-databases > all_dbs.sql

[Denis Denisov]

[root@ns398679 ~]# mysqld_safe --skip-grant-tables
151115 11:50:27 mysqld_safe Logging to '/var/lib/mysql/ns3.e rr'.
151115 11:50:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/ mysql

[root@ns398679 ~]# mysqldump -u root -p --all-databases > all_dbs.sql
Enter password:
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'prefix\_geo\_city'': Got error 28 from storage engine (1030)

[Denis Denisov]

karlson1980: [root@ns398679 ~]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 128263
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 128263
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited