OOM killing mysql process

63bus

Active Member
Mar 31, 2007
35
0
156
This has happened a few times over the past week. As far as I know the system is not out of memory, although I could be wrong. Whenever I look at my site, I typically have 9GB of 16GB free according to free -m

I got the email below at 2:03 AM.
I was reading this thread:
Troubleshooting high server loads on Linux servers
If I look at sar -r on my server, here's the memory in use from that time period:
Code:
00:00:16    kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad
... trimmed out time in between ...
01:30:02      9396760   7380456     43.99         0    503028         0         0      0.00         0
01:40:03      9444648   7332568     43.71         0   1235036         0         0      0.00         0
01:50:01      9345020   7432196     44.30         0    746924         0         0      0.00         0
02:00:01      9266724   7510492     44.77         0   1177916         0         0      0.00         0
02:10:01     12181424   4595792     27.39         0   3569096         0         0      0.00         0
02:20:01     12099440   4677776     27.88         0   3622424         0         0      0.00         0
02:30:01     12033620   4743596     28.27         0   3687508         0         0      0.00         0
I can see it freed up memory by I still had 9GB free at the time, right?


Email sent to me:

Out of memory: The process “mysqld” was terminated because the system is low on memory.


In order to avoid a system crash due to low memory, the kernel terminated the process named “mysqld” with the PID “8880”.
Server myserver
Process Name mysqld
Event Time Tuesday, February 16, 2016 10:41:01 AM UTC
PID 8880
Status Out of Memory ⚠
Memory Information
Used 12.7 GB
Available 14.88 GB
Installed 16 GB
Load Information 2.25 5.87 5.76
Uptime 106 days, 6 hours, 50 minutes, and 18 seconds
IOStat Information avg-cpu: %user %nice %system %iowait %steal %idle
14.17 0.02 1.07 4.21 0.00 80.53
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
Top Processes
PID Owner CPU % Memory % Command
974 mysql 2.16 5.16 /usr/sbin/mysqld --basedir=/usr/ --datadir=/var/lib/mysql --plugin-dir=/usr//lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysite.com.err --open-files-limit=16000 --pid-file=/var/lib/mysql/mysite.com.pid
12014 root 0.32 0.39 spamd child
32690 nobody 0.13 0.39 /usr/local/apache/bin/httpd -k start
1132 root 5.06 0.16 tailwatchd - chkservd - sending OOM notification

For addtional details, see the attached dmesg log dump.

Preview of “oom_dmesg.txt”
[9188879.421125] Out of memory: OOM killed process 8880 (mysqld) score 0 vm:15286932kB, rss:4751340kB, swap:7014812kB

This notice was generated “Tuesday, February 16, 2016 9:03:21 AM UTC”.

“Out of Memory” notifications are currently configured to have an importance of “High”. You can change the importance or disable this type of notification in WHM’s Contact Manager at: <removed>

Do not reply to this automated message.

cP

Copyright© 2016 cPanel, Inc.


oom_dmesg.txt

[9188879.421125] Out of memory: OOM killed process 8880 (mysqld) score 0 vm:15286932kB, rss:4751340kB, swap:7014812kB
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
Hello :)

Could you post your /etc/my.cnf configuration?

Thank you.
 

63bus

Active Member
Mar 31, 2007
35
0
156
Here you go. The table_open_cache and open_files_limit were lower when this problem occurred yesterday and in the past, I increased those as I was trying to minimize the growth of the opened_files parameter.

I also just removed
default-storage-engine=MyISAM
as that is no longer needed.

I am using mySQL 5.6.29

Side note: Ever since my system upgraded to WHM 54 I have been having some performance issues. Restarting the database typically solves it for a day or 2.
Code:
[mysqld]
performance_schema=off
innodb_ft_min_token_size=2
ft_min_word_len=2
slow-query-log=0
long_query_time=1
read_buffer_size=2M
max_allowed_packet=500M
read_rnd_buffer_size=8M
wait_timeout=15
interactive_timeout=15
max_connections=150
table_open_cache=12000
table_open_cache_instances=8
datadir="/var/lib/mysql"
thread_cache_size=150
sort_buffer_size=2M
join_buffer_size=2M
key_buffer_size=256M
query_cache_type=1
query_cache_limit=512K
query_cache_size=32M
query_cache_min_res_unit=2048
basedir="/usr/"
max_heap_table_size=32M
tmp_table_size=32M
myisam_sort_buffer_size=64M
innodb_file_per_table=1
innodb_buffer_pool_size=10G
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb-file-format=Barracuda
innodb_write_io_threads=16
innodb_read_io_threads=16
open_files_limit=25000
default-storage-engine=MyISAM
[safe_mysqld]
err-log="/var/log/mysqld.log"
Here's a run of SQLTuner too, if that adds any information
Code:
 >>  MySQLTuner 1.6.2 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 90)
[--] Data in InnoDB tables: 8G (Tables: 32)
[!!] Total fragmented tables: 30

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  -------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 26m 35s (8M q [116.413 qps], 1M conn, TX: 17B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Binary logging is disabled
[--] Total buffers: 10.3G global + 14.2M per thread (150 max threads)
[OK] Maximum reached memory usage: 10.6G (66.03% of installed RAM)
[OK] Maximum possible memory usage: 12.4G (77.60% of installed RAM)
[OK] Slow queries: 0% (4K/8M)
[OK] Highest usage of available connections: 11% (17/150)
[OK] Aborted connections: 0.00%  (0/1484377)
[OK] Query cache efficiency: 33.9% (1M cached / 3M selects)
[!!] Query cache prunes per day: 93988
[OK] Sorts requiring temporary tables: 0% (143 temp sorts / 97K sorts)
[!!] Temporary tables created on disk: 31% (38K on disk / 121K total)
[OK] Thread cache hit rate: 99% (17 created / 1M connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 4% (1K/24K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 36.1% (96M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/380.2M
[OK] Read Key buffer hit rate: 99.9% (31M cached / 44K reads)
[!!] Write Key buffer hit rate: 45.4% (687K cached / 375K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 10.0G/8.7G
[!!] InnoDB buffer pool instances: 8
[!!] InnoDB Used buffer: 71.21% (466673 used/ 655356 total)
[OK] InnoDB Read buffer efficiency: 99.99% (2875664791 hits/ 2876089716 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 759314 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_size (> 32M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_instances(=10)
 
Last edited:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
Have you tried updating the values as suggested under "Variables To Adjust" in the tuner output to see if that helps?

Thank you.
 

63bus

Active Member
Mar 31, 2007
35
0
156
I have adjusted them in the past and they have been at higher values when this problem was occurring 1-2 weeks ago.

Can you explain how adjusting those parameters will affect why OOM seems to think that the system is out of memory when this does appear to be correct?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
The output you provided suggests MySQL was using the most memory when it was killed. Thus, tuning the MySQL performance is one way to reduce it's usage. That said, I suggest consulting with a qualified system administrator for help with determining the cause of the memory issues on your system if this continues to happen, as this is not a cPanel function or feature that's killing the process.

Thank you.
 

63bus

Active Member
Mar 31, 2007
35
0
156
Thank you, I understand. I mistakenly thought OOM and it's ability to kill processes was a new feature of WHM as this never occurred before the 11.54 upgrade.

I will work on examining and optimizing my mySQL configuration and memory usage.