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 high memory usage problem

Discussion in 'Workarounds and Optimization' started by szk, Feb 13, 2014.

  1. szk

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,
    I would appreciate any help for optimization
    I use VPS server with next configuration
    Total processors: 10
    Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    Total memory: 2GB
    mysql 5.1.56
    with daily visitors round 8000.

    Mysql works stable except I can see that it uses lot of memory
    and sometimes causes memory used peeks up to 56%
    Does anybody see in this my.cnf something that could cause issue or solve it
    Thank You

    This is my.cnf

    Code:
    [mysqld]
    max_connections=200
    max_user_connections=50
    key_buffer_size=256M
    myisam_sort_buffer_size=64M
    join_buffer_size=2M
    read_buffer_size=1M
    sort_buffer_size=1M
    table_open_cache=2500
    thread_cache_size=128
    interactive_timeout=45
    wait_timeout=20
    connect_timeout=8
    max_allowed_packet=16M
    max_connect_errors=10
    query_cache_limit=1M
    query_cache_size=64M
    query_cache_type=1
    #flush
    #flush_time=60
    table_definition_cache = 512
    max_heap_table_size = 32M
    tmp_table_size = 32M
    
    [mysqld_safe]
    open_files_limit=8192
    
    [mysqldump]
    max_allowed_packet=16M
    
    [myisamchk]
    key_buffer_size=64M
    sort_buffer_size=64M
    read_buffer_size=16M
    write_buffer_size=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl and post results
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Here are the results from mysqltuner
    Thank You

    Code:
    -------- General Statistics -------------------------------------------------
    -
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics ------------------------------------------
    -
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 106)
    [!!] InnoDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    -------- Performance Metrics ------------------------------------------------
    [--] Up for: 1d 8h 43m 32s (472M q [4K qps], 116M conn, TX: 385B, RX: 80B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 362.0M global + 4.4M per thread (200 max threads)
    [OK] Maximum possible memory usage: 1.2G (61% of installed RAM)
    [OK] Slow queries: 0% (6/472M)
    [OK] Highest usage of available connections: 25% (50/200)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/281.6M
    [OK] Key buffer hit rate: 99.9% (437M cached / 512K reads)
    [OK] Query cache efficiency: 93.3% (113M cached / 121M selects)
    [!!] Query cache prunes per day: 1522159
    [OK] Sorts requiring temporary tables: 0% (607 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 447757
    [!!] Temporary tables created on disk: 37% (641K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (54 created / 116M connections)
    [OK] Table cache hit rate: 83% (1K open / 2K opened)
    [OK] Open file limit used: 24% (1K/8K)
    [OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
    
    -------- Recommendations ----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    here

    [mysqld]
    max_connections=200
    max_user_connections=50

    key_buffer_size=325M
    myisam_sort_buffer_size=64M

    join_buffer_size=512K
    sort_buffer_size=256K

    table_open_cache=500
    thread_cache_size=16

    interactive_timeout=45
    wait_timeout=20
    connect_timeout=8

    max_allowed_packet=16M
    max_connect_errors=10

    query_cache_limit=1M
    query_cache_size=50M
    query_cache_type=1

    max_heap_table_size = 32M
    tmp_table_size = 32M

    innodb_buffer_pool_size = 10M

    [mysqld_safe]
    open_files_limit=8192

    [mysqldump]
    max_allowed_packet=16M

    [myisamchk]
    key_buffer_size=64M
    sort_buffer_size=64M
    read_buffer_size=16M
    write_buffer_size=16M

    [mysqlhotcopy]
    interactive-timeout

    what sites do you have there ? if something on CMSes, like wordpress, you can add caching plugin
     
  6. szk

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank You,
    Type of site is CMS but fully custom, it is not open source
    As I implemented params and restarted mysql service, memory usage on server dropped from 32% to 22%.
    Here are new results from mysqltuner

    Code:
    -------- General Statistics -------------------------------------------------
    -
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics ------------------------------------------
    -
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 106)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 1
    
    -------- Performance Metrics ------------------------------------------------
    [--] Up for: 6m 51s (1M q [4K qps], 479K conn, TX: 1B, RX: 325M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 419.0M global + 1.3M per thread (200 max threads)
    [OK] Maximum possible memory usage: 681.5M (33% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 10% (20/200)
    [OK] Key buffer size / total MyISAM indexes: 325.0M/282.0M
    [OK] Key buffer hit rate: 99.7% (1M cached / 5K reads)
    [OK] Query cache efficiency: 93.2% (457K cached / 490K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (26 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 1527
    [!!] Temporary tables created on disk: 37% (2K on disk / 6K total)
    [OK] Thread cache hit rate: 99% (27 created / 479K connections)
    [OK] Table cache hit rate: 94% (131 open / 138 opened)
    [OK] Open file limit used: 2% (203/8K)
    [OK] Table locks acquired immediately: 100% (82K immediate / 82K locks)
    -------- Recommendations ----------------------------------------------------
    -
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 512.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yeah, and max memory usage based on mysqltuner dropped from
    [OK] Maximum possible memory usage: 1.2G (61% of installed RAM)
    to
    [OK] Maximum possible memory usage: 681.5M (33% of installed RAM)

    The only thing you can do now, since this is custom CMS,

    [!!] Temporary tables created on disk: 37% (2K on disk / 6K total)


    you can review slow queries and optimize them
     
  8. szk

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thanks for great help and for next suggestions
    Greetings
     
Loading...

Share This Page