0

Im not sure why this is but my Wordpress Site keeps crashing with the "There was an error establishing a database connection" error.

Restarting the mysql service seems to temporarily fix this problem but it inevitably crashes again and again.

I thought it may be a memory issue so I upgraded my instance from 1gb to 2gb. But nothing seems to have changed.. I'd rather not pay the massive extra cost for this if it isn't memory related.

Here is some of the mysql log file relating to the crash. All the crashes seem to contain the following:

160519 13:03:21 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160519 13:03:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160519 13:03:21 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 18275 ...
160519 13:03:21 [Note] Plugin 'FEDERATED' is disabled.
160519 13:03:21 InnoDB: The InnoDB memory heap is disabled
160519 13:03:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160519 13:03:21 InnoDB: Compressed tables use zlib 1.2.8
160519 13:03:21 InnoDB: Using Linux native AIO
160519 13:03:21 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160519 13:03:21 InnoDB: Completed initialization of buffer pool
160519 13:03:21 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160519 13:03:21 [ERROR] Plugin 'InnoDB' init function returned error.
160519 13:03:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160519 13:03:21 [ERROR] Unknown/unsupported storage engine: InnoDB
160519 13:03:21 [ERROR] Aborting

160519 13:03:21 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

****UPDATE****

So I have changed the size of the innodb_buffer_pool_size as mentioned below and also made a swapfile. My site still crashes every few hours and I am slowly losing the will to live.

160507 16:28:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160507 16:28:20 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 26457 ...
160507 16:28:20 [Note] Plugin 'FEDERATED' is disabled.
160507 16:28:20 InnoDB: The InnoDB memory heap is disabled
160507 16:28:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160507 16:28:20 InnoDB: Compressed tables use zlib 1.2.8
160507 16:28:20 InnoDB: Using Linux native AIO
160507 16:28:20 InnoDB: Initializing buffer pool, size = 128.0M
160507 16:28:20 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
160507 16:28:20  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
160507 16:28:21  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
160507 16:28:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
160507 16:28:21  InnoDB: Waiting for the background threads to start
160507 16:28:22 InnoDB: 5.5.46 started; log sequence number 0
160507 16:28:22 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160507 16:28:22 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160507 16:28:22 [Note] Server socket created on IP: '0.0.0.0'.
160507 16:28:22 [Note] Event Scheduler: Loaded 0 events
160507 16:28:22 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.46'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
160507 17:56:05 [Note] /usr/libexec/mysql55/mysqld: Normal shutdown

160507 17:56:05 [Note] Event Scheduler: Purging the queue. 0 events
160507 17:56:05  InnoDB: Starting shutdown...
160507 17:56:06  InnoDB: Shutdown completed; log sequence number 2733606
160507 17:56:06 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
zarate32
  • 29
  • 2
  • 7
  • There's something that is causing high load on your database. Try increasing swap memory, and check your queries or try to disable plugins you have and see if the crash happens again – dingo_d May 31 '16 at 12:48
  • Tried increasing this but each time I do I lose permissions on my account or seem to screw up the instance. – zarate32 May 31 '16 at 12:51
  • Check this question http://stackoverflow.com/questions/25965638/mysql-fatal-error-cannot-allocate-memory-for-the-buffer-pool – Piyush Patil May 31 '16 at 13:04
  • Tried this to no avail. I've updated the question. – zarate32 Jun 02 '16 at 08:44

1 Answers1

0

Your buffer size is too high. It probably does not need to be that size, see this answer on dba.stackexchange.com

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

If you are using Amazon Linux, according to mysql docs (https://dev.mysql.com/doc/refman/5.7/en/ha-vm-aws-setup.html) the setting for this is in /etc/my.cnf

Probably altering the setting in this file to innodb_buffer_pool_size = 10M would fix your problem

The above fixes the specific problem shown in the logs above. If there is another problem being logged which concerns memory use then add a 4GiB swapfile with these commands

/bin/dd if=/dev/zero of=/swapfile bs=1M count=4096; /sbin/mkswap /swapfile; /sbin/swapon /swapfile

Using swap isn't magic it will allow your system to run slow and bloated. However, if this gets you over "blips" in memory usage, it could be good

Community
  • 1
  • 1
Vorsprung
  • 32,923
  • 5
  • 39
  • 63
  • I have tried doing this but my site continues to go down and is inaccessible. If I restart mysql and apache it works breifly but will die in half an hour or so. I just dont get it. Now it doesn't load at all and just hangs before saying the site cannot be reached – zarate32 May 31 '16 at 20:47
  • if you've made that change then it must be logging a different error – Vorsprung Jun 01 '16 at 08:00
  • do you think it could be the swapfile thing also documented with this problem? – zarate32 Jun 01 '16 at 08:43
  • ok, the comments about the swapfile assume that the vast amount of memory required for the 128M innodb buffer pool is actually needed. To add a swapfile see addition to the answer above – Vorsprung Jun 01 '16 at 11:00
  • Can I just re emphasis that if you have altered the innodb_buffer_pool_size to a lower, saner value then if it crashes it will not be giving the error that you originally reported. Either add the new error details to your question above or ask another question – Vorsprung Jun 01 '16 at 11:07
  • The new log shows that mysql is doing a "Normal shutdown", there is no particular error there. Look at the apache logs next – Vorsprung Jun 02 '16 at 10:49
  • Oh ok. How do I do that. I see in my apache logs folder there is an access_log file as well as error log files. – zarate32 Jun 02 '16 at 11:16
  • Concentrate on the apache error logs for now. You should start another question to address any problems you find. Please accept/upvote my answer if you like. I won't be adding anything else to this question. Bonne chance! – Vorsprung Jun 02 '16 at 11:59