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 ccalby, Nov 1, 2012.

  1. ccalby

    ccalby Registered
    PartnerNOC

    Joined:
    May 29, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    Any optimization tips? This is a VPS on a node (CPU = Fair Share)
    Load is always around 4-11 and it needs to be reduced.
    Code:
    root@server [/]# free -m
                 total       used       free     shared    buffers     cached
    Mem:          3096       1161       1934          0          0          0
    -/+ buffers/cache:       1161       1934
    Swap:            0          0          0
    
    Code:
    root@server [/]# uname -a
    Linux server.host.org 2.6.18-308.8.2.el5.028stab101.1 #1 SMP Sun Jun 24 20:25:35 MSD 2012 i686 i686 i386 GNU/Linux
    root@server [/]#
    Code:
    root@server [/]# cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 0
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.80
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 2
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.44
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 2
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 4
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.55
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 3
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 6
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.36
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 4
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 1
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.52
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 5
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 3
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.47
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 6
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 5
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.33
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 7
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 42
    model name      :           Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    stepping        : 7
    cpu MHz         : 1596.452
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 7
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 13
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
    bogomips        : 6385.52
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    root@server [/]#
    
    Code:
    [mysqld]
    default-storage-engine=MyISAM
    max_allowed_packet=500M
    innodb_file_per_table=1
    local-infile=0
    
    Code:
    root@server [/]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.27-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 12527)
    [--] Data in InnoDB tables: 72M (Tables: 1472)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 252K (Tables: 80)
    [!!] Total fragmented tables: 486
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 16m 21s (20K q [21.142 qps], 499 conn, TX: 232M, RX: 3M)
    [--] Reads / Writes: 87% / 13%
    [--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 573.8M (18% of installed RAM)
    [OK] Slow queries: 0% (4/20K)
    [OK] Highest usage of available connections: 8% (13/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/562.5M
    [OK] Key buffer hit rate: 99.1% (313K cached / 2K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
    [!!] Joins performed without indexes: 32
    [!!] Temporary tables created on disk: 33% (384 on disk / 1K total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 49% (400 open / 815 opened)
    [OK] Open file limit used: 17% (700/4K)
    [OK] Table locks acquired immediately: 99% (23K immediate / 23K locks)
    [OK] InnoDB data size / buffer pool: 72.8M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        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
        Set thread_cache_size to 4 as a starting value
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
    
    root@server [/]#
    
    Thanks
     
    #1 ccalby, Nov 1, 2012
    Last edited: Nov 1, 2012
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    change max_allowed_packet=500M to max_allowed_packet=32M

    and add in the end of my.cnf:

    Code:
    skip-name-resolve
    myisam_use_mmap=1
    
    key_buffer_size = 650M
    max_connections = 100
    
    join_buffer_size = 1M
    read_buffer_size = 128K
    sort_buffer_size = 256K
    read_rnd_buffer_size = 1M
    
    query_cache_type = 1
    query_cache_size = 16M
    query_cache_limit = 2M
    
    max_heap_table_size = 32M
    tmp_table_size = 32M
    
    thread_cache_size = 128
    table_definition_cache = 2000
    table_cache = 10000
    
    concurrent_insert = 2
    max_write_lock_count = 5
    
    long_query_time=0.3
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    
    innodb_buffer_pool_size = 128M
    innodb_additional_mem_pool_size = 10M
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 8
    innodb_flush_method = O_DIRECT
    innodb_old_blocks_time=1000	
    
    remember to remove ib_logfile0 and ib_logfile1 from mysql dir before restarting

    settings are for mysql > 5.1
     
Loading...

Share This Page