MySQL Forums
Forum List  »  InnoDB

Out of Memory errors although MYSQL only uses 3% of RAM
Posted by: Rea rillert
Date: August 05, 2018 11:46AM

Sorry, total noob here, and frankly I have no idea what I am doing .. so please be gentle

I have an app running on CENTOS 6 x64
24 GB RAM, 512MB swap memory

free -h shows
total used free shared buffers cached
Mem: 23G 2.3G 21G 183M 27M 275M
-/+ buffers/cache: 2.0G 21G
Swap: 512M 0B 512M

htop shows the same picture


yet mysql runs out of memory every so often

my app log shows:
[error] [exception.CDbException] SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
[error] [exception.CDbException] exception 'CDbException' with message 'CDbConnection failed to open the DB connection.' in /var/www/html/cededw/apps/common/framework/db/CDbConnection.php:405

I tried optimizing my settings in my.cnf

everybody told me to set innodb_buffer_pool_size to 14GB and up
This doesn't work at all
max size I can set it to is 6GB
If I try to set more I get error:
InnoDB: Initializing buffer pool, size = 20.0G
InnoDB: mmap(21978152960 bytes) failed; errno 12
InnoDB: Completed initialization of buffer pool
InnoDB: Fatal error: cannot allocate memory for the buffer pool
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting


Here's my my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 128M
thread_cache_size = 4
tmp_table_size = 160M
max_heap_table_size = 160M
myisam_sort_buffer_size = 256M
read_rnd_buffer_size = 32M
read_buffer_size = 8M
sort_buffer_size = 8M
table_open_cache = 4096
table-definition-cache = 4096
max_allowed_packet = 4M
key_buffer_size = 1336M
innodb_buffer_pool_size = 6G
open-files-limit = 65535
innodb_flush_log_at_trx_commit = 2
max_connections = 500
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2

I googled my issues and tried almost everything I found in google

added this to /etc/security/limits.conf
* hard nofile 102400
* soft nofile 102400

Played round in my.cnf

nothing so far has worked


Can anyone tell me why I am running out of memory for mysql, what changes do I have to make to my.cnf?
or what tools to debug the issue?
Or may this be a server configuration issue?
Like server is giving priority to some other ressources?
But I still don't see why I run out of memory at all if there's 21GB of RAM untouched?


All and any help much appreciated!! Thxs

Options: ReplyQuote


Subject
Views
Written By
Posted
Out of Memory errors although MYSQL only uses 3% of RAM
2498
August 05, 2018 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.