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.

MySQLTuner shows wrong amount of installed RAM?

Discussion in 'Workarounds and Optimization' started by Patrik Johnsson, Aug 11, 2015.

  1. Patrik Johnsson

    Patrik Johnsson Registered

    Joined:
    Dec 4, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello, when I run mysqltuner.pl on my server with 16GB of RAM, it says that I only have 1.56GB available?

    Code:
    nope@vps05 [/]# perl mysqltuner.pl
    >>  MySQLTuner 1.5.0 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.23
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 1M (Tables: 29)
    [--] Data in InnoDB tables: 480K (Tables: 14)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] There is not basic password file list !
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 18m 8s (213 q [0.196 qps], 82 conn, TX: 255K, RX: 30K)
    [--] Reads / Writes: 67% / 33%
    [--] Binary logging is disabled
    [--] Total buffers: 4.5G global + 2.5M per thread (600 max threads)
    [!!] Maximum reached memory usage: 4.5G (288.95% of installed RAM)
    [!!] Maximum possible memory usage: 6.0G (381.96% of installed RAM)
    [OK] Slow queries: 0% (0/213)
    [OK] Highest usage of available connections: 0% (3/600)
    [!!] Aborted connections: 3.66%  (3/82)
    [!!] Key buffer used: 18.2% (781M used / 4B cache)
    [OK] Key buffer size / total MyISAM indexes: 4.0G/437.0K
    [!!] Read Key buffer hit rate: 88.6% (690 cached / 79 reads)
    [!!] Write Key buffer hit rate: 5.3% (225 cached / 213 writes)
    [!!] Query cache efficiency: 0.0% (0 cached / 59 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6 sorts)
    [OK] Temporary tables created on disk: 2% (1 on disk / 49 total)
    [OK] Thread cache hit rate: 96% (3 created / 82 connections)
    [OK] Table cache hit rate: 94% (127 open / 134 opened)
    [OK] Open file limit used: 0% (109/50K)
    [OK] Table locks acquired immediately: 100% (119 immediate / 119 locks)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 5.0M/480.0K
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [OK] InnoDB Used buffer: 80.25% (256 used/ 319 total)
    [!!] InnoDB Read buffer efficiency: 87.00% (1720 hits/ 1977 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Reduce or eliminate unclosed connections and network issues
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 5M, or use smaller result sets)
        innodb_buffer_pool_instances (=1)
    nothing@vps05 [/]#
    
    My /etc/my.cnf file looks like this:
    
    [mysqld]
    local-infile=0
    max_connections = 600
    max_user_connections=1500
    key_buffer_size = 4096M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 60
    connect_timeout = 10
    table_open_cache=2048
    tmp_table_size = 256M
    max_heap_table_size = 512M
    max_allowed_packet=268435456
    net_buffer_length = 16384
    max_connect_errors = 10
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 5M
    query_cache_size = 256M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65535
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    log-error
    external-locking=FALSE
    open_files_limit=50000
    innodb_buffer_pool_size=0
    innodb_flush_log_at_trx_commit=2
    thread_cache=16
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [mysqld_safe]
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    [myisamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    ### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K
    
    
    Any suggestions would be most welcome.
     
    #1 Patrik Johnsson, Aug 11, 2015
    Last edited by a moderator: Aug 11, 2015
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Please keep in mind that tuner is a third-party application that's not developed by cPanel. Per the output:

    You may want to report this issue to their website.

    Thank you.
     
  3. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    I am noticing this too on my server, and I doubt this is the bug, but something else (some calculation for memory)

    Hopefully somebody more experienced than us will respond to this thread (or mine) as i am asking for similar things :)
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Do you notice the correct memory output when using the "free -m" command or when reviewing memory information in Web Host Manager?

    Thank you.
     
  5. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    In my case i do;

    total used free shared buffers cached
    Mem: 32007 13539 18468 1150 142 3059
    -/+ buffers/cache: 10337 21670
    Swap: 8189 137 8052



    MySqltuner:
    [!!] Maximum possible memory usage: 15.8G (505% of installed RAM)



    Tunning-Primer:
    MEMORY USAGE
    Max Memory Ever Allocated : 11.14 G
    Configured Max Per-thread Buffers : 4.24 G
    Configured Max Global Buffers : 10.54 G
    Configured Max Memory Limit : 14.79 G
    Physical Memory : 31.25 G
    Max memory limit seem to be within acceptable norms


    Completely weird.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I don't see any aspect of the cPanel/WHM software that would cause the false result in the third-party MySQL tuning application. I suggest consulting with the developers of that application to report the bug, as it's not an application that cPanel develops.

    Thank you.
     
  7. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    Sounds good / ill do that - just in case to see what they say about it.

    Once i hear from them, ill respond to this thread (so, other people that come here looking for the same answers / will be able to find it)
     
  8. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
  9. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    There is also this:
    https://github.com/major/MySQLTuner-perl/issues/96

    Looks like it has to do something with some calculations (how memory is used by MySQL) - as I assumed.

    This actually might not be an error? but a important 'warning' ?

    I wish (it would be nice) eva2000 would respond :)
     
  10. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
Loading...

Share This Page