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 - Dedicated Server

Discussion in 'Workarounds and Optimization' started by NEK4TE, Aug 8, 2015.

  1. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    Good evening ladies and gents,

    Would like to ask MySQL Guru's to help me optimize MySQL / Server the best, possible.

    Server's specs:
    E3-1245v3
    32GB DDR3
    2x2TB

    Current Output of 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] Currently running supported MySQL version 5.6.23
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 118M (Tables: 157)
    [--] Data in InnoDB tables: 3G (Tables: 376)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 33
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 4h 40m 0s (2M q [25.451 qps], 148K conn, TX: 7B, RX: 572M)
    [--] Reads / Writes: 65% / 35%
    [--] Total buffers: 11.0G global + 41.0M per thread (256 max threads)
    [!!] Maximum possible memory usage: 21.3G (680% of installed RAM)
    [OK] Slow queries: 0% (15/2M)
    [OK] Highest usage of available connections: 8% (21/256)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/23.0M
    [OK] Key buffer hit rate: 98.9% (2M cached / 21K reads)
    [OK] Query cache efficiency: 73.7% (1M cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 309K sorts)
    [!!] Joins performed without indexes: 865
    [OK] Temporary tables created on disk: 5% (22K on disk / 397K total)
    [OK] Thread cache hit rate: 99% (21 created / 148K connections)
    [OK] Table cache hit rate: 89% (1K open / 1K opened)
    [OK] Open file limit used: 0% (525/200K)
    [OK] Table locks acquired immediately: 99% (661K immediate / 661K locks)
    [OK] InnoDB buffer pool / data size: 8.0G/3.7G
    [OK] 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
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 8.0M, or always use indexes with joins)
    
    
    My.CNF
    Code:
    [myisamchk]
    sort_buffer_size=1024M
    write_buffer_size=256M
    key_buffer_size=1024M
    ft_min_word_len=3
    read_buffer_size=256M
    
    [mysqldump]
    max_allowed_packet=512M
    quick
    
    [mysqld]
    max_allowed_packet=268435456
    query_prealloc_size=262144
    query_cache_size=1024M
    max_heap_table_size=1024M
    transaction_alloc_block_size=8192
    connect_timeout=15
    innodb_file_per_table=1
    back_log=50
    socket="/var/lib/mysql/mysql.sock"
    max_connect_errors=1000000
    ft_min_word_len=3
    thread_cache_size=500
    join_buffer_size=8M
    sort_buffer_size=16M
    group_concat_max_len=1024
    open_files_limit=50000
    low_priority_updates=1
    local-infile=0
    query_cache_limit=30M
    max_connections=256
    myisam_max_sort_file_size=2048M
    bulk_insert_buffer_size=8M
    interactive_timeout=300
    default-storage-engine=MyISAM
    net_buffer_length=16384
    myisam_sort_buffer_size=768M
    max_seeks_for_key=1000
    transaction_prealloc_size=4096
    table_definition_cache=32000
    range_alloc_block_size=4096
    query_cache_type=1
    wait_timeout=180
    key_buffer_size=1024M
    datadir="/var/lib/mysql"
    query_alloc_block_size=65536
    tmp_table_size=1024M
    innodb_buffer_pool_size=8G
    innodb_buffer_pool_instances=8
    max_write_lock_count=4
    table_open_cache=100000
    read_rnd_buffer_size=786432
    concurrent_insert=2
    expire_logs_days=10
    read_buffer_size=16M
    max_length_for_sort_data=1024
    
    [mysqlhotcopy]
    interactive-timeout
    
    For some reason, MySQLTuner is reporting weird MAX RAM Allocation, looks like it thinks its "32bit OS"?
    Code:
    [!!] Maximum possible memory usage: 21.3G (680% of installed RAM)
    
    Code:
    /usr/bin/perl: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped
    
    OS / Kernel Version
    Code:
    2.6.32-573.1.1.el6.x86_64 #1 SMP Sat Jul 25 17:05:50 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux (CentOS 6, Latest stable)
    
    Memory Limits
    Code:
    core file size          (blocks, -c) 1000000
    data seg size           (kbytes, -d) unlimited
    scheduling priority             (-e) 0
    file size               (blocks, -f) unlimited
    pending signals                 (-i) 127927
    max locked memory       (kbytes, -l) 64
    max memory size         (kbytes, -m) unlimited
    open files                      (-n) 4096
    pipe size            (512 bytes, -p) 8
    POSIX message queues     (bytes, -q) 819200
    real-time priority              (-r) 0
    stack size              (kbytes, -s) 8192
    cpu time               (seconds, -t) unlimited
    max user processes              (-u) 14335
    virtual memory          (kbytes, -v) unlimited
    file locks                      (-x) unlimited
    
    For couple of days (before opening this thread) I was tweaking/monitoring server myself, it doesn't look "bad" in my opinion :) but, i am seeing pretty high CPU usage.

    [Removed]

    Anything i can do to make it perform better?

    Thank you for your time and support.
     
    #1 NEK4TE, Aug 8, 2015
    Last edited by a moderator: Aug 10, 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
    Of course you will suffer from high load, your MySQL only uses 5.6GB of ram let alone that you've assigned 21.3GB of ram to MySQL although you don't have this amount.

    About ram given to MySQL, you can use this formula to calculate it:

    MySQL Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables)

    Global buffers include:
    key_buffer_size
    innodb_buffer_pool_size
    innodb_additional_mem_pool_size
    innodb_log_buffer_size
    query_cache_size

    Per thread variables include:
    read_buffer_size
    read_rnd_buffer_size
    sort_buffer_size
    join_buffer_size
    thread_stack
    net_buffer_length

    or you could use MySQL tuning primer to do the heavy lifting for you (calculation and stuff):
    https://launchpad.net/mysql-tuning-primer
     
  3. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    Thanks for responding.

    What exactly do you mean by ' your MySQL only uses 5.6GB of ram let alone that you've assigned 21.3GB of ram to MySQL although you don't have this amount ' - i am a bit confused by this, i apologize.

    The system has 32GB of RAM, currently there is around 24GB free

    Code:
                 total       used       free     shared    buffers     cached
    Mem:         32007       7846      24161         81         63        712
    -/+ buffers/cache:       7070      24936 
    Swap:         8189       2336       5853 
    
    Tuning primer Output:

    Code:
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 21 out of 610200 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 500
    Current threads_cached = 13
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 256
    Current threads_connected = 5
    Historic max_used_connections = 18
    The number of used connections is 7% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See "MEMORY USAGE" section to make sure you are not over-allocating
    
    INNODB STATUS
    Current InnoDB index space = 722 M
    Current InnoDB data space = 3.74 G
    Current InnoDB buffer pool free = 53 %
    Current innodb_buffer_pool_size = 8.00 G
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 10.73 G
    Configured Max Per-thread Buffers : 10.25 G
    Configured Max Global Buffers : 10.01 G
    Configured Max Memory Limit : 20.26 G
    Physical Memory : 31.25 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 23 M
    Current key_buffer_size = 1.00 G
    Key cache miss rate is 1 : 278
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 1.00 G
    Current query_cache_used = 13 M
    Current query_cache_limit = 30 M
    Current Query cache Memory fill ratio = 1.36 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 16 M
    Current read_rnd_buffer_size = 768 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 8.00 M
    You have had 238 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 200266 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 100000 tables
    Current table_definition_cache = 32000 tables
    You have a total of 667 tables
    You have 1249 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 1.00 G
    Current tmp_table_size = 1.00 G
    Of 131200 temp tables, 5% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 16 M
    Current table scan ratio = 802769 : 1
    read_buffer_size is over 8 MB there is probably no need for such a large read_buffer
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 25550
    Your table locking seems to be fine
    
    Please advise
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    I just wanted to make a quick note that it's import to upload screenshots/images directly to the thread instead of linking to third-party image hosting websites. Feel free to upload the image from your initial post and continue to seek user-feedback on your MySQL configuration.

    Thank you.
     
  5. NEK4TE

    NEK4TE Member

    Joined:
    Aug 8, 2015
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    cPanel Access Level:
    Website Owner
    Hello Michael,

    I apologize for that.

    I will be waiting (for hopefully) more suggestions about this.

    Regards
     
Loading...

Share This Page