mysql memory usage

[James_43]

Hi all, I am running a mySQL database on a webserver hosting multiple Wordpress installations.

It seems that database is getting swamped with memory and going into a continual loop of shutting down and restarting. A reboot fixes the issue temporarily, but I have no idea what I can do to fix this.

When the issue presented itself a month ago I realise I had not created a swap drive. I have now done so, but web traffick has increased since then and I suspect this may be the cause.

Interestingly, when I SSH into my Ubuntu Server, I get a readout of my memory usage. It always says 0% SWAP usage, which I cannot explain.

The full log is below, please help!

160317 14:16:09 mysqld_safe mysqld restarted
2016-03-17 14:16:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see $
2016-03-17 14:16:09 0 [Note] /usr/sbin/mysqld (mysqld 5.6.28-0ubuntu0.15.10.1) starting as process 11889 ...
2016-03-17 14:16:09 11889 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2016-03-17 14:16:09 11889 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2016-03-17 14:16:09 11889 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in $
2016-03-17 14:16:09 11889 [Note] Plugin 'FEDERATED' is disabled.
2016-03-17 14:16:09 11889 [ERROR] Function 'innodb' already exists
2016-03-17 14:16:09 11889 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-03-17 14:16:09 11889 [ERROR] Function 'federated' already exists
2016-03-17 14:16:09 11889 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2016-03-17 14:16:09 11889 [ERROR] Function 'blackhole' already exists
2016-03-17 14:16:09 11889 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2016-03-17 14:16:09 11889 [ERROR] Function 'archive' already exists
2016-03-17 14:16:09 11889 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2016-03-17 14:16:09 11889 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-17 14:16:09 11889 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-17 14:16:09 11889 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-17 14:16:09 11889 [Note] InnoDB: Memory barrier is not used
2016-03-17 14:16:09 11889 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-17 14:16:09 11889 [Note] InnoDB: Using Linux native AIO
2016-03-17 14:16:09 11889 [Note] InnoDB: Using CPU crc32 instructions
2016-03-17 14:16:09 11889 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2016-03-17 14:16:09 11889 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-03-17 14:16:09 11889 [ERROR] Plugin 'InnoDB' init function returned error.
2016-03-17 14:16:09 11889 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-03-17 14:16:09 11889 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-03-17 14:16:09 11889 [ERROR] Aborting
2016-03-17 14:16:09 11889 [Note] Binlog end
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'partition'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_METRICS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_CMP'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'INNODB_TRX'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'ARCHIVE'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'BLACKHOLE'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'MRG_MYISAM'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'MyISAM'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'CSV'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'MEMORY'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'sha256_password'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'mysql_old_password'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'mysql_native_password'
2016-03-17 14:16:09 11889 [Note] Shutting down plugin 'binlog'
2016-03-17 14:16:09 11889 [Note] /usr/sbin/mysqld: Shutdown complete

160317 14:16:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[cereal]

Hi, it could be a problem with the innodb_buffer_pool_size, the default value is 128MB of RAM, try to increase it, you can follow the suggestions in the documentation:

• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

If this does not help check the others processes to see if something else (the web server) is using too much resources.

[rch1231]

I would take a look at what processes are being kicked off in MySQL. You can run the following command to see what is running in MySQL
mysqladmin processlist -u root -p
and it will prompt your for the root MySQL password then give you a list of what is running.
If you are having trouble with the INNODB databases I suggest looking at the tools on the Percona web site. They have several free tools for configutring MySQL and for recovery.
https://www.percona.com/software/mysql-tools

[James_43]

Hi all, thanks for the good responses. I actually realised that even though I had created a swap drive, there was a spelling mistake in /etc/fstab so it wasn't mounting on restart. Now that the swap file is back online, it seems to have no further issues.

I will look at increasing the buffer size, since 128 does seem too small.

Cheers!