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 johnchristy, Apr 27, 2014.

  1. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    CentOS 6.5
    Ram 3GB
    Core 8

    MySQL Tuner

    Code:
    
     >>  MySQLTuner 1.3.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] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 7M (Tables: 465)
    [--] Data in InnoDB tables: 82M (Tables: 3558)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 15
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 18h 34m 37s (459K q [3.000 qps], 6K conn, TX: 564M, RX: 100M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (20% of installed RAM)
    [OK] Slow queries: 0% (2/459K)
    [OK] Highest usage of available connections: 4% (7/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.3M
    [OK] Key buffer hit rate: 99.4% (274K cached / 1K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 92K sorts)
    [!!] Joins performed without indexes: 1280
    [OK] Temporary tables created on disk: 9% (6K on disk / 68K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 1% (400 open / 32K opened)
    [OK] Open file limit used: 0% (77/17K)
    [OK] Table locks acquired immediately: 100% (872K immediate / 872K locks)
    [OK] InnoDB buffer pool / data size: 128.0M/82.8M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_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)
        thread_cache_size (start at 4)
        table_cache (> 400)
    
    Pls check and let me know what are the exact changes I should do. If you could provide me the file name and the code I need to change/add it would be helpful

    Thanks
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    add this in my.cnf

    under [mysqld]

    query_cache_type = 1
    query_cache_size = 30M
    table_cache_size = 1000
    thread_cache_size = 20
    join_buffer_size = 512K
     
  3. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    This didnt work

    I got this error while restarting mysql :

    Starting MySQL... ERROR! The server quit without updating PID file. I had to remove these entries from my.cnf and restart. Any idea?
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    can you copy your my.cnf here ?
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,833
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Could you provide the output from your MySQL error log (/var/lib/mysql/$hostname.err) when adding those entries? It should output the specific entry that was invalid.

    Thank you.
     
  6. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Sure


    [~]# cat /etc/my.cnf
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    log-slow-queries=/var/lib/mysql/slow.log
    open_files_limit=17058
     
    #6 johnchristy, Apr 28, 2014
    Last edited: Apr 28, 2014
  7. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi michael


    140429 10:30:00 mysqld_safe mysqld from pid file /var/lib/mysql/hostname.pid ended
    140429 10:30:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    140429 10:30:01 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
    140429 10:30:01 [Note] Plugin 'FEDERATED' is disabled.
    140429 10:30:01 InnoDB: The InnoDB memory heap is disabled
    140429 10:30:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140429 10:30:01 InnoDB: Compressed tables use zlib 1.2.3
    140429 10:30:01 InnoDB: Using Linux native AIO
    140429 10:30:01 InnoDB: Initializing buffer pool, size = 128.0M
    140429 10:30:01 InnoDB: Completed initialization of buffer pool
    140429 10:30:01 InnoDB: highest supported file format is Barracuda.
    140429 10:30:01 InnoDB: Waiting for the background threads to start
    140429 10:30:02 InnoDB: 5.5.36 started; log sequence number 192786833
    140429 10:30:02 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache_size=1000'
    140429 10:30:02 [ERROR] Aborting

    140429 10:30:02 InnoDB: Starting shutdown...
    140429 10:30:03 InnoDB: Shutdown completed; log sequence number 192786833
    140429 10:30:03 [Note] /usr/sbin/mysqld: Shutdown complete

    140429 10:30:03 mysqld_safe mysqld from pid file /var/lib/mysql/hostname.pid ended

    - - - Updated - - -

    the variable is table_cache and not table_cache_size?

    and michael these values should work best with my setup?

    query_cache_type = 1
    query_cache_size = 30M
    table_cache_size = 1000
    thread_cache_size = 20
    join_buffer_size = 512K
     
  8. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You should use correct variables for your mysql version:

    Use:

    Instead of:

    I'm not sure if slow_query_log_file= enables the log, or you need to use the slow_query_log variable also.

    edit:

    Either of these would work if you need to use it:
    slow_query_log
    slow_query_log=1
     
    #8 Archmactrix, Apr 29, 2014
    Last edited: Apr 29, 2014
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes sorry

    use table_cache instead of table_cache_size
    in newer versions its table_cache_size
     
  10. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    cool, Ill try this and will update you
     
Loading...

Share This Page