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 Optimize Request

Discussion in 'Workarounds and Optimization' started by trackpads, Oct 31, 2012.

  1. trackpads

    trackpads Active Member

    Joined:
    Jul 5, 2004
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    Hi,

    New server and reopened my forums, vbulletin. Has a joomla site and a Photo Gallery. All large. I do all of this for free so I have no money to go mega if you know what I mean.

    Any help is appreciated,

    Mysqltuner:

    Code:
    root@caesar [~]# /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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.65-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 619M (Tables: 169)
    [--] Data in InnoDB tables: 20G (Tables: 402)
    [--] Data in MEMORY tables: 756K (Tables: 5)
    [!!] Total fragmented tables: 52
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 8h 48m 0s (1M q [11.697 qps], 38K conn, TX: 22B, RX: 254M)
    [--] Reads / Writes: 39% / 61%
    [--] Total buffers: 1.3G global + 1.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.9G (12% of installed RAM)
    [OK] Slow queries: 1% (13K/1M)
    [OK] Highest usage of available connections: 7% (39/500)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/551.7M
    [OK] Key buffer hit rate: 100.0% (17M cached / 7K reads)
    [OK] Query cache efficiency: 55.8% (396K cached / 709K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 6% (3K temp sorts / 48K sorts)
    [!!] Joins performed without indexes: 4951
    [OK] Temporary tables created on disk: 15% (17K on disk / 117K total)
    [OK] Thread cache hit rate: 99% (39 created / 38K connections)
    [OK] Table cache hit rate: 90% (975 open / 1K opened)
    [OK] Open file limit used: 2% (465/16K)
    [OK] Table locks acquired immediately: 99% (859K immediate / 859K locks)
    [!!] InnoDB data size / buffer pool: 20.1G/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 256.0K, or always use indexes with joins)
        innodb_buffer_pool_size (>= 20G)
    
    mysql -e 'show databases'

    Code:
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | cphulkd            |
    | eximstats          |
    | horde              |
    | leechprotect       |
    | logaholicDB_caesar |
    | members_prime      |
    | mysql              |
    | newspad_prime      |
    | roundcube          |
    +--------------------+
    
    
    my.cnf

    Code:
    [mysqld]
    bind-address=127.0.0.1
    back_log = 100
    #skip-innodb
    #innodb=OFF
    max_connections = 500
    key_buffer_size = 1024M
    myisam_sort_buffer_size = 256M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    table_definition_cache = 8000
    table_open_cache = 8000
    thread_cache_size = 256
    wait_timeout = 120
    interactive_timeout = 60
    connect_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    max_seeks_for_key = 1000
    group_concat_max_len = 1024
    max_length_for_sort_data = 1024
    net_buffer_length = 16384
    max_connect_errors = 100000
    concurrent_insert = 2
    read_rnd_buffer_size = 256K
    bulk_insert_buffer_size = 8M
    query_cache_limit = 1M
    query_cache_size = 256M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    range_alloc_block_size = 4096
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    max_write_lock_count = 4
    
    # mysql 5.5 specific
    #innodb_buffer_pool_instances=1
    #innodb_file_per_table = 1
    #innodb_open_files = 1000
    #innodb_data_file_path= ibdata1:10M:autoextend
    #innodb_buffer_pool_size = 64M
    #innodb_additional_mem_pool_size = 32M
    
    #innodb_log_files_in_group = 2
    #innodb_log_file_size = 5M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 2
    #innodb_thread_concurrency = 0
    #innodb_lock_wait_timeout=50
    #innodb_support_xa=0
    #innodb_doublewrite=0
    
    #innodb_io_capacity = 200
    #innodb_read_io_threads = 2
    #innodb_write_io_threads = 2
    
    long_query_time=2
    slow_query_log=0
    slow_query_log_file=/var/log/mysql/slowq.log
    
    [mysqld_safe]
    nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 64M
    
    [myisamchk]
    key_buffer_size = 1024M
    sort_buffer_size = 1M
    read_buffer_size = 1M
    write_buffer_size = 1M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld]
    innodb_file_per_table=1
    
    
    

    /var/log/mysql/slowq.log is empty.

    cat /proc/cpuinfo

    Code:
    cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6600.46
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6599.95
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6600.24
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6600.12
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6600.01
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6599.95
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6599.98
    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           : 58
    model name      :       Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    stepping        : 9
    cpu MHz         : 1600.000
    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        : 6600.06
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    top

    Code:
    top - 22:20:35 up 1 day,  9:00,  1 user,  load average: 4.07, 3.40, 3.11
    Tasks: 260 total,   1 running, 259 sleeping,   0 stopped,   0 zombie
    Cpu(s): 27.7%us,  5.3%sy,  0.0%ni, 55.4%id, 11.4%wa,  0.0%hi,  0.1%si,  0.0%st
    Mem:  16415804k total, 16301576k used,   114228k free,    42524k buffers
    Swap: 10190840k total,      164k used, 10190676k free, 14682176k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     3810 mysql     10  -5 1719m 433m 4932 S 261.0  2.7   5800:14 mysqld
      845 root      10  -5     0    0    0 S  0.3  0.0   0:13.95 kjournald
     3148 root      11  -4 27364  808  528 S  0.3  0.0   0:10.89 auditd
    21465 root      15   0 12888 1228  816 R  0.3  0.0   0:00.02 top
        1 root      15   0 10364  644  548 S  0.0  0.0   0:02.01 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:09.23 migration/0
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.32 ksoftirqd/0
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
        5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.82 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:01.14 ksoftirqd/1
        7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
        8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.11 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:03.37 ksoftirqd/2
       10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
       11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.13 migration/3
       12 root      34  19     0    0    0 S  0.0  0.0   0:13.57 ksoftirqd/3
       13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
       14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.13 migration/4
       15 root      34  19     0    0    0 S  0.0  0.0   0:00.58 ksoftirqd/4
       16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
       17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.15 migration/5
       18 root      34  19     0    0    0 S  0.0  0.0   0:00.59 ksoftirqd/5
       19 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/5
       20 root      RT  -5     0    0    0 S  0.0  0.0   0:00.20 migration/6
       21 root      34  19     0    0    0 S  0.0  0.0   0:01.31 ksoftirqd/6
       22 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/6
       23 root      RT  -5     0    0    0 S  0.0  0.0   0:00.25 migration/7
       24 root      34  19     0    0    0 S  0.0  0.0   0:03.95 ksoftirqd/7
       25 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/7
       26 root      10  -5     0    0    0 S  0.0  0.0   0:00.16 events/0
       27 root      10  -5     0    0    0 S  0.0  0.0   0:00.04 events/1
       28 root      10  -5     0    0    0 S  0.0  0.0   0:00.03 events/2
       29 root      10  -5     0    0    0 S  0.0  0.0   0:00.02 events/3
    








    Thanks again,

    -Jason
     
  2. thinkbot

    thinkbot Well-Known Member

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

    Code:
    [mysqld]
    
    bind-address=127.0.0.1
    
    myisam_use_mmap=1
    skip-name-resolve
    
    max_connections = 500
    key_buffer_size = 1024M
    myisam_sort_buffer_size = 256M
    myisam_max_sort_file_size = 2048M
    
    join_buffer_size = 8M
    read_buffer_size = 128K
    sort_buffer_size = 1M
    read_rnd_buffer_size = 8M
    thread_stack = 256K	
    
    table_definition_cache = 1000
    table_open_cache = 5000
    thread_cache_size = 256
    wait_timeout = 120
    interactive_timeout = 60
    connect_timeout = 2
    max_connect_errors = 100
    
    tmp_table_size = 128M
    max_heap_table_size = 128M
    
    max_allowed_packet = 16M
    
    concurrent_insert = 2
    
    bulk_insert_buffer_size = 8M
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    
    query_prealloc_size = 128K
    query_alloc_block_size = 256K
    preload_buffer_size=512K
    
    default-storage-engine = MyISAM
    max_write_lock_count = 4
    
    long_query_time=0.3
    slow_query_log=1
    slow_query_log_file=/var/log/mysql/slowq.log
    #log-queries-not-using-indexes
    
    
    # InnoDB Specific
    innodb_buffer_pool_size = 12G			
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 16M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 8
    innodb_flush_method = O_DIRECT
    innodb_file_per_table = 1	
    #innodb_adaptive_flushing = TRUE
    innodb_doublewrite = 0
    innodb_old_blocks_time=1000	
    
    innodb_stats_on_metadata=0
    
    
    [mysqld_safe]
    nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 64M
    
    [myisamchk]
    key_buffer_size = 1024M
    sort_buffer_size = 1M
    read_buffer_size = 1M
    write_buffer_size = 1M
    
    [mysqlhotcopy]
    interactive-timeout
    
    remember to remove ib_logfile0 and ib_logfile1 from mysql dir before restarting

    and run
    service cpuspeed stop
    yum remove cpuspeed
     
  3. trackpads

    trackpads Active Member

    Joined:
    Jul 5, 2004
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    Hi,

    I tried restarting mysql but it failed and gave me this:

    Starting MySQL...... ERROR! Manager of pid-file quit without updating file.

    Thanks again,

    -Jason
     
  4. trackpads

    trackpads Active Member

    Joined:
    Jul 5, 2004
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    Found it, had to disable this line: innodb_old_blocks_time=1000

    - - - Updated - - -

    ok, something horrible happened when I restarted, all the innodb tables were corrupt. Reset to old my.cnf and all is fine.
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    if so, you probably had this innodb_file_per_table = 1, as 0

    so uncomment this
    innodb_file_per_table = 1

    make it
    #innodb_file_per_table = 1

    and restart again
     
  6. trackpads

    trackpads Active Member

    Joined:
    Jul 5, 2004
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    No, I used your my.cnf exclusively except for that blocks line. When it restart the innodb tables were all showing as 'in use' in phpmyadmin.

    Thanks again,

    -Jason
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I mean, you had

    [mysqld]
    innodb_file_per_table=1
    but [mysqld] was repeated twice, so it probably wasn't considered I guess

    so its like you had innodb_file_per_table=0

    so I suggest to comment this line in my config like this
    #innodb_file_per_table = 1

    and try again

    and let us know the last lines from mysql error log

    tail -n 100 path_to_error.log

    probably
    tail -n 100 /var/lib/mysql/*.err
     
Loading...

Share This Page