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

Discussion in 'Workarounds and Optimization' started by jazz1611, Oct 25, 2013.

  1. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    I have Dedicated Server with E3-1230v2, 8GB RAM, 2TB HDD SATA2 (RAID 0) for Shared Hosting. I have fewer large database ex: 200Mb - 500Mb and mysql used 43% of memory

    /http://i.imgur.com/iP84nHs.png

    Code:
    top - 22:27:47 up 4 days, 20:58,  1 user,  load average: 3.94, 5.30, 5.06
    Tasks: 256 total,   1 running, 255 sleeping,   0 stopped,   0 zombie
    Cpu(s):  0.9%us,  1.9%sy, 13.1%ni, 76.8%id,  7.2%wa,  0.0%hi,  0.2%si,  0.0%st
    Mem:   8031044k total,  7318368k used,   712676k free,   112324k buffers
    Swap:        0k total,        0k used,        0k free,  2616332k cached
    There is result of mysqltuner.pl

    Code:
    root@serv989 [~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 2429)
    [--] Data in InnoDB tables: 1G (Tables: 2119)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 31M (Tables: 344)
    [!!] Total fragmented tables: 264
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 20h 50m 54s (140M q [333.208 qps], 2M conn, TX: 886B, RX: 21B)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 962.0M global + 3.5M per thread (300 max threads)
    [OK] Maximum possible memory usage: 2.0G (25% of installed RAM)
    [OK] Slow queries: 2% (3M/140M)
    [OK] Highest usage of available connections: 22% (68/300)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/259.6M
    [OK] Key buffer hit rate: 100.0% (1B cached / 159K reads)
    [OK] Query cache efficiency: 82.2% (100M cached / 122M selects)
    [!!] Query cache prunes per day: 2457854
    [OK] Sorts requiring temporary tables: 0% (10K temp sorts / 5M sorts)
    [!!] Joins performed without indexes: 23267
    [OK] Temporary tables created on disk: 19% (1M on disk / 7M total)
    [OK] Thread cache hit rate: 99% (68 created / 2M connections)
    [!!] Table cache hit rate: 3% (5K open / 144K opened)
    [OK] Open file limit used: 44% (4K/10K)
    [OK] Table locks acquired immediately: 99% (38M immediate / 38M locks)
    [!!] InnoDB data size / buffer pool: 1.0G/256.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 50M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 5000)
        innodb_buffer_pool_size (>= 1G)
    There is config of my.cnf

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    local-infile=0
    
    port=3306
    socket=/var/lib/mysql/mysql.sock
    
    max_connections=300
    max_user_connections=30
    max_connect_errors=15
    key_buffer=512M
    myisam_sort_buffer_size=64M
    join_buffer_size=1M
    read_buffer_size=1M
    sort_buffer_size=1M
    table_cache=10000
    thread_cache_size=30M
    wait_timeout=300
    connect_timeout=10
    max_allowed_packet=16M
    query_cache_limit=1M
    query_cache_size=50M
    query_cache_type=1
    tmp_table_size=128M
    max_heap_table_size=128M
    innodb_buffer_pool_size=256M
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    delayed_insert_timeout=1
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    Can you help me optimize mysql use low resource (ram, cpu) but performance still remains? Thank you.
     
    #1 jazz1611, Oct 25, 2013
    Last edited: Oct 26, 2013
  2. 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 :)

    I just want to point out that memory usage is not always a bad thing with Linux. There is a thread on this at:

    Memory Usage Higher Than Expected

    That being said, there are likely optimizations that can be made to your MySQL configuration. I will leave this thread open to allow for user feedback to your MySQL tuner results.

    Thank you.
     
  3. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    I have problem with mysql. When i'm trying Optimize with 2 database, got same error. Please look at below with database have 3Mb and 400Mb

    /http://i.imgur.com/2C4ohPH.png
    /http://i.imgur.com/2BYS7je.png
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Based on that
    table_cache (> 5000)
    table_cache=10000


    [!!] InnoDB data size / buffer pool: 1.0G/256.0M
    innodb_buffer_pool_size (>= 1G)


    It seems like you got different my.cnf, and didn't restart mysql with that config



    Anyways correct those:

    read_buffer_size=128K
    sort_buffer_size=256K
    thread_cache_size=50
    query_cache_size=100M
    innodb_buffer_pool_size=1300M


    This will increase RAM usage, since you need bigger innodb buffer, but will increase a speed a bit
    You got
    [OK] Maximum possible memory usage: 2.0G (25% of installed RAM)

    So still, not much
     
  5. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I am still getting 500 error. I dont know what that? i got it when server load avarage: 0.5+ not more.
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    its error from apache/php
    which handler you use ? mod_php, fastcgi ?
    you should check apache/php error logs
     
  7. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    It from phpmyadmin when i trying optimize database. I use Litespeed with handler suPHP.
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    so check litespeed error log
     
  9. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Error in Processing Request
    Error code: 500
    Error text: Internal Error

    I dont found anything error on error log. Why show that on phpmyadmin when i trying optimize database?

    ---------- Updated ----------

    I checked and see optimize database with MyISAM is normal and fine. If with InnoDB is show error like top. Although small database InnoDB still error. Not need larger
     
    #9 jazz1611, Oct 27, 2013
    Last edited: Oct 27, 2013
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    enable php display_errors and display_startup_errors,
    if you got 500 error you should have entry in logs anyways
     
  11. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I'm facing I/O high load. Look at picture

    /http://up9x.net/X

    Code:
    root@serv989 [~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 2483)
    [--] Data in InnoDB tables: 1G (Tables: 2116)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 349)
    [!!] Total fragmented tables: 285
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 54m 31s (1M q [364.504 qps], 20K conn, TX: 6B, RX: 166M)
    [--] Reads / Writes: 72% / 28%
    [--] Total buffers: 2.0G global + 1.9M per thread (300 max threads)
    [OK] Maximum possible memory usage: 2.6G (33% of installed RAM)
    [OK] Slow queries: 0% (7/1M)
    [OK] Highest usage of available connections: 13% (40/300)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/520.3M
    [OK] Key buffer hit rate: 99.2% (5M cached / 43K reads)
    [OK] Query cache efficiency: 86.2% (908K cached / 1M selects)
    [!!] Query cache prunes per day: 852747
    [OK] Sorts requiring temporary tables: 0% (72 temp sorts / 24K sorts)
    [!!] Joins performed without indexes: 12
    [OK] Temporary tables created on disk: 19% (5K on disk / 27K total)
    [OK] Thread cache hit rate: 99% (40 created / 20K connections)
    [OK] Table cache hit rate: 98% (1K open / 1K opened)
    [OK] Open file limit used: 4% (1K/24K)
    [OK] Table locks acquired immediately: 99% (268K immediate / 268K locks)
    [OK] InnoDB data size / buffer pool: 1.1G/1.3G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        query_cache_size (> 100M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
    there is current my.cnf

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    local-infile=0
    
    port=3306
    socket=/var/lib/mysql/mysql.sock
    
    max_connections=300
    max_user_connections=30
    max_connect_errors=15
    key_buffer=512M
    myisam_sort_buffer_size=64M
    join_buffer_size=1M
    read_buffer_size=128K
    sort_buffer_size=256K
    table_cache=10000
    thread_cache_size=50
    wait_timeout=300
    connect_timeout=30
    max_allowed_packet=16M
    query_cache_limit=1M
    query_cache_size=100M
    query_cache_type=1
    tmp_table_size=128M
    max_heap_table_size=128M
    innodb_buffer_pool_size=1300M
    slow_query_log=1
    delayed_insert_timeout=1
    
    open_files_limit=24440
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
     
Loading...

Share This Page