wordpress websites msql errors.

March 30, 2016 478 views
MySQL WordPress LEMP

Hi,
i have 23 wordpress websites on my droplet (2 GB Memory / 40 GB Disk / NYC3).
i take database errors, frequently.
And i change my /etc/mysql/my.cnf :

skip-name-resolve
maxconnections = 500 # cok yogun sitelerde bu deger arttirilabilir
thread
cachesize = 128
key
buffer = 128M

querycachetype = 1
querycachesize = 32M
querycachelimit = 4M
tablecache = 2000 # cok fazla tablolu veritabanlarinda bu deger arttirilabilir
low
priority_updates = 1

innodbbufferpoolsize=256M
innodb
additionalmempoolsize=64M
innodb
threadconcurrency=8
innodb
flushmethod=ODIRECT

my error logs:
160330 4:55:36 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160330 4:55:36 [Note] Plugin 'FEDERATED' is disabled.
160330 4:55:36 InnoDB: The InnoDB memory heap is disabled
160330 4:55:36 InnoDB: Mutexes and rwlocks use GCC atomic builtins
160330 4:55:36 InnoDB: Compressed tables use zlib 1.2.8
160330 4:55:36 InnoDB: Using Linux native AIO
160330 4:55:36 InnoDB: Initializing buffer pool, size = 64.0M
160330 4:55:36 InnoDB: Completed initialization of buffer pool
160330 4:55:36 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!
160330 4:55:37 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...
160330 4:55:37 InnoDB: Waiting for the background threads to start
160330 4:55:38 InnoDB: 5.5.43 started; log sequence number 2320511750
160330 4:55:38 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160330 4:55:38 [Note] - '127.0.0.1' resolves to '127.0.0.1';
160330 4:55:38 [Note] Server socket created on IP: '127.0.0.1'.
160330 4:55:38 [Warning] 'user' entry 'root@phpmyadmin-build' ignored in --skip-name-resolve mode.
160330 4:55:38 [Warning] 'proxiespriv' entry '@ root@phpmyadmin-build' ignored in --skip-name-resolve mode.
160330 4:55:38 [Note] Event Scheduler: Loaded 0 events
160330 4:55:38 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.43-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
160330 4:55:41 [ERROR] /usr/sbin/mysqld: Table './lisztmuzik/wp
options' is marked as crashed and should be repaired
160330 4:55:42 [Warning] Checking table: './lisztmuzik/wpoptions'
160330 4:56:21 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160330 4:56:21 [Note] Plugin 'FEDERATED' is disabled.
160330 4:56:21 InnoDB: The InnoDB memory heap is disabled
160330 4:56:21 InnoDB: Mutexes and rw
locks use GCC atomic builtins
160330 4:56:21 InnoDB: Compressed tables use zlib 1.2.8
160330 4:56:21 InnoDB: Using Linux native AIO
160330 4:56:21 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
160330 4:56:21 InnoDB: Completed initialization of buffer pool
160330 4:56:21 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160330 4:56:21 [ERROR] Plugin 'InnoDB' init function returned error.
160330 4:56:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160330 4:56:21 [ERROR] Unknown/unsupported storage engine: InnoDB
160330 4:56:21 [ERROR] Aborting

160330 4:56:21 [Note] /usr/sbin/mysqld: Shutdown complete

160330 4:56:22 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160330 4:56:22 [Note] Plugin 'FEDERATED' is disabled.
160330 4:56:22 InnoDB: The InnoDB memory heap is disabled
160330 4:56:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160330 4:56:22 InnoDB: Compressed tables use zlib 1.2.8
160330 4:56:22 InnoDB: Using Linux native AIO
160330 4:56:22 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
160330 4:56:22 InnoDB: Completed initialization of buffer pool
160330 4:56:22 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160330 4:56:22 [ERROR] Plugin 'InnoDB' init function returned error.
160330 4:56:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160330 4:56:22 [ERROR] Unknown/unsupported storage engine: InnoDB
160330 4:56:22 [ERROR] Aborting

160330 4:56:22 [Note] /usr/sbin/mysqld: Shutdown complete

160330 5:07:11 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160330 5:07:11 [Note] Plugin 'FEDERATED' is disabled.
160330 5:07:11 InnoDB: The InnoDB memory heap is disabled
160330 5:07:11 InnoDB: Mutexes and rwlocks use GCC atomic builtins
160330 5:07:11 InnoDB: Compressed tables use zlib 1.2.8
160330 5:07:11 InnoDB: Using Linux native AIO
160330 5:07:11 InnoDB: Initializing buffer pool, size = 64.0M
160330 5:07:11 InnoDB: Completed initialization of buffer pool
160330 5:07:11 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!
160330 5:07:11 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...
160330 5:07:11 InnoDB: Waiting for the background threads to start
160330 5:07:12 InnoDB: 5.5.43 started; log sequence number 2320513613
160330 5:07:12 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160330 5:07:12 [Note] - '127.0.0.1' resolves to '127.0.0.1';
160330 5:07:12 [Note] Server socket created on IP: '127.0.0.1'.
160330 5:07:12 [Warning] 'user' entry 'root@phpmyadmin-build' ignored in --skip-name-resolve mode.
160330 5:07:12 [Warning] 'proxiespriv' entry '@ root@phpmyadmin-build' ignored in --skip-name-resolve mode.
160330 5:07:12 [Note] Event Scheduler: Loaded 0 events
160330 5:07:12 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.43-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
160330 5:07:14 [ERROR] /usr/sbin/mysqld: Table './xdnyalsp
taylanc/wpoptions' is marked as crashed and should be repaired
160330 5:07:14 [Warning] Checking table: './xdnyalsp
taylanc/wp_options'

1 comment
  • in addition to the answer below, I like to have my /etc/my.cnf be as "clean" as possible. For example, I would recommend commenting out the "innodb ...", "querycache ...", "querycache ...", "lowpriority_updates ..." parameters that you have in the /etc/my.cnf file so that you would be using the defaults. It is possible that one of those parameters that you have specified may be causing a conflict.

1 Answer

This immediately jumps out at me:

160330 4:56:21 InnoDB: Fatal error: cannot allocate memory for the buffer pool

This indicates that your issues with MySQL crashing is due to your droplet running out of available memory for the process. Adding a swap file should alleviate the issue by allowing your OS to write memory data to disk to prevent running out and services crashing but you may want to investigate upgrading to a larger droplet.

by Justin Ellingwood
Swap space can be used as an "overflow" area for your system when you run out of RAM. The operating system can store data that would normally be kept in RAM on the hard drive in a specially formatted file. In this guide, we'll demonstrate how to create and use one of these files in Ubuntu 14.04.
Have another answer? Share your knowledge.