The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

OOM killing mysql process

Discussion in 'General Discussion' started by 63bus, Feb 16, 2016.

  1. 63bus

    63bus Active Member

    Joined:
    Mar 31, 2007
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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
     
    #1 63bus, Feb 16, 2016
    Last edited by a moderator: Feb 19, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. 63bus

    63bus Active Member

    Joined:
    Mar 31, 2007
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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 <major@mhtx.net>
     >>  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)
    
     
    #3 63bus, Feb 16, 2016
    Last edited: Feb 16, 2016
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. 63bus

    63bus Active Member

    Joined:
    Mar 31, 2007
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  7. 63bus

    63bus Active Member

    Joined:
    Mar 31, 2007
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
  8. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Additionally monitor the users and their databases overloading server by using mysqladmin pr and find out culprit user.
     
Loading...

Share This Page