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.

Help me to optimize my.cnf

Discussion in 'Workarounds and Optimization' started by cbogdan, Jun 29, 2011.

  1. cbogdan

    cbogdan Member

    Joined:
    Jun 29, 2011
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    I have a VPS with 1.5G RAM.
    I use mysqltunner, this is the output:

    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 150M (Tables: 228)
    [!!] Total fragmented tables: 13
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1m 59s (2K q [17.151 qps], 106 conn, TX: 11M, RX: 254K)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 362.0M global + 5.4M per thread (150 max threads)
    [OK] Maximum possible memory usage: 1.2G (78% of installed RAM)
    [OK] Slow queries: 0% (0/2K)
    [OK] Highest usage of available connections: 5% (8/150)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/22.1M
    [OK] Key buffer hit rate: 96.4% (24K cached / 867 reads)
    [OK] Query cache efficiency: 45.5% (731 cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 228 sorts)
    [!!] Temporary tables created on disk: 38% (117 on disk / 301 total)
    [OK] Thread cache hit rate: 92% (8 created / 106 connections)
    [OK] Table cache hit rate: 91% (62 open / 68 opened)
    [OK] Open file limit used: 1% (116/8K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
    
    I know about 24 hours, but the problem is with temporary tables, and I cant figure out why from two days ago its start to show this increases of tmp_table_size and max_heap_table_size. Everything its happening after I enable eAccelerator.

    The actual my.cnf is:

    Code:
    [mysqld]
    log-slow-queries=/var/lib/mysql/slow.log
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    long_query_time=5
    max_connections=150
    max_connect_errors=10
    max_allowed_packet=32M
    interactive_timeout=30
    wait_timeout=100
    query_cache_size=160M
    query_cache_limit=64M
    connect_timeout=10
    table_cache=4000
    thread_cache_size=96
    tmp_table_size=64M
    max_heap_table_size=64M
    join_buffer_size=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    key_buffer_size=128M
    thread_concurrency=3
    
    [mysqld_safe]
    open_files_limit=8192
    
    [mysql.server]
    user=mysql
    
    [mysqldump]
    quick
    max_allowed_packet=96M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=32M
    write_buffer=32M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=32M
    write_buffer=32M
    
    [client]
    socket=/var/lib/mysql/mysql.sock
    port=5511
    
    And the apache configuration from /usr/local/apache/conf/httpd.conf>

    Code:
    Timeout 300
    TraceEnable Off
    ServerSignature Off
    ServerTokens Full
    FileETag All
    StartServers 8
    <IfModule prefork.c>
    MinSpareServers 5
    MaxSpareServers 20
    </IfModule>
    ServerLimit 256
    MaxClients 256
    MaxRequestsPerChild 2000
    KeepAlive On
    KeepAliveTimeout 2
    MaxKeepAliveRequests 200
    
    I`m not so expert but unfortunable, I manage the VPS by my own. Please help me with sugestions for, "why the server reboot for 5-10 times per day, every time after a high load avarange, but with no important errors in error_log or other logs".

    Thanq you!

    Later edit: I have 3 websites on the vps, one of them with an everyday trafic of more than 7000, and the rests under 1000.
     
    #1 cbogdan, Jun 29, 2011
    Last edited: Jun 29, 2011
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    How do you know the issue is MySQL specifically? What has led you to that conclusion?

    Next, have you checked the /var/lib/mysql/slow.log for slow queries by databases? Have you checked WHM > Daily Process Logs to see if anything stands out there for any specific account and/or page of that account with top processes?

    The very best tool available is top which can be used in root SSH when the server load is high to watch for 5-10 minutes to see the highest using processes on the machine when the load is elevated.
     
  3. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You could try installing munin, this might help you track down the exact problem.

    And yeah Top is a great tool I use it a lot, you should monitor TOP screen and see what process is causing the load first.
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Munin can be server intensive. I would not personally recommend installing it onto a machine that has any load issues, since it will only exacerbate the situation in my opinion.
     
Loading...

Share This Page