OOM killing mysql process

63bus

• February 16, 2016 09:53

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:
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: 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

• 

  cPanelMichael

  • February 16, 2016 18:14

  Hello :) Could you post your /etc/my.cnf configuration? Thank you.

  0
• 

  63bus

  • February 16, 2016 21:12

  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.
  [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
  >> MySQLTuner 1.6.2 - Major Hayden >> 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)
  

  0
• 

  cPanelMichael

  • February 18, 2016 20:53

  Have you tried updating the values as suggested under "Variables To Adjust" in the tuner output to see if that helps? Thank you.

  0
• 

  63bus

  • February 18, 2016 23:19

  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?

  0
• 

  cPanelMichael

  • February 19, 2016 12:37

  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.

  0
• 

  63bus

  • February 29, 2016 23:01

  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.

  0
• 

  storminternet

  • March 01, 2016 14:50

  Additionally monitor the users and their databases overloading server by using mysqladmin pr and find out culprit user.

  0

Please sign in to leave a comment.