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.

Advice Tuning my server

Discussion in 'Workarounds and Optimization' started by stefano, Jul 7, 2015.

  1. stefano

    stefano Registered

    Joined:
    Jul 7, 2015
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Japan
    cPanel Access Level:
    Website Owner
    Hi all , as many others I Learn many info from you in the past and now I manage to run server independently , now I am new to sql tuning and I been searching around the web a while now and tuning seems to be a difficult think so I would appreciate some advice/help . Can anyone comment on the results below are good, bad or worst.

    I downloaded and launched the mysqltuner.pl and the result is :

    Code:
    [/COLOR][/FONT]
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5s (9 q [1.800 qps], 6 conn, TX: 14K, RX: 1K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 522.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 691.9M (8% of installed RAM)
    [OK] Slow queries: 0% (0/9)
    [OK] Highest usage of available connections: 0% (1/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
    [OK] Thread cache hit rate: 83% (1 created / 6 connections)
    [OK] Table cache hit rate: 89% (60 open / 67 opened)
    [OK] Open file limit used: 0% (16/5K)
    [OK] Table locks acquired immediately: 100% (70 immediate / 70 locks)
    [!!] Connections aborted: 16%
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB BufferPool Size :450.0M
    [--] InnoDB BufferPool Inst :1
    [OK] InnoDB buffer pool / data size: 450.0M/302.3M
    [OK] InnoDB buffer pool instances: 1
    [OK] InnoDB log waits: 0
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Restrict Host for user@% to user@SpecificDNSorIp
        MySQL started within last 24 hours - recommendations may be inaccurate
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_limit (> 1M, or use smaller result sets)
    
    [FONT=Tahoma][COLOR=rgb(0, 0, 0)]
    my.cnf as follow

    Code:
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    query_cache_type = 1
    query_cache_size= 16777216
    query_cache_limit=1048576
    
    innodb_buffer_pool_instances =1
    innodb_buffer_pool_size = 450M
    
    tmp_table_size      = 32M
    max_heap_table_size = 32M
    join_buffer_size=256K
    
    skip-host-cache
    skip-name-resolve
    explicit_defaults_for_timestamp=TRUE
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    and below the server information and memory

    Code:
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    processor       : 2
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    processor       : 3
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    processor       : 4
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    processor       : 5
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 6
    model name      : QEMU Virtual CPU version 0.9.1
    stepping        : 3
    cpu MHz         : 2000.074
    cache size      : 32 KB
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 4
    wp              : yes
    flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
    bogomips        : 4000.14
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 40 bits physical, 48 bits virtual
    power management:
    
    [root@sub0000540810 ~]# free
                 total       used       free     shared    buffers     cached
    Mem:       8060476    6786964    1273512          0     305988    4918716
    -/+ buffers/cache:    1562260    6498216
    Swap:      1048568     376732     671836
    
    Code:
    show variables like '%query_cache%';
    +------------------------------+----------+
    | Variable_name                | Value    |
    +------------------------------+----------+
    | have_query_cache             | YES      |
    | query_cache_limit            | 1048576  |
    | query_cache_min_res_unit     | 4096     |
    | query_cache_size             | 16777216 |
    | query_cache_type             | ON       |
    | query_cache_wlock_invalidate | OFF      |
    +------------------------------+----------+
    6 rows in set (0.00 sec)
    
    Code:
    mysql> SHOW STATUS LIKE 'qcache%';
    +-------------------------+----------+
    | Variable_name           | Value    |
    +-------------------------+----------+
    | Qcache_free_blocks      | 1        |
    | Qcache_free_memory      | 11320176 |
    | Qcache_hits             | 3571     |
    | Qcache_inserts          | 1934     |
    | Qcache_lowmem_prunes    | 0        |
    | Qcache_not_cached       | 176      |
    | Qcache_queries_in_cache | 1666     |
    | Qcache_total_blocks     | 3538     |
    +-------------------------+----------+
    8 rows in set (0.00 sec)
    
    I am using Mysql 5.6.12
    Thanks in advance for your help
    STef
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,478
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You need to have MySQL up for at least 24 hours.
     
  3. stefano

    stefano Registered

    Joined:
    Jul 7, 2015
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Japan
    cPanel Access Level:
    Website Owner
    Hi Stefano thx for your reply I will post a new update tomorrow , hope some one can help
     
  4. stefano

    stefano Registered

    Joined:
    Jul 7, 2015
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Japan
    cPanel Access Level:
    Website Owner
    Hi Infopro the follow is the latest result , can you please advice
    Thx
    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 5h 10m 1s (488K q [4.654 qps], 9K conn, TX: 2B, RX: 114M)
    [--] Reads / Writes: 85% / 15%
    [--] Total buffers: 522.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 691.9M (8% of installed RAM)
    [OK] Slow queries: 0% (1/488K)
    [OK] Highest usage of available connections: 5% (9/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
    [OK] Key buffer hit rate: 100.0% (465K cached / 30 reads)
    [OK] Query cache efficiency: 70.9% (305K cached / 431K selects)
    [!!] Query cache prunes per day: 38311
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 35K sorts)
    [!!] Joins performed without indexes: 1369
    [!!] Temporary tables created on disk: 58% (6K on disk / 11K total)
    [OK] Thread cache hit rate: 99% (9 created / 9K connections)
    [OK] Table cache hit rate: 33% (2K open / 5K opened)
    [OK] Open file limit used: 3% (160/5K)
    [OK] Table locks acquired immediately: 100% (222K immediate / 222K locks)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB BufferPool Size :450.0M
    [--] InnoDB BufferPool Inst :1
    [OK] InnoDB buffer pool / data size: 450.0M/310.3M
    [OK] InnoDB buffer pool instances: 1
    [OK] InnoDB log waits: 0
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Restrict Host for user@% to user@SpecificDNSorIp
        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
    Variables to adjust:
        query_cache_size (> 16M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    Note that you can always implement some of the recommendations provided by the MySQL tuner, let MySQL run for another 24 hours, and post the updated results for user-feedback. You may also want to post the results on forums where more discussions of MySQL optimization occur (e.g. WebHostingTalk or StackOverflow) as often times the users on our forums focus on discussions of the cPanel/WHM software itself.

    Thank you.
     
Loading...

Share This Page