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.

Optimizing MySql Server

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

  1. AmazingTrans

    AmazingTrans Registered

    Joined:
    Jul 27, 2015
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    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 tuning-primer.sh (./tuning-primer.sh > result.txt) , but i have a couple of errors:
    Code:
    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"
    user@linux:~$

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

    Code:
     >>  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
    [[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 Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    667
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page