Out of Memory errors although MYSQL only uses 3% of RAM
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
Subject
Views
Written By
Posted
Out of Memory errors although MYSQL only uses 3% of RAM
2683
August 05, 2018 11:46AM
840
August 05, 2018 12:58PM
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.