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 my.cnf File Optimization help

Discussion in 'Workarounds and Optimization' started by ashishpatel1992, Jun 8, 2014.

  1. ashishpatel1992

    Joined:
    Jan 8, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I need help for Optimizing my MySQL Server for my Dedicated server with configuration of Intel Core 2 Duo CPU E7500 @ 2.93GHz 4GB of RAM installed. OS installed is CentOS 6.5. Mostly on my Dedicated Server I run Wordpress based websites over MyISAM Tables. The Server Response Time is greater than 1.5ms which I would like to reduce.
    If I open a page which is only basic php it takes 800ms but if I open page with (Wordpress Website)php and mysql it takes 1.5ms to respond.

    Some Information from phpmyadmin (All Status Variables):-
    Code:
    Aborted clients 92.8k
    Aborted connects 2.3k
    My Current my.cnf configuration is:-
    Code:
    [mysqldump]
    quick
    max_allowed_packet=16M
    [mysql]
    no-auto-rehash
    [mysqld]
    server-id=14
    table_open_cache=512
    default-storage-engine=MyISAM
    sort_buffer_size=2M
    thread_concurrency=4
    open_files_limit=6192
    key_buffer=384M
    read_rnd_buffer_size=8M
    thread_cache_size=8
    query_cache_size=32M
    skip-external-locking
    innodb_file_per_table=1
    connect_timeout=10
    max_user_connections=60
    wait_timeout=40
    max_allowed_packet=1M
    myisam_sort_buffer_size=64M
    read_buffer_size=2M
    max_connections=300
    [mysqlhotcopy]
    interactive-timeout
    [myisamchk]
    key_buffer=256M
    write_buffer=2M
    read_buffer=2M
    sort_buffer_size=256M
    and the MySQLTuner.pl output is:-

    Code:
    MySQLTuner 1.3.0 - Major Hayden 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.16 [ [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: 622M (Tables: 962)
    [ [0;34m-- [0m] Data in InnoDB tables: 6M (Tables: 155)
    [ [0;34m-- [0m] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [ [0;34m-- [0m] Data in MEMORY tables: 0B (Tables: 3)
    [ [0;31m!! [0m] Total fragmented tables: 247
    
    -------- Security Recommendations  -------------------------------------------
    [ [0;32mOK [0m] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [ [0;34m-- [0m] Up for: 59d 0h 20m 19s (144M q [28.319 qps], 2M conn, TX: 432B, RX: 22B)
    [ [0;34m-- [0m] Reads / Writes: 94% / 6%
    [ [0;34m-- [0m] Total buffers: 576.0M global + 12.5M per thread (300 max threads)
    [ [0;31m!! [0m] Maximum possible memory usage: 4.2G (112% of installed RAM)
    [ [0;32mOK [0m] Slow queries: 0% (83/144M)
    [ [0;32mOK [0m] Highest usage of available connections: 27% (82/300)
    [ [0;32mOK [0m] Key buffer size / total MyISAM indexes: 384.0M/234.3M
    [ [0;32mOK [0m] Key buffer hit rate: 99.7% (3B cached / 11M reads)
    [ [0;31m!! [0m] Query cache is disabled
    [ [0;32mOK [0m] Sorts requiring temporary tables: 0% (4 temp sorts / 42M sorts)
    [ [0;31m!! [0m] Joins performed without indexes: 96992
    [ [0;31m!! [0m] Temporary tables created on disk: 33% (5M on disk / 15M total)
    [ [0;32mOK [0m] Thread cache hit rate: 99% (4K created / 2M connections)
    [ [0;31m!! [0m] Table cache hit rate: 0% (512 open / 620K opened)
    [ [0;32mOK [0m] Open file limit used: 16% (1K/6K)
    [ [0;32mOK [0m] Table locks acquired immediately: 99% (158M immediate / 158M locks)
    [ [0;32mOK [0m] InnoDB buffer pool / data size: 128.0M/6.3M
    [ [0;32mOK [0m] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_type (=1)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_open_cache (> 512)
    I would be grateful to you for your optimized valuable suggestion and times.
     
  2. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    621
    Likes Received:
    6
    Trophy Points:
    18
    im noob but i would raise
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    ..to 32M or 64M, then restart mysqld service
    but this wont improve main issue you have - 800ms to load basic .php file
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    Feel free to try implementing some of the suggestions from the tuner output, let MySQL run for an additional 24 hours, and then run the tuner again to see the updated results.

    Thank you.
     
  4. ashishpatel1992

    Joined:
    Jan 8, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Do I really need to restart the MySQL server so that old results are not again calculated by MySQLTuner or I can do changes without restarting MySQL Server
    Regards,
    Ashish
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,814
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  6. johntang

    johntang Registered

    Joined:
    Jun 10, 2014
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I think you need lowering key_buffer and sort_buffer_size will boost also. and other variables are nice. then service mysqld restart.
     
Loading...

Share This Page