Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

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.

MySQL + Memory Issues on Cent OS 7

Discussion in 'Workarounds and Optimization' started by Wil Huddleston, Apr 25, 2017.

Tags:
  1. Wil Huddleston

    Wil Huddleston Registered

    Joined:
    Apr 25, 2017
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Valencia, CA
    cPanel Access Level:
    Website Owner
    We have been hosting 30+ WordPress sites on an old VPS with Cent OS 5 and it has been running smooth for years. We recently needed to upgrade a few of the websites to Cent OS 7 due to some requirements for PayPal & WooCommerce running on those sites. We have migrated about 5 or 6 of the WordPress sites over to the new server and are now suddenly having memory issues where the MySQL is being shut down often due to excessive memory use.

    We have had the hosting company check the server multiple times and they have made numerous adjustments, none of which have resolved the issue. We also added some RAM because we were told that the system itself needed more memory to run everything. However, the issue still persists. We currently have CentOS 7 running ( CENTOS 7.3 x86_64 virtuozzo – WHM 62.0 (build 21) ), 3 GB of RAM installed, 8+ shared dual Xeon processors. The websites are all running the latest WordPress and a couple have the latest WooCommerce installed. The sites on average get about 20 users/day and the most active of the bunch gets 50-100 users per day.

    The hosting company is currently installing MySQLTuner in order to see what setting adjustments might be recommended. I will post those details here as soon as I receive them. In the interim, is there anything else you would recommend that I check?

    Thanks,
    Wil
     
  2. vacancy

    vacancy Well-Known Member

    Joined:
    Sep 20, 2012
    Messages:
    140
    Likes Received:
    14
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    What's in my.cnf file?
     
  3. Wil Huddleston

    Wil Huddleston Registered

    Joined:
    Apr 25, 2017
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Valencia, CA
    cPanel Access Level:
    Website Owner
    Here's the current my.cnf file...

    Code:
    [mysqld]
    innodb_file_per_table
    innodb_buffer_pool_size=128M
    innodb_use_sys_malloc=0
    datadir=/var/lib/mysql
    performance_schema=ON
    slow_query_log=0
    slow_query_log_file=/var/lib/mysql/slow_query.log
    long_query_time=2
    query_cache_limit=512K
    query_cache_size=128M
    query_cache_type=0
    max_user_connections=80
    max_connections=30
    thread_cache_size=128
    table_open_cache=2512
    table_definition_cache=2512
    key_buffer_size=128M
    max_connect_errors=20
    max_allowed_packet=2M
    innodb_log_file_size=16M
    innodb_log_files_in_group=4
    
    #WT commenting buffer size values
    
    #join_buffer_size=2M
    #sort_buffer_size=2M
    #read_buffer_size=2M
    #read_rnd_buffer_size=2M
    thread_concurrency=4
    myisam_sort_buffer_size=16M
    open_files_limit=8192
    concurrent_insert=1
    local-infile=0
    max_heap_table_size=96M
    tmp_table_size=96M
    tmpdir=/var/lib/mysql/tmpdir/tmp
    innodb_log_buffer_size=16M
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_buffer_pool_instances = 1
    innodb_thread_concurrency = 8
    
    
    default-storage-engine=MyISAM
    [myisamchk]
    key_buffer_size=128M
    sort_buffer=128M
    read_buffer=32M
    write_buffer=32M
     
  4. Wil Huddleston

    Wil Huddleston Registered

    Joined:
    Apr 25, 2017
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Valencia, CA
    cPanel Access Level:
    Website Owner
    Here were the recommendations reported by the tuner...

    Code:
    General recommendations:
    Control warning line(s) into /var/lib/mysql/hostname.com.err file
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 512K, or use smaller result sets)
    tmp_table_size (> 96M)
    max_heap_table_size (> 96M)
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=128M) if possible.
     
  5. vacancy

    vacancy Well-Known Member

    Joined:
    Sep 20, 2012
    Messages:
    140
    Likes Received:
    14
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    You can edit it in this way and follow the situation.

    Code:
    [mysqld]
    innodb_file_per_table
    innodb_buffer_pool_size=128M
    innodb_use_sys_malloc=0
    datadir=/var/lib/mysql
    performance_schema=off
    slow_query_log=0
    slow_query_log_file=/var/lib/mysql/slow_query.log
    long_query_time=2
    query_cache_limit=1M
    query_cache_size=96M
    query_cache_type=1
    max_user_connections=200
    max_connections=200
    thread_cache_size=512
    table_open_cache=2048
    table_definition_cache=2048
    key_buffer_size=128M
    max_connect_errors=20
    max_allowed_packet=2M
    innodb_log_file_size=16M
    innodb_log_files_in_group=4
    
    #WT commenting buffer size values
    
    #join_buffer_size=2M
    #sort_buffer_size=2M
    #read_buffer_size=2M
    #read_rnd_buffer_size=2M
    thread_concurrency=4
    myisam_sort_buffer_size=64M
    open_files_limit=8192
    concurrent_insert=1
    local-infile=0
    max_heap_table_size=96M
    tmp_table_size=64M
    tmpdir=/var/lib/mysql/tmpdir/tmp
    innodb_log_buffer_size=16M
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_buffer_pool_instances = 1
    innodb_thread_concurrency = 8
    
    
    default-storage-engine=MyISAM
    [myisamchk]
    key_buffer_size=128M
    sort_buffer=128M
    read_buffer=32M
    write_buffer=32M
     
  6. Wil Huddleston

    Wil Huddleston Registered

    Joined:
    Apr 25, 2017
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Valencia, CA
    cPanel Access Level:
    Website Owner
    Thank you for the quick replies. Before I go changing things, can you give a little detail about why the items were changed that you changed? Also, I have read some guides about optimizing MySQL memory performance and a couple of sources seem to recommend keeping the values for tmp_table_size and max_heap_table_size equal. I noticed yours were different, so is keeping them equal not really an issue?

    Thanks,
    Wil
     
  7. vacancy

    vacancy Well-Known Member

    Joined:
    Sep 20, 2012
    Messages:
    140
    Likes Received:
    14
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    It is not possible to make a definite arrangement without knowing the databases, the instant queries, the hardware in the system.

    The mysql_tunner software will never give you the exact values to use. Since you said that the memory usage is high, I made an interpretation accordingly.

    Depending on the status of your system, you can find the appropriate settings by making minor adjustments.
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page