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!

Optimizing MySql Server

Discussion in 'Workarounds and Optimization' started by AmazingTrans, Jul 27, 2015.

  1. AmazingTrans

    AmazingTrans Registered

    Jul 27, 2015
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    I have a 2gb ram memory, and somehow mysqld.bin is utilizing 20%+ memory when nobody is accessing the server. I just installed everything fresh. not sure why it is taking so much memory. I am utilizing InnoDb MySQL 5.6.23.
    When the database grows, does the mysqld.bin memory usage grow?

    I tried (./ > result.txt) , but i have a couple of errors:
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"
    would you like to provide a diff. socket? : n
    do you have your login handy? y
    user: xxxx
    password: xxxxxxxx
    tput: Unknown terminal "linux"
    woudl you like me to crate a ~/.mycnf file for you? n
    tput: Unknown terminal "linux"
    tput: Unknown terminal "linux"

    I downloaded sqltuner, and ran it by: sudo perl > result.txt.
    How does the recommendation below helps me to lower the memory usage?

     >>  MySQLTuner 1.5.0 - Major Hayden <>
    >>  Bug reports, feature requests, and downloads at
    >>  Run with '--help' for additional options and output filtering
    [[0;32mOK[0m] Currently running supported MySQL version 5.6.23
    [[0;32mOK[0m] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
    [[0;34m--[0m] Data in MyISAM tables: 118K (Tables: 1)
    [[0;34m--[0m] Data in InnoDB tables: 6M (Tables: 49)
    [[0;31m!![0m] Total fragmented tables: 2
    -------- Security Recommendations  -------------------------------------------
    [[0;31m!![0m] User '@linux' is an anonymous account.
    [[0;31m!![0m] User '@localhost' is an anonymous account.
    [[0;31m!![0m] User '@linux' has no password set.
    [[0;31m!![0m] User '@localhost' has no password set.
    [[0;31m!![0m] User '@linux' has user name as password.
    [[0;31m!![0m] User '@localhost' has user name as password.
    [[0;34m--[0m] There is 605 basic passwords in the list.
    -------- Performance Metrics -------------------------------------------------
    [[0;34m--[0m] Up for: 3h 48m 17s (12K q [0.924 qps], 3K conn, TX: 4M, RX: 2M)
    [[0;34m--[0m] Reads / Writes: 98% / 2%
    [[0;34m--[0m] Binary logging is disabled
    [[0;34m--[0m] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
    [[0;32mOK[0m] Maximum reached memory usage: 175.8M (8.78% of installed RAM)
    [[0;32mOK[0m] Maximum possible memory usage: 338.9M (16.93% of installed RAM)
    [[0;32mOK[0m] Slow queries: 0% (0/12K)
    [[0;32mOK[0m] Highest usage of available connections: 3% (6/151)
    [[0;32mOK[0m] Aborted connections: 0.96%  (37/3835)
    [[0;31m!![0m] Key buffer used: 18.2% (1M used / 8M cache)
    [[0;32mOK[0m] Key buffer size / total MyISAM indexes: 8.0M/161.0K
    [[0;32mOK[0m] Read Key buffer hit rate: 96.0% (25 cached / 1 reads)
    [[0;31m!![0m] Query cache is disabled
    [[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts)
    [[0;32mOK[0m] Temporary tables created on disk: 0% (0 on disk / 83 total)
    [[0;32mOK[0m] Thread cache hit rate: 99% (6 created / 3K connections)
    [[0;32mOK[0m] Table cache hit rate: 94% (131 open / 138 opened)
    [[0;32mOK[0m] Open file limit used: 0% (48/5K)
    [[0;32mOK[0m] Table locks acquired immediately: 100% (4K immediate / 4K locks)
    -------- InnoDB Metrics -----------------------------------------------------
    [[0;34m--[0m] InnoDB is enabled.
    [[0;32mOK[0m] InnoDB buffer pool / data size: 128.0M/6.8M
    [[0;31m!![0m] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [[0;31m!![0m] InnoDB Used buffer: 8.02% (657 used/ 8192 total)
    [[0;32mOK[0m] InnoDB Read buffer efficiency: 96.68% (18522 hits/ 19159 total)
    [[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 158 writes)
    -------- Replication Metrics -------------------------------------------------
    [[0;34m--[0m] No replication slave(s) for this server.
    [[0;34m--[0m] This is a standalone server..
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Remove Anonymous User account - there is 2 Anonymous account.
        Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
    Variables to adjust:
        query_cache_type (=1)
        innodb_buffer_pool_instances (=1)
    #1 AmazingTrans, Jul 27, 2015
    Last edited by a moderator: Jul 27, 2015
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice