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.

Need help with mysql optimization

Discussion in 'Workarounds and Optimization' started by tictocman, Aug 4, 2015.

  1. tictocman

    tictocman Member

    Joined:
    Aug 4, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Hello,

    I have been working around until I finally came looking for your help.

    Our server has got 3GB dedicated Ram and uses exclusively MyIsam tables.

    Here is the my.cnf file:
    Code:
    
    [client]
    port        = 3306
    socket        = /var/run/mysqld/mysqld.sock
    
    
    
    [mysqld_safe]
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0
    
    [mysqld]
    
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket        = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir        = /usr
    datadir        = /var/lib/mysql
    tmpdir        = /tmp
    
    skip-external-locking
    
    bind-address        = 127.0.0.1
    
    max_allowed_packet    = 64M
    thread_stack        = 192K
    thread_cache_size       = 8
    
    key_buffer = 768M
    query_cache_size = 128M
    table_cache = 64
    query_cache_type = 1
    query_cache_limit = 10M
    default-storage-engine=MyISAM
    wait_timeout = 30
    interactive_timeout = 30
    tmp_table_size = 32M
    max_heap_table_size = 32M
    
    max_connections = 50
    
    sort_buffer_size = 4M
    read_rnd_buffer_size = 2M
    
    
    init_connect='SET collation_connection = utf8_unicode_ci'
    init_connect='SET NAMES utf8'
    character-set-server = utf8
    collation-server = utf8_unicode_ci
    skip-character-set-client-handshake
    
    default_storage_engine=MyISAM
    skip-innodb
    
    log-slow-queries=/var/log/mysql/slow-queries.log
    
    [client]
    default-character-set = utf8
    
    
    myisam-recover = BACKUP
    
    expire_logs_days    = 1
    max_binlog_size         = 10M
    
    
    
    [mysqldump]
    quick
    quote-names
    




    When we run ./mysqltuner.pl we get this result after more than 24 hours use:

    Code:
    >>  MySQLTuner 1.4.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] Logged in using credentials from debian maintenance account.
    [OK] Currently running supported MySQL version 5.5.44-0ubuntu0.12.04.1-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 248M (Tables: 369)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 1
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 3h 7m 31s (78M q [803.119 qps], 126K conn, TX: 107B, RX: 30B)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 928.0M global + 6.4M per thread (50 max threads)
    [OK] Maximum possible memory usage: 1.2G (41% of installed RAM)
    [OK] Slow queries: 0% (11/78M)
    [OK] Highest usage of available connections: 72% (36/50)
    [OK] Key buffer size / total MyISAM indexes: 768.0M/200.1M
    [OK] Key buffer hit rate: 99.8% (3B cached / 6M reads)
    [OK] Query cache efficiency: 77.3% (60M cached / 77M selects)
    [!!] Query cache prunes per day: 14481876
    [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 5M sorts)
    [!!] Joins performed without indexes: 717
    [OK] Temporary tables created on disk: 5% (88K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (386 created / 126K connections)
    [!!] Table cache hit rate: 0% (64 open / 218K opened)
    [OK] Open file limit used: 10% (104/1K)
    [OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_open_cache (> 64)
    
    


    Thanks for helping me out.
     
    #1 tictocman, Aug 4, 2015
    Last edited by a moderator: Aug 4, 2015
  2. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Hello,

    From what I'm seeing, first you need to add table_open_cache, but what's the best value to use?

    Note: You need to perform these steps in peak hours to get the best result.

    1. Find out total tables of your database, type mysql to login into mysql server, then execute this command
    Code:
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
    2. Find threads currently connected to your database, execute this command:
    Code:
    show global status like '%Threads_connected%';
    3. Calculate the best value for table_open_cache using this formula:
    Code:
    table_open_cache = (total_tables x Threads_connected) / 2
    Note: We divided it here as not all the users are accessing all the tables.

    To help with the rest of the values, please get the results using MySQL Tuning Primer after 48 hours of MySQL uptime:
    https://launchpad.net/mysql-tuning-primer

    Waiting the results.

    Regards,
     
  3. tictocman

    tictocman Member

    Joined:
    Aug 4, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Thank you very much,
    I put the new variable to the test and restarted the server.

    I also run (for testing only ) tuning-primer but it finishes before the end of the script because of this error:

    tuning-primer.sh: line 1318: *100/: syntax error: operand expected (error token is "*100/")

    Thanks.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    A search of this error message indicates it happens when InnoDB is disabled. You do have the skip-innodb option added to your /etc/my.cnf file. Is there any reason you have disabled it on this server?

    Thank you.
     
  5. tictocman

    tictocman Member

    Joined:
    Aug 4, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    You were right, I skipped innodb and now runs fine.
    I will let it for the whole weekend and let you know.

    Thank you.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,774
    Likes Received:
    663
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  7. tictocman

    tictocman Member

    Joined:
    Aug 4, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Hello,

    After 4 days up, this is the mysqltuner

    Code:
    >>  MySQLTuner 1.4.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] Logged in using credentials from debian maintenance account.
    [OK] Currently running supported MySQL version 5.5.44-0ubuntu0.12.04.1
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 251M (Tables: 369)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 3
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5d 20h 6m 43s (408M q [810.418 qps], 682K conn, TX: 566B, RX: 161B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 224.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 629.8M (20% of installed RAM)
    [OK] Slow queries: 0% (94/408M)
    [OK] Highest usage of available connections: 26% (40/151)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/202.1M
    [OK] Key buffer hit rate: 100.0% (28B cached / 4M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (190 temp sorts / 63M sorts)
    [!!] Joins performed without indexes: 5783
    [OK] Temporary tables created on disk: 13% (5M on disk / 41M total)
    [OK] Thread cache hit rate: 99% (2K created / 682K connections)
    [!!] Table cache hit rate: 0% (400 open / 107K opened)
    [OK] Open file limit used: 61% (630/1K)
    [OK] Table locks acquired immediately: 99% (1B immediate / 1B locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_open_cache (> 400)
    
    
     
    #7 tictocman, Aug 11, 2015
    Last edited by a moderator: Aug 11, 2015
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,774
    Likes Received:
    663
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    To clarify, is this a cPanel server, or just a standard server running MySQL? cPanel is not typically supported on Ubuntu.

    Thank you.
     
  9. tictocman

    tictocman Member

    Joined:
    Aug 4, 2015
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    It is a standard server

    Thanks.
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,774
    Likes Received:
    663
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You may find more useful feedback at a website such as WebHostingTalk or Stackoverflow. The forums here are primarily intended for help with cPanel servers, so while you are welcome to seek user-feedback, you likely won't get as many responses as you would on other forums.

    Thank you.
     
Loading...

Share This Page