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 optimization

Discussion in 'Workarounds and Optimization' started by tank, Oct 31, 2015.

  1. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Hello all,

    I have read lots and lots of threads and just want to get some thoughts on tuning mysql settings. I do not have alot of sites but some of these are intense databases.

    Here is my conf
    Code:
    [mysqld]
    # SAFETY #
    low_priority_updates=1
    concurrent_insert=ALWAYS
    local-infile=0
    
    # CACHES AND LIMITS #
    query_cache_size=200M
    query_cache_limit=4M
    key_buffer_size=256M
    tmp_table_size=144M
    max_heap_table=144M
    table_definition_cache=4096
    table_open_cache=11k
    open_files_limit=15020
    max_connections=600
    
    max_allowed_packet=268435456
    read_buffer_size=1Mthread_cache_size=3M
    join_buffer_size=3M
    innodb_buffer_pool_size=300M
    
    max_user_connections=1000
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    # LOGGING #
    long_query_time = 5
    log-slow-queries=/var/lib/mysql/slow.log
    Here is my tuning script.
    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.46-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 589M (Tables: 2032)
    [--] Data in InnoDB tables: 172M (Tables: 1496)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 74)
    [!!] Total fragmented tables: 185
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4m 47s (3K q [13.645 qps], 339 conn, TX: 34M, RX: 660K)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 916.0M global + 6.5M per thread (600 max threads)
    [OK] Maximum possible memory usage: 4.7G (30% of installed RAM)
    [OK] Slow queries: 0% (2/3K)
    [OK] Highest usage of available connections: 0% (5/600)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/158.1M
    [OK] Key buffer hit rate: 96.2% (45K cached / 1K reads)
    [OK] Query cache efficiency: 45.3% (1K cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 390 sorts)
    [!!] Temporary tables created on disk: 33% (146 on disk / 441 total)
    [OK] Thread cache hit rate: 98% (5 created / 339 connections)
    [OK] Table cache hit rate: 99% (3K open / 3K opened)
    [OK] Open file limit used: 17% (4K/23K)
    [OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)
    [OK] InnoDB data size / buffer pool: 173.0M/300.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 144M)
        max_heap_table_size (> 144M)
    I am thinking of changes these variables

    Code:
    max_connections= 500 # was 600
    query_cache_limit=3M #was 4M
    tmp_table_size=256M # 144M
    max_heap_table=256M # 144M
    

    How to I get the temporary tables to stop forming? I assume they are bad? Any other recommendations would be great.
     
  2. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    I did not make any changes and here is my mysql tuner again.
    Code:
     >>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
    
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    
     >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    
    [OK] Currently running supported MySQL version 5.5.46-cll
    
    [OK] Operating on 64-bit architecture
    
    
    -------- Storage Engine Statistics -------------------------------------------
    
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    
    [--] Data in MyISAM tables: 747M (Tables: 2032)
    
    [--] Data in InnoDB tables: 238M (Tables: 1496)
    
    [--] Data in MEMORY tables: 5M (Tables: 74)
    
    [!!] Total fragmented tables: 181
    
    
    -------- Security Recommendations  -------------------------------------------
    
    [OK] There is no anonymous account in all database users
    
    [OK] All database users have passwords assigned
    
    [!!] User 'munin@localhost' has user name as password.
    
    [!!] User *****' hasn't specific host restriction.
    
    [!!] User '*******' hasn't specific host restriction.
    
    [!!] User '*******' hasn't specific host restriction.
    
    [--] There is 605 basic passwords in the list.
    
    
    -------- Performance Metrics -------------------------------------------------
    
    [--] Up for: 2d 0h 31m 22s (2M q [16.830 qps], 247K conn, TX: 29B, RX: 658M)
    
    [--] Reads / Writes: 87% / 13%
    
    [--] Binary logging is disabled
    
    [--] Total buffers: 916.0M global + 6.5M per thread (600 max threads)
    
    [OK] Maximum reached memory usage: 981.0M (6.17% of installed RAM)
    
    [OK] Maximum possible memory usage: 4.7G (30.31% of installed RAM)
    
    [OK] Slow queries: 0% (2/2M)
    
    [OK] Highest usage of available connections: 1% (10/600)
    
    [OK] Aborted connections: 0.00%  (2/247105)
    
    [OK] Query cache efficiency: 64.2% (1M cached / 2M selects)
    
    [!!] Query cache prunes per day: 4944
    
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 159K sorts)
    
    [!!] Temporary tables created on disk: 43% (47K on disk / 108K total)
    
    [OK] Thread cache hit rate: 99% (10 created / 247K connections)
    
    [OK] Table cache hit rate: 98% (6K open / 6K opened)
    
    [OK] Open file limit used: 24% (5K/23K)
    
    [OK] Table locks acquired immediately: 99% (963K immediate / 964K locks)
    
    
    -------- MyISAM Metrics -----------------------------------------------------
    
    [!!] Key buffer used: 27.7% (74M used / 268M cache)
    
    [OK] Key buffer size / total MyISAM indexes: 256.0M/158.3M
    
    [OK] Read Key buffer hit rate: 99.9% (28M cached / 25K reads)
    
    [!!] Write Key buffer hit rate: 26.6% (218K cached / 160K writes)
    
    
    -------- InnoDB Metrics -----------------------------------------------------
    
    [--] InnoDB is enabled.
    
    [OK] InnoDB buffer pool / data size: 300.0M/238.2M
    
    [OK] InnoDB buffer pool instances: 1
    
    [OK] InnoDB Used buffer: 99.99% (19198 used/ 19199 total)
    
    [OK] InnoDB Read buffer efficiency: 99.91% (24618464 hits/ 24640528 total)
    
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    
    [OK] InnoDB log waits: 0.00% (0 waits / 2869 writes)
    
    
    -------- AriaDB Metrics -----------------------------------------------------
    
    [--] AriaDB is disabled.
    
    
    -------- Replication Metrics -------------------------------------------------
    
    [--] No replication slave(s) for this server.
    
    [--] This is a standalone server..
    
    
    -------- Recommendations -----------------------------------------------------
    
    General recommendations:
    
        Run OPTIMIZE TABLE to defragment tables for better performance
    
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    
        Restrict Host for user@% to user@SpecificDNSorIp
    
        Increasing the query_cache size over 128M may reduce performance
    
        When making adjustments, make tmp_table_size/max_heap_table_size equal
    
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
    
    Variables to adjust:
    
        query_cache_size (> 200M) [see warning above]
    
        tmp_table_size (> 144M)
    
        max_heap_table_size (> 144M)
    
     
    #2 tank, Nov 2, 2015
    Last edited: Nov 2, 2015
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    The second result is likely more accurate because MySQL was running for a longer period of time. You can try modifying your /etc/my.cnf file with changes based on the "Variables to adjust" section of the tuner results to see if that helps.

    Thank you.
     
  4. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Yea i understand that. I was hoping for more help than that. We all know that this is a simple program that does not do everything we need to optimize a MYSQL setting.

    Specifically it does not address to issues i am having.

    1.
    Query cache prunes per day: 4944
    2.
    [!!] Temporary tables created on disk: 43% (47K on disk / 108K total)

    Again I was looking for some insight to solving these.

    Thanks
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You may need to consult with a qualified system administrator, or post to another forum such as StackOverflow if you do not receive sufficient user-feedback on this thread.

    Thank you.
     
Loading...

Share This Page